field values,group,compare (1 Viewer)

vengsiva

Registered User.
Local time
Today, 13:58
Joined
Jan 9, 2004
Messages
31
I have a table with the following fields
LabID PatientNo Testdate Testresult
On different dates the test may be repeated
The test result may be either Sensitive,Resistant or null
I have grouped all patients using find duplicates(PatientNo)
I wanted to build a query or VBA to find out the patients who on a testdate was sensitive but became resistant on a latter date(not vice versa)
Thanks in advance for any help
:(
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 19:28
Joined
Jan 20, 2009
Messages
12,849
Use a self join.

Drag the table into the designer twice to make an alias.
Join the table to its alias on PatientID.

Select the PatientID field from the first table.

Select the Result field from the first table with a conditon that it is Sensitive.
Select the Result field from the alias table with the conditon Resistant.

Select the date field from the alias table with the condition that it is greater then the date field from the first table.
 

vengsiva

Registered User.
Local time
Today, 13:58
Joined
Jan 9, 2004
Messages
31
Thank you-That is exactly what I wanted
 
Last edited:

Users who are viewing this thread

Top Bottom