I have a data set of about 20000 records of which around 1800 records are duplicates(repe at themselves). I would like to delete the duplicates through comparing the records following each other ie.compare the record in the 1st row if its the same with the one in the 2nd row; if its true then i assign one the records true and the other record false; so that at the end of the day i delete all the false records at once. My database is in ms access and that is where i want to do this. I have tried to do it in excel by using if command eg IF(C2=C3,BJ3=0, BJ3=1) but excel has a tendency of changing my uniquekey field from data type autonumber to number yet my database is linked to other tables.
-
-
Moved to AccessOriginally posted by jssenge -
Crank up Access' Help and enter the phrase
Automatically delete duplicate records from a table
Then click on the same statement in the choices that pops up! Help will explain exactly how to do this! As with any major move, backup your database first!
Good Luck!
Linq ;0)>Comment
-
Hi there, I only joined this site about 2 mins ago so your my first point of contact. I think I can help you.
1) Open a query in Access
2) Only bring in the fields that make the record a duplicate, except for the UniqueID for each record (bring this is twice)
3) click the totals button in the toolbar (looks like a backwards E)
4) The first UniqueID, select First from the grouping options.
5) The second UniqueID, select Count from the grouping options.
Run the query, this should show a count of all records. All records that are duplicate will have a count greater than 1.
Turn this into a Delete query and set the criteria on the count field >=2.
That should do it.Comment
Comment