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.

 > Report by Week (7 days from initial date)

 zharditto 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
 StarTrekker 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
 pkstormy Moderator 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: 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. __________________ 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.
 zharditto 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.