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