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]):

SELECT [qryAverage].*, tblGrades.txtGradeName FROM [qryAverage] LEFT JOIN tblGrades ON [qryAverage].[Average]>=tblGrades.lngLowerLimit AND [qryAverage].[Average]<=tblGrades.lngUpperLimit;
 

Regards,
Fish