A query to combine multiple row values into one field in MS-Access 2003?

Question

Possible Duplicate:
Combine rows in Access 2007

If I have two tables: let's say OrderStatus and Comments, how can I sum up all comments which are made related to one order, so that I can put them into one field?

Tabel OrderStatus:

OrderID      OrderStatus       OrderDate
64           OK                13.08.2011
77           Deleted           21.06.2011
99           OK                18.04.2011

Table Comments:

CommID       CommOrder         CommText
1            64                "EAN 304"
2            64                "resent"
3            64                "no. 499"
4            99                "in stock"
5            99                "EAN 111"

What I want:

OrderID    OrderStatus         Comments
64         OK                  "EAN 304, resent, no. 499"
99         OK                  "in stock, EAN 111"

The total number of comments related to an order status is unknown.

I am trying to achieve this with Access SQL-subqueries. I already managed with own VBA routines called from the query builder but the OrderStatus table has more than 30,000 records and VBA is too slow (takes more than 10 minutes to generate a report). In Microsoft Access, SQL is much faster than VBA but can be complicated. From Access 2007 on, Microsoft has included multivalued fields, however I'm using Access 2003 which isn't capable of automatically listing multiple values comma-separated.

Thanks for your help!

see: http://stackoverflow.com/questions/92698/combine-rows-in-access-2007/93863#93863 — Fionnuala, Sep 05 '11 at 20:55
Like I said, I already managed with VBA in a similar way like the thread you suggested. VBA however is much slower than SQL. — johnnyciao, Sep 10 '11 at 12:01

score 0 · Answer 1 · answered Sep 06 '11 at 20:23

VBA is the way to go. But, I wouldn't call it from the query builder. I would add a "Comments" field to your original table, and make a stand-alone VBA function using the Recordset object to loop through the records in the second table, and paste the data in the Comments field. A function like this for just 30,000 records should run very quickly.

A query to combine multiple row values into one field in MS-Access 2003?

1 Answers1