Prev: Filtering a Crosstab Query from Combo Boxes on Unbound Form
Next: Multiple "Not Like" not working
From: JD on 22 Apr 2010 10:49
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).
From: John Spencer on 22 Apr 2010 13:20
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).
 | 
Pages: 1
Prev: Filtering a Crosstab Query from Combo Boxes on Unbound Form
Next: Multiple "Not Like" not working