3

here's a regular SQL statement:

SELECT SUM(CASE WHEN [Column2] = 'Cylinder' THEN 1 ELSE 0 END) as 'Cylinder count',
SUM(CASE WHEN [Column2] = 'Snap' THEN 1 ELSE 0 END) as 'Snap count',
SUM(CASE WHEN [Column2] = 'Tip' THEN 1 ELSE 0 END) as 'Tip count',
SUM(CASE WHEN [Column2] = 'Other' THEN 1 ELSE 0 END) as 'Other count'
FROM [TableName]
WHERE [Column1] = '1.9 QNS-Quantity Not Sufficient'

can you please convert it to ms-access??

based on this question:

https://stackoverflow.com/questions/3153829/question-on-complex-select-statement

Community
  • 1
  • 1
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062

1 Answers1

4

Microsoft Access doesn't support CASE, but you can use IIF instead:

SELECT
    SUM(IIF([Column2] = 'Cylinder', 1, 0)) as 'Cylinder count',
    SUM(IIF([Column2] = 'Snap', 1, 0)) as 'Snap count',
    SUM(IIF([Column2] = 'Tip', 1, 0)) as 'Tip count',
    SUM(IIF([Column2] = 'Other', 1, 0)) as 'Other count'
FROM [TableName]
WHERE [Column1] = '1.9 QNS-Quantity Not Sufficient'

References

Community
  • 1
  • 1
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452