Calculating Grades based on query results

[dublindan]

Hi everyone,

First post here, so hi all

I'm creating a database holding the results of audits we are doing. Each audit gets an overall % score, and each individual is given an overall grade based on the average of their audit scores.

How can I get this grade to calculate?

Currently my query is:

SELECT tblHandlers.HandlerName, Avg(tblQualityAudit.TOTAL_SCORE) AS AvgOfTOTAL_SCORE, IIf([TOTAL_SCORE]>=94.1,"4",IIf([TOTAL_SCORE]>=74.1,"3",IIf([TOTAL_SCORE]>=49.1,"2","1"))) AS PBO


FROM tblQualityAudit INNER JOIN tblHandlers ON tblQualityAudit.Handler = tblHandlers.ID
GROUP BY tblHandlers.HandlerName, IIf([TOTAL_SCORE]>=94.1,"4",IIf([TOTAL_SCORE]>=74.1,"3",IIf([TOTAL_SCORE]>=49.1,"2","1")));

It shows a list of the handlers but by individual audit and individual score?

I genuinely have no idea whether I'm explaining myself well enough, apologies.

All I need is

HANDLER - AVERAGE SCORE (over all audits with their name on it) - GRADE (based on that average score)

>94.1% average = '4'
>74.1% average = '3'
>49.1% average = '2'
All below = '1'

Please help, I'm totally clueless on whether this can even be done?

Thanks
Dan

[orange]

You need a detailed description (plain English) of WHAT you are trying to do --no Access, no jargon.
Then you need to design your database, based on the business requirements.

Work through this tutorial to understand the process.
Good luck.

[NoellaG]

Hi,

I think the easiest way to handle this is working with 2 queries:
Query 1 (base query):
--------------------------
SELECT tblHandlers.HandlerName, Avg(tblQualityAudit.TOTAL_SCORE) AS AvgOfTOTAL_SCORE
FROM tblQualityAudit INNER JOIN tblHandlers ON tblQualityAudit.Handler = tblHandlers.ID
GROUP BY tblHandlers.HandlerName;

Query 2 (results query):
---------------------------
select Query1.HandlerName, Query1.AvgOfTOTAL_SCORE, IIIf([AvgOfTOTAL_SCORE]>=94.1,"4",IIf([AvgOfTOTAL_SCORE]>=74.1,"3",IIf([AvgOfTOTAL_SCORE]>=49.1,"2","1"))) as PBO