If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Access > Report by Week (7 days from initial date)

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Oct 2009
Posts: 3
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
Reply With Quote
  #2 (permalink)  
L33t Helpa Munky
Join Date: Nov 2007
Location: Adelaide, South Australia
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"?
Owner and Manager of
CypherBYTE, Microsoft Access Development Specialists.
Microsoft Access MCP.
And all around nice guy!

"Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
"...teach a man to code and he'll be frustrated for life! " -- georgev
Reply With Quote
  #3 (permalink)  
Join Date: Dec 2004
Location: Madison, WI
Posts: 3,925
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:


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.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Last edited by pkstormy; 10-16-09 at 00:50.
Reply With Quote
  #4 (permalink)  
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.
Reply With Quote
  #5 (permalink)  
Jaded Developer
Join Date: Nov 2004
Location: out on a limb
Posts: 12,335
isn't their a function whic returns a week number within Access..?
as you don't care about the absolute value it should be good enough
you may need to group by year and week number to keep rows in the same order

if you can't live with that order, which runs form the first week in january then you will have to consider making your own calendar table
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On