Help retrieving first instance of Duplicate record.

[ddean1968]

Hello and thanks for taking your time to view this.

I am hoping this is not a difficult request, merely one I have never had the need to explore yet. Difficult is even ok, impossible would be bad.

Basically I have a large list of duplicate files. I found a way to sort them in such a way that the ones that should be kept would be first in order. What I need to be able to do is Pull the first record of each duplicate group and append it into a table.

Hope that makes sense.


IE:

Test 123
Test 234
Test 456

The top Test 123 would be placed into a new table, the remainders would be left as is.

Thanks

 

[lespaul]

What makes the top one the one to be inserted? Would there be another record after Test456 that would need to be entered too?

Leslie

 

[ddean1968]

Taking all fields into consideration, there are about 20 in the actual table, I can sort it so the first one in a group is the valid record.

 

[lespaul]

So you have:

Test 123
Test 234
Test 456
Some 678
Some 789
Some 987
Thing 456
Thing 654
Thing 897

Out of this you want:
Test 123
Some 678
Thing 456

If so, then a query like this should work (if you are using a newer version of Access - if not, it's still possible, just not with a single query)

SELECT * FROM tblName INNER JOIN (SELECT Field1, Min(Field2) FROM tblName) As B On tblName.Field1 = B.Field2

Leslie