Access 2010 crosstab/transpose/pivot data 1

[merlizbet]

I need to crosstab/transpose/pivot some data in a table that has 2 columns. The table looks like this...

<pre>
ID          Song
---        ------
927361283   If I Only Knew What I Was Doing
927361283   This Stuff Is Making Me Crazy
718263510   Hep Me, Hep Me
615243019   A Simple Solution Would Make My Day
</pre>

I need for it to look like this...

<pre>
ID          Song1                                Song2
---        ------                               -------
927361283   If I Only Knew What I Was Doing      This Stuff Is Making Me Crazy
718263510   Hep Me, Plz!
615243019   A Simple Solution Would Make My Day
</pre>

Theoretically, there could be a large number of Songs for a particular ID, so the preference would be to create the number of Song columns dynamically.

I *thought* I had done something like this using a Crosstab Query, but I'm not seeing how to do it now. I've scoured the Internet for a simple way to do this (which seems like a simple problem that isn't that unique), but have not found anything simple. I've found several results using VBA to do something sort of along the same lines of what I need, but nothing doing exactly what I need. Based on the VBA examples I've found, I've been attempting to kluge together my own VBA to create a new table and loop through the IDs and fill columns with the Songs data, but I'm not all that good with VBA so it's about to get out of hand for me.

Does anyone know of a simple way to do this??

Thanks for your help!

 

[dhookom]

Since this is a Reports forum, you should consider a main report based on SELECT DISTINCT ID FROM [Your Table Name Here]. Then create a multi-column subreport that displays across then down. The subreport will be linked to the main report based on the ID field.

Duane
Hook'D on Access
MS Access MVP

 

[merlizbet]

Thanks for your response, Duane! I see you posting everywhere so I'm *very* happy to have your input!

Unfortunately, I got to this forum while searching *everywhere* for an answer so I guess this question really belongs in some other forum. What I need to do with the results (i.e., the transposed data) is use that along with some other data to do a mailmerge into a WORD document. Having only 1 letter per ID, I need to string this (and other) data out (worded properly) in the mailmerge, and the only way I can think to do this is to have each Song in a separate field. So, unfortunately, an Access report won't handle my problem.

Should I repost this problem in a different forum (or is there a way to just move *this* thread to a different forum)?

Thanks for your response!

 

[dhookom]

There is an FAQ in the query forum that has code to concatenate the child/song records into a single expression.

If you want to continue on the path to a crosstab query, it typically requires two queries. The first creates a sequence/rank number for each song within the ID so they are numbered 1,2,3,... The second query is the crosstab where the sequence/rank is used in the Column Heading. You can search Tek-Tips and/or the web on ranking query. I typically create these like the following which assigns a rank of the Orders table grouped by CustomerID.

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Count(Orders_1.OrderID) AS Rank
FROM Orders AS Orders_1 INNER JOIN Orders ON Orders_1.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate)>=[Orders_1].[OrderDate]))
GROUP BY Orders.OrderID, Orders.CustomerID, Orders.OrderDate
ORDER BY Orders.CustomerID, Orders.OrderDate;

The crosstab query would use the ranking query as its source.

Duane
Hook'D on Access
MS Access MVP

 

[merlizbet]

Great! Thanks for the info! I'll dive into this and see if I can get it to work (and will post back if I hit a snag ).

 

[merlizbet]

Got it! Took me a while to figure out how to properly translate the SQL for my own stuff (funny how things are obvious when you finally figure them out) since I'm normally using the Grid View, but using the 2 queries works like a charm and is so much simpler than trying to use VBA. The key piece is really knowing to search for "ranking query" first. As is always the case, you just don't know what you don't know.

Thanks so much for your help, Duane!

 

[dhookom]

Great to be of some assistance and welcome to Tek-Tips.

Duane
Hook'D on Access
MS Access MVP