sql - Very special join on float value -

I was stuck with a difficult query (in MS Access 2013). I want to do a fairly simple job:

I have two tables (see examples given below): Table "Score" with an exam and a score of the table "grading_key"

There is a field called "quotient" in the score table in which one percent represents the percentage of success (1.0 answers to all the questions are correct). The grading_key table has a part limit that separates a grade level from the next. Thus the "grading_key" table can be used to get grades for the value of any value.

A grade can be obtained by performing:

  grading_key WHERE {ANY_QUOTIENT_VALUE} to top Top 1 grade & lt; = Quotient  

Sample Tables:

  | -read_key- | | ----- Score ----- | Quotient Grade StudentId Quotient 0,92 1 123 0,85 0,87 1,5 321 0,8 0,81 2 766 0,91 0,76 2,5 222 0,78  

My problem is that, I want to include a score and grade in a query, resulting in the addition of each grade with a grade in the table "grade_key" to the table "score" (see the desired_model below). Unfortunately I just can not join, because the quotes in "scores" do not match the grade limit defined in "grade_key" Currently I have used a VBA function (calculateScoreForQuotient ()) but I want to remove the VBA dependency because the resulting table should be called outside of MS Access and in this case VBA functions do not work.

  | -------- desired_message ------- | StudentId Quotient Grade 123 0,85 2 312 0,8 2,5  

Does anyone know a way to get the desired table with plain SQL? I played with different combinations of Johns and WHEREs for a while, but my best result was to associate all the available grades with each student (not really meaningful).

Any help will be safe on my day; -)

You can use a related sub-query, student. You can use Max () or Top 1 with the order by section, whatever you want.

  Select Student ID, Quantit, (Select grade from Max where the grade quality  Student =) Quality as student from grade  < / Pre>