MS Access-> can I SUM order amt detail records and place in a total amt field in order header table?

Question

I have a fully functional database with queries and reports but I don't know how to do programs so I hope I can do this direct in the table defn (similar to how you can put SQL code in Lookup tab). Order table has total amt order field >> is linked with many records in the order detail table. I'd like to multiply unit amt * qty fields for all the items in that order and place in the order header total amt field. Is that possible? thanks!

Answer 1

Sorry, what you want is a calculated field, and you don't put calculated fields in table design. Tables are meant to hold raw data only, and all calculations are performed in queries, forms, and reports, wherever you need to see them. You can't put a formula in the table design to make the calculation you want. Even you wrote code to loop through the tables, making calculations, and updating your Total Amt field, it's still bad design.



You could create a query, and put a calculated field that goes something like Total: [Unit Amt] * [Qty]. I assume your Order table has an OrderID field or something similar because you say you have a relationship. If you include your Order ID in the query, along with any relevant fields from the Order table, you could then click the Totals button, set all your Order table fields to Group by, and then set your calculated field to Sum. Run the query, and you have a single record per order, showing the total amount of the order.

Answer 2

It seems to me that you can make it in a query. Just create a field that performs the multiplication between the 2 fields. You can also try the query Totals button in the created field and see what happens.



Sorry for this incomplete help.



Makiavel

Answer 3

You can if you use a crosstab query with Group By (record key) and sum Amount.