Grading Values

[7 of 9]

Hello all,

Here is my question. I have a query that calculates the average weight of an item. I would like to assign that weight to a predefined grade. Let’s say I have the following grading scale:

Grade Range
A 0-999
B 1000-99999
C 100000-999999


If the Average weight of Item X is 5000 lbs i want the Grade column to return a "B"

How can I do this?

-Thanks

[FishVal]

Hi, there.

I'd like to suggest you the following solution.
Create and fill with an appropriate values a table (if not yet) with the following structure:

tblGrades

txtGradeName     Text
lngLowerLimit      Number(Long)
lngUpperLimit      Number(Long)

Then join it with the query returning mentioned average (let us say the field has name [qryAverage].[Average]):
[code=sql]
SELECT [qryAverage].*, tblGrades.txtGr adeName FROM [qryAverage] LEFT JOIN tblGrades ON [qryAverage].[Average]>=tblGrades.lng LowerLimit AND [qryAverage].[Average]<=tblGrades.lng UpperLimit;
[/code]

Regards,
Fish