Results 1 to 2 of 2
  1. #1
    Join Date
    May 2005
    Posts
    21

    Smile Unanswered: MS Access equiv of CASE statement?

    Greetings all

    I am writing a query that in ORACLE would look like:

    Code:
    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"
    FROM tblData
    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...
    Code:
    SELECT person, department, IIF(grade=1,1,0) as "STAFF"
    FROM tblData
    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.
    Code:
    SELECT person, department, IIF(grade="DK",1,0) as "STAFF"
    FROM tblData
    comes up with an error on every record

    Code:
    SELECT person, department, IIF(grade LIKE "DK",1,0) as "STAFF"
    FROM tblData
    doesn't error, but still returns 0 for every record

    ~Shiv
    Last edited by shiv_379; 10-13-05 at 07:07.

  2. #2
    Join Date
    May 2005
    Posts
    21
    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
    Code:
    SELECT person, department, IIF([qryData].[grade]="DK",1,0) as "STAFF"
    FROM qryData
    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!
    Sorry all!

    ~Shiv

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •