Combine multiple fields from different records on distinct query

[YoMo]

Hello All,


I have an issue and racking my brains, asked others and haven't found a solution yet.

Let me show you how I have it in the table:

OrderID ItemID 123 A 123 B 123 C 456 A 456 C 789 D

The records are unique on the ItemId level. I need to come up with a query (or other technique ) that there should be one line unique to the OrderID with the ItemID listed in the same row. Preferably in one field separated with a comma ans space.
Here's what I want the end result to look like:

OrderID	ItemID
123	A, B, C
456	A, C
789	D

If that is not possible (that all ItemID be concatenated in one field), I will have to go with OrderId and the ItemId in seperate fields to the right.

Any help is greatly appreciated!!

[rpeare]

I don't believe this is possible in a query unless you have a very limited number of item ID's that you can work around. Try this:

http://www.databasejournal.com/featu...ith-Access.htm

[YoMo]

rpeare -
The link you provided was exactly the issue I have on hand. However I wasn't able to implement the VBA code - I don't understand it. Does it work only for older versions of access? I have no to limited knowledge of vba (except for copy/paste and fill in the blanks/names).
Any suggestions?

[rpeare]

you'll have to be more specific about what you can't implement, the code assumes you are pasting information to a temp table called tblCopy with fields Column1, Column2, you have to adapt the code for your own purposes, for instance if you name your table 'tblConcantenatedStuff' with the fields OrderID and ConcantValue you would have to modify the code to use those values rather than just cutting and pasting the code and expecting it to work.

What the code is doing is basically cycling through your records one at a time building a single record for each orderID then pasting that record to a temp table