MS Access - Concatenate values from related records

Question

I'm trying to concatenate my search results. I found one article describing this, but couldn't get it to work.

I'm trying to do the following: - I have created two tables (tblBus and tblJoin). I related the tables (1:M). - I have created a search form with a few fields to search for data. - I've also created a query.

For most of the part everything works, except if I try to concatenate my data.

Here is an example of what I'm trying to do:

Stop Number     -     Route Number
        110     -     111
        110     -     222
        115     -     111
        115     -     222

I would like to combine the route numbers like this:

Stop Number     -     Route Number
        110     -     111, 222
        115     -     222, 222

Both fields are Integer fields.

Answer 1

You will need to use a VBA record set to create the comma delimited list of numbers.

The VBA will store the data to be displayed in a temporary table.

Your VBA will open a record set based on a, SQL query that contains your example data. The code will loop through every row in the data detecting when the number in the first column changes resetting a string variable to empty string. As it loops through each row it will add to the comma delimited string.

Alternatively you could write a function that builds a single comma delimited string that is called by a query. The calling quiet will only list the unique values in the first column. The function may be slower than VBA method. Which method you use depends on the number of rows in your table and speed.