'Combine values from related rows into a single concatenated string value

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?

ms-access

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'Combine values from related rows into a single concatenated string value

Sources

Related Questions