Streamline Many to 4

[Cydney]

I have a table of Workman Codes which are assigned to individual employees.
Some employees have 1 WC assigned to them, others have up to 4. I've created
a crosstab query that gives me one row with individual name and then the WC's
assigned to that person. However, there are about 40 different WC's so they
are all listed horizontally. I would like to change this to 4 columns of WC's
listed by each name. Like this:
WC1 WC2 WC3 WC4
Joe 234 798 989 992
Bill 589 683 992
Bob 567 778
Sue 798

Right now I'm seeing:

Joe 234 798 989
992
Bill 589 683
992
Bob 567 778
Sue 798
....etc. in other words... all spread out horizontally when what I want to
see is 4 columns of codes.

Do I need to create an append to a table programmatically running through
the list of records? I'm hoping I can do it within a query..
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

 

[John Spencer]

You can do it, but you need a ranking query first to use as the source for
your crosstab.

SELECT A.PersonID
, A.WC
, Count(B.WC) + 1 as Rank
FROM SomeTable as A LEFT JOIN SomeTable As B
ON A.PersonID = B.PersonID
AND A.WC < B.WC
GROUP BY A.PersonID, A.WC

NOW use that as the source for your crosstab
Group on PersonID (row)
Group on Rank (Column)
First on WC (Value)

TRANSFORM First(WC) as TheWC
SELECT PersonID
FROM SavedRankQuery
GROUP BY PersonID
Pivot "WC" & Rank IN ("WC1","WC2","WC3","WC4","WC5")

Only add the IN list if you know the maximum number of columns.

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

 

[Cydney]

How do I modify this if I don't know the maximum number of columns for the
"IN"? It's variable and could change depending on the sub-group we are
reporting on.
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

 

[Cydney]

This worked beautifully for up to 5 WC's! Thanks so much, John!

~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

 

[Cydney]

Never mind... that was a dumb question...
The answer is: Leave out the "IN" portion... =\ ...duhh..
It's been a long day... Your solution worked perfectly.
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson