Access Duplicates Question

[tdpman]

I have a little Access issue I'm hoping that someone here could help me with. You guys have helped me out so much in the past and it has been most appreciated. I have two scenarios that are very similar, but one is slightly different.

Scenario #1: I have a Table named SO COMBINED DATA that has a field named "ORDER NUMBER" that contains duplicates. I need to delete all but one of the duplicates in this table. I do not care which one is left. I have tried to do a UNIQUE records query on this table to no avial. Not all of the fields in the record are duplicates, only the "ORDER NUMBER" field. Does any one have any code or ideas that would accomplish leaving only one of the duplicate records?

Scenario #2: Very similar to scenario #1. I have a table called ASR COMBINED TABLE that has a field named "ASR NUMBER" and one called "ASR VERSION". The "ASR NUMBER" field contains duplicates. But the catch is that now I want to delete all but one of the duplicates and leave the one with the Highest value in the "ASR VERSION" field. Keep in mind that the "ASR VERSION" field may (not always) also contain duplicates. Any ideas?

I currently have a macro written into Excel that will accomplish both of these tasks, but takes entirely too long. I am really needing to speed up the process.

Thanks in advance for your help!

 

[vbajock]

Problem #1 - I usually solve that one by creating a temporary table that has a primary key assigned, allowing no dups. I then set up a query which inserts records from the source table containing the dups into the keyed table. The keyed table will reject all but one record.

Next I delete all the dups from the source table. Finally add back the single record from the primary keyed table.

Problem #2, You would just do an insert query, in which the source table would be a summary query, with Max selected on the field that you want the max of. This query would only return one record.

 

[zevw]

What I do in these situations is as follows, maybe someone has an easier softer way:

I go to Queries, Click on New, Click on Find Duplicates Query Wizard, Follow the Wizard. Once the Wizard created the Query (This is a Select Query), I convert it to a Make Table Query and Save all my duplicates in a new Table. I then Convert the same query To a Delete Query and delete all Duplicates.

Then I run an Append Query with a Select Distinct from my new created table with all the duplicates based on a certain criteria and append only one record into the table.

I hope this is clear.

 

[tdpman]

vbajock,

Question for you on scenario #2: Would this still only return one record if not all of the fields were duplicates? I think I've done what you have said, but I'm still getting some duplicates if not all of the fields are identical.

Any ideas?

 

[vbajock]

Well, logically the maximum value of a series of numbers is a single value. I don't know how a max query could return more than one record. Are you sure your using a summary query?