Changing Row Data into Column Data

[Guest]

Hello -

I have a query that potentially results in multiple rows of data for the
same critical fields (Order # and Due Date). Whenever the same Order # and
Due Date appear (concatenated if necessary) I would like the rows to come out
as additional columns and thus have one single result (row) with multiple
columns as necessary.

For example - original data comes out as:

Order# Due Date Op# Operation Description
123 15-Jun-05 100122 PULL PARTS
123 15-Jun-05 120696 VERIFY PARTS
123 15-Jun-05 350999 ASSEMBLE PARTS
123 15-Jun-05 150001 QC INSPECT
123 15-Jun-05 450633 BOX PARTS
123 15-Jun-05 999999 SHIP PARTS
123 01-Aug-05 100122 PULL PARTS
123 01-Aug-05 120696 VERIFY PARTS
123 01-Aug-05 350999 ASSEMBLE PARTS
123 01-Aug-05 150001 QC INSPECT
123 01-Aug-05 450633 BOX PARTS
123 01-Aug-05 999999 SHIP PARTS
124 01-Jun-05 111122 PULL KIT PARTS
124 01-Jun-05 999999 SHIP PARTS
500 15-Sep-05 888888 STOCK SHELVES
505 15-Jul-05 777777 KIT PARTS

What I would like it to come out as:

Order# Due Date Op# Op# Op# Op# Op# Op#
123 15-Jun-05 100122 120696 350999 150001 450633 999999
123 01-Aug-05 100122 120696 350999 150001 450633 999999
124 01-Jun-05 111122 999999
500 01-Jun-05 888888
505 15-Jul-05 777777

Sorry - the copy and paste I did wrapped the last Op# on Order 123 back
under the original row for the first two samples. It should come out as a
single row for each.

Note that Order 123 is listed twice because it has two separate/distinct due
dates. Also note the remaining orders have less operations and thus blank
columns.

Is it possible to create a query that performs this way? Thank you for your
time and consideration.

Regards,

Mike

 

[TPratt]

Sounds like you're looking for a Crosstab Query.

On the Queries window of your database click "New" then "Crosstab Query
Wizard" and it'll walk you through the rest.

 

[Guest]

Thank you for your reply/suggestion.

I have tried a variety of Crosstab attempts but the bottom line is I am not
seeking to apply an aggregate function to any of my fields/data. Even when I
come up with an acceptable Column Heading, that would accommodate multiple
records on a single row, it still creates multiple rows with a single record
instead on a single row with multiple records in the columns.

Yes, I appreciate it is difficult to picture.

I can export the data to Excel and apply a formula in there that I have used
previously; however, I was trying to avoid the extra step as I would also
have to link the Excel file to continue down the query process.

 

[Duane Hookom]

I would think that you need some field that identifies the order of
operations.

There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane that
would work if you don't mind the operations being returned in a single
expression. If that doesn't work, you would need to identify a sort order to
create a crosstab.

 

[Guest]

Duane,

Very interesting formulas within the sample concatenate DB; thank you for
the link to the libraries overall. I will keep plugging away at it.

Thanks again,

Mike