Transforming field values to new boolean fields in a query 1

[mbrichmo]

Hi,
I would like to transform via a select query a table of the form: PatientMeds(PatientID,Med)
to the form: TransformedPatientMeds(PatientID,Med1,Med2,...Med_n)

I know the names of the specific Meds and can specify them explicitly. After the transformation I would add criteria for example:
SELECT PatientID FROM TransformedPatientMeds WHERE Med1=TRUE AND Med2=FALSE

Thanks!

Example Data:
PatientMeds Table
PatientID,Med
1,Aspirin
1,Antacid
2,Ginseng

TransformedPatientMeds Query
PatientID,Aspirin,Antacid,Ginseng
1,True,True,False
2,False,False,True

 

[dhookom]

You are describing a basic crosstab query with PatientID as the Row Heading, Med as the Column Heading, and Count PatientID as the value. If you want to actually display True or False, you can do this in your report or form.

Duane
Hook'D on Access
MS Access MVP