Dynamic Lookup (Access 97 SR2)

Topic

New Reply

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.

productivity	rating
1	1
1250	1.5
1500	2.0
1750	2.5
2000	3.0
2250	3.5
2500	4
2750	4.5
3000	5.0

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.

Reply | Quote

Replies

How about a query like

SELECT TOP 1 rating FROM tbl WHERE (productivity= condition).

Reply | Quote

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

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

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

Thank you Hans!

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

Reply | Quote