Problems with this query 1

[twice300]

I have developed a small app that keeps track of employee information. One table is Sacntions. I keep track of disciplinary measures given out to employees.

At the end of the month, I need to give out a grade per employee based on the amount of sanctions given, if any.

I have wrote this query, but I can't get it to work:

SELECT tblSanctions.SanEmployeeId, 
sum(case when SanCategory = "Verbal" then -5
         when SanCategory = "Written" then -15
         when SanCategory = "1 Day Suspension" then -40
         when SanCategory = "3 Day Suspension" then -60
         when SanCategory = "5 Day Suspension" then -80
         when SanCategory = "8 Day Suspension" then -100
         else then 0 end) as [TempSanctionGrade]
FROM tblSanctions
GROUP BY tblSanctions.SanEmployeeId
Where (((tblSanctions.SanctionDate)>=#8/1/2007# And (tblSanctions.SanctionDate)<=#8/31/2007#));

When I try to save the query I get the following error: "Syntax error (missing operator) in query expression 'sum...."

Is this not allowed in Access. I know this will run in MS SQL.

Thanks in advance for your input.
George Adams

 

[Melagan]

I am pretty sure that there is no support for CASE functions in JetSQL.

~Melagan
______
"It's never too late to become what you might have been.

 

[belovedcej]

Correct, Access does not support a case statement. The cleanest "Access" way of doing this would be to have a table containing the possible categories and their values, and then do a sum of the values, grouping by employeeID.

 

[Melagan]

Hmm...I was just browsing around and found this older thread: thread701-941309

The Switch() function looks promising.

~Melagan
______
"It's never too late to become what you might have been.

 

[lespaul]

the IIF statement is the correlated function in Access for the CASE statement in other languages:

IIF(logical comparison, value if true, value if false)

yours would be:

SELECT tblSanctions.SanEmployeeId,
sum(
iif(SanCategory = "Verbal", -5, 
iif(SanCategory = "Written", -15, 
iif(SanCategory = "1 Day Suspension", -40, 
iif(SanCategory = "3 Day Suspension", -60, 
iif(SanCategory = "5 Day Suspension", -80, 
iif(SanCategory = "8 Day Suspension", -100, 0)))))))

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[belovedcej]

true enough IIF would work. I just don't like all those nasty nested expressions! Makes me dizzy. LOL

I wonder, too - would there be any place that these values would be used? Because if so, a table with the categories and values might be better whether SQL or Access, because who would want to go to several places to make changes should those values ever change? I try to think in terms of long term maintainence whenever possible.

 

[twice300]

Thanks to all for your help. I'll try to create the table with the different categories. Although I do have to present this by tomorrow, so I'll do the IIF function now and add the table for next month.

Thanks again.
George Adams

 

[Melagan]

SELECT t.SanEmployeeId,
SUM(
SWITCH(
SanCategory = 'Verbal', -5
,SanCategory = 'Written', -15
,SanCategory = '1 Day Suspension', -40
,SanCategory = '3 Day Suspension', -60
,SanCategory = '5 Day Suspension', -80
,SanCategory = '8 Day Suspension', -100, 0))

FROM tblSanctions t

GROUP BY t.SanEmployeeId

I think that looks pretty clean myself.

~Melagan
______
"It's never too late to become what you might have been.

 

[belovedcej]

good research Melagon. I'm going to make a note of that, because it looks useful and I didn't know it existed till you brought it up.

 

[lespaul]

Caveat: I think the Switch function is only available in the newer versions of Access (2000 and above)...