ACCESS SQL > SELECT CASE HELP

Question

I have a set of tables including Reps, Teams, Errors, Error Log. Error Log lists all errors made by a rep. Each rep is in a team. Each rep has an Edge ID (user number) Each different type of error has a points value (stored in the errors table).

I have this query in Access to total the points value for each rep in a team between two dates:

SELECT 
    Reps.Forename
    , Reps.Surname
    , [Error Log].[Edge ID]
    , Teams.[Team Code]
    , Sum(Errors.Points) AS SumOfPoints
FROM Teams 
    INNER JOIN (
     Reps INNER JOIN (
      Errors INNER JOIN [Error Log] 
       ON Errors.[E&CD code] = [Error Log].[E&CD Code]) 
      ON Reps.[Edge ID] = [Error Log].[Edge ID]) 
     ON Teams.[Team Code] = Reps.[Team Code]
WHERE 
    ((([Error Log].[Date Logged]) Between [Error logged from: (dd/mm/yyyy)] And [Error logged to: (dd/mm/yyyy)]) 
AND 
    ((Teams.[Team Code])=[Team Code:]))
GROUP BY 
    Reps.Forename
    , Reps.Surname
    , [Error Log].[Edge ID]
    , Teams.[Team Code]
ORDER BY 
    Sum(Errors.Points) DESC;

I want to give each rep a grade dependant on their number of points, where: 0-4 points = grade 3, 5-9 = 4 and 10+ = 5

How can I code this (SELECT CASE perhaps)? I've been trying for the last hour but can't get it right!

Thanks for any help.

Answer 1

I think you can use the IIF( ) statement inline in MS Access SQL to get the same thing...

You could try

SELECT 
    Reps.Forename
    ...
    , Sum(Errors.Points) AS SumOfPoints
    , IIf(Sum(Errors.Points) > 5, "Super Star", "Dummy")
    ... and so on ...

Answer 2

Access doesn't support select case structure. Instead you need to use the Switch function

Switch(SumOfPoints >= 10, 5, SumOfPoints >=5, 4, ...)

Answer 3

You may want to create a table for grades so you only maintain the table and not the code. Join your scores to it.

Score | Grade 0, 3 1, 3 ... 10, 5

If not in table then = 5.

Also, could have StartScore, EndScore and Grade. Any score between the Start and End would get the grade.