Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    67

    Unanswered: Report summary by week

    I have report that have daily sales and I want to subtotal ever seven days. How do I group them?

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    In Sorting and Grouping, you can specify what you want to group on (your date field) and at what interval (a week).

    What version of Access?
    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
    Apr 2009
    Posts
    67
    Thanks I'll try it I'm using 2007

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Instructions for 2007 are difficult to write from memory and I don't have it where I am atm. Have a go, if you can't figure it out, post back
    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

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends on how you define a week
    is that the first calendar week
    based on a fiscal or accounting week....

    if the latter then you may well need a table to define accounting periods
    if its the former then you may get away with using a function to to identidy weeks based, say on the subtracting the curent date from the first day, or day of the first week, in January and (using integer dividion "\" not "/" dividing by 7
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    here a function I wrote Years ago
    Code:
    Function WEEKEND(dat)
    'Author StePhan Mckillen
    'Created 01/07/1999
    'Returns the Saturdays date of a date
    If IsNull(dat) Then Exit Function
       dat = DateSerial(year(dat), Month(dat), Day(dat))
    If dat Mod 7 > 0 Then 
       WEEKEND = dat - dat Mod 7 + 7
    Else
       WEEKEND = dat
    End If
    End Function
    save it to a module
    then in a query do

    WeekEnd:WeekEnd(dateValue)

    it will put the saturday date of datevalue into the Weekend feild
    them you can group your data by the Weekend feild
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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