Query isn't totalling correctly

[snowygirl1]

I have a query which is pulling names of volunteers.
The DB contains the dates they have volunteered and how many hours for each of those dates. Essentially volunteers need to undertake a certain number of hours per month etc.

The query is pulling the volunteers names and how many hours they've done along with the date they volunteered.
I need the query to be able to be for specific date periods so I added the date field of when they volunteer and then set that to criteria "Between [Start Date DD/MM/YY] And [End Date DD/MM/YY]" so that when I launch the query it prompts for the date period I need to view.

The query & report appear to work fine but when we checked the totals they aren't correct.
We have some volunteers with the same first names, and we are finding the query is summing people with the same first name together. E.g. we have 3 x John's but only one is displayed in the final results with the total hours of all volunteer work from all 3 people named John.
I cannot work out why it's doing this.


Can anyone point me in the direction of how I fix this?

[Micron]

Sorting or using aggregate functions on such a field is not advised, for exactly the problem that you are experiencing. You need to use a field that uniquely identifies the individual. Without knowing what your table looks like, I can only guess what that might be. You should have a table for only volunteer names and pertinent data (such as DOB, phoneNum, etc) without any other data such as hours worked. In a query, you would join that table to the hours table and link via the unique field from the names table to the hours table, which would also have that identifier, but once for every record of hours worked.

[snowygirl1]

I've just looked closer and the query is actually working and I can see that there are 3 x John's.

It's in the Report that the problem is occurring because I can see in the query there are 3 x John's. But the report only shows 1.

I have a main table which contains all the persons details DOB etc. It is then linked to a volunteer table where we record all volunteer hours. They are linked by a volunteer ID number.

[Micron]

Are you grouping by the name in the report, or by the volunteer ID number? Or something else?
I presume you mean 3 different Johns, not just 3 John records.

[snowygirl1]

Ok I think I've fixed it.
I've added the ID to the query & report and it seems to be working now.
But now i cannot remember how to set the sort order so that the list is in alphabetical order by surname rather than by the ID.

[Micron]

Grouping and sorting button on ribbon. Or right click on report and choose that from the resulting context menu. Reports have their own sorting feature, so it doesn't matter if you sort the query or not.