ms access query to find % of invoice amt per plant
I have a table that has the following columns:
invoice Plant Case_Inv_Amt Tot_Inv_amt
3115 02 100 500
3115 02 100 500
3115 02 100 500
3115 05 100 500
3115 05 100 500
4225 02 50 200
4225 05 50 200
4225 05 50 200
4225 05 50 200
etc...............
I need to determine how much of the invoice amount is by plant - so I am looking for the following result:
invoice Plant Amt_Per_Plant Tot_Inv_amt
3115 02 300 500
3115 05 200 500
4225 02 50 200
4225 05 150 200
Or I could use it in this format:
invoice Amt_02 Amt_05 Tot_Inv_amt
3115 300 200 500
4225 50 150 200
I am thinking I need to do a few queries - I need to determine the weighted average for each plant -
Any suggestions
Thanks,
invoice Plant Case_Inv_Amt Tot_Inv_amt
3115 02 100 500
3115 02 100 500
3115 02 100 500
3115 05 100 500
3115 05 100 500
4225 02 50 200
4225 05 50 200
4225 05 50 200
4225 05 50 200
etc...............
I need to determine how much of the invoice amount is by plant - so I am looking for the following result:
invoice Plant Amt_Per_Plant Tot_Inv_amt
3115 02 300 500
3115 05 200 500
4225 02 50 200
4225 05 150 200
Or I could use it in this format:
invoice Amt_02 Amt_05 Tot_Inv_amt
3115 300 200 500
4225 50 150 200
I am thinking I need to do a few queries - I need to determine the weighted average for each plant -
Any suggestions
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great - thanks so much for your help.
use this query
select T.invoice, T.plant,T.Amt_Per_Plant,T.
From
(
SELECT Table1.invoice, Table1.plant, Sum(Table1.Case_Inv_Amt) AS Amt_Per_Plant, Table1.Tot_Inv_amt
FROM Table1
GROUP BY Table1.invoice, Table1.plant, Table1.Tot_Inv_amt
) As T