Solved

Convert columnar data to rows

Posted on 2012-03-16
4
289 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:Crxfrd
4 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 37728625
Did you try to add a "transpose" query ?
http://www.meadinkent.co.uk/acctranspose.htm


Can you give an overview of the tables where the data is coming from ...
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 37728732
Look example
DBtrans.mdb
0
 
LVL 8

Expert Comment

by:gpizzuto
ID: 37728764
SELECT Date, [Job No], max(Order Qty) as [Order Qty],
sum(iif(Status='GOOD',Order Qty,0)) as [Good Qty]),
sum(iif(Status='SCRAP',Order Qty,0)) as [Scrap Qty]),
sum(iif(Status='LOST',Order 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)
0
 

Author Closing Comment

by:Crxfrd
ID: 37728769
Works Perfectly,

Thank you
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now