将相关行中的值组合成一个串联的字符串值 [英] 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屋！