Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004

    Unanswered: Report breakdown by month, quarter, year?


    I am using Access 2000. I have a sales table that contains fields for invoice date and invoice amount. I want to create a report that shows sales totals for a three year period. I'd this report to be in a grid format that not only shows the three year total, but also shows the breakdown by month, quarter, and year.

    I am trying to find the easiest, most efficient way to do this. If anyone can give me any advice to point me in the right direction, or tell me where I can find a good example, I'd really appreciate it.


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    define a query for the rpeort data
    extract the year and month the invoicedate
    in the design dialog drag the fields you require formt he relevant table(s)
    add column InvoiceYear: Year([invoiceDate])
    add column InvoiceMonth: Month([invoiceDate])

    Create a report (or switch to you prexisting development report)
    set the reports data source to the query created above
    set the sort sequence to be aas required
    set report groupings for year and month
    set any headings you want
    set any sub totals and footings you want in the relevant group foooter
    say you wanted to sum the InvoiceValue
    create a control in the month & year footers with its data source set to =sum([InvoiceValue]).
    if you wanto know the number of invoices create a control in the month & year footers with its data source set to =count([InvoiceValue]) & " Number of invoices"
    I always find it helpfull to change the default Access headers & footers to be more meaningful the Header0, footer1 etc - so chaneg them in the properties box

    Te identification of the quarter is a bit more problematical but the above should give yuou a few ideas, hint you need to incorporate a means of identifying the quarter, unless you dataset alread has it in. Use either the switch or IIF command.
    You may have a problem with your financial year if it doesn't change with the calendar year. If this happens you will need to use some code to generate a financial year, again investigate iif function

Posting Permissions

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