Lookup grade earned from GradeBoundary table

[James Reay]

(Simplified version of problem)

I have a table named GradeBoundary that describes grade boundaries for a particular course eg

GRADE PERCENTAGE TO ACHEIVE
U 0
D 20
C 40
B 60
A 80

I also have a table named PupilMarks that gives the mark that each student acheived eg

PUPIL MARK
Julie 50
Caroline 20
Matthew 95
Kelly 80

My question is when I create a query to display

PUPIL MARK GRADE
Julie 50 C
Caroline 20 D
Matthew 95 A
Kelly 80 A

 

[James Reay]

.......(Pressed post accidentally)


How can I lookup/match the applicable grade from the GradeBoundary table?

Thanks in advance.

 

[Rob.Mills]

When I've done something similar to this I created in the boundary table two fields for a start and finish. This is how it would look:

GRADE Start Finish
U 0 20
D 20 40
C 40 60
B 60 80
A 80 100

Then in your query create a dlookup

DLookUp("Grade","Table Name","Start<=" & [Mark] & " And Finish<" & [Mark])

 

[James Reay]

Thanks - Worked a treat!

I had thought of dlookup but didn't know where to put all the quotation marks and so had previously only been returned error.

In the end I modified your design to cut out the need for a start and finish:

Grade: DLookUp("[TABLE Grade Schema Details]![Grade]","TABLE Grade Schema Details","[TABLE Grade Schema Details]![Percentage to achieve] <= " & [Mark])