Populate a table field based on table fields from another table...

[gacollier]

Sorry for the cryptic subject line, but here's what I'm looking for...

I'd like to populate a "total_cost" field within a table called "Orders" by summing the product of the "Qty" field of a table called "Order_Line_Detail" with the "cost" field of a table called "Products". Here's a summary of my tables...

Orders (table)
- Orderno (PK)
- total_cost

Order_Line_Detail (table)
- Detailno (PK)
- Orderno (linked to Orders.Orderno)
- Qty
- Productno (linked to Products.Productno)

Products (table)
- Productno (PK)
- Cost

Again, I'd like "Orders.total_cost" to work out to be equal to (in english): For each "Orders.Orderno" figure out what "Orders.total_cost" by summing the product of "Order_Line_Detail.Qty" and "Products.Cost".

This may be simple, but being new to Access, I can't seem to find an answer.

Thanks in advance.

 

[lespaul]

Try this:

UPDATE ORDERS O SET O.TOTAL_COST = (SELECT SUM(COST) FROM ORDER_LINE_DETAIL OD INNER JOIN PRODUCTS P ON OD.PRODUCTNO = P.PRODUCTNO WHERE OD.ORDERNO = O.ORDERNO)

Leslie

 

[PHV]

Something like this ?
UPDATE Orders SET total_cost = (SELECT Sum(Cost*Qty) FROM Order_Line_Detail INNER JOIN Products ON Order_Line_Detail.Productno=Products.Productno WHERE Order_Line_Detail.Orderno=Orders.Orderno);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gacollier]

Leslie/PHV,

Where do I place this code? Within the table field?

 

[PHV]

Create a query, choose the SQL view pane, paste the sql code posted, execute it with the ! button.
Obviously do the test in a backup copy of your database.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

and PHV's is more correct, I forgot about quantity!

leslie