Summing on a report 1

[janetlyn]

I have a table that I input invoice amounts into along with the month of the invoice. There is a report using this table that adds up the invoices for each month and each line has:

Month/Year $$$$$$$ (total for each month)

At the end of the report is gives a total for all invoices since inception of business and a monthly average of how much we are billing each month.

My boss would like data at the bottom of the report that shows each year and how much was billed each year. So it would look like this

1999 Total Billings $$$$$$$$$
2000 Total Billings $$$$$$$$$

I have absolutely no idea how to accomplish this. Any ideas? Thanks, JL

 

[lupins46]

Use unbound textboxes with DSum() functions:

= Dsum("[invoiceAmount]", "invoicetable"," Year(invoicedate)= 1999")

Use your own field and table names.

 

[janetlyn]

That would mean once a year I would have to add another textbox correct? Is there some way to make it an automatic thing? Thanks for taking time to help me. Janet Lyn

 

[lupins46]

You would need to create a subreport based on a Grouped query. This sounds pretty simple in the scenario you have described.

You just need two fields in the query:

InvYr:Year(InvoiceDate)
InvoiceAmount

Turn this into a Totals query andChnage the GroupBy to Sum for the amount field.
Then Base a new report on this query, which lists the results.
Add this report as a subreport to the main report's Report Footer section.

 

[janetlyn]

I was able to do everything but the sub-report. Help did not give me any answers either. That stupid paperclip. Can you please explain the procedure. Thanks again, JL

 

[janetlyn]

Lupins, could you please explain how to do a sub-report? Thanks, JL

 

[lupins46]

Create a normal report which lists your totals.
Don't bother with any Page header stuff as it will be ignored. If you need column headings put them into the report header.
Save the report.

Open your main report in design view.
Make sure the Report Footer is visible ('View' menu if necessary).
Display the Toolbox. Make sure the Wizard button is set on.
Clic the Subform/Subreport control and then draw out a box in the report footer. When you let the mouse go just follow the Wizard. You will select the report you saved above.
You will need to do a bit of resizing but that should be it.

 

[janetlyn]

Thank you. That worked great. Appreciate all your help, Janet Lyn