Results 1 to 2 of 2
2009-03-12, 19:31 #1
- Join Date
- Feb 2007
- Thanked 0 Times in 0 Posts
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......
if i havent explained this well enough feel free to ask more questions...
2009-03-12, 19:59 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
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.
Finally add a calculated column
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]