Finding totals withing a seven day period

[Tom Cunningham]

If I didn?t want to group student scores by date but rather scores in a given seven day period; how would I go about that? What I am looking to do 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 write the expression by date (3/1/12-3/7/12) but I believe that would require me to write out each date and not automatically provide a count of a seven day period. 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

 

[Bob Barrows]

If I didn?t want to group student scores by date but rather scores in
a given seven day period; how would I go about that? What I am
looking to do 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 write the expression by date (3/1/12-3/7/12)
but I believe that would require me to write out each date and not
automatically provide a count of a seven day period. 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!

This is a relatively simple task in a report IIRC. Do you really need to do
it in the query itself?

 

[Tom Cunningham]

No, it does not need to be in a query. What do you suggest instead.
Thanks,
Tom

 

[Bob Barrows]

No, it does not need to be in a query. What do you suggest instead.
Thanks,
Tom

If I recall correctly, one of the builtin grouping options in an Access
report is "by week" - just go through the report wizard.
Somebody correct me if I'm wrong - I'm not on a machine with Access
installed right now to check myself.

 

[Tom Cunningham]

Thanks for your suggestion. I am not fimiliar with IIRC and would be interested whatever information you might have on how to go about it.

I was looking at my database again and I do have a count of days a student is with us and it seems that I may have figured out another way accomplish what I am looking for. Can I take my queries that I have for students total scores and in the count of days field write an expression < 8 days, which will give me the totals for the first 7 days. Make a copy of the query and change the count of days expression to > = 7 AND < 15, I should get the next seven days. Make another copy of the query and change the count of days expression accordingly and repeat this until I cover the rough number of days I have students, I should get several queries containing all the necessary information. From there, I should be able to write another query using the queries I just created, joining by StudentID (unique number) and create a query which is a complete list of all students and their totals for each 7 day period. Correct?
I can see that this is very labor intensive but in my mind it should accomplish the same thing. Let me know what you think and any suggestions you might have.

Thank you for all your assistance.

Tom

 

[Bob Barrows]

Thanks for your suggestion. I am not fimiliar with IIRC and would be
interested whatever information you might have on how to go about it.

IIRC - If I Recall Correctly

I was looking at my database again and I do have a count of days a
student is with us and it seems that I may have figured out another
way accomplish what I am looking for. Can I take my queries that I
have for students total scores and in the count of days field write
an expression < 8 days, which will give me the totals for the first 7
days. Make a copy of the query and change the count of days
expression to > = 7 AND < 15, I should get the next seven days. Make
another copy of the query and change the count of days expression
accordingly and repeat this until I cover the rough number of days I
have students, I should get several queries containing all the
necessary information. From there, I should be able to write another
query using the queries I just created, joining by StudentID (unique
number) and create a query which is a complete list of all students
and their totals for each 7 day period. Correct? I can see that this
is very labor intensive but in my mind it should accomplish the same
thing. Let me know what you think and any suggestions you might have.

No, that's not the way to do that at all.
Did you try using the Report Wizard? Does it offer a "group by week" option?
Again, I don't have Access on this machine so I cannot confirm it myself. I
just seem to recall that Access reports had this option.

 

[Bob Barrows]

If I didn?t want to group student scores by date but rather scores in
a given seven day period; how would I go about that? What I am
looking to do 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 write the expression by date (3/1/12-3/7/12)
but I believe that would require me to write out each date and not
automatically provide a count of a seven day period. 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!

I can now confirm that Access reports do have the ability to group by week.
Take a look at this:
http://office.microsoft.com/en-us/a...e-values-in-an-access-report-HA001034582.aspx


If it absolutely needs to be done in a query, use the datepart function to
get the week-number of each date and group by it - like this:

select student, datepart("ww",[scoredate]) as Week,sum(scores) as scoretotal
from scores
group by student, datepart("ww",[scoredate])

 

[John W. Vinson]

Thanks for your suggestion. I am not fimiliar with IIRC and would be interested whatever information you might have on how to go about it.

IIRC is an acronym for "If I Recall Correctly".

I was looking at my database again and I do have a count of days a student is with us and it seems that I may have figured out another way accomplish what I am looking for. Can I take my queries that I have for students total scores and in the count of days field write an expression < 8 days, which will give me the totals for the first 7 days. Make a copy of the query and change the count of days expression to > = 7 AND < 15, I should get the next seven days. Make another copy of the query and change the count of days expression accordingly and repeat this until I cover the rough number of days I have students, I should get several queries containing all the necessary information. From there, I should be able to write another query using the queries I just created, joining by StudentID (unique number) and create a query which is a complete list of all students and their totals for each 7 day period. Correct?
I can see that this is very labor intensive but in my mind it should accomplish the same thing. Let me know what you think and any suggestions you might have.

Thank you for all your assistance.

Tom

You can use builtin date functions to get criteria for a range of dates. A
Criterion of

= DateAdd("d", -7, Date()) AND <= Date()

will get all records for the past seven days (including today, if there is no
time component in the field).

= DateAdd("d", -14, Date()) AND < DateAdd("d", -7, Date())

will get the previous week. Just be sure to choose the "WHERE" option on the
totals line in the query grid to keep it from grouping by date.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Tom Cunningham]

Thanks for your help! I was able to get the DatePart function to work within a query and have since grouped the information by week. I went with the query option only because I wasn't as fimiliar with the Report option as I thought I needed to be. I am however having another problem now.
Students scores are tracked 4 times per day in 5 categories. Each of the 4 time periods scores is kept in it's own table (4 total tables of scores). I have been able to complete the query for each of the tables and group scores by week using the DatePart Function. The challenge I'm having now is bring all for of these queries together so that I will have an actual total score for each week. I been able to in the past join the queries using the student's ID. However, when I try that now, I get a huge number of duplicate records (instead of roughly 200 records I'm getting about 3500) in the final query which of course is throwing off the total score. Any Suggestions you might have would be great!
Thanks,
Tom

 

[Bob Barrows]

Thanks for your help! I was able to get the DatePart function to work
within a query and have since grouped the information by week. I went
with the query option only because I wasn't as fimiliar with the
Report option as I thought I needed to be. I am however having
another problem now.
Students scores are tracked 4 times per day in 5 categories. Each of
the 4 time periods scores is kept in it's own table (4 total tables
of scores). I have been able to complete the query for each of the
tables and group scores by week using the DatePart Function. The
challenge I'm having now is bring all for of these queries together
so that I will have an actual total score for each week. I been able
to in the past join the queries using the student's ID. However, when
I try that now, I get a huge number of duplicate records (instead of
roughly 200 records I'm getting about 3500) in the final query which
of course is throwing off the total score. Any Suggestions you might
have would be great! Thanks,
Tom

Without looking at the queries and how they're being joined, it's impossible
to make a suggestion. Try this:
Make new copies of all the queries involved, then eliminate everything in
the queries that does not help illustrate the problem - hopefully that
leaves you with queries that return only two or three fields apiece. Switch
them to sql view and copy the sql of each query into your reply.
Then show us a few sample rows of what each query returns so we can see the
duplicates returned by the final query.