I have developed a database that tracks participation of clients in the programs of a disability service. I want a report of the total number of clients booked to attend each day of the week. The following query successfully counts Monday attendance. Rather than separately replicating this for each day of the week, I want to develop a single query that counts each day. The query for Monday is:
TRANSFORM Count(Clients.[Client ID]) AS [CountOfClient ID]
SELECT Clients.[Monday Attendance]
FROM Clients
WHERE (((Clients.[Monday Attendance])=Yes))
GROUP BY Clients.[Monday Attendance]
PIVOT Clients.[Monday Attendance];
What do I need to do? I have had no success with crosstab queries.

---

Reply from Richard Rost:

The problem is that your field is date specific. You would need to UNION QUERY all seven separate day counts together to get the results you want.

A better solution would be to NOT make fields called [Monday Attendance], [Tuesday Attendance], etc. but to have an actual DATE field and then the attendance count. Then you could use the DAY() function to determine what day of the week it is.