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

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