I am trying to run a report to show the Total Sale amount by customer. The calculations are very simple and they work perfectly on the order form.
Sample:
2 at $200 = $400 UnitTotal
2 at $ 50 = $100 UnitTotal
------------------------------------
$500 SubTotal
The above is computed in a Detailed Orders Subform in a New Orders Form. Only the Quantity and Product ID are stored in a Detail Records Table.
$100 Freight
$ 53 Sales Tax
------------------------------------
$653 Total Sale
The above is computed on a New Orders Form. Since the Freight Charge changes I am storing the Freight Charge in an Orders Table.
When I run a report to show Total Sale by Customer. It computes the UnitTotal and Subtotal portion correctly (pulls the data from a Detail Record Query. But the report Adds the $100 Freight Charge to each detail record so it shows as a Total Sale of $753 instead of $653 (in the example detailed above). I am trying not to store all the totals because I read that you should not store calculations in tables. But I can't figure out how to get the Freight to only count once per order instead of once per detail record. The receipt that I generate from the Form works perfectly but the Summary report doesn't. I hope this makes sense. I am new to Access and am not sure quite how to explain this. If you need anymore information please let me know.
Thanks,
Cathy