Vlookup type function

[Snara]

Hello - I am considering transferring our data recording system from an Excel
Spreadsheet to an Access database. I work in a school and one of the key
things we do in Excel is to enter the test scores for students and use the
lookup function in the next column to automatically lookup their score and
return their grade. Does anyone know how I can do this in Access? If I create
a table with the scores and grade boundaries in Access and link this to the
students score field it only returns grades where the students have scored
the exact grade boundary.

Does anyone have any ideas? Many thanks

Natalie

 

[Allen Browne]

It does take a bit more effort to set this up in Access, but it's worth
doing.

Tom Ellision explains how in this article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

 

[John W. Vinson/MVP]

If I create
a table with the scores and grade boundaries in Access and link this to the
students score field it only returns grades where the students have scored
the exact grade boundary.

Neither VLookUp nor the Access semi-equivalent DLookUp is appropriate
here. What you can do is tweak your query a little bit, using a "Non
Equi Join". Open the query that gives the exact boundary value and
choose View... SQL and post it here - or look in it for the JOIN
clause which will have something resembling

INNER JOIN Scores ON yourtable.Score = Scores.LowBoundary

to

INNER JOIN Scores ON yourtable.Score >= Scores.LowBoundary AND
yourtable.Score <= Scores.HighBoundary