Count unique days – Access report

Topic

New Reply

Hi, I am trying to count the number of unique days in a report, grouped first on ClientID, then on Date (by Day), the Detail under Date is a listing of events and times that take place on each of those days. I have section headers and footers for both ClientID and Date that display a count of events (via =Count(*) or =Count([ClientID]) ) or sum of hours (e.g. =Sum([hours]) ) from the detail. However I can not work out how to include a count of the number of days per client. All attempts just count the number of events, not the number of days on which there are events (for that client). I’m probably missing something simple and I just can’t see what it is!

The underlying query (if this matters) is based on several tables containing the client and event tables, through an “attendances” table as the relationship between clients and events is many-to-many. All other aspects of this report work nicely.

Access 2010

Regards Roger

Reply | Quote

Replies

Basically you first need a query that returns a single record for each Client and Date, then that is the source for your final query. Something like this:

SELECT ClientID, Count(EventDate) AS UniqueDays FROM (SELECT DISTINCT ClientID, EventDate FROM Events) GROUP BY ClientID

Reply | Quote