Subtotals, Formulas & Totals - Query, Form or Report?

[Dorothy]

Where is the best place to perform calculations? I am working on a fairly simple database. The tables and relationships are set, and confirmed correct.
To summarize, I have a customer table (One to Many) Menu table, Items table and Items detail table(Joins Menu and Items).
I have a form with subform to record Items detail per Menu.
I then need to calculate the total cost of items ordered: Line Item Total=[unitprice]*[quantity]. Subtotal= SUM([Line item total])
This is then used to calculate tax,service charge and grand total:
Tax=[Subtotal]*0.1,
Service=[Subtotal]*0.2
Grand Total= [Subtotal]+[Tax]+[Service]

I want to perform these calculations on the sub total of each Menu, not on each line item of the order.


I know calculated fields do not get stored in tables.
Should I put all the calculations in the footer of the form?
When I try a query, it won't let me use the calculated field Subtotal in the other calculations.
Should I put the calculations in a report?
I will need to eventually pull data in several formats in reports - Individual Menu, Totals by day, totals by week,etc. and don't want to repeat the calculations on each report.

[jzwp11]

It depends on what you want to do. If you want to print the information, then use a report. If you want to view the information (and have the ability to edit the information) then use a form (sub & grand total calculations would be in the form's footer).

[Dorothy]

Thanks for the quick response. If I put the calculations in the form footer, can I access that information in reports?

[jzwp11]

If I put the calculations in the form footer, can I access that information in reports?

You may be able to do that, but generally, you would just add similar controls to the report to handle the subtotals/grand totals. Depending on what information you push to the report, those controls would most likely be in a group footer.

[Dorothy]

The calculations I'm performing are actually a little more complex (involve if and round to get tax to compute properly) and would rather not have to rewrite the formulas on every form and report. Plus, it seems like the point of using Access would be to eliminate redundancy and the errors that can result.
Would it work to write a query that takes fields from the Menu Table, the Menu Subtotal on the sub form, and add calculated fields for tax, service and grand total?

[jzwp11]

A query can get values from a form but they would be constants for each record in the query results.

You could do some grouping queries and then do the calculations in fields in that query for those items that apply to the group of records such as the sales tax. You can then use that as the record source for your form or report, but of course you will lose the detail records once you do the grouping.

..would rather not have to rewrite the formulas on every form and report. Plus, it seems like the point of using Access would be to eliminate redundancy and the errors that can result.

You typically would have 1 form and 1 report (that would show detail records); so at most you would have to enter the formulas twice.

I will need to eventually pull data in several formats in reports - Individual Menu, Totals by day, totals by week,etc. and don't want to repeat the calculations on each report.

You could actually duplicate your report that has the detail records, remove the detail section and adjust the grouping levels to get the various total reports you want.

[Dorothy]

Thanks, this is what I needed to know. Will now try writing the reports with the formulas.

[jzwp11]

You're welcome.