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?
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,
GROUP BY Upperlevel