Calculations in Reports - adding two totals

[Katherine]

I think the easiest way for me to explain is by actually giving example of
queries I've created adn what I need and what I'm getting.

What I need is for the Total Item Fee (multiple items) to be added to the
Total Postage Bank Fees (one time charge – not added to each item)

Problem is Total Postage/Transfer Fee is being added multiple times – based
on how many items are in order instead of adding it only once.

Example:
Order # 17753 has 2 items (see table below for details)
Item One Fees: $70
Item Two Fees: $80
Grand Total Item Fees: $150

Total Postage Bank Fees: $20 (charged 1x per order, not by items)

I need a Report with a field that shows Total Order Fee: $170

I have created two queries:
1) Postage and Transfer Fees: - order table – one time fee per order

Order # Postage Fee Bank Transfer Fee TotalPostageBankFees
17753 10.00 10.00 20.00


2) Total Item Fees per Order – item table – multiple items and fees

Order # Item ID Photo Permission Division Special Handling
TotalItemFees
17753 101-Q 50.00 10.00 10.00 10.00 80.00
17753 102-Q 20.00 10.00 10.00 30.00 70.00

I've tried creating an unbound field in report and building calculation
=sum(etc.), but it's not working.

I've tried creating a 3rd query (combining queries) but it still adds
postage/bank fee for each item instead of one time per order.

I'd greatly appreciate any help.

Thank you,
Katherine

 

[Marshall Barton]

I think the easiest way for me to explain is by actually giving example of
queries I've created adn what I need and what I'm getting.

What I need is for the Total Item Fee (multiple items) to be added to the
Total Postage Bank Fees (one time charge – not added to each item)

Problem is Total Postage/Transfer Fee is being added multiple times – based
on how many items are in order instead of adding it only once.

Example:
Order # 17753 has 2 items (see table below for details)
Item One Fees: $70
Item Two Fees: $80
Grand Total Item Fees: $150

Total Postage Bank Fees: $20 (charged 1x per order, not by items)

I need a Report with a field that shows Total Order Fee: $170

I have created two queries:
1) Postage and Transfer Fees: - order table – one time fee per order

Order # Postage Fee Bank Transfer Fee TotalPostageBankFees
17753 10.00 10.00 20.00


2) Total Item Fees per Order – item table – multiple items and fees

Order # Item ID Photo Permission Division Special Handling
TotalItemFees
17753 101-Q 50.00 10.00 10.00 10.00 80.00
17753 102-Q 20.00 10.00 10.00 30.00 70.00

I've tried creating an unbound field in report and building calculation
=sum(etc.), but it's not working.

I've tried creating a 3rd query (combining queries) but it still adds
postage/bank fee for each item instead of one time per order.

The report needs to have a group (VBA window - View menu,
Sorting and Grouping) on the order number field. In the
group footer section use a text box with an expression like:
=Sum(TotalItemFees) + TotalPostageBankFees

If you also want a grand total in the report footer section,
then add a text box (named txtRunBankFees) to the group
footer. Bind it to the the TotalPostageBankFees field and
set its RunningSum property to Over All. Then the report
footer text box can display the grand total by using the
expression:
=Sum(TotalItemFees) + txtRunBankFees