Formatting an access Query into multiple columns

I have a query that looks like this

Item                                            Desc

AH037                                             HOTWATT    DATE
AH037                                             W & V
AH037                                             DO NOT RUN WITHOUT AIRFLOW
AH037/1MF                                     HOTWATT  DATE
AH037/1MF                                     W & V
AH037/1MF                                      DO NOT RUN WITHOUT AIRFLOW

My goal is to make the query look like this
 
Item                   Desc1          Desc2        Desc3
AH037                DATE           W & V         DO NOT RUN WITHOUT AIRFLOW
AH037/1MF       DATE           W & V         DO NOT RUN WITHOUT AIRFLOW

I am not sure how to go about doing this. Would you use dconcat? Any suggestions would be very much appreciated!
HOTWATTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If you want the desc as separate columns, you'll want to use a Cross Tab query in Access.   This will allow you to choose the field for the rows, and for the columns.

 You'd only use dconcat if you wanted all the Descriptions in a single column.

Jim.
0
PatHartmanCommented:
The crosstab query requires three columns and this sample is showing only two.  Somehow, you will need to manufacture a column to work as desc1, desc2, desc3 if there really isn't one in the table already..
0
Dale FyeCommented:
start with a query that looks something like:

SELECT T.*
FROM (
SELECT A.Item, A.Desc, Count(B.Item) as DescCount
FROM yourTable as A
INNER JOIN yourTable as B ON A.Item = B.Item and A.Desc <= B.Desc
GROUP BY A.Item, A.Desc
) as T

Then modify that as a crosstab query with Item as the RowHeader, DescCount as the column header, and First(Desc) as the value.

If you actually want your column headers to read "DESC1", "DESC2", then use:

 "DESC" & [DescCount]  

as the ColumnHeader.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

HOTWATTAuthor Commented:
Hey Dale that worked great one issue I have is the text needs to be in order and right now it is not.

Example (Original query)

Item                         DESCR
AH0250762001      SEE 13F0318-6 #17/MAKE
AH0250762001      SURE YOU DO THE BAND AROUND
AH0250762001      THE HEATER FIRST/THAN DO THE
AH0250762001      REGULAR ETCH ON THE HEATER


Item                     DESCR                                                   DescCount
AH0250762001      REGULAR ETCH ON THE HEATER             4
AH0250762001      SEE 13F0318-6 #17/MAKE                             3
AH0250762001      SURE YOU DO THE BAND AROUND             2
AH0250762001      THE HEATER FIRST/THAN DO THE             1

So when I run the cross tab on that query I get

Item                                                  1                                                                     2                                              3                                    
AH0250762001      THE HEATER FIRST/THAN DO THE      SURE YOU DO THE BAND AROUND      SEE 13F0318-6 #17/MAKE      REGULAR ETCH ON THE HEATER

Is there any way to get them to display in the correct order from the original query?
0
Dale FyeCommented:
If you don't have a autonumber, SeqNum or a DataTime field or some other field in the original table, to identify the correct sequence, then no.  The way I have that query written, it depends on the alphabetical sequence of the records within each item to determine the sequence.  You could invert that by changing the join to:

INNER JOIN yourTable as B ON A.Item = B.Item and A.Desc >= B.Desc

But that will not resolve the issue as that will simply sort them in the other direction, numerically.

One way to resolve this would be to add a "SeqNum" (long integer) field to your table, then write a function that opens the table as a recordset and loops through the recordset,  incrementing the "SeqNum" within each "Item".  When a new item is encountered, reset "SeqNum" to 1.

But even that may not result in what you are looking form.
0
HOTWATTAuthor Commented:
Actually just looked through the database fields and found line number field that numbers the lines properly. How would I add that into the query. It is called RNR_LINE_NO. Would I use that instead of DEScCount?
0
HOTWATTAuthor Commented:
Actually got the cross tab to work now that I have the Line number field. Thanks everyone for the help!
0
HOTWATTAuthor Commented:
Dales solution was the best solution for my original problem before I found a field that numbers the lines so I could just use a simple cross tab query.
0
Dale FyeCommented:
Just to make certain, with the line number, you should no longer need to join the table to itself.
0
HOTWATTAuthor Commented:
correct just using a cross tab query with RNR_LINE_NO as the Column Heading
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.