Getting Rid of Dupes Need Your Expertise 1

[AccessHawaii]

I just imported a bunch of data that into a table and I need to get rid of the duplicate records. I'm basing unique records on 4 separate fields. I can run a find duplicates query but all that's going to show me is which records are duplicates and I'd have to manually go into the table and remove a couple hundred records.

Does anyone know of an automated way to do this so I'm left with only 1 record of each duplicate with all the information?

What I was going to do was to first run a query to find the dupes and create a table off of it and then get rid of all the duplicate records in the main table and then build a query off the created table to show only 1 record for each duplicate so if I have 5 duplicates of the same record, I only want it to show once so I can append it back into the main table. If anyone knows how to do this, I'd appreciate it.

 

[jhall01]

One way is copy the tables structure, highlight the 4 columns in design view and make the primary keys. Then append the table with the data to the newly created table with the primary keys.

this will delete all but 1 record based on the 4 primary keys. If you don't care about any other data being deleted then this should work fine.

 

[MichaelRed]

I ususlly just write a small procedure.

Using the 'Find Dups' query, set the value of the 'key' to the first 'record'.

Do FindFirst (or equivalent) on that key.

Do Find next then Delete until the key doesn't match.

Get next Key.

Continue until last dup of last record is removed.






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[AccessHawaii]

Thanks Guys. Got it to work.