Solved

Convert columnar data to rows

Posted on 2012-03-16
4
347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 40

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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