Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Feb 2007
    Posts
    15
    Thanks
    0
    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......

    Many thanks,

    Michael

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    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

    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]
    Attached Files Attached Files

Posting Permissions

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