# Thread: Using queries to select individuals in a population

1. This is slightly complicated to me as a relative beginner but hopefully someone can tell me how to do this.

I have a population of individuals in a database. I have recorded various variables for each individual.
So each individual has a Value for A, B, C, D etc.

In my dataset there are critical values for each variable such that say if A>12 or <4 then A = Positive, or if B>20 then B=Positive etc.
I know how to do isolated queries to find all the people with say Positive A.

Now with say 4 variables A, B, C, D there are different amounts of positive variables each individual can have obviously
so you can have:
No Variables Positive - 1 Combination
1 Positive Only - 4 Combinations
2 Positive - 6 Combinations
3 Positive - 4 Combinations
All Positive - 1 Combinations

At the moment to pick out people based on how many positive variables they have I am using a really convuluted method. I am basically doing lots of different queries for each particular combination of variables so I have 6 queries to find all the different people in the population who have 2 positive variables.

Is there a better way? Basically I want a query that says: IF EXACTLY N of A/B/C/D are POSITIVE then include this person in the output from the query......

Many thanks,

Michael

if i havent explained this well enough feel free to ask more questions...

2. You can do this with 2 queries:

1) Create a query based on your data table.
Add the fields you want, plus a calculated column

ScoreA: [A]<4 Or [A]>12

and similar for the other fields, each with their own cutoff points. These columns will show -1 (true) if the field it's based on is "Positive" and 0 (false) otherwise.

CountPos: Abs(Sum([ScoreA]+[ScoreB]+[ScoreC]+[ScoreD]))

including all the Score... columns. CountPos is the number of "Positive" values for the person.

Save this query as (say) qryScore.

Next, create a new query based on qryScore. Add the fields you want, including CountPos.

If you want to return the persons with exactly 3 positive scores, enter 3 in the Criteria line for CountPos.

The attached database has a working example: [attachment=82830:Combinations.zip]

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•