将相关行中的值合并为单个串联的字符串值 [英] 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具有:
tbl_Instructors has:
N_ID | F_Name | L_Name
001 Joe Smith
002 Henry Fonda
003 Lou Reed
tbl_Courses具有:
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
我要返回的是:
N_ID | First_Name | Last_Name | Course_IDs
001 Joe Smith AAA, AAB
002 Henry Fonda CCC
003 Lou Reed DDD, FFF
我认为我需要做一些事情,从tbl_Instructors中选择所有N_ID,然后通过串联从tbl_Courses返回Course_ID,但是这个神奇的步骤暗示了我.有什么帮助吗?我可以通过SQL选择来做到这一点,还是需要使用VB?
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?
推荐答案
使用艾伦·布朗(Allen Browne)的 ConcatRelated()很容易功能.从该网页复制功能并将其粘贴到Access标准代码模块中.
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;
请考虑在两个表中将N_ID
的数据类型从文本更改为数字.如果这样做,则在该ConcatRelated()
表达式的第三个参数中不需要单引号.
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]
并且每当需要使用前导零显示N_ID
时,请使用Format()
表达式.
And whenever you need N_ID
displayed with leading zeros, use a Format()
expression.
Format(N_ID, "000")
这篇关于将相关行中的值合并为单个串联的字符串值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!