asked on Convert columnar data to rows
Thank you for looking at my question,
I have a table that lists the output from manufacturing orders in the format:
Date, Order No, Order Qty, Processed Qty, Status
Where Destination could be 'GOOD', 'SCRAP', 'LOST' or 'QA'
For an order that results in all pieces having the same status the data would look like
16/03/2012, JOB001, 20, 20, GOOD (or SCRAP / LOST / QA as necessary)
Equally, you could get
16/03/2012, JOB002, 40, 20, GOOD
16/03/2012, JOB002, 40, 10, SCRAP
16/03/2012, JOB002, 40, 10, QA
I need to query this data and output one row per job no in the format
Date, Job No, Order Qty, Good Qty, Scrap Qty, Lost Qty, QA Qty
Ideally, where an order has all parts with the same status I would like all the other status quantities to be zero.
Any help you can offer will be greatly appreciated.
I have a table that lists the output from manufacturing orders in the format:
Date, Order No, Order Qty, Processed Qty, Status
Where Destination could be 'GOOD', 'SCRAP', 'LOST' or 'QA'
For an order that results in all pieces having the same status the data would look like
16/03/2012, JOB001, 20, 20, GOOD (or SCRAP / LOST / QA as necessary)
Equally, you could get
16/03/2012, JOB002, 40, 20, GOOD
16/03/2012, JOB002, 40, 10, SCRAP
16/03/2012, JOB002, 40, 10, QA
I need to query this data and output one row per job no in the format
Date, Job No, Order Qty, Good Qty, Scrap Qty, Lost Qty, QA Qty
Ideally, where an order has all parts with the same status I would like all the other status quantities to be zero.
Any help you can offer will be greatly appreciated.
Microsoft AccessSQL
Last Comment
Gary Croxford
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SELECT Date, [Job No], max(Order Qty) as [Order Qty],
sum(iif(Status='GOOD',Orde
sum(iif(Status='SCRAP',Ord
sum(iif(Status='LOST',Orde
sum(iif(Status='QA',Order Qty,0)) as [Qa Qty]
FROM YourTable
GROUP BY Date,[Job No]
Hope this helps (check sintax, I have not Access installed and cannot try)
sum(iif(Status='GOOD',Orde
sum(iif(Status='SCRAP',Ord
sum(iif(Status='LOST',Orde
sum(iif(Status='QA',Order Qty,0)) as [Qa Qty]
FROM YourTable
GROUP BY Date,[Job No]
Hope this helps (check sintax, I have not Access installed and cannot try)
ASKER
Works Perfectly,
Thank you
Thank you
http://www.meadinkent.co.uk/acctranspose.htm
Can you give an overview of the tables where the data is coming from ...