Combine values from related rows into a single concatenated string value

The Problem (Q-score 5, ranked #30th of 67 in the Access VBA archive)

The scenario as originally posted in 2012

I’m trying to aggregate some instructor data (to easily show which courses an instructor taught in a semester), and up until now I’ve just accepted having multiple rows for each instructor. However, it would be beneficial to some business processes if I could have all of an instructor’s teaching in a single row. Here is some example data (my tables have a lot more columns, but the general idea won’t change much.

tbl_Instructors has:

    N_ID | F_Name | L_Name
    001    Joe      Smith
    002    Henry    Fonda
    003    Lou      Reed

tbl_Courses has:

    Course_ID | N_ID | Course_Info
    AAA         001    PHYS 1
    AAB         001    PHYS 2
    CCC         002    PHYS 12
    DDD         003    PHYS 121
    FFF         003    PHYS 224

What I want to return is:

    N_ID | First_Name | Last_Name | Course_IDs
    001    Joe          Smith       AAA, AAB
    002    Henry        Fonda       CCC
    003    Lou          Reed        DDD, FFF

I think I need to do something with selecting all N_IDs from tbl_Instructors, then returning the Course_IDs from tbl_Courses via concatenation, but that magic step has alluded me. Any help? Can I do this via SQL selects or will I need to use VB?

Why community consensus is tight on this one

Across 67 Access VBA entries in the archive, the accepted answer here holds solid answer (above median) status — meaning voters are unusually aligned on the right fix.

The Verified Solution — solid answer (above median) (+9)

11-line Access VBA pattern (copy-ready)

This is easy using Allen Browne’s ConcatRelated() function. Copy the function from that web page and paste it into an Access standard code module.

Then this query will return what you asked for.

SELECT
    i.N_ID,
    i.F_Name,
    i.L_Name,
    ConcatRelated(
        "Course_ID",
        "tbl_Courses",
        "N_ID = '" & [N_ID] & "'"
        ) AS Course_IDs
FROM tbl_Instructors AS i;

Consider changing the data type of N_ID from text to numeric in both tables. If you do that, you don’t need the single quotes in the third argument to that ConcatRelated() expression.

"N_ID = " & [N_ID]

And whenever you need N_ID displayed with leading zeros, use a Format() expression.

Format(N_ID, "000")

When to Use It — vintage (14+ years old, pre-2013)

Ranked #30th in its category — specialized fit

This pattern sits in the 71% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Access VBA archive for a higher-consensus alternative.

What changed between 2012 and 2026

The answer is 14 years old. The Access VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.