Results 1 to 5 of 5
  1. #1
    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

  2. #2
    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

  3. #3
    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.
    Last edited by pkstormy; 10-16-09 at 00:50.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    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.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,410
    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
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •