Creating groups based on multiple values

[cardgage]

Hello all,

I've created a database that keeps track of medications that patients are discharged on - patient information is stored in tblPatientInfo, and medications are stored in a subtable named tblMeds. tblMeds has the fields PatientID (linked to tblPatientInfo), Drug, Dose, Route, and AsNeeded (True or False). Patients may be on the same medication with two or more routes (i.e., oral, intramuscular, subcutaneous, etc...), and they may be scheduled or as needed.

I'm trying to create a query that groups patients based on whether they are discharged on two INDIVIDUAL, scheduled medications (Group A), or not (Group B). Here's an example of what I'm talking about:

PatientID	Drug	Dose	Route	AsNeeded
1	DrugA	10 mg	Oral	False
1	DrugB	5 mg	Oral	True
2	DrugA	10 mg	Oral	False
2	DrugA	5 mg	SubQ	False
2	DrugB	20 mg	Oral	True
3	DrugA	10 mg	Oral	False
3	DrugA	5 mg	SubQ	False
3	DrugB	20 mg	Oral	False
3	DrugB	5 mg	Oral	True

In this example, Patient 1 would be placed in Group B (on two individual drugs, but only one is scheduled), Patient 2 would be placed in Group B (on two scheduled medications, but both are DrugA with different routes), and Patient 3 would be placed in Group A (on three scheduled medications, two of which are different - DrugA, DrugA and DrugB.

I hope this makes sense. I created a query that counts scheduled and AsNeeded medications separately, and assigned anyone with a Count of scheduled meds >1 to Group A; but, that didn't account for patients being on different routes of the same scheduled medication.

Thanks in advance for any help with this!

[rpeare]

How are you defining a scheduled medication? I would have thought that AS NEEDED being FALSE meant their medication was scheduled but that doesn't hold with your definition of patient 3 being in group A. In your example Patient 3 would only have 1 scheduled medication (drug B, 20mg, Oral). If ASNEEDED being TRUE means their medication is scheduled here's an example:

Cardgage.zip

What you're doing is a 2 step process and breaking it out in to the separate parts has always been my preferred method in case your criteria change over time it's easier to fix/update than if you roll it into a select in a select query.

Look at Qry_Group or Query1

[cardgage]

Thanks for the help rpeare! I actually made a mistake in the table above and reversed the meanings of "True" and "False"...it is updated now.

Your solution works perfectly...didn't think to count the DrugName (was counting the AutoID created by the Drug table). Thanks again!