Delete Duplcates Using Criteria

[priceman31]

I have a database that I am building and the data will be inserted a few times a day. The data set only changes when more is added. In essence most of the data will be duplicated on the next data pull.

What I would like to be able to do is delete duplicates from a table if 2 fields are duplicated and the ID of the record is higher(ID's are incremental). I would want to delete the record(s) that have the higher ID number.
I have seen some ways but they were very convoluted and I wasn't able to make them work for my needs.



Any help would be greatly appreciate

[JoeM]

I would approach it a bit differently.

I would import the new data into a "temporary" table, which is really just a blank copy of your final table.
Then, do an Unmatched query between the two tables to find the new records, and then turn that into an Append Query to just add those records to the final table.
After that, delete all records out of your temporary table, to prepare it for the next file load.

[Bulzie]

How are you importing the data into that table each time? Is there a append query? How many records are usually added each time? What fields do you check to see if it is a duplicate? Sounds like ID is the key field and unique?

I would check for dups before importing into table and only write those that are not dups. Also if the dupes were already in table, you could maybe use query with Totals/GroupBy on ID to Min (other fields GroupBy) which I think would give you the first record only of any dupes.

[priceman31]

I am importing the data from an excel sheet. Long story short source data is a mess so using excel to reformat and using Transferspreadsheet code to import into table. The # of records will vary. One of the fields would be a date field and the other an pre-assigned text string.

[JoeM]

So, have you given any thought to using the approach Bulzie and I recommended?