By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Vba delete duplicated value in table

P: 20
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

Attached Images
File Type: jpg dup.jpg (73.9 KB, 172 views)
4 Weeks Ago #1

✓ answered by 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.

Share this Question
Share on Google+
10 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,158
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 programmatically prevent duplicates from being created. That is a question for another thread, though.
4 Weeks Ago #2

P: 20
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
4 Weeks Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,158
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.
4 Weeks Ago #4

P: 20
thanks again for your advice, I'll try to do your way
4 Weeks Ago #5

zmbd
Expert Mod 5K+
P: 5,331
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
3 Weeks Ago #6

P: 20
Thanks so much for carrying about my issue, actually I have no VBA script!
1 Week Ago #7

P: 1
  • Remove Duplicates Using Row_Number.
  • Remove Duplicates using self Join.
  • Remove Duplicates using group By
    Expand|Select|Wrap|Line Numbers
    1. SELECT FirstName, LastName, MobileNo, COUNT(*) as CNT FROM CUSTOMER GROUP BY FirstName, LastName, MobileNo; HAVING COUNT(*) = 1
    .
1 Week Ago #8

NeoPa
Expert Mod 15k+
P: 31,308
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 :-(
1 Week Ago #9

twinnyfo
Expert Mod 2.5K+
P: 3,158
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....
1 Week Ago #10

zmbd
Expert Mod 5K+
P: 5,331
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



Open Access
Ribbon>External Data>Import & Link>Excel
then select the range name to import



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.
Attached Images
File Type: jpg Excel972533.jpg (39.3 KB, 51 views)
File Type: jpg access972533.JPG (34.7 KB, 52 views)
1 Week Ago #11

Post your reply

Sign in to post your reply or Sign up for a free account.