Automate number used

[rookiedev]

Can anyone help me with this? I think I need a series of queries to change a value in the Transactions table based on record totals from another table but I'm not sure how to go about it. Here is the Transactions Information collected when the Products are purchased:

TransID TransDate ProductID POID Price Ordered Received Sold
1447 3/31/01 AA160D 546 $1.05 105 105 15
1448 3/31/01 AA046B 546 $0.94 60 60 47
1449 3/31/01 AA046C 546 $2.98 252 252 24

This is the Workorder Parts information collected as the items are "Sold"

WorkorderPartID Workorder ID Part ID Quantity Unit Price
21 4146 AA943Z 2 $0.41
22 4147 AB955M 1 $6.60
23 4147 AA094D 1 $1.18

I think what I have to do is create a query that will store the summary values of the Quantity in the Workorder Parts table. Then I get lost because each items quantity has to be added to the Transactions "Sold" field and many times the quantity "Sold will exceed the quantity we have purchased on a single PO. I can not figure out how to set this up.
Does anyone have any suggestions?
RookieDev

 

[rac2]

To me the Transactions table looks like a PurchaseOrderLineItems table. It should not have a Sold column because the number sold is not something involved in purchasing.

It looks like ProductID in the Transactions table and PartID in the WorkorderParts table are foreign keys referring to codes for the items you buy and sell. The item code might be in another table that you have not mentioned; I will suppose it is named Parts with a key column for the item code named part_id

I think you need to make a query that summarizes the number of items received and the number of items requested. Summarize by the item code. So-


SELECT p.part_id,
SUM(t.Ordered) AS "Number Ordered",
SUM(t.Received) AS "Number Received",
SUM(wop.Quantity) AS "Number Sold"
FROM Parts p
LEFT JOIN Transactions t ON p.part_id = t.ProductID
LEFT JOIN WorkOrderParts wop ON p.part_id = PartID
GROUP BY p.part_id


Hope this helps.