# Thread: Report by Week (7 days from initial date)

1. Registered User
Join Date
Oct 2009
Posts
3

## Unanswered: Report by Week (7 days from initial date)

Hello all, I am having a small issue with report and I would appreciate your advices. I have the following fields that I want to sum-up by week (7 days) and not 7 calendar days.

Date - Total 1 - Total 2 - Total 3

when I am doing a sum and order by week it is taking the date as a calendar date of the week and making the summarize..

For example if the date is 01/10/2009 and I want to sum it up to 7 days, what I need as a result is a total for 01/10/2009 - 08/10/2009 but access is considering it as 01/10/2009 - 04/10/2009 as a week and start monday 05/10/2009 as a new week.

my report must summarize entries from year 2008-2009 weekly based on the initial date and not calendar days.

thanks a lot

2. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
How are you defining a week in your calculations? Sounds like you're using DatePart with the "w" or something and you want to be adding seven days with DateAdd() instead.

How are you calculating your "week"?

3. Moderator
Join Date
Dec 2004
Location
Posts
3,928
I will often add in an expression (and group on) to the summarizing query such as:

ForWeekOf: Format(([MyDateField]-Weekday([MyDateField]))+1,"mm/dd/yyyy")

This essentially returns the first date of that week (ie. Sunday). Thus, all totals are based upon weekly totals (sun to sat) since it's grouped on this expression and the user will see the Sunday date in the query.

I then apply any date criteria needed under the MyDateField column in the query (where statement.)

They then see totals like this:

ForWeekOf......CountOfSubjects
10/4/09............23
10/11/09..........42
10/18/09..........29
10/25/09..........53

If you wanted to group on Year (which is unecessary in the example above), you could use this expression to group on by year:

ForYear: Format([MyDateField],"yyyy')

otherwise if 2008-2009 is the time period criteria, under the MyDateField you'd add the criteria: Between #01/01/08# and #12/31/09# (or a better option might be to prompt the user for the date range such as: Between [Enter Starting Date:] and [Enter Ending Date:]

You'll have to decide if it matters if Sunday/Saturday is included in the totals. For us, it doesn't matter and typically there's no transactions on the weekend (which we'd want if there was) so it doesn't scew any Mon-Fri needed totals (although I'd think you'd also want to count in Saturdays/Sundays like we want to). If not, you can revamp the ForWeekOf expression above to start with Monday and then create additional expressions for your criteria (although I think this is the more complicated method to go about it.) Still, if this were the route I "had" to go, I might consider a query within a query. One for the criteria and the other to sum and group the expressions.
Last edited by pkstormy; 10-16-09 at 00:50.

4. Registered User
Join Date
Oct 2009
Posts
3
Thanks pkstormy for the answer but I am a bit confused with the expression.

Let me simplify my problem for you:

I have two tables

Table 1: Id, Name, Starting, Type, Location

Table 2: Id, Name, Date, OutX, OutY, OutZ, Notes

In my report, I am summarizing on a weekly basis the data by Date, so I have the "Date Header" with the summary (totals for outx, outy etc.. with some weekly calculations) and in "Detail" that is supposed to includes 7 lines for the 7 dates details but that's not happening

for example:

Table 1: Id, Name, Starting, Type, Location
1, Consata, 21/8/2009, Blue, WR

Table 2: Id, Name, Date, OutX, OutY, OutZ, Notes
1, Consata, 21/8/2009, 5, 6, 7, bla bla
2, Consata, 22/8/2009, 8, 9, 15, bla bla
3, Consata, 23/8/2009, 58, 61, 16, bla bla
4, Consata, 24/8/2009, 16, 56, 67, bla bla
5, Consata, 25/8/2009, 75, 106, 57, bla bla

the problem is that with the normal weekly sorting I am having
a summary with

date 21 Till 22/8/2009 as as start and then 23 Till 29/8/2009 etc...

so as you see as 28/8/2009 is a Saturday, it is considering Sat and Sun as the first week and the second week starting from 23 but for me I need the first week to be from 21/8/2009 till 28/8/2009, 2nd from 28/8/2009 till 3/9/2009 etc....

Thanks again for your very kind cooperation
Last edited by zharditto; 10-16-09 at 04:08.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692