Combining

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

One of my access tables has data in the following format:

Record Name Location
1 Jim SF
2 John SF
3 Julie SF
4 Chad LA
5 Sam LA
6 Suzy LA
7 Tina Cin
8 Amy Cin
9 Don Cin


Is it possible within a query to change this data (the names!!) to appear in
the following format?

Record Name Location
1 Jim, John, Julie SF
2 Chad, Sam, Suzy LA
3 Tina, Amy, Don Cin



Thank You,
 
Thanks for your response.

It looks like it should work well. Although I am still having some trouble
setting up the Query after I pasted the function into a VBA module. I set up
a table with the name Customers, one field called CustomerID (which would be
similar to the names), and one field called ContactTitle (similar to the
location). I then pasted the sql formula into the query. Any suggestions?

Thank You
 
Your query would look something like:

SELECT Location, fConcatFld("YourTableName",_
"Location",_
"Name", _
"string", _
[Location]) as Names
FROM YourTableName

Getting the values for the records is a little more problematic, but we can
work around that once you get this part of things working.

HTH
Dale
 
Thank You, I got it to work





Dale Fye said:
Your query would look something like:

SELECT Location, fConcatFld("YourTableName",_
"Location",_
"Name", _
"string", _
[Location]) as Names
FROM YourTableName

Getting the values for the records is a little more problematic, but we can
work around that once you get this part of things working.

HTH
Dale

tjh said:
Thanks for your response.

It looks like it should work well. Although I am still having some trouble
setting up the Query after I pasted the function into a VBA module. I set up
a table with the name Customers, one field called CustomerID (which would be
similar to the names), and one field called ContactTitle (similar to the
location). I then pasted the sql formula into the query. Any suggestions?

Thank You
Click to expand...
Click to expand...
 
Dale,
I am having the same issue but I have hit an error message. My SQL looks
like this:

SELECT [mktbl_Data for Corporate 4].[Group #], fConcatFld("mktbl_Data for
Corporate 4","Group #","Client #","string",[Group #]) AS CustomerIDs
FROM [mktbl_Data for Corporate 4]
GROUP BY [mktbl_Data for Corporate 4].[Group #];

The thought process is...I have multiple clients for each group number and I
want to have my query results give me the following:

Group Num Client Number(s) Revenue
1 A;B;C;D
XXXXX
2 E;F;G
XXXXX

Any suggestions? I get the error code 3464-data type mismatch in criteria
expression. Thanks


Dale Fye said:
Your query would look something like:

SELECT Location, fConcatFld("YourTableName",_
"Location",_
"Name", _
"string", _
[Location]) as Names
FROM YourTableName

Getting the values for the records is a little more problematic, but we can
work around that once you get this part of things working.

HTH
Dale

tjh said:
Thanks for your response.

It looks like it should work well. Although I am still having some trouble
setting up the Query after I pasted the function into a VBA module. I set up
a table with the name Customers, one field called CustomerID (which would be
similar to the names), and one field called ContactTitle (similar to the
location). I then pasted the sql formula into the query. Any suggestions?

Thank You
Click to expand...
Click to expand...
 
Back
Top