deduplication

  • Thread starter Thread starter Richard Pitman
  • Start date Start date
R

Richard Pitman

I have a table in the following format:

field1 field2 field3 field4
1 22/4/2005 c 32
1 24/4/2005 a 32
1 30/4/2005 d 32
2 13/7/1998 f 12
2 5/7/1998 a 12
3 4/6/2005 a 56

I need to deduplicate field1 by selecting, for each value of field1, the
record containing the earliest date in field2.

Can anyone suggest how I might do this in access?

Many thanks

Richard
 
Richard,
I'd use a MakeTable query combined with View/Totals to find only the
latest record for each Field1 value.
Group on Field1, and Max on Field2 should do it.
Always keep a backup of your original table!
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
The following should return the rows you want to keep:

SELECT *
FROM YourTable AsT1
WHERE field2 =
(SELECT MIN(field2)
FROM YourTable AS T2
WHERE T2.field1 = T1.field1);

If you want to permanently delete the redundant rows this should do it:

DELETE *
FROM YourTable AsT1
WHERE field2 >
(SELECT MIN(field2)
FROM YourTable AS T2
WHERE T2.field1 = T1.field1);
 
Back
Top