Comparison-query does not return all records

[thecheyenne]

Dear all!
I have a database that stores student results - no, it's not a chart-graph question :):):) via the back door.

Within this database, sits a query called "qry-yr10-end-of-year-results". This query contains the fields "student_ID", "Name" and "SumofWeighting_applied".

This "sumofWeighting_applied" is nothing other than a calculated numerical value, I won't bore you with the resaons for its name.

The query returns 11 records, as it should do, and the SQL for the query is

SELECT [qry-yr-10-end-of-year-exams].[StudentID], 
Sum([qry-yr-10-end-of-year-exams].[Weighting_applied]) AS SumOfWeighting_applied, 
[qry-yr-10-end-of-year-exams].[Name]
FROM [qry-yr-10-end-of-year-exams]
GROUP BY [qry-yr-10-end-of-year-exams].[StudentID], [qry-yr-10-end-of-year-exams].[Name]

So far everything works as it should, the query returns 11 records, one for each of the students.

I would now like to link the numerical value stored in the field "SumOfWeighting_applied", to a Grade, a letter, in other words an 'A', a 'B', a 'C' etc.

For this, I have set up a table named "%-2-GCSE-Grade", containing the fields "low-Perc"; "high-Perc" and "GCSE-Grade". The idea comes from the O'Reilly Access Cookbook

Next I have created a qry called "qry_percentage_into_Grades"; this query contains the newly created table "%-2-GCSE-GRade" and the fully functioning query "qry-yr10-end-of-year-results"; by 'fully functioning, I mean, returning all 11 records.

I then specified the criteria for the field "SumOfWeighting_applied", using the statement

Between [%-2-GCSE-Grade].[low-perc] And [%-2-GCSE-Grade].[high-perc]

The complete sql for "qry_percentage_into_Grades" is:

SELECT [qry-yr10-end-of-year-results_for_New_GCSE].StudentID,
[qry-yr10-end-of-year-results_for_New_GCSE].Name, 
 
[qry-yr10-end-of-year-results_for_New_GCSE].SumOfWeighting_applied, 
 
[%-2-GCSE-Grade].[GCSE-Grade]
 
FROM [qry-yr10-end-of-year-results_for_New_GCSE], [%-2-GCSE-Grade]
 
WHERE ((([qry-yr10-end-of-year-results_for_New_GCSE].SumOfWeighting_applied) Between [%-2-GCSE-Grade].[low-perc] And [%-2-GCSE-Grade].[high-perc]));

But for some reason that I cannot figure out, this query returns only 9 of the 11 records;

I double checked my table "%-2-GCSE-Grade"to make sure that I really do have a letter-Grade for each value but I think I have.


tbl %-2-GCSE-Grade

low ,   high ,  Grade
 0,      19,      U
20,      29,      G
30,      39,      F
40,      49,      E
50,      59,      D
60,      69,      C
70,      79,      B
80,      89,      A
90,     100,      A*

The two records missing are the ones where
SumOfWeighting_applied = 69
and
SumOfWeighting_applied = 79

Any ideas???

[patjones]

Hi,

Different database environments treat the BETWEEN operator differently. Some of them use it in an inclusive manner, meaning that if you say WHERE a BETWEEN b AND c - it will actually include the values b and c. Others will return only values that lie between b and c, and will actually leave b and c out. I think it is very telling that it is not returning the records with 69 and 79, as those are end values.

Try using this for your WHERE clause instead:

WHERE ((([qry-yr10-end-of-year-results_for_New_GCSE].SumOfWeighting_applied) >= [%-2-GCSE-Grade].[low-perc] AND ([qry-yr10-end-of-year-results_for_New_GCSE].SumOfWeighting_applied) <= [%-2-GCSE-Grade].[high-perc]))

There is also a way to do this using a JOIN operation, which is basically equivalent to a WHERE but a little cleaner. Let me know how it works out first.

Pat

[patjones]

The way to do this using a JOIN is:

SELECT [qry-yr10-end-of-year-results_for_New_GCSE].StudentID,
       [qry-yr10-end-of-year-results_for_New_GCSE].Name,
       [qry-yr10-end-of-year-results_for_New_GCSE].SumOfWeighting_applied,
       [%-2-GCSE-Grade].[GCSE-Grade]
FROM [qry-yr10-end-of-year-results_for_New_GCSE] INNER JOIN [%-2-GCSE-Grade] ON ([qry-yr10-end-of-year-results_for_New_GCSE].SumOfWeighting_applied >= [%-2-GCSE-Grade].[low-perc] AND [qry-yr10-end-of-year-results_for_New_GCSE].SumOfWeighting_applied <= [%-2-GCSE-Grade].[high-perc])

Like I said, this should give results equivalent to using a WHERE clause, but this method is a little easier for the SQL engine to optimize.

Pat

[thecheyenne]

Thanks so much Pat
the thought had occured to me too but i dismissed it as soon as i realised that the query had picked up the value 49. this being the case, i just couldn't see why it didn't pick up the values 69 and 79. in the end, running out of time - it is world-cup-year after all - i changed the values in tbl_%_2_grades but your idea is lots neater, mine just happens to work this time round because no student happens to have the round values which now appear twice:
low-perc; high-perc; grade
50; 60; c
60; 70; b
70; 80; a

i know i'll have to change it soon.
Thanks again for the tip
all the best

[NeoPa]

The values in the table are not all-inclusive I'm afraid.

They may show as integers, but the calculation of weighting surely result in fractional amounts and your table doesn't include values between 69.0 and 70.0 (nor for any other of the gaps).

For now I would make the upper and lower limits real numbers (EG. 69.999) rather than integers (or Longs) but if I can I'll look into knocking up some more reliable SQL to allow finding the correct value with just upper limits (using Max()).

[NeoPa]

I should say that Between works on the inclusive criteria so making 20-30 impractical. Of course saying A>=20 AND A<30 would do the trick. There's still a purer way, but I'm working again now (lunch over) so unable to post it just yet.

[thecheyenne]

NeoPa, you're a life saver :) which is not to say that I'm not grateful to all of you who've contributed.
But when under pressure to produce, a lay-man like myself tends to get a little nervous, so after trying Pat's Where clause with no success - probably 'cause I forgot a bracket somewhere, plugging the gaps by changing the perc-high field property to 3 fixed decimal points picked up the 2 remaining records.
So now I know that as far as my database is concerned,
79 is not always equal to 79
Thanks again

[patjones]

@NeoPa
Wow, I completely missed that aspect of the situation. So maybe what cheyenne thinks are values of 69 and 79 really aren't?

I used to run into something similar to this in FORTRAN all the time. You'd think you were looking at 43.00, and it was actually 43.00005 or something like that.

Pat

[NeoPa]

A more reliable way to do it would be something like :

SELECT tR.StudentID
      ,tR.Name
      ,tR.SumOfWeighting_applied
      ,(
    SELECT [GCSE-Grade]
    FROM   [%-2-GCSE-Grade]
    WHERE  [Low-perc]=(
        SELECT Min([Low-Perc])
        FROM   [%-2-GCSE-Grade]
        WHERE  [Low-Perc]<=tR.SumOfWeighting_applied
        )
    )
 
FROM   [qry-yr10-end-of-year-results_for_New_GCSE] AS [tR]

This works simply on the understanding that the low value is always whole, and needs no high value. This is an example that works with your original table layout. Personally, I'd change it round to use the high value instead and use Max() and < as opposed to the current Min() and >=.

[NeoPa]

Pat,

I don't know if the values are all supposed to be integers and just slightly off due to being stored in variables of non-integral type, or calculated values that can fall anywhere in the range. We don't have the info to draw a conclusion on that. However, I'd infer, from the name of the field ([SumOfWeighting_applied]), that it may be the latter. The fact that both missing values ended in 9s was the clue that they fell ouside the range supplied. It really is better to define the range as a single comparison generally. Each record has it's own value and determining which a record matches is done by checking the value as valid and finding the closest match (as per my latest SQL).

All good fun :)

[patjones]

@NeoPa
Another piece of elegant SQL from you! But you do mean ">=" in the subquery WHERE clause, yes?

Pat

[NeoPa]

Not in this case Pat.

As I am comparing the table values on the left with the student's achieved value on the right it needs to be <= here. Does that make sense?

My subsequent comments (Post #9) may have been somewhat confusing as they work from a different basis (They are talking about comparing the student score (left) against the table (right)).

[patjones]

@NeoPa
Right, I see. Maybe the Min() needs to be a Max() then? Something isn't clicking for me. Won't the innermost subquery always pick zero? I could be having a really bad day.

[NeoPa]

You're absolutely right Pat (It should have been Max()).
The innermost subquery selects all records in the [%-2-GCSE-Grade] table where [Low-Perc] is less than or equal to the grade attained by the student. The value we are intereset in is actually the max of these, so Max() should be used in this case.

Here is a fixed version :

SELECT tR.StudentID
      ,tR.Name
      ,tR.SumOfWeighting_applied
      ,(
    SELECT [GCSE-Grade]
    FROM   [%-2-GCSE-Grade]
    WHERE  [Low-perc]=(
        SELECT Max([Low-Perc])
        FROM   [%-2-GCSE-Grade]
        WHERE  [Low-Perc]<=tR.SumOfWeighting_applied
        )
    )
 
FROM   [qry-yr10-end-of-year-results_for_New_GCSE] AS [tR]

Well done for sticking to it :)