Calculate a percentage then store the results in another table

[skd2726]

I have 2 tables:

PurchaseOrderTbl & LineItemTbl

PurchaseOrderTbl contains information on each PO like vendor, total amount, etc. etc.

LineItemTbl contains information on each line item of the purchase order - I need this separated because different line items can be charged to different work order numbers. I need to be able to run a report each month by work order number to get all of the material charge to the work order number. The user will enter a total amount for the purchase order in the PurchaesOrderTbl and then break out the cost for the work orders in the LineItemTbl

Example:

Purchase Order #123 is for $100

That $100 is then charged to:

WorkOrderNumber 1 - 20%
WorkOrderNumber 2 - 80%

Then store the calculated amount ($20.00 & $80.00) in the field LineItemAmount in the LineItemTbl

(I need access to calculate the Line Item amount) i.e. LineItemAmount = $20.00 (purchase order total amount from PurchaseOrderTble * percentage from LineItemTbl entered by the user)

I don't mean to ramble - just want to draw a clear picture.

 

[Golom]

Here are two queries for you to mess with. The first one computes the Amounts and Percentages allocated to each workorder within each purchase order.

SELECT O.OrderNumber, O.WorkOrderNumber, 
       Sum(O.Amount) AS [WOAmount], 

       ((Sum(O.Amount) * 100) / 
        (SELECT Sum(L.Amount) FROM OrderLines As L
         WHERE L.OrderNumber = O.OrderNumber)) AS [Percent]

FROM OrderLines AS O
GROUP BY O.OrderNumber, O.WorkOrderNumber;

This second query just flips it around and computes the Amounts and Percentages that come from each Purchase Order that contributed to a WorkOrder

SELECT O.WorkOrderNumber, O.OrderNumber, 
       Sum(O.Amount) AS [POAmount], 

       ((Sum(O.Amount) * 100) / 
        (SELECT Sum(L.Amount) FROM OrderLines As L
         WHERE L.WorkOrderNumber = O.WorkOrderNumber)) AS [Percent]

FROM OrderLines AS O
GROUP BY O.WorkOrderNumber, O.OrderNumber

I suggest that this approach may be preferable to storing computed results in your tables but you will need to make that decision based on your own business environment.