Duplicate Records

[C Hetland]

I am working on a query with duplicates in one column.

Med_ID ATP
7469
7469 Y

I want to write a query where if the Med_ID column has a duplicate, only
include the record where the ATP column = Y.

 

[Steve Schapel]

C Hetland,

Possibly the simplest approach here is to do it in 2 steps.

First create a query that identifies the duplicated IDs. The SQL view of
such a query may look like the equivalent of this:

SELECT Med_ID, Count(*) AS Frequency
FROM YourTable
GROUP BY Med_ID

Then create a second query, comprised of the table and the first query,
joined on the Med_ID field. The SQL may be like this:

SELECT YourTable.Med_ID, AnyOtherFields
FROM YourTable INNER JOIN FirstQuery ON YourTable.Med_ID = FirstQuery.Med_ID
WHERE [Frequency]=1 OR ([Frequency]>1 AND [ATP]="Y")