Help with query multiple records into one

[Cam]

Hello,

I have a query with workcenter and different machines tie to the workcenter
So there could be multiples machines in a workcenter. How do I query so that
only one record of workcenter is displayed, then add another field to list
all the machine number? Sample

workcenter wcdesc machine etc......
A32 T50 avenger 4022
A32 T50 avenger 6022
A32 T50 avenger 1022
A52 T35 bently 6022
A52 T35 bently 9025

Result wanted:
workcenter wcdesc machine etc......
A32 T50 avenger 1022, 4022, 6022
A52 T35 bently 6022, 9025

Thanks

 

[Marshall Barton]

I have a query with workcenter and different machines tie to the workcenter
So there could be multiples machines in a workcenter. How do I query so that
only one record of workcenter is displayed, then add another field to list
all the machine number? Sample

workcenter wcdesc machine etc......
A32 T50 avenger 4022
A32 T50 avenger 6022
A32 T50 avenger 1022
A52 T35 bently 6022
A52 T35 bently 9025

Result wanted:
workcenter wcdesc machine etc......
A32 T50 avenger 1022, 4022, 6022
A52 T35 bently 6022, 9025

You need to create a function to do that. There's a good
one at:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

 

[Cam]

Marshall,

Thank for the link. My problem is I am using Access 2003 which does not have
Concatenate function. What is the equivalent function?

 

[John Spencer MVP]

You need to copy the concatenate function from the VBA module in the sample
database to a VBA module in your database. You may also need to modify it per
the instructions in the module. Uncomment some lines and comment out other
lines (add or remove an apostrophe at the beginning of the line).

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

 

[Cam]

THank John, I tried edited the VBA module. Unlike the sample database, my
issue is I don't have a unique field on any of the workcenter or machine
table. The tables are from a ODBC link source. What can I do?

 

[John Spencer MVP]

Based on your posted data sample you would need something like the following
in the query

FIELD: Machines: Concatenate("SELECT Machine FROM [YourTable] WHERE WorkCenter
= '" & [WorkCenter] & "' AND WcDesc = '" & [wcDesc] & "'")

In SQL view that might look like

SELECT DISTINCT WorkCenter
, wcdesc
, Concatenate("SELECT Machine FROM [YourTable] WHERE WorkCenter = '" &
[WorkCenter] & "' AND WcDesc = '" & [wcDesc] & "'") as Machines
FROM SomeTable

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