summing unique records in report (1 Viewer)

hockey8837

Registered User.
Local time
Today, 00:59
Joined
Sep 16, 2009
Messages
106
Hello,
I'm trying to get a report to give me a total count of volunteers for the year. I have an overall count, because for each event I enter the total bodies that were there. My problem starts when I try to count individuals.

My DB is designed so that I can look at a contact and see all events they've attended, or, look at an event and see all contacts who attended. I don't always get every person who was at the event to sign in (especially if they're children), but I do get a final head count and record that #. Additionally, I may have repeat volunteers, who come to more than one event over the year.

So, now I want to get an idea of how many individuals I had over the year. I know it will still be a rough #, since some events with schools had over 100 people, but I only have the names of the handful of teachers actually entered into the DB. I think it will come down to something like:

Code:
(Volunteer Number for all events-Total ContactID)+(Unique Contact IDs for the year to eliminate repeat volunteers)

So, how do I get this into a query or, better, a report?
 

branston

Registered User.
Local time
Today, 04:59
Joined
Apr 29, 2009
Messages
372
Do you have a table with all the names from all the events? If so couldn't you just put that table in a query and use the 'group by' function to give you a unique list of names? Then use another query to count how many records there are?
(There are probably nicer ways of doing it but thats how I would initially go about it!)
Hope that helps
 

vbaInet

AWF VIP
Local time
Today, 04:59
Joined
Jan 22, 2010
Messages
26,374
From the sound of things you have a junction table between Events and contacts. From that table you can get a count and eliminate the duplicates. Even if you haven't got a junction table (which you should), we can still work with that. So from the query you have set up, these are the steps to achieve what you want:

1. Create a query to include only the ContactID
2. Put the DISTINCT clause in the sql of the query to eliminate duplicates
http://www.techonthenet.com/sql/distinct.php
3. Perform the Count based on the values.

You can now join this query to any table or query you would like to use to get maybe the contacts name, address, number etc... Join via ContactID.
 

Users who are viewing this thread

Top Bottom