change format of "find duplicates query"

[lscheer]

When I use the "Find Duplicates" query wizard, the results are grouped and there is a number given as the COUNT of the number of duplicates. I would like to see all of the duplicate records so that I can pick which ones to delete.

Is it possible to change the wizard to that it returns all the records (somewhere in preferences or tools or something)?

If not, can anyone offer suggestions on how to get them all displayed by modifying the design of the query after the wizard builds it?

Here's my SQL if that helps:
SELECT First(tblInviteeList.ApplID) AS [ApplID Field], First(tblInviteeList.InviteeList) AS [InviteeList Field], Count(tblInviteeList.ApplID) AS NumberOfDups
FROM tblInviteeList
GROUP BY tblInviteeList.ApplID, tblInviteeList.InviteeList
HAVING (((Count(tblInviteeList.ApplID))>1) AND ((Count(tblInviteeList.InviteeList))>1));

 

[lscheer]

That still doesn't solve my problem because I'm searching for duplicates based on two fields, but I have to see the results of the rest of the record to determine which one is the correct record to delete.

 

[Waltang]

You can definately build a second query related to the find duplicates query you just built. Have the query show all fields, and build a relation ship to the find duplicates query.

Only problem is that the resulting query may not be updateable.

Sometimes these things are easier to do than to describe how to do. If you can attatch a database with your table, and the find duplicate query, it would be much easier for me to give you the SQL syntax.

 

[Jon K]

Try this:-

SELECT *
FROM tblInviteeList
WHERE ApplID & InviteeList in (Select ApplID & InviteeList from tblInviteeList group by ApplID & InviteeList having count(*)>1);