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.
Distinct count and overall count on same report line
-
- 3StarLounger
- Posts: 235
- Joined: 08 Jun 2010, 20:03
- Location: Morgantown, PA
Distinct count and overall count on same report line
Cordially,
Bob Sullivan
Elverson, PA
Bob Sullivan
Elverson, PA
-
- 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
I find it hard to visualize. Could you attach a stripped down and zipped sample database?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 235
- Joined: 08 Jun 2010, 20:03
- Location: Morgantown, PA
Re: Distinct count and overall count on same report line
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.
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
Bob Sullivan
Elverson, PA
-
- 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
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.
Post back if the setup isn't clear.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 235
- Joined: 08 Jun 2010, 20:03
- Location: Morgantown, PA
Re: Distinct count and overall count on same report line
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
Bob Sullivan
Elverson, PA
-
- 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
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 235
- Joined: 08 Jun 2010, 20:03
- Location: Morgantown, PA
Re: Distinct count and overall count on same report line
Thank you Hans! Works perfectly.
Cordially,
Bob Sullivan
Elverson, PA
Bob Sullivan
Elverson, PA