Grouping and Stringing Values Together

  • Thread starter Thread starter Brian Smith
  • Start date Start date
B

Brian Smith

I have a table that contains information from people where people were
allowed to make multiple responses. Know I would like to group this
information and string it together. The table looks something like this (two
fields).

RespID Comments
1 Dogs
1 Cats
1 Rats
2 Cats
2 Horses
3 Monkeys
3 Elephants

I would now like to create a table that would look like this.

RespID Comments
1 Dogs, Cats, Rats
2 Cats, Horses
3 Monkeys, Elephants

I'm lost as to how to concatenate the pieces of information together while
grouping on the RespID field. Can this be done using a Make Table Query or
do I need to resort to using VBA?

Thanks.

Brian
 
Brian Smith said:
I have a table that contains information from people where people were
allowed to make multiple responses. Know I would like to group this
information and string it together. The table looks something like this
(two fields).

RespID Comments
1 Dogs
1 Cats
1 Rats
2 Cats
2 Horses
3 Monkeys
3 Elephants

I would now like to create a table that would look like this.

RespID Comments
1 Dogs, Cats, Rats
2 Cats, Horses
3 Monkeys, Elephants

I'm lost as to how to concatenate the pieces of information together while
grouping on the RespID field. Can this be done using a Make Table Query or
do I need to resort to using VBA?
Click to expand...


You can use the fConcatChild function at this link:

http://www.mvps.org/access/modules/mdl0004.htm
Modules: Return a concatenated list of sub-record values

.... in a query to return your values. Do you have another table that is the
parent table to this one, a table that has RespID as its primary key? That
would be the simplest basis for the query. If you don't, if all you have is
the one table as described above, you buld a slightly more complex query
like this:

SELECT
A.RespID,
fConcatChild("TestTable","RespID","Comments","Long",[RespID])
AS Comments
FROM
(SELECT DISTINCT TestTable.RespID FROM TestTable) AS A;
 
Hi Brian,

You can certainly concatentate the values, grouped by RespID, for reporting.
However, you should not store the data in a table in this fashion. That would
be taking a properly normalized structure and creating a multi-valued field.
Please see the second download, "Database Design Tips by Michael Hernandez",
available here:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

A MVF design will be a lot more difficult to work with in the future. For
example, if you wanted to create a report grouped by comment type (ie. cats,
dogs, etc.). Here are a couple of articles that you may find helpful:

How to Concatenate Data from the 'Many' Side of a Relationship
http://support.microsoft.com/?id=210163

Return a concatenated list of sub-record values
http://www.mvps.org/access/modules/mdl0004.htm


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Dirk, your solution worked out perfectly. Thanks!

Brian

Dirk Goldgar said:
Brian Smith said:
I have a table that contains information from people where people were
allowed to make multiple responses. Know I would like to group this
information and string it together. The table looks something like this
(two fields).

RespID Comments
1 Dogs
1 Cats
1 Rats
2 Cats
2 Horses
3 Monkeys
3 Elephants

I would now like to create a table that would look like this.

RespID Comments
1 Dogs, Cats, Rats
2 Cats, Horses
3 Monkeys, Elephants

I'm lost as to how to concatenate the pieces of information together
while grouping on the RespID field. Can this be done using a Make Table
Query or do I need to resort to using VBA?
Click to expand...


You can use the fConcatChild function at this link:

http://www.mvps.org/access/modules/mdl0004.htm
Modules: Return a concatenated list of sub-record values

... in a query to return your values. Do you have another table that is
the parent table to this one, a table that has RespID as its primary key?
That would be the simplest basis for the query. If you don't, if all you
have is the one table as described above, you buld a slightly more complex
query like this:

SELECT
A.RespID,
fConcatChild("TestTable","RespID","Comments","Long",[RespID])
AS Comments
FROM
(SELECT DISTINCT TestTable.RespID FROM TestTable) AS A;


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Tom, thanks for the information. I always use a normalized structure but in
this case I wanted to create a multi-valued field for a totally different
reason. I do not want to store the data that way but wanted it for creating
a list by RespID.

Brian
 
Back
Top