Help with query multiple records into one


C

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
 
Ad

Advertisements

M

Marshall Barton

Cam said:
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
 
C

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?
 
J

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
 
C

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?
 
Ad

Advertisements

J

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top