Combining values ​​from related strings into one concatenated string value

Question

I am trying to combine some instructor data (to easily show what courses the teacher taught in the semester), and so far I have just accepted a few lines for each instructor. However, it would be useful for some business processes if I could train teachers in just one row. Here are some examples of data (there are more columns in my tables, but the general idea will not 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 

I want to return:

  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 by selecting all N_IDs from tbl_Instructors and then returning Course_ID from tbl_Courses via concatenation, but this magic step hinted to me. Any help? Can I do this with SQL queries or will I need to use VB?

Answer 1

This is easy to use with the Allen Browne ConcatRelated () function. Copy the function from this web page and paste it into the standard Access code module.

Then this query will return what you requested.

 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 N_ID from text to numeric in both tables. If you do this, you do not need single quotes in the third argument for this ConcatRelated() expression.

 "N_ID = " & [N_ID] 

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

 Format(N_ID, "000")