Count Yes/No in multiple columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
Back
Top