Question posted 2012 · +5 upvotes
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?
Accepted answer +9 upvotes
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")
3 code variants in this answer
- Variant 1 — 10 lines, starts with
SELECT - Variant 2 — 1 lines, starts with
"N_ID = " & [N_ID] - Variant 3 — 1 lines, starts with
Format(N_ID, "000")
Top ms-access Q&A (6)
- How can I modify a saved Microsoft Access 2007 or 2010 Import Specification? +31 (2008)
- OleDbCommand parameters order and priority +28 (2009)
- Is there an equivalent to the SUBSTRING function in MS Access SQL? +26 (2009)
- What do I need to read Microsoft Access databases using Python? +25 (2009)
- MS Access library for python +24 (2009)
- is there any replacement of Access? +21 (2009)
ms-access solutions on this site
.