comparing records with same data in one field and different in ano

[Kari]

Here is my dilemma:

I have a query with the following fields:

AntibioticID (primary key)
ICUAdmit (foreign key)
OrderNumber
AntibioticName
Date

What this is showing is antibiotic doses. Subjects can have multiple doses
on the same day. These doses may have the same order number or can have
different order numbers. I need to capture records for each ICUAdmit where
the AntibioticName and Date are the same BUT the OrderNumber is different.

I.e, patient has an orderNumber 1 for antibioticA on 5/2/2001 and an
OrderNumber 2 for Antibiotic A on 5/2/2001.

How do I write this query?

 

[Allen Browne]

So you want to select records where there exists *another* record in the
same table that matches on 3 fields and differs on the 4th?

Use a subquery to see if the other record exists.
This kind of thing:

SELECT * FROM Table1
WHERE EXISTS
(SELECT AntibioticID
FROM Table1 AS Dupe
WHERE Dupe.ICUAdmit = Table1.ICUAdmit
AND Dupe.AntibioticName = Table1.AntibioticName
AND Dupe.[Date] = Table1.[Date]
AND Dupe.OrderNumber <> Table1.OrderNumber);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[Kari]

Thanks, that seems to get me the records I need.

Kari

So you want to select records where there exists *another* record in the
same table that matches on 3 fields and differs on the 4th?

Use a subquery to see if the other record exists.
This kind of thing:

SELECT * FROM Table1
WHERE EXISTS
(SELECT AntibioticID
FROM Table1 AS Dupe
WHERE Dupe.ICUAdmit = Table1.ICUAdmit
AND Dupe.AntibioticName = Table1.AntibioticName
AND Dupe.[Date] = Table1.[Date]
AND Dupe.OrderNumber <> Table1.OrderNumber);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html#AnotherRecord

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query with the following fields:

AntibioticID (primary key)
ICUAdmit (foreign key)
OrderNumber
AntibioticName
Date

What this is showing is antibiotic doses. Subjects can have multiple
doses
on the same day. These doses may have the same order number or can have
different order numbers. I need to capture records for each ICUAdmit
where
the AntibioticName and Date are the same BUT the OrderNumber is different.

I.e, patient has an orderNumber 1 for antibioticA on 5/2/2001 and an
OrderNumber 2 for Antibiotic A on 5/2/2001.

How do I write this query?