converting SQL to ACCESS

Question

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 · Accepted Answer · 2017-05-23 12:01:11Z

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

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered Jun 30, 2010 at 22:43

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David-W-Fenton Over a year ago

I don't think you need the quotes around the field aliases.

Collectives™ on Stack Overflow

converting SQL to ACCESS

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related