Duplicate records based on Multiple Fields

[sippyuconn]

Hi

Trying to write a query to find duplicate records in table using 2 fields

field1 field2 field3

1111 abc Part abc <-----
2222 xyz Part xyz
3333 uvw Part uvw
1111 abc Part bbbb <-----

Trying to find how many duplicates I have Of Field1 and Field2 combination
being the same ie the 1st and 4th

thanks

 

[KARL DEWEY]

Try this --
SELECT [1st_Table].Field1, [1st_Table].Field2, Count([1st_Table].Field1) AS
CountOfField1
FROM 1st_Table
GROUP BY [1st_Table].Field1, [1st_Table].Field2;

 

[Jerry Whittle]

Show us the SQL for a query where you are "close" to the solution or at least
joins displays the appropriate fields. Open the query in design view. Next
go to View, SQL View and copy and past it here.

In general it would look something like so:

SELECT YOURTABLE.FIELD1,
YOURTABLE.FIELD2,
Count(YOURTABLE.FIELD2) AS CountOfFIELD2
FROM YOURTABLE
GROUP BY YOURTABLE.FIELD1,
YOURTABLE.FIELD2
HAVING Count(YOURTABLE.FIELD2) >1 ;

 

[John Spencer]

Use the find duplicates query wizard. It will write the code for you.

You can then switch to SQL view and see how this works. The SQL would look
something like

SELECT *
FROM TheTable
WHERE Field1 IN
(SELECT Tmp.Field1
FROM TheTable as Tmp
GROUP BY Field1, Field2
Having COUNT(*) > 1 and Tmp.Field2 = TheTable.Field2)
ORDER BY Field1, Field2

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