Query to filter out "duplicate" data

[crisenr]

Ok i've got a table with about 105,000 records. It has fields: AccNo, PatientName, MRNO, TestCode, OrderLoc, DOS, TOS, etc, etc.

The table does have records that WE consider as a "duplicate". If two records both have the same "AccNo" & "TestCode", it is CONSIDERED a "duplicate".

I need a query which will FIRST "filter" out these duplicates as ONE RECORD only. The query should only display records that don't have a same "AccNo" & same "TestCode". And also display all other misc fields.

How do i go about doing this?

 

[Jon K]

You can use a Totals Query to GROUP BY AccNo and TestCode and use First() or Last() on all other fields e.g.

SELECT AccNo,
Last([TableName].PatientName) AS PatientName, Last([TableName].MRNO) AS MRNO,
TestCode,
Last([TableName].OrderLoc) AS OrderLoc, Last([TableName].DOS) AS DOS,
Last([TableName].TOS) AS TOS
FROM [TableName]
GROUP BY AccNo, TestCode;
.