Assigning a value to a variable based on the Count of another variable

[cardgage]

Hello all,



It seems like this should be easy enough to do, but of course it's not.

I'm essentially trying to assign my cohort of patients into two groups based on how many medications they were discharged on. I've created a query called "qryGroups" in order to do this. I am pulling the values "PatientID", "Drug" and "AsNeeded" from a table called "tblDCDrugs".

My query looks as follows:

Field:	PatientID	Drug	AsNeeded
Table:	tblDCDrugs	tblDCDrugs	tblDCDrugs
Total:	Group By	Count	Group By
Criteria:			False

I tried to create a variable called "Group", with the syntax "Group: IIf("CountOfDrug">1,1,2)" but I got the error message "Data type mismatch in criteria expression". I would like to assign patients with >1 Drug to Group 1, and all other patients to Group 2. Any thoughts on how I might go about doing this?

Any help is appreciated!

[June7]

First, don't put field name within quotes, use [].

Second, AFAIK, can't use a field created by aggregate calculation in another expression in same aggregate query.

Use the grouping query as source for another query.

[cardgage]

/facepalm

Don't know why I didn't think of that...thanks June7! Worked like a charm.