Using Case When in a Query (1 Viewer)

[LadyDi]

I am trying to create a query using SQL that will look at a table in my database and create a summary. I am trying to use the SQL statement CASE WHEN . . . THEN . . . END AS. Every time I try to run the query, I get an error message stating "The Microsoft Office Access database engine could not find the object 'CASE WHEN [Reject Reason] = 'CantAlign' THEN 1 ELSE 0 END', Make sure the object exists and that you spell its name and the path name correctly." The [Reject Reason] is a field in my table that contains the reason something was rejected. "CantAlign" is one of my reason codes. Can't I use a Case statement in an Access query? Is there a way to fix this? Any suggestions you may have would be greatly appreciated. Below is the query I have right now.

SELECT X.ATMID
, X.TRANSACTION_DATE
, SUM(X.CANT_ALIGN) AS CANT_ALIGN
, SUM(X.NOTE_REJECT) AS NOTE_REJECT
, SUM(X.TOO_SHORT) AS TOO_SHORT
, SUM(X.TOO_LONG) AS TOO_LONG

FROM
(SELECT A.ATMID, [TRANSACTION DATE]
, CASE WHEN [REJECT REASON]='CantAlign'
THEN 1 ELSE 0 END AS CANT_ALIGN
, CASE WHEN [REJECT REASON]='NoteReject'
THEN 1 ELSE 0 END AS NOTE_REJECT
, CASE WHEN [REJECT REASON]='TooShort'
THEN 1 ELSE 0 END AS TOO_SHORT
, CASE WHEN [REJECT REASON]='TooLong'
THEN 1 ELSE 0 END AS TOO_LONG
FROM qryENARejected) AS X;

 

[plog]

Short answer, yes but its called a Switch (http://www.techonthenet.com/access/functions/advanced/switch.php).

Long answer, you are reinventing a cross-tab query. Look into those, that's what you really want: http://office.microsoft.com/en-us/a...ad-by-using-a-crosstab-query-HA010229577.aspx