Crosstab Query with multiple text values

[Susan]

I am trying to create a cross tab but instead of having a sum/count/min/max,
there are some cells that will have multiple text values.

Is there a way I can have these values populated into one cell?

 

[KARL DEWEY]

It takes a little manipulation. Post sample data and what you would like
for the output to look like.

 

[Susan]

Here is my raw data

Company Ticker Rank Account Rep Level Contact Name
XYZ Inc. XYZ 2 Exec Jane Doe
XYZ Inc. XYZ 2 Manager John Doe
XYZ Inc. XYZ 2 Manager Jim Doe

And this is what I want it to look like

Company Ticker Rank Exec Manager
XYZ Inc. XYZ 2 Jane Doe John Doe; Jim Doe

With the values John Doe and Jim Doe in the same cell. Maybe the crosstab
query is not the best option for this but am open to suggestions.

Thanks for the help!

 

[Duane Hookom]

You can use First or Max or other aggregates as a crosstab value. There is a
generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16.

I expect your syntax might have
Concatenate("SELECT ContactName FORM RawData WHERE Company=""" & [Company] &
"""","; ")

 

[KARL DEWEY]

I was going to suggest Duane Hookom but I see post already.

Here is something I put together for someone else --

Hello,

I have a query that have order#, multiple oper, wc, task, etc... (more than
one records) that I would like to combine into just one records with
concatenate all the wc, oper and task into one field. See example.

Original query records: (5 records for Order# 123456)
Order# Item# Oper WC Task
123456 25W80 40 abc 1001
123456 25W80 50 abc 1005
123456 25W80 60 zim 1030
123456 25W80 80 dmc 1111
123456 25W80 95 xyz 1035

Want to achieve: (1 record for Order# 123456)
Order# Item# Oper WC
Task
123456 25W80 40, 50, 60, 80, 95 abc, abc, zim, dmc, xyz
1001, 1005...

Thanks for suggestions

I think this will do it.
I used table named Test1 with fields Order# and Item# as constants and Oper,
WC, and Task as varriables.
Query Text1X labels a rank on all records with the same constants but
different varriables.
Query Text1Y extracts the maximum rank of each constant.
Query Text1Z1 through Text1Z5 concatenates those records with same constants
and have maximum rank matching Text1Y.
Query Text1Z_All is a union query to pull them all together.

Text1X --
SELECT Q.[Order#], Q.[Item#], Q.[Oper], Q.[WC], Q.[Task], (SELECT COUNT(*)
FROM [Test1] Q1
WHERE Q1.[Order#] = Q.[Order#]
AND Q1.[Item#] & Q1.[Oper] & Q1.[WC] & Q1.[Task] <= Q.[Item#] &
Q.[Oper] & Q1.[WC] & Q.[Task]) AS Rank
FROM Test1 AS Q
ORDER BY Q.[Order#], Q.[Item#], Q.[Oper], Q.[WC], Q.[Task] DESC;

Text1Y --
SELECT Test1X.[Order#], Test1X.[Item#], Max(Test1X.Rank) AS MaxOfRank
FROM Test1X
GROUP BY Test1X.[Order#], Test1X.[Item#];

Text1Z1 --
SELECT Test1X.[Order#], Test1X.[Item#], Test1X.Oper AS Oper_, Test1X.WC AS
WC_, Test1X.Task AS Task_
FROM Test1Y INNER JOIN Test1X ON (Test1Y.[Order#] = Test1X.[Order#]) AND
(Test1Y.[Item#] = Test1X.[Item#])
WHERE (((Test1X.Rank)=1) AND ((Test1Y.MaxOfRank)=1))
GROUP BY Test1X.[Order#], Test1X.[Item#], Test1X.Oper, Test1X.WC, Test1X.Task;

Text1Z2 --
SELECT Test1X.[Order#], Test1X.[Item#], [Test1X].[Oper] & " " &
[Test1X_1].[Oper] AS Oper_, [Test1X].[WC] & " " & [Test1X_1].[WC] AS WC_,
[Test1X].[Task] & " " & [Test1X_1].[Task] AS Task_
FROM Test1Y INNER JOIN (Test1X LEFT JOIN Test1X AS Test1X_1 ON
(Test1X.[Item#] = Test1X_1.[Item#]) AND (Test1X.[Order#] =
Test1X_1.[Order#])) ON (Test1Y.[Order#] = Test1X.[Order#]) AND
(Test1Y.[Item#] = Test1X.[Item#])
WHERE (((Test1X.Rank)=1) AND ((Test1X_1.Rank)=2) AND ((Test1Y.MaxOfRank)=2))
GROUP BY Test1X.[Order#], Test1X.[Item#], [Test1X].[Oper] & " " &
[Test1X_1].[Oper], [Test1X].[WC] & " " & [Test1X_1].[WC], [Test1X].[Task] & "
" & [Test1X_1].[Task];

Text1Z3 --
SELECT Test1X.[Order#], Test1X.[Item#], [Test1X].[Oper] & " " &
[Test1X_1].[Oper] & " " & [Test1X_2].[Oper] AS Oper_, [Test1X].[WC] & " " &
[Test1X_1].[WC] & " " & [Test1X_2].[WC] AS WC_, [Test1X].[Task] & " " &
[Test1X_1].[Task] & " " & [Test1X_2].[Task] AS Task_
FROM Test1Y INNER JOIN ((Test1X LEFT JOIN Test1X AS Test1X_1 ON
(Test1X.[Item#] = Test1X_1.[Item#]) AND (Test1X.[Order#] =
Test1X_1.[Order#])) LEFT JOIN Test1X AS Test1X_2 ON (Test1X.[Item#] =
Test1X_2.[Item#]) AND (Test1X.[Order#] = Test1X_2.[Order#])) ON
(Test1Y.[Order#] = Test1X.[Order#]) AND (Test1Y.[Item#] = Test1X.[Item#])
WHERE (((Test1X.Rank)=1) AND ((Test1X_1.Rank)=2) AND ((Test1X_2.Rank)=3) AND
((Test1Y.MaxOfRank)=3))
GROUP BY Test1X.[Order#], Test1X.[Item#], [Test1X].[Oper] & " " &
[Test1X_1].[Oper] & " " & [Test1X_2].[Oper], [Test1X].[WC] & " " &
[Test1X_1].[WC] & " " & [Test1X_2].[WC], [Test1X].[Task] & " " &
[Test1X_1].[Task] & " " & [Test1X_2].[Task];

Text1Z4 --
SELECT Test1X.[Order#], Test1X.[Item#], [Test1X].[Oper] & " " &
[Test1X_1].[Oper] & " " & [Test1X_2].[Oper] & " " & [Test1X_3].[Oper] AS
Oper_, [Test1X].[WC] & " " & [Test1X_1].[WC] & " " & [Test1X_2].[WC] & " " &
[Test1X_3].[WC] AS WC_, [Test1X].[Task] & " " & [Test1X_1].[Task] & " " &
[Test1X_2].[Task] & " " & [Test1X_3].[Task] AS Task_
FROM Test1Y INNER JOIN (((Test1X LEFT JOIN Test1X AS Test1X_1 ON
(Test1X.[Order#] = Test1X_1.[Order#]) AND (Test1X.[Item#] =
Test1X_1.[Item#])) LEFT JOIN Test1X AS Test1X_2 ON (Test1X.[Order#] =
Test1X_2.[Order#]) AND (Test1X.[Item#] = Test1X_2.[Item#])) LEFT JOIN Test1X
AS Test1X_3 ON (Test1X.[Order#] = Test1X_3.[Order#]) AND (Test1X.[Item#] =
Test1X_3.[Item#])) ON (Test1Y.[Item#] = Test1X.[Item#]) AND (Test1Y.[Order#]
= Test1X.[Order#])
WHERE (((Test1X.Rank)=1) AND ((Test1X_1.Rank)=2) AND ((Test1X_2.Rank)=3) AND
((Test1X_3.Rank)=4) AND ((Test1Y.MaxOfRank)=4))
GROUP BY Test1X.[Order#], Test1X.[Item#], [Test1X].[Oper] & " " &
[Test1X_1].[Oper] & " " & [Test1X_2].[Oper] & " " & [Test1X_3].[Oper],
[Test1X].[WC] & " " & [Test1X_1].[WC] & " " & [Test1X_2].[WC] & " " &
[Test1X_3].[WC], [Test1X].[Task] & " " & [Test1X_1].[Task] & " " &
[Test1X_2].[Task] & " " & [Test1X_3].[Task];

Text1Z5 --
SELECT Test1X.[Order#], Test1X.[Item#], [Test1X].[Oper] & " " &
[Test1X_1].[Oper] & " " & [Test1X_2].[Oper] & " " & [Test1X_3].[Oper] & " " &
[Test1X_4].[Oper] AS Oper_, [Test1X].[WC] & " " & [Test1X_1].[WC] & " " &
[Test1X_2].[WC] & " " & [Test1X_3].[WC] & " " & [Test1X_4].[WC] AS WC_,
[Test1X].[Task] & " " & [Test1X_1].[Task] & " " & [Test1X_2].[Task] & " " &
[Test1X_3].[Task] & " " & [Test1X_4].[Task] AS Task_
FROM Test1Y INNER JOIN ((((Test1X LEFT JOIN Test1X AS Test1X_1 ON
(Test1X.[Item#] = Test1X_1.[Item#]) AND (Test1X.[Order#] =
Test1X_1.[Order#])) LEFT JOIN Test1X AS Test1X_2 ON (Test1X.[Item#] =
Test1X_2.[Item#]) AND (Test1X.[Order#] = Test1X_2.[Order#])) LEFT JOIN Test1X
AS Test1X_3 ON (Test1X.[Item#] = Test1X_3.[Item#]) AND (Test1X.[Order#] =
Test1X_3.[Order#])) LEFT JOIN Test1X AS Test1X_4 ON (Test1X.[Item#] =
Test1X_4.[Item#]) AND (Test1X.[Order#] = Test1X_4.[Order#])) ON
(Test1Y.[Order#] = Test1X.[Order#]) AND (Test1Y.[Item#] = Test1X.[Item#])
WHERE (((Test1X.Rank)=1) AND ((Test1X_1.Rank)=2) AND ((Test1X_2.Rank)=3) AND
((Test1X_3.Rank)=4) AND ((Test1X_4.Rank)=5) AND ((Test1Y.MaxOfRank)=5))
GROUP BY Test1X.[Order#], Test1X.[Item#], [Test1X].[Oper] & " " &
[Test1X_1].[Oper] & " " & [Test1X_2].[Oper] & " " & [Test1X_3].[Oper] & " " &
[Test1X_4].[Oper], [Test1X].[WC] & " " & [Test1X_1].[WC] & " " &
[Test1X_2].[WC] & " " & [Test1X_3].[WC] & " " & [Test1X_4].[WC],
[Test1X].[Task] & " " & [Test1X_1].[Task] & " " & [Test1X_2].[Task] & " " &
[Test1X_3].[Task] & " " & [Test1X_4].[Task];


Text1Z_All --
SELECT Text1Z5.[Order#], Text1Z5.[Item#], Text1Z5.Oper_, Text1Z5.WC_,
Text1Z5.Task_
FROM Text1Z5
UNION ALL SELECT Text1Z4.[Order#], Text1Z4.[Item#], Text1Z4.Oper_,
Text1Z4.WC_, Text1Z4.Task_
FROM Text1Z4
UNION ALL SELECT Text1Z3.[Order#], Text1Z3.[Item#], Text1Z3.Oper_,
Text1Z3.WC_, Text1Z3.Task_
FROM Text1Z3
UNION ALL SELECT Text1Z2.[Order#], Text1Z2.[Item#], Text1Z2.Oper_,
Text1Z2.WC_, Text1Z2.Task_
FROM Text1Z2
UNION ALL SELECT Text1Z1.[Order#], Text1Z1.[Item#], Text1Z1.Oper_,
Text1Z1.WC_, Text1Z1.Task_
FROM Text1Z1;

Maybe It will work for you.

 

[John W. Vinson]

Company Ticker Rank Exec Manager
XYZ Inc. XYZ 2 Jane Doe John Doe; Jim Doe

With the values John Doe and Jim Doe in the same cell. Maybe the crosstab
query is not the best option for this but am open to suggestions.

See http://www.mvps.org/access/modules/mdl0004.htm for code to concatenate all
the Managers into one string.