CODEWITHSUNDEEP
sqlms-accesscountcriteria
Kamawoop
Kamawoop

Reputation: 13

MS Access SQL - Count if a field matches either of two criteria

I have a table that contains employees attendance details. For this example let’s assume only three different attendance types: ‘Holiday’ / ‘Bank-Holiday’ / ‘Sickness’.

I can find the totals of each for each employee without difficulty, however I need to combine the results from ‘Holiday’ and ‘Bank-Holiday’.

e.g. if the table contained the following data:

John - ‘Holiday’

John - ‘Bank-Holiday’

John - ‘Sickness’

Jenny – ‘Holiday’

The results should be

Name - Days Sick – Days Holiday

John – 1 – 2

Jenny – 0 - 1

Upvotes: 1

Views: 9380

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123429

My two cents' worth:

Instead of hard-coding this type of categorization into queries you really should create an [AbsenceCategories] table with values like...

AbsenceType     AbsenceCategory
-----------     ---------------
Sickness        Sick
Holiday         Holiday
Bank-Holiday    Holiday

...so when "Top Management" sends you the memo informing you that "Unscheduled Absence" must be tracked separately and categorized as "Holiday" then you can just add it to your table and you don't have to change all of your queries and such.

Upvotes: 1

GarethD
GarethD

Reputation: 69759

You can use IIF inside a SUM Statement to count certain conditions:

SELECT  [YourTable].[Name],
        SUM(IIF([YourTable].[Attendance] = 'Sickness', 1, 0)) AS [Days Sick],
        SUM(IIF([YourTable].[Attendance] IN ('Holiday' , 'Bank-Holiday'), 1, 0)) AS [Days Holiday]
FROM    [YourTable]
GROUP BY [YourTable].[Name];

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You want a conditional aggregation, something like:

select name,
       sum(iif(which = 'Sickness', 1, 0)) as SickDays,
       sum(iif(which = 'Holiday' or which = 'Bank Holiday', 1, 0)) as Holiday
from t
group by name

Upvotes: 0

Related Questions