Finding Totals within a specific date range

[Tom Cunningham]

Hello,

I have an Access Database which is used to record student behavior scores each day. I have been able to create queries easy enough to report students total scores. At this time I would like to create queries so that I can report students total score within a given date range. As I mention, I record each score by date already but, I?m having trouble writing the expression so that it will only total the scores between the specified date ranges.

Thank you in advance for your assistance!

Tom

 

[Bob Barrows]

Hello,

I have an Access Database which is used to record student behavior
scores each day. I have been able to create queries easy enough to
report students total scores. At this time I would like to create
queries so that I can report students total score within a given date
range. As I mention, I record each score by date already but, I?m
having trouble writing the expression so that it will only total the
scores between the specified date ranges.

Thank you in advance for your assistance!

Tom

There are two ways to filter a query's results: a WHERE clause and a HAVING
clause. The WHERE clause, if present is always used. The HAVING clause is
used only in grouping queries.
In grouping queries, the WHERE clause filters the data BEFORE the
aggregation (grouping) occurs. The HAVING clause filters the results of the
aggregation/grouping, AFTER the grouping occurs. It is important that you
understand the implications of these behaviors, but if you can just remember
to only use HAVING for aggregated fields (SUM, COUNT, etc.) you will be 90%
there.

From your description, you want to use the WHERE clause to filter the dates.
The problem is, the Access query builder defaults to using HAVING when
grouping is involved. You have to be aware of this and change the selection
in the "Total" row to "Where" when you want your filter criteria to be
evaluated in the WHERE clause (before aggregation) rather than the HAVING
clause. Switch the query to SQL View to be sure your criteria are in the
correct clause, just to be sure. Your sql should be similar to:

select student, sum(score)
from studentscores
where scoredate >= #3/1/2012# and scoredate < #4/1/2012#
group by student

This sql instructs the query engine to assemble a resultset of each
student's scores within the date range, and then group the result and
perform the aggregation by student.

 

[Tom Cunningham]

Thanks for your help, this works nicely. I do have a second questions that would really help me out.

Similarly, if I didn?t want to group student scores by date (i.e. not equal to 3/1/12) but rather scores in a given seven day period; how would I go about that? What I am looking to do at this point is to evaluate scores every seven days, comparing the total score of the first seven days to the next seven days and so on.

What I would like to end up with is a Week One (first Thursday) with totals column, Week Two (second Thursday) with totals column, Week Three (third Thursday) with totals column and so on. As students move through, this will give me the ability to see if students are making improvements from week to week.

I realize that I could use the suggestion you?ve previously given me but that would require that I write the expression by date rather than every seven days and I would not be able to look at it in an ongoing manner. Lastly, I would like that this not require me to write an expression each week which would require my attention. But rather that it would be something that anyone could go to and either print or view without my attention.

What do you think? Would this be possible?
Thanks in advance for your help!

Tom