Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Thanked 0 Times in 0 Posts

    Count unique days - Access report

    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

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Crystal Beach, FL, Florida, USA
    Thanked 40 Times in 39 Posts
    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
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts