Deleting Duplicate Records

[EHittner]

Trying to learn a new trick here. I have a table littered with duplicate records. I'm only concerned with two data fields: Receipt Number and Receipt Date. I want to keep only the record of the latest Receipt Date.

How do I run a delete query and single out only the older receipt dates for deletion?

[Dal Jeanis]

First, do all testing in a junk version of your database, until you're certain the code will work as desired.
Second, try this delete query. Replace the "MyTable" with your table name, and make sure the field names in the square braces are correct.

Delete FROM MyTable AS T1
WHERE T1.[Receipt Date] <
   (SELECT MAX(T2.[Receipt Date])
   FROM MyTable AS T2 
   Where T2.[Receipt Number] = T1.[Receipt Number]);

The subselect gets the highest receipt date for each receipt number, and the outer deletes any record that is less than that.

[alansidman]

Here is a simple way to do it. Look at this video tutorial

http://www.datapigtechnologies.com/f...teproblem.html

[EHittner]

Thanks to BOTH Dal Jeanis and alansidman! I actually used both of your solutions. Dal, yours took care of a good chunk of the dups -- all except those where the dates were identical (a few hundred). Alan, yours cleaned up the rest of the job when I moved the records to a new table. You BOTH saved me a ton of work. Much, much appreciated!!

[EHittner]

Dal Jeanis,

I tried this code on a database in my work PC -- which also has MS Access 2010, but is still running XP as its OS. It wouldn't run through the query -- just stalled. Any ideas?

[Dal Jeanis]

Shouldn't be the OS. OS should be transparent to the Jet database engine. You'll have to play with a backup/test/junk copy of the database.
1) Try again, Make sure that you haven't turned off error messages
2) Check to make sure there aren't any associated records that might have to be deleted first.

[EHittner]

Hi Again Dal,

Can't understand it. The query worked great the first time. Before attempting to run in on a different table, I checked error messages and associated records. Checked and double checked table and field names - same PC, same database. Checked the data to make sure there were no erred or blank records -- or records with blank data in the applicable fields. Now, it just sits there with the "Run Query" progress bar blank at the bottom. At first, I figured since the table has over 51K of records, that it might take a while to get through. But I should at least be showing some progress in the indicator, right?

[Dal Jeanis]

Is it possible that anyone else is connected to the table and has a lock on a record, any record?

[EHittner]

Nope. It's a stand-alone app and the table I tried it on is a test copy.

[Dal Jeanis]

Then I have no idea what the holdup is.
The next method is to create a copy with only the records you want, then delete the whole table and copy it back:

INSERT INTO MyTableCopy
SELECT * FROM MyTable AS T1
WHERE T1.[Receipt Date] =
   (SELECT MAX(T2.[Receipt Date])
   FROM MyTable AS T2 
   Where T2.[Receipt Number] = T1.[Receipt Number]);

DELETE FROM MyTable;

INSERT INTO MyTable
SELECT * FROM MyTableCopy;