Vba delete duplicated value in table

[adamdaban]

Dear All,
I need your help to solve my problem to remove the duplicated value in my table by using VBA if you check my image here is some times I may add again record but once I click on the button will remove duplicate data.
thanks for help

[IMGnothumb]https://bytes.com/attachment.php? attachmentid=99 94[/IMGnothumb]

[twinnyfo]

You can create a "Find Duplicates" query--which is built into the Access system. Go to Create > Query Wizard > Find Duplicates Query Wizard.

You will have to manually delete any duplicates, as Access cannot "delete a duplicate" record, because it does not know which one is a duplicate and which one is the original.

However, as good advice, you may want to address how you are adding records to your table, so that you can programmaticall y prevent duplicates from being created. That is a question for another thread, though.

[adamdaban]

thanks for your advice, but I have one problem is: I upload excel file into my DB some times I forget to upload again, so the best way is when I upload excel file then after append data into my table vb will delete duplicated data, hope someone can help me to solve my problem

[twinnyfo]

Again, VBA won't delete "duplicate" data because it doesn't know which is a duplicate and which is the original. You risk deleting both. However, based upon what you explain, you would have to prevent your upload from adding duplicate data in the first place.

You could only approach this by importing the data line-by-line and comparing. But, again, that discussion would be more suitable for a different thread.

[adamdaban]

thanks again for your advice, I'll try to do your way

[zmbd]

adamdaban,
Does your imported data have a primary key?
I import a lot of data from various sources and these are usually excel files or text files where the record has a unique key which makes it quite easy to work with as one only need to search the record set for this value.

Would you mind posting the VBA-Script you are using for the import? Please remember to format the script using the [CODE/] formatting tool.


I have a few other ideas such as using a temp table (I usually avoid these due to bloat) or using a few other post import methods; however, a tad more information would be helpful...
-Z

[adamdaban]

Thanks so much for carrying about my issue, actually I have no VBA script!

[shivkumar1]

• Remove Duplicates Using Row_Number.
• Remove Duplicates using self Join.
• Remove Duplicates using group By

  SELECT FirstName, LastName, MobileNo, COUNT(*) as CNT FROM CUSTOMER GROUP BY FirstName, LastName, MobileNo; HAVING COUNT(*) = 1

  .

[NeoPa]

Not sure what this is for. It's not a DELETE query at all, and it's not even an updatable SELECT query that might be converted to a DELETE one.

Confused :-(

[twinnyfo]

Yeah, I almost deleted this post myself as it did not seem relevant--not to mention a couple other spurious posts by the same user....

[zmbd]

I read this at 5:30am local and thought maybe I wasn't awake yet... so that's a third person that didn't follow the post... I almost deleted it then.

Post also basically restates the "find duplicate wizard" suggested buy TwinnyFo.

I do have a kludge work around for OP as for the import that is causing the heart burn...

Ideally, OP would create a unique key in the excel table, not easy, but best practice...

However, I suspect that adamdaban is using
Ribbon>External Data>Import & Link>Excel

Before import prepare is dataset in Excel
Open the Excel workbook of interest
Select the range of data to import
Name the range in the excel workbook, save, close
[IMGnothumb]https://bytes.com/attachment.php? attachmentid=10 013[/IMGnothumb]

Open Access
Ribbon>External Data>Import & Link>Excel
then select the range name to import
[IMGnothumb]https://bytes.com/attachment.php? attachmentid=10 014[/IMGnothumb]

If adamdaban names each range by date then OP will have a good idea what data has already been imported.

Another option is to simply link to the Excel workbook instead of importing the data. Can be a bit of a kludge for the SQL; however, one can write SQL against the linked table. I do this for a database where I'm using Excel as the data entry form (yes, I know...) because they want a non-normalized format that I cannot easily create with a form.