Multiple values to comma separated value

[csk003]

I have a MS Access table with two columns ID, Category
Table1
ID- Category
1 AAA
1 PVC
1 ASP
2 AAA
3 ABC
3 AAA

What I would like to do is create another table Table2
ID Combined_Category
1 AAA, PVC, ASP
2 AAA
3 ABC,AAA

Should I create a VBA code/module? (I am very new to this)
For each id I have to loop until all ids are done
and for each id depending on how many rows it returns from table1., I should concatenate and update table2. I do not know how I can access individual rows that the query retirns
Eg: select category from table1 where id =1
returns 3 rows how would I access first element in that return
It is almost like an array
tried select category(0) from table1 where id =1 but didn't work

[OBP]

Hello csk, you should be able to do this with an Append and an Update query, provided that you set the 2nd table's ID to "Indexed" with "No duplicates".
First of all run the Append query to put the first set of values in the table based in the ID numbers.
Second run an Update query where the "Update to" row has Combined_Category = Combined_Category & Category

[csk003]

Should I create a VBA module? or can I use plain queries?
If id 1 has multiple categories how can I pick just one and insert into another table. Is there a way or command to access one by one of the muliple values of category colmn for each value of id?

[OBP]

csk, sorry I gave you the wrong expression in the Update Query. :blush
I have attached a database tha does what you want.

[csk003]

Thanka a bunch. Works great. Good way to use join to concatenate.

[stanl]

If you are curious as to how it could be done with a module. Stan

http://www.databasejournal.com/features/msaccess/article.php/2247531

[Mavver]

Oops, it got sent to quick

[Mavver]

<<sorry about the post above, my machine went haywire and posted it too soon>>

Can anyone help with this in reverse?

I have a table generated from a "linked table" from outlook

The table holds anecdotal information about 150 towns in England, with the town information being held in the subject.

For instance if the user was talking about Town A s/he would type in the subject part of the email type

subject: Town A

if s/he was talking about Town A AND Town B s/he would type in the subject of the email

subject: Town A, Town B

When it comes back into Access, the records will look like the following

1. Town A
2. Town A
3. Town B
4. Town A, Town B
5. Town C,
6. Town A, Town B, Town C

What I would like it to say, in a seperate table if need be

1. Town A
2. Town A
3. Town B
4. Town A
4. Town B
5. Town C
6. Town A
6. Town B
6. Town C

Hopefuly this can be done

Thanks in advance for any help

Mav

[OBP]

Mav, I have already posted an example of how to do this kind of Seperation on a previous Thread, although the whole file was seperated by commas.
As the table is "Linked" to the Outlook Table it would be best if the conversion was put in to a new table thus preserving the Outlook one.
Is it possible to Upload a zipped copy as an Attachment with some actual data for me to work with?
Or you can email it to me, I can provide my email address in a Forum Private Mail to you.