Update Query that uses fields from other tables?

[DarrenReeder]

Hello,



This is my situation.. I have got two table "tbl_Ordered-Products" and "tbl_Orders"..

The tbl_Orders just stores the order information, then Ordered Products has a relationship with the orders and stores what products are in the order..

Here is a screenshot of the relationship between tbl_Order and tbl_Ordered-products..



So, basicly i need to get all of the "Total" fields from tbl_Ordered-Products and add them all together for the Total_Cost field in tbl_Orders.... I know this is done through a query although im just not sure how i would do this...

-thanks for help on this!

[jzwp11]

First and foremost, you should not be storing the totals in either the tbl_Ordered-Products or in tbl_Orders. The totals should be calculated on the fly when you need them; you would use an aggregate query to determine the totals.

The following illustrates the basic structures of the queries you need; you will have to substitute your own field/table names (I tried to use your actual ones as much as possible from your post)

query name: qryOrderDetail
Select OrderIDField, ProductID, Price, quantity, Price*quantity as LineTotal
From tbl_Ordered-Products

query name: qryOrderTotal

Select OrderIDfield, Sum(LineTotal) as OrderTotal
From qryOrderDetail
Group by OrderIDfield