Another row value to columns question

[sanniep]

Hello!

I have an access table filled with 10k records that look like this:

Product Car
601945 Vauxhall Astra
602117 Peugeot 806
602117 Peugeot Expert
602117 Citroen Dispatch
602227 Vauxhall Vectra B
602227 Opel Vectra B

I want it to look like this:
Product Car
601945 Vauxhall Astra
602117 Peugeot 806, Peugeot Expert, Citroen Dispatch
602227 Vauxhall Vectra B, Opel Vectra B

So putting the car models in one comma seperated column for each productcode that is the same. Now I have seen a lot of posts like this but I can't find the solution. Can you help me please?

Thanks! Sander

[NeoPa]

Hi Sander.

Please let me start by saying that what you propose is a very bad idea indeed. This leads you towards a way of storing data that will be clumsy and very difficult to work with. On the other hand, displaying the data that way, just as one might in MS Excel, may well be very reasonable. For more on this see Database Normalisation and Table Structures.

The link (Listing Items Within GROUP BY Set) will help you to form a query that handles providing data in the format requested. You can use the concept to display or export the data, just as you can to save it and store it. I strongly recommend you avoid doing the latter but I'll leave it with you anyway.