Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Lookup (Access 97 SR2)

    I have a database that I use to track operator productivity. I am attaching a snapshot of one of the reports. I need help with calculating the rating on the fly.

    I want to have the rating calculated and placed in the blank box below daily productivity. This is basically a lookup. If the below table represented the ratings scale, the value of 3.5 would be placed in the blank box.

    <table border=1><td>productivity</td><td>rating</td><td>1</td><td>1</td><td>1250</td><td>1.5</td><td>1500</td><td>2.0</td><td>1750</td><td>2.5</td><td>2000</td><td>3.0</td><td>2250</td><td>3.5</td><td>2500</td><td>4</td><td>2750</td><td>4.5</td><td>3000</td><td>5.0</td></table>

    Any suggestions on how to accomplish this. I know how to do a vlookup in Excel, but am drawing a blank on how to accomplish this in Access. Just a side note, but the ratings scale needs to be easily available as the scale could change as often as monthly.

    Thanks in advance for any help.
    Attached Images Attached Images

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Lookup (Access 97 SR2)

    How about a query like

    SELECT TOP 1 rating FROM tbl WHERE (productivity<= TrgtValue) ORDER BY productivity DESC;

    You would need to have the TrgtValue as a parameter so that say 1800 returns 2.5 as the rating. (Should you want 3.0 then you need to modify the query to sort ascending and use a >= condition).

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Lookup (Access 97 SR2)

    As an alternative to Andy Ainscow's solution, you can use

    SELECT Max(rating) FROM tblRating WHERE productivity<=Reports!rptProductivity!txtTotalProd uctivity

    The ratings are stored in a table tblRating. The criteria of the query refer to a text box txtTotalProductivity on a report rptProductivity. If you calculate total productivity in a query, you can use that instead.

  4. #4
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Lookup (Access 97 SR2)

    Where do I put the SQL string? I have tried putting it in the 'Control Source' property of the blank text box, but all I can ever get for results are #NAME?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Lookup (Access 97 SR2)

    I'm sorry, you need a domain function here instead of a SQL statement. Try

    =DMax("rating", "tblRating", "productivity<=" & [txtTotalProductivity])

    as Control Source where tblRating is the table with the productivity and rating values, and txtTotalProductivity is the name of the text box containing the total productivity.

  6. #6
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Lookup (Access 97 SR2)

    Thank you Hans! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Once I corrected my typos, the domain function worked like a charm. Exactly what I was looking for.

Posting Permissions

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