Turning column stored data into one row

[blom0344]

Dear all,

I'm trying to work out a procedure to create a concatenated string from an x (1< x< 10) number of rows which contain a host of options related to products.

The source-table looks like this:

ITEM OPTION
**** ******
A Blue
A Long
A Cheap
B Red
B Short
B Expensive
B $45

An item can have up to 10 options, with a minimum of 1

I need as output something like this:

ITEM STRING
**** ******
A Blue,Long,Cheap
B Red,Short,Expensive,$45

Attempts with SQL have stranded, because performance was terrible (table is quite large). I did find some VBA code that does the trick, but then just for 1 item. I need to transform the entire table.

Anyone any idea how to solve this in a VBA module??

Thanx T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[thornmastr]

I just did this. I set up a small test database with an export spec, and simply used a transfertext statement.
It gives you a text file and could care less how many items you wish to use.

If you would like the database, drop me a line and tell me what version of Access you are using and I will zip it to you.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com