Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2013
    Posts
    7

    Unanswered: equivalent of "dynamic" vlookup in MS Access 2007

    I have a table that contains a prevalence of scores by age for a certain test.

    age 0 1 2 3 4
    20 90 92 95 96 100
    21 88 89 92 97 100
    22 86 88 90 98 100
    23 75 80 84 87 100

    This means that for all the 20 year olds who took the test 90% got a 0 or better, 92% got a 1 or better .... and 100% got a 4 or better.

    Right now i have an excel program where you input your age and the score you got on the test and it tells you what percentage of people you did better than, the same as and worse than.

    For example, if you are a 22 year old who got a 2 than you did better than 10% of 22 year olds, the same as 2% and worse than 88% of 22 year olds.

    in excel i have three formulas:
    1) people you did better than: 1-(people you did the same as)-(people you did worse than)
    2) people you did the same as: =VLOOKUP(<age>,<lookup table rang>,<score>+2,FALSE)-(people you did worse than)
    3) people you did worse than: =IF(<score>=0,0,VLOOKUP(<age>,<lookup table range>,<score>+1,FALSE))

    how can i convert this to msaccess 2007? the fields in the lookup table are age, 0, 1, 2, 3, 4. how can i get dlookup to pick the field from the lookup table dynamically (i.e. score+1 or score+2 and not "0" or "1")

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    In a relational database the normalized design for that table is 3 fields: age, score and result:

    20 0 90
    20 1 92

    With that a query or DLookup() with the 2 parameters will return the value.
    Paul

  3. #3
    Join Date
    Jul 2013
    Posts
    7

    dlookup syntax?

    If i restructure the table what is the syntax for the dlookup?

    Table 1: id, age, score
    Table 2: age, score, prevalence

    also - what is the advantage (if any) to using a dlookup vs a query?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    here y'go
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2013
    Posts
    7
    Thank you!

Tags for this Thread

Posting Permissions

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