I need to write a query that eliminates both duplicate records.

[mdub]

Hi,

I need to write a query that eliminates all records where the Contract and
Order fields are equal. I cannot use the DISTINCT keyword, since it leaves
one record. Thanks,

Michael

 

[John Spencer]

Do you want to DELETE the records or just not show any records that are
duplicated based the two fields?

Assuming that you just want to show all the records that are not duplicates,
then what you want is basically a duplicates query with a change in the
where clause so it will show only the non-duplicated records (based on the
two fields)

SELECT YourTable.*
FROM YourTable
WHERE YourTable.Contract IN
(SELECT Tmp.Contract
FROM YourTable as Tmp
GROUP BY Tmp.Contract, Tmp.Order
HAVING Count(*) = 1 AND
Tmp.Order = YourTable.Order)

Use the Duplicates query wizard to build a query that finds duplicates.
Then in the criteria for Contract find Count(*)>1 and change that to
Count(*) = 1
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..