Transpose Rows into Colums

[aravindk]

Hello all,

I have a table with two fields in it:

Customer ID, Order No
12345, 556
12345, 678
12345, 123
00678, 12
00678, 145
97867, 456

I want to build a query which returns one record for each customer id and one column for each order no. The result set from the above table should look something like this:

12345, 556, 678, 123
00678, 12, 145,
97867, 456

Is this possible?

TIA,

 

[GoDawgs]

Check out the cross-tab query wizard, that should do it. Hope that helps.

Kevin

 

[aravindk]

Thanks for the quick response.

Crosstab requires three fields atleast so that I can one in the row, one in column and one in the cell area. In my case i have only two. Also it will group by row and then by column. In my case, I dont want it to group by column.

e.g:
Customer ID, Order No
12345, 556
12345, 678
12345, 123
00678, 556
00678, 145

A crosstab will return as follows:
12345, 556, 678, 123
00678, 556, 0, 0, 145

But what I want is this:
12345, 556, 678, 123
00678, 556, 145

Regards,

 

[GoDawgs]

My fault, misread the first post. I think you'd have to do this in code...it wouldn't be too hard though. You can call a function from within a query...this function could create a text list of the order numbers ("556, 678, 123&quot based on which customer ID you're on in the query. Make sense? Hope it helps...

Kevin

 

[GoDawgs]

I answered a similar question recently...check this thread:
thread701-749457

 

[dhookom]

Check this forum's faq701-4233 for a generic function that will do this for you.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[aravindk]

Both your functions are fantastic! I am sure I am going to use both the techniques over and over again. Thanks for making my day!