Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2010
    Posts
    6

    Unanswered: Row to Column Access SQL Help

    Hello All,
    I have a database table that gets a new row record for child items related to a Serial Number.
    The table has three fields: Upperlevel, SerialNumber, Reference.
    Reference has 3 constant entries: PS, Display, MAIN.
    Now I want to create a query with these fields based on data provide by the table I mentioned above:
    Upperlevel, PSSN, DISOPLAYSN, and MAINSN.

    The following code works fine in MS SQL, but doesn't play well with Access (I think it's the case statement. Any idea how run this query in MS Access?

    Code:
     SELECT Upperlevel,
    MAX(CASE WHEN Reference = PS THEN SerialNumber else '' END) as PSSN,
    MAX(CASE WHEN Reference = DISPLAY THEN SerialNumber else '' END) as DISPLAYSN,
    MAX(CASE WHEN Reference = MAIN THEN SerialNumber else '' END) as MAINSN,
     FROM INVENTORY
     GROUP BY Upperlevel
    Thanks,
    -Ken

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The common replacement for CASE in Access is the IIf() function. Check it out and see if it works for you.
    Paul

Posting Permissions

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