Getting rid of the duplicates is the fun part. Here's a general game plan on 1 way to do it:
1. Change the query Missinglinq mentioned above to a make-table query and make a table (call it for example: DupRecs)
2. If you have a lot of linked tables to your main table with duplicates, this will make it more difficult, if not, then proceed....
3. Change the query mentioned above to a delete query and delete the records.
4. Using your new DupRecs table, create a query with this table and change the query properties to unique values.
5. Append the unique records back into your main table (I can't remember though if you need to create another table first or you can just do an append query (on unique records in conjunction with the Find Duplicates query) back to your main table - I think you can set it to unique records and also append back to your main table). Otherwise, if you have to, do a make-table and make another table of unique records and then simply append that table back to your main table.
If you had a lot of linked tables to your main table with duplicates, you're in for some fun and don't do steps 3-5 above yet. For example Mary Smith might be duplicated with a primary key (or autonumber) of 35 and 40. The question is (in the other relational tables), which number do you keep. Some people may have entered some information under Mary Smith's id of 35 and others may have entered information under the id of 40.
If this is the case, let us know and I'll go further into explanation of some ways you can handle this - it can get pretty tough if it's not done right.
Last edited by pkstormy; 12-19-06 at 14:10.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)