Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009

    Unanswered: Group total on report

    Monthly report subtotal weekly
    I have report that I want to group monthly and then weekly where the week starts beginning of each month. For example for the month of June I want to subtotal in the following
    And the next month the same way. Is this possible in access.

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    Everything is possible in Access... it all depends on how much time you want to throw at it!

    You can group by week easily in grouping options in reports. I am not sure what your extra needs are;

    where the week starts beginning of each month
    I don't understand this... at the beginning of every month is a week. In fact there are four of them in every month... sometimes more! ^^
    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
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    You can do this with a user function that takes a date and returns a value according to the day value of that date. You then group on the function's return value. It means adding a column to the report's underlying query.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #4
    Join Date
    Apr 2009
    but how do i do that

  5. #5
    Join Date
    Dec 2004
    Madison, WI
    In your query for the report or your query which groups/sums, INSTEAD of using the actual date field, put in this expression:

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

    This will create a "grouping" type field which will hold the first day of the week for the "MyDateField" date field. (it's all about formatting the date field to create "grouping" type fields to group of off for totalling.)

    You can do the same for month by just formatting the MyDateField as: "mm/yyyy"
    MonthlyTotals: Format([MyDateField],"mm/yyyy")

    and again, group on this field in either your query or report.

    Make sure your query doesn't have any other fields which would through the totals off if you're grouping/sum it (ie. don't include the MyDateField as a field by itself - but you can use it for criteria for example to limit the records for a certain date range - just uncheck the "Show" row for MyDateField or set it to "Where" if grouping/summing in the query)!!

    If you need the EndDayOfWeek, check the code bank as there's a word document in one of the posts which shows different expressions you can get date values (I don't recall what it is off hand.) You just need to add it in the query and group on it like the FirstDayOfWeek expression if the query groups/sums.

    If your query groups/sums, simply group on the FirstDayOfWeek field (and EndDayOfWeek) OR MonthlyTotals field (or however you decide to format the date field). If it doesn't group/sum, you can still use the expressions above in the query to create a "grouping" type field for the report, otherwise, if you group/sum the query, make sure to design the report to reflect the totalling fields.
    Last edited by pkstormy; 06-28-09 at 00:23.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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