Moving duplicate records to a separate table.

[Brad Granath]

I have a table that stores [Unit ID] and [Inspection ID]. The table should
only have one instance of each [Unit ID]. The [Inspection ID] field is an
autonumber field. For every duplicate I would like to remove the older record
(the one having the smaller [Inspection ID]. This is a series of two
queries. The first copies the records, and the second deletes them from the
original table. I have written the first query as follows:

SELECT [Unit ID], INTO Inspections_Archive
FROM Inspections
WHERE ((Inspections.[Unit ID]) In (SELECT [Unit ID] FROM [Inspections] As
Tmp));

This, however, copies ALL duplicate records, rather than only the one record
with the smaller [Inspection ID]. How do I add this criterion to my WHERE
clause?

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

THe problem here is that

SELECT * INTO Inspections_Archive
FROM Inspections
WHERE Inspections.[Inspection ID] In (SELECT Min([Inspection ID]
FROM [Inspections] GROUP BY [Unit ID]
HAVING COUNT(*) > 1)


DELETE
FROM Inspections
WHERE Inspections.[Inspection ID] In
(SELECT Min([Inspection ID]
FROM [Inspections]
GROUP BY [Unit ID]
HAVING Count(*) > 1)

This will only take out one duplicate from a group. SO, if you have 4
duplicates Unit Id records you would need to run the above queries 3 times
sequentially to get rid of all the duplicates.

IF your table is fairly small, you might try

SELECT * INTO Inspections_Archive
FROM Inspections
WHERE Inspections.[Inspection ID] NOT In
(SELECT Max([Inspection ID]
FROM [Inspections]
GROUP BY [Unit ID])


DELETE
FROM Inspections
WHERE Inspections.[Inspection ID] NOT In
(SELECT Max([Inspection ID]
FROM [Inspections]
GROUP BY [Unit ID])

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Brad Granath]

SELECT * INTO Inspections_Archive
FROM Inspections
WHERE Inspections.[Inspection ID] NOT In
(SELECT Max([Inspection ID]
FROM [Inspections]
GROUP BY [Unit ID])

You forgot a ")" but it does the trick. Of course I'm going to make sure
that the SELECT INTO query is working properly before I go testing the DELETE
one. Thanks so much for the help!