Problem with 'Count' in One-to-Many Relationship

[Barry Skidmore]

tbl_Volunteers
--------------- 1
| VolunteerID |--- tbl_Hours
| Vol_Name | | --------------
--------------- | many | HoursID |
|------|-> ID |
| Vol_Date |
| Num_Hours |
|-------------|


Above indicates the relationship between two tables in a database
that keeps track of the number of hours a volunteer puts in on
various days they volunteer. Vol_Date is the date they volunteer
and Num_Hours is the number of hours they put in on that date.

I have no problem summing the number of hours that all volunteers
put in between a given beginning and ending date. However, what
I am unable to determine is the total number of volunteers that
put in time between those same dates.

If I set up the following query to try to determine the number
of volunteers, it counts each VolunteerID more than once, based
on the number of times each person volunteered.

Instead, I want to only count each volunteer once, irrespective
of how many times they happen to volunteer during a given period.

Query:

Field: Num_Vol: VolunteerID Vol_Date
Table: tbl_Volunteers tbl_Hours
Total: Count Where
Sort:
Show: X X
Criteria: between [begin] and [end]



Thanks,
Barry

 

[Duane Hookom]

Are you expecting to display your results in a report or form? Your solution
might vary depending on how you expect to display your results.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe this will do (use Access 2002 w/ SQL-92 option):

PARAMETERS [Start Date?] Date, [End Date?] Date;
SELECT Count(H.ID) As Volunteers, Sum(H.Hrs) As Num_Hrs
FROM (SELECT ID, Sum(Num_Hours) As Hrs
FROM tbl_Hours
WHERE Vol_Date Between [Start Date?] And [End Date?]
GROUP BY ID) AS H

The above query will give the number of volunteers and the number of
hours they all worked between the indicated dates.

If you're not using the SQL-92 option replace the delimiting parentheses
around the derived table w/ square brackets, like this:

..... [SELECT ID, Sum(Num_ ... etc... GROUP BY ID]. As H ....

Note the period immediately after the right bracket.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiaBxIechKqOuFEgEQKUwQCfURtS7ePNv1EzXGp7tgUuMWcm7SIAn2FU
z9ALTM1wENo+N2HtZoVVkWV9
=YG0M
-----END PGP SIGNATURE-----