Looking up values

[Mrsostrich]

I have a table with student marks in, and I want to allocate a grade which corresponds to the score, eg over 6 is an A, over 5 is a B etc. Vlookup or nested if do it beautifully on Excel. Any idea how I can do it on Access other than using a VBA If else statement which seems a bit complex? Iif function only allows one of two values.

 

[Jon K]

You can nest the iif() function in Access.

Try this query (type/paste in the SQL View of a new query, replacing with the correct table name and field name):-

SELECT Marks, iif(Marks>6,"A", iif(Marks>5,"B", iif(Marks>4,"C", iif(Marks>3,"D", iif(marks>2, "E", iif(Marks>1, "F", "G")))))) AS Grade
FROM TableName
ORDER BY Marks DESC;


Instead of nesting iif(), you can also use a Switch() function:-

Switch(Marks>6,"A", Marks>5,"B", Marks>4,"C", Marks>3,"D", Marks>2,"E", Marks>1,"F", Marks<=1,"G")

 

[Mrsostrich]

Jon, you are an absolute star - the switch function seems the easiest route, and avoids having to go into sql. It works just like an Excel nested if, so it's easy to follow for students (I'm a teacher).

Thanks a million
Di

 

[Brand-X]

Hmm...

Would there be a way to do this in a VLOOKUP sort of fasion? Say if a nested IIF became to complex? [i.e. if you decided to assign +'s and -'s aswell (A+, A, A-, B+, etc.)] would there be another way????

-Brandon

 

[Mrsostrich]

I don't know of a way of doing that as a vlookup, because that's the way I do it in Excel and it was my first thought in Access. That switch function works well, though and I'm not sure how many arguments it will take. I know it looks messy but it doesn't take long to put together through the expression builder. My only snag with it is that as far as I know the vales in the query have to be table specific, so if I want to do the same in another table with similar field namesI have to write a new query rather than linking it to the same boundary table as I would have done with vlookup.

 

[Brand-X]

Gah!... How about using DLookup? I've heard that has a similar function but never figured it out myself

-Brandon

 

[Jon K]

To use a lookup table is the closest to VLookup.

Attached is a demo DB that uses a lookup table to allocate Grades according to students' scores: over 6 is an A, over 5 is a B etc. As these are inequalities, the way to set up the lookup table is important.

The LookupTable contains the fields Marks and Grade. Students' scores are contained in the StudentScores table.

With a lookup table, either DLookup() or a subquery can be used in a query:-

SELECT StudentID, Score, DLookup("Grade", "LookupTable", "Marks <" & Score) AS Grade
FROM StudentScores;

SELECT StudentID, Score, (Select Min(Grade) from LookupTable where Marks < Score) AS Grade
FROM StudentScores;


Both queries produce the same result. DLookup() is an inefficient function yet the query is updatable. A subquery is optimised but the query is not updatable.

A subquery is preferred if the data do not need to be edited in the query, particularly if there are many student records and the query is run on a slow system,

 

[Illadopsis]

Thanks Jon K for the sample DLookup file. It works fine, but when I the code below in my own database query I get asked for a parameter value for site_ref when I hoped that it would automatically return all the values into field Area2:

Area2: DLookUp("[ContigBL]","Woodareas","[Woodcode]='" & [site_ref] & "'")

ContigBL is numeric and Woodcode/site_ref are text. Any clue as to what I'm doing wrong?

I

 

[Jon K]

Illadopsis,

Is [site_ref] a field in the table on which the query is based? If it is, you need to add the table to the query.