Invoice totals & summary sheet

[b_lwalker]

I've written a database to take care of our company's invoicing each
month. It works just fine, but I'm having trouble figuring out the
logic to get the summary reports to work.

What I have is:

Table: tblInvoice - Invoice ID (1st key), Invoice Number, etc
Table: tblDetails - Details ID (1st key), Invoice ID (2nd key),
Details, Amount
Table: tblDisbursements - Disbursements ID (1st key), Invoice ID (2nd
key), Disburement, Amount

tblDetails and tblDisbursements are subforms on the main Invoice form,
and all totals fields are calculated. I print each invoice in a
report that also has calculated fields for the totals (i.e. each sub
form has a field that Sum([Amount]) with which I do my totals).

What I need to do is print a summary sheet for each month showing what
invoices were issued. I can get all the invoices to list, but I can't
figure out how to get their totals calculated.

I hope I've made myself clear - any ideas?

 

[Allen Browne]

Okay, so you have 2 one-to-many relations, like this:
tblInvoice => tblDetails (on InvoiceID)
tblInvoice => tblDisbursements (on InvoiceID)

There's a couple of ways to get the totals.

Method 1: Report with subreport and Running Sum
=====================================
1.1 Create a query using tblInvoice and tblDetails.
Use this as the source for the main report.

1.2 Add a text box to the Report Footer to sum the amount:
=Sum([Amount])

1.3 Add a subreport to list the disbursements.
In the report footer of the subreport, add a text box for the subtotal:
=Sum([Amount])

1.4 To get the total back onto the main report, see:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

1.5 Use a text box with its Running Sum set to
Over All
so it accumulates the total over the entire report.

Method 2: Use a subquery
===================
If you just need the totals (don't need to list each invoice and
disbursement), you could do the whole thing in a query. This might cause
problems if you then use it as the source for a report.

The idea is to use a Totals query based on tblInvoice and tblDetails.
Group by InvoiceID.
Sum Amount.
Then add a subquery that returns the SumOfAmount from tblDisbursements for
the InvoiceID. For some examples of how to work with subqueries, see:
http://allenbrowne.com/subquery-01.html

 

[b_lwalker]

Thanks Allen - very much appreciate your time and knowledge.

 

[b_lwalker]

Thanks Allen - very much appreciate your time and knowledge.