Concatenate Column Values from Multiple Rows into a Single Column with Access
August 15, 2003
Coming up with a meaningful title for this article was the hardest part. The issue is one that I have seen a couple times in the Access newsgroups, but it is hard to describe without a specific example. One post to comp.databases.ms-access some years ago put it this way:
I would like to combine a field's values from multiple records in a single field. For example:
Last First Code ------- --------- ---- Lesand Danny 1 Lesand Danny 2 Lesand Danny 3 Benedi Eric 7 Benedi Eric 14
Result should look like:
Last First Codes ------- --------- ----- Lesand Danny 1,2,3 Benedi Eric 7,14
The sample code download for this article contains both an Access 97 and Access 2000 version. For simplicity, I have created a table with only two columns: one to group by and one to aggregate.
Graphically, the input and output look like this ...
This kind of output suggests a group by query, but there is no built-in aggregate function that will concatenate field values like this. You can sum them, average them, count them, etc., but you can't string them together as a delimited list of values. For that, it is necessary to process the recordset using VBA code.
As mentioned above, this kind of concatenation must be done in code. Accordingly, I created a module and named it basAggregate. It contains two functions. One to recreate the tables on the fly called, oddly enough, RecreateTables(). The other function, FixTable() does the actual work of aggregating column data into rows.
The image below clearly exposes the code, but for the sake of brevity, there are no inline comments. Rather than narrate the entire code block, I'll leave it to the reader to review the code, but the process is basically as follows:
Notice in the image below that the word Stop is highlighted in yellow. By placing a Stop command in the code, the processing halts at this line of code, allowing you to step through, line by line, observing the process. Download the code for this article and give it a try yourself.
There is really no trick to this. It is just simple recordset stuff. However, if you are relatively new to recordsets, there are some things you should know.
If Not rst.BOF And Not rst.EOF Then ...
Set rst = Nothing
Set db = Nothing
For no extra charge ...
The other bit of code simply recreates the tables. While this is somewhat unnecessary for the sake of this article, when posting the solution on the newsgroup, I wanted to supply a script to create the tables, since attachments (such as a sample mdb file) are generally discouraged, if not prohibited.
The code to create tables using SQL DDL is pretty simple, as can be seen in the screen shot below of the RecreateTables() function. First, if a test for tblOriginal indicates that it exists, then it is deleted. Next, a CREATE TABLE script recreates the table, followed by a number of INSERT statements to populate it. Finally, tblCopy is recreated.
While this piece of the code is not necessary to explain how to aggregate data, it is good for Access database programmers to be reminded from time to time that regular SQL DDL scripts do work in Access, and at times may be the best way to dynamically create tables.