Count Yes/No in multiple columns

[Guest]

Sorry gang. I've search the group answers, but just don't seems to get it.
So thanks very much for your help.

I have a transaction file with EmpNo, date, absent and tardy as the fields.
absent and tardy are yes/no fields. The query result should be number of
times absent and tardy in a given period. E.G.

For October
EmpNo Absent tardy
1 0 1
2 1 1
3 0 2
etc

 

[[MVP] S.Clark]

If the structure of your table is the following:

EmployeeID
Workdate
Status (Ontime, Tardy, Absent)

Then you could make a crosstab query with the EmpID as the Row Header, the
Status as the Column Header, and the count of Workdate as the value.

 

[John Spencer (MVP)]

One possible way would be something like the following.

SELECT EmpNo,
Abs(SUM(Absent)) as CountAbsent,
Abs(Sum(Tardy)) as CountTardy,
Format([Date],"yyyymm") as YearMonth
FROM YourTableName
WHERE [Date] Between #1/1/2004# and #1/31/2004#
GROUP BY EmpNo, Format([Date],"yyyymm") as YearMonth