Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    27

    Unanswered: Report Totals by Month

    Hi guys

    I have a small database for recording Purchases and Payments.

    Each record has a "Date" field and an "PaymentAmount" or a "PurchaseAmount" field.

    What I want to create is a report with the end of year totals.

    For example, the headings what look like:

    Month Total Payments Total Purchase

    How can I run a query to calcualate the total payments, and total purchase for each month?

    I'm using 2002. Many thanks in advance!

    Adam

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    In your query, use an expression to format the date field by month-year (ie. ByMonth: Format([MyDateField],"mmm-yy") and then use that field to group by in the summarization query. To calculate by year, format the date field as year only (ie. ByYear: Format([MyDateField],"yyyy"). To specify the range for the date field, add in the date field column (unchecking the Show checkbox), change it to "where" (in the Total row if in the summarization is set) and put in the date criteria in the criteria row (ie. Between [Enter Start Date:] and [Enter End Date:]).
    Last edited by pkstormy; 08-17-08 at 16:21.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2008
    Posts
    27
    Hi PKStormy

    Thanks very much for your help so far.

    (ie. ByMonth: Format([MyDateField],"mmm-yy") - should I add this as a new field in the query? Or do i put the under the current "Date" field?

    Thanks
    Adam

  4. #4
    Join Date
    Aug 2008
    Posts
    27
    Hi PK

    Thanks so much - I've got this working now :-)

    Just one other query - when I have this in a report, and sort ascending by month, the months are obviously alphabetical, rather than chronological - is there any way round this?!
    Thanks
    adam

  5. #5
    Join Date
    Feb 2002
    Posts
    108
    Just add another field to your query:

    sortMonth:format([yourdate],"m")
    and make it "ascending" sort and place this on the right side of the year sort.

Posting Permissions

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