By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,940 Members | 1,391 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,940 IT Pros & Developers. It's quick & easy.

Grading Values

7 of 9
P: 23
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?

Feb 14 '08 #1
Share this Question
Share on Google+
1 Reply

Expert 2.5K+
P: 2,653
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:
Expand|Select|Wrap|Line Numbers
  1. tblGrades
  3. txtGradeName     Text
  4. lngLowerLimit      Number(Long)
  5. lngUpperLimit      Number(Long)
Then join it with the query returning mentioned average (let us say the field has name [qryAverage].[Average]):
Expand|Select|Wrap|Line Numbers
  1. SELECT [qryAverage].*, tblGrades.txtGradeName FROM [qryAverage] LEFT JOIN tblGrades ON [qryAverage].[Average]>=tblGrades.lngLowerLimit AND [qryAverage].[Average]<=tblGrades.lngUpperLimit;
Feb 14 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.