I am writing a query that in ORACLE would look like:
SELECT person, department, SUM(CASE grade='STAFF' THEN 1) as "STAFF", SUM(CASE grade='MGR' THEN 1) as "MANAGER", SUM(CASE grade='DIR' THEN 1) as "DIRECTOR"
GROUP BY person, department
(ok I admit, that's not my actual code due to confidentiality issues, but you get the idea).
How would I write this in SQL for MS Access? I've tried playing with various forms of IF and CASE but nothing seems to work
EDIT: I've tried using the IIF function like this...
SELECT person, department, IIF(grade=1,1,0) as "STAFF"
but that doesn't work, just returns 0 for everything!
FYI the "grade" field is a text field that has number 1-11 or a 2 char text code.
SELECT person, department, IIF(grade="DK",1,0) as "STAFF"
comes up with an error on every record
SELECT person, department, IIF(grade LIKE "DK",1,0) as "STAFF"
doesn't error, but still returns 0 for every record
Got it sorted!
Something I didn't point out in the above is that the data was actually based on a table (shoulda but qryData not tblData). The problem was being caused because I was fully qualifying the reference
SELECT person, department, IIF([qryData].[grade]="DK",1,0) as "STAFF"
as soon as I took the [qryData] out it worked!
Again, this hilights how hard it is to get help when not providing your original code and stuff, because no one woulda figured that from the examples I gave!