troubleshooting Question

Convert columnar data to rows

Avatar of Gary Croxford
Gary CroxfordFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessSQL
4 Comments1 Solution458 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros