User entering Date Range for a query with Sum function present

[rwilliams]

Let's see if I can explain this situation effectively...

I've been handed a database that is set up to track tutoring sessions of students. The original creator designed the database for 1 semester. I am trying to make it functional for x number of semesters. So here's my dilemma: I have several reports that are based on queries. I would like for the user to be able to run the report and it prompt for them to enter a date range, which would then output desired results for that range (I thought this would be more efficient than setting up the db to run reports by semester, so that the user would be able to narrow their results).

So in corresponding queries, I am adding the following criteria to the "Date" field:
Between [Enter Beginning Date] And [Enter Ending Date]. The query produces the right results, just not exactly in the way I would like to see them. This is because the query tracks the number of hours per mentor (the person giving the tutoring) and does a sum.

So in my results, it is summing the hours for every change in date occurrence. For example, the results should look like this (on the report):

Student 1
Mentor A xtotal hours

Student 2
Mentor A xtotal hours
Mentor B xtotal hours


Instead what I'm getting is a sum of hours for every different date, as follows:

Student 1
Mentor A xtotal hours
Mentor A xtotal hours
Mentor A xtotal hours

Student 2
Mentor A xtotal hours
Mentor A xtotal hours
Mentor B xtotal hours
Mentor B xtotal hours
Mentor B xtotal hours
Mentor B xtotal hours

And so on...

Maybe I'm approaching this is the wrong fashion? I haven't had much luck searching google or access help... maybe I'm searching with the wrong strings.

Any advice would be greatly appreciated.

 

[rainman89]

looks like u are trying to query a unique identifier in the query. thats why it is repeating for each one.
can we see the SQL of your query?
or can you post a small sample of your db?

 

[boblarson]

If you are doing the calculations in the query, the fields you should have in there are Student ID, Mentor ID, and the Sum (if you include date, then it won't total correctly the way you want. Any other fields you want should be a separate query with this query nested inside of it and the links appropriately set with other tables.

I think I would leave it as you have it now, but set the grouping levels in the report so that you have a sum by date (in the details section, which you don't have to display) and then set the group footers to have the sum function.

Also, I would use a form to select/enter dates so that the user doesn't have to type them more than once if they run that report, or others for which they want the same date, over and over again.

 

[rwilliams]

Thanks for the replies.

I like the idea of doing the sum of the sum on the report and the form for entering dates - I will have to figure those out later.

I'm still confused on how to go about writing the query(ies).

Here's a picture of my query now - maybe that will help:

 

[boblarson]

Looks right to me. Use the report wizard to create the report and watch carefully as you go through it as it presents you with grouping and aggregate option buttons to select things such as whether you want a sum of a field for the details or the grouping or both.

 

[rwilliams]

Just thought I'd update and say that I got the report to work right and thank you so much for all the help.

In the wizard I had it do a sum on the sumofhours field. And in my grouping, I hide the header that included the sumofhours field and only showed the [sum(sumofhours)] field. It shows like I want.

 

[boblarson]

Thanks for posting back with your success. That might help someone else in the future.