Using a Total from a Sub Report

J

Jen

I have a report for Orders, and a subreport for each Order for Invoices.

The subreport shows the total amount for Invoices against each Order.

The Orders are grouped by Area. On the Area header/footer I have an Order
total. I now want to show the total of ALL Invoices by the Order total.

Please can someone tell me as simply as possible how to do this?

For ease I will call my main report ORDERS and my subreport INVOICES.

Any help is greatly appreciated - I have searched through relevant questions
but have not found an expression that works!
 
D

Duane Hookom

I would create a totals query that sums the invoice amount and groups by
area. You can then add this query to your main report's record source and
join the area fields. Drop the "InvoiceTotalAmount" field into the grid so it
is available in the main report.
 
J

Jen

Many thanks Duane

Unfortunately I have no idea how to create a totals query! I have created a
query joining Orders and Invoices (tables) but if one Order has 6 invoices
then the Order appears 6 times, and I don't know how to show just one Order
with an Invoice total. Sorry!
 
D

Duane Hookom

I don't have your table structures in front of me so I can only guess what
your query might look like.

If invoices are related to a single order record and order records are
related to a single area record then your totals query should include these
three tables. Join the appropriate field. Change the query to a totals query
by selecting View->Totals. Add the Area primary key field to the grid and set
its Total to Group By. Add the InvoiceAmnt o the grid and set its Total to
Sum. Save this query with a name like qtotAreaInvAmtTotals.

Then open the report in design view and go to the record source designeer
(query) and add the qtotAreaInvAmtTotals query and join the Area fields. You
can now add the total amount of invoices by area to your report's fields.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top