[Information] Duplicates - A possible answer

[Ecniv]

Hi,

Came across this yesterday as my wife is doing some data processing and wanted to get rid of duplicates, yet keep the first instance. I never knew (or conveniantly forgot) about this function and I usually code up a loop to do the checks for me.

However, this may be slightly faster.
I'm going to write it in three steps although it may be accomplishes in two...

Example Data
ID - autonumber
Data - text

1 aaab
2 aabb
3 aaab
4 abba

Step one.
Make a query to get the data from your table. This should be something like:

SELECT tblData.Data,FIRST(tblData.ID) as FirstID
FROM tblData G
GROUP BY tblData.Data

Save this query (run it to check that only ids 1,2 and 4 are returned)

Step two.
Make a table to hold these matched IDs (tblMatched - ID - Number-long)
Make an append query using the above query to put the Ids into the tblMatched - Save this too.
** Edit:
Something like this

INSERT INTO tblMatched ( ID )
SELECT qryFirst.ID
FROM qryFirst

Step three.
Make a third query to delete (or flag up in a field or do whatever) the duplicated records, using a left join. Something like the following:

DELETE *.tblData
FROM tblData Left Join tblMatched ON tblData.ID=tblMatched.ID
WHERE tblMatched.id is Null

Save this too.
You could use an update query if you only want to flag those record and not delete them.


To use, delete everything from tblMatched, then run the append query then run the delete (update?) query



Vince