Combine multiple records' field value into one field?

[Beemermonkey]

Let's say I have two tables:

Product.
Item    Description
A        It's Red
B        It's Blue
C        It's Green
D        You'll love it

Category.
Item    Category_name
A        Cat1
A        Cat2
A        Cat3
B        Cat1
B        Cat4
C        Cat1
C        Cat6

I want to create a query/table that takes all the possible values for Category.Item and mix them into one field seperated by say a comma, so it would look like this:

ItemCategoryConsolodation.
Item    Desc                  Categories
A        It's Red               Cat1,Cat2,Cat3
B        It's Blue              Cat1,Cat4

...


How does one do that?!

What's it called when you try to do this so I can Google it?

I've looked through like 8 Access books at the book store and none of them address anything like this. Can someone recommend a book that would cover "weird" stuff like this?

 

[FoFa]

Basically (I think I am right) you are doing a crosstab query to get the comma seperated values, then writing a select using your table and the crosstab query to come up with your end results.
2 queries to get you where you want to be.
Any one else?

 

[Beemermonkey]

Basically (I think I am right) you are doing a crosstab query to get the comma seperated values, then writing a select using your table and the crosstab query to come up with your end results.
2 queries to get you where you want to be.
Any one else?

Uhhhh.. I'm not sure what any of that meant... I'm a total novice in Access in case it's not blindly apparent.

I'll do some poing around on "crosstab" in google.

 

[Jon K]

I'll do some poing around on "crosstab" in google.

A crosstab query requires at least three fields. You will need a special technique to display the 2-field Category table in a crosstab.


To answer your original questions
It's called Concatenate.
It requires VBA code.

Microsoft has an example on concatenating in a report.
ACC2000: How to Concatenate Data from the 'Many' Side of a Relationship
http://support.microsoft.com/kb/210163/en-us


I have attached an example on concatenating in a temporary table. You can click on the command button on the form to update the temporary table 'tblTemp'. The code used is in the On Click event of the command button.


To concatenate in a query, you can adapt my code in a public function in a module and use the function in a query. But updating a temporary table is more efficient than concatenating in a query.

Hope this helps.
.