Displaying data across columns

[JD]

I need a arrange the data from a table across columns similar to a crosstab
query, but the crosstab didn't work for me. Do anyone have any suggestions?

Here is a basic breakdown of what I'm looking to do...
I have a table with basically two columns: AcctID & AssocID. The AcctID
could be listed once or several times depending on how many AssocIDs are
related to each particular AcctID.

What I need is to have the AcctID listed only once (as primary key) with the
AssocIDs listed across. How can I achieve this. My first thought was to do
it manually in Excel, but then I found that there are a total of 173,878
records (my end result will have 130,380 records).

 

[John Spencer]

The following may be too slow to be practical, but you can try doing this.

Create a ranking query and save it
SELECT A.AcctID, A.AssocID, 1 + Count(B.AssocID) as Rank
FROM [YourTable] as A LEFT JOIN [YourTable] As B
ON A.AcctID = B.AcctID
AND A.AssocID < B.AssocId
GROUP BY A.AcctID, A.AssocID

You can now use that as the source for a crosstab query.

TRANSFORM First(AssocID)
SELECT AcctID
FROM [TheSavedQuery]
GROUP BY AcctID
PIVOT Rank

It you can't build those queries in SQL view, post back and I will try to tell
you how to do this using query design view. If you postback, please tell use
the name of the table.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County