Concatenate Column Values from Multiple Rows into a Single Column with Access

August 15, 2003

The Problem

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 ...

Click for larger image

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.

The Solution

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:

• Create an ordered recordset of the original table.
• Capture the values of Column1 and Column2 into variables.
• Loop through the recordset, comparing the current value of Column1 with the previous value for Column1, which was saved into the local variable.
• If the value for Column1 has not changed, then add the value of Column2 to the local variable, separating the values with a delimiter of some kind.
• If the value has changed, execute a SQL INSERT statement to add a new row to the new table with the Column1 value and the concatenated Column2 value(s).
• After inserting a new row, reset the values for both Column1 and Column2 to the next values in the recordset.
• Repeat this process until the entire recordset is processed.
• Run the final SQL INSERT statement to add the last record.

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.

Click for larger image

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.

1. My code uses DAO recordsets, which will require that you set a reference to the Microsoft DAO 3.xx Object Library. (Alternatively, you could use ADO recordsets, but most Access old-timers I know are still using DAO for tasks like these.)
2. It is CRITICAL to order the recordset on the column that serves as the "group by" data.
3. Opening the recordset as a forward-only snapshot will greatly improve performance though it will likely only be noticed when processing large datasets.
4. Always include code to test for empty recordset

If Not rst.BOF And Not rst.EOF Then ...

5. Always remember to dispose of your database objects:

Set rst = Nothing

Set db = Nothing

6. Remember to delimit string values with single quotes (') in SQL INSERT statements.

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.

Click for larger image

» See All Articles by Columnist Danny J. Lesandrini