Stuck on what should be a pretty simple issue...

[badbox29]

Hello all,

I am very new to the forum. I have been trying to learn my way around Access for a month or so and have recently become stuck on what I think should be a pretty easy thing to do. I have attached the database I am building. It simply holds a list of regions and scholars for an association I am helping. What I need to be able to do is this:

Run a report that shows the average hours of community service, per region. It has to be based upon the number of scholars in the region, not the number of entries made to record community service. Several service entries can exist per scholar. A good example of my problem can be found by opening the "find duplicates for service details" report. In there, you will see three entries for Region 2. The average seems to be calculated by the number of entries, but we need to know the average based on the number of individuals that performed service within the region. The current calculation is (20+30+8)/3=19.33333333. Since two of the three entries are by the same person, it needs to be (20+30+8)/2=29.

Does this make sense?

Thanks for whatever help you folks can offer. I've been frustrated by this long enough! Oh and sorry for the .zip attachment. Not sure if that's frowned upon here, but I tried uploading the database and was told it was too large. It's 904kb after repair/compact. Limit is 500kb.



Chris

[badbox29]

Ok, I fixed it. I guess it just took me writing out the problem and walking away from it for a while. What I did to address it was to create a query which sums the hours of service performed by each individual and includes the region of which they are part. Then, I ran a report that sorts by regions from that query and averages based on the number of unique individuals. It works... only thing is now, the report shows numbers for the regions (1, 2, 3) instead of the name of the region. Guess I get to work on that now:-)