Distinct count and overall count on same report line

BobSullivan
3StarLounger
Posts: 235
Joined: 08 Jun 2010, 20:03
Location: Morgantown, PA

Distinct count and overall count on same report line

Post by BobSullivan »

Hello,

I'm writing an access report that shows participation in a recreation program. The database has Events, and these are grouped into Recreation Categories. The database tracks event attendance. I am trying to obtain an overall participation number by Event, and also a unique participation number by Event. For example, if you attended 6 Lunchtime Workshops, your overall participation would be 6, but unique participation would be 1.

The access report is based on a query and calculates overall participation with a simple count function in the group headers. I hide the detail section, and the report is grouped by Recreation Category and within that, Event Title. If I want unique participation, I could change the query to a groupby totals query, but then I wouldn't get overall totals. How can I get both distinct count and overall count on the same line of the report?

Thank you for any help.
Cordially,

Bob Sullivan
Elverson, PA

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Distinct count and overall count on same report line

Post by HansV »

I find it hard to visualize. Could you attach a stripped down and zipped sample database?
Best wishes,
Hans

BobSullivan
3StarLounger
Posts: 235
Joined: 08 Jun 2010, 20:03
Location: Morgantown, PA

Re: Distinct count and overall count on same report line

Post by BobSullivan »

Attached is a zip file of the database. I kept the query that drives the report, and the report.
On the report, where it states need distinct count, is where I need a distinct count of participants for each event.

Thanks.
You do not have the required permissions to view the files attached to this post.
Cordially,

Bob Sullivan
Elverson, PA

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Distinct count and overall count on same report line

Post by HansV »

Thanks! I created a few extra queries and used one of them as the new record source for the report.

Post back if the setup isn't clear.
RecreationTrackingTest1.zip
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

BobSullivan
3StarLounger
Posts: 235
Joined: 08 Jun 2010, 20:03
Location: Morgantown, PA

Re: Distinct count and overall count on same report line

Post by BobSullivan »

Not sure exactly what you did, but it works, so thanks. One other thing which I did not illustrate in the report. I need distinct count by recreation category as well.
Cordially,

Bob Sullivan
Elverson, PA

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Distinct count and overall count on same report line

Post by HansV »

See the attached version.
RecreationTrackingTest1.zip
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

BobSullivan
3StarLounger
Posts: 235
Joined: 08 Jun 2010, 20:03
Location: Morgantown, PA

Re: Distinct count and overall count on same report line

Post by BobSullivan »

Thank you Hans! Works perfectly.
Cordially,

Bob Sullivan
Elverson, PA