If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Vlookup type function
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 |
#2
|
|||
|
|||
Vlookup type function
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 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Snara" wrote in message ... 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 |
#3
|
|||
|
|||
Vlookup type function
On Fri, 18 Jul 2008 02:45:01 -0700, Snara
wrote: 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 -- John W. Vinson/MVP |
Thread Tools | |
Display Modes | |
|
|