Gary Croxford
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT Date, [Job No], max(Order Qty) as [Order Qty],
sum(iif(Status='GOOD',Orde r Qty,0)) as [Good Qty]),
sum(iif(Status='SCRAP',Ord er Qty,0)) as [Scrap Qty]),
sum(iif(Status='LOST',Orde r Qty,0)) as [Lost Qty],
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 ...