How to count items on group header (Desparate in need of help)

[timscronin]

I have a report grouped by state,location, and person. Each person may have more than 1 record. How do I count the number of people at a location? If I do a count on the persons id field which is in that group header it still counts their detail records. So if I have 3 records, two belonging to John Smith, and one to Martha Helms inside of a location, I get a count of 3 people, even though there were only 2. Help (I am sure I am missing something easy)

 

[LetsGiveItAShot]

Probably not the "right" way to do this .. but what works works, right? Here's what I'd do:

Create a select query grouped by State - Location - Person against your original table (query, whatever) - What this does is in essence create 1 record for each person within location

Create a second select query based on the above, grouped by State - Location - Count([person]) - counts the number of people in your above locations

Join the above in a query to your original table, then base the report on your newest query.

Good luck - since you said "desperate" I'll add the e-mail notification if you have any questions.

John