Concatenate multiple records in query

  • Thread starter Thread starter calculating additional data help
  • Start date Start date
C

calculating additional data help

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
 
You don't say the source of the query. If it is a single table, I don't
know how you will go about it. If Order is in one table, and the
Operations, etc. in a related table, you can use one of the concatenation
functions here:
http://allenbrowne.com/func-concat.html
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane (look for
"Generic Function ot Concatenate Child Records")

If you are using a single table, post details of the real-world situation
behind the database. It may be possible to suggest a way to break the data
into properly normalized tables. The following is from one of John Vinson's
posts:

*****
Take a look at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Crystal's videos would be a good start.
******
 
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;
 
Back
Top