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).

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

JD wrote:
> 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).