Vlookup type function

  • Thread starter Thread starter Snara
  • Start date Start date
S

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
 
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.
Click to expand...

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
 
Back
Top