sql - 将包含多个值的多行连接到 MS Access 中的单行

Question

我正在尝试创建简单的需求管理数据库。基本上我有 2 个如下表:

Contract_requirements 有 2 列:

CR_ReqID    |   Description
reqCR1      |   Contract req description 1
reqCR2      |   Contract req description 2

软件需求

Title               |   SW_ReqID     |  RootReq
SW req description 1|   reqSW1       |   reqCR1, reqCR2
SW req description 2|   reqSW2       |   reqCR1
SW req description 3|   reqSW3       |   reqCR2

我想编写查询来接收这样的表:

CR_ReqID  |Description                  |where used?
reqCR1    |Contract req description 1   |reqSW1, reqSW2  
reqCR2    |Contract req description 2   |reqSW1, reqSW3

“契约(Contract)要求”和“SW 要求”表通过“RootReq”列关联

我试过实现 Allen Browne 的代码 http://allenbrowne.com/func-concat.html#Top

这是我的查询

SELECT Contract_requirements.CR_ReqID, ConcatRelated("SW_ReqID     ","SW_requirements","RootReq = """ & [CR_ReqID] & """") AS Expr1
FROM Contract_requirements;

但我在 Access 中遇到错误

"Error3831: The multi-valued field 'RootReq' cannot be used in a WHERE or HAVING clause"

你们能帮我解决这个问题吗？提前致谢

Answer 1

构建一个将多值字段元素扩展到单个记录的查询。

查询1

SELECT SW_Requirements.Title, SW_Requirements.SW_ReqID, SW_Requirements.RootReq.Value 
FROM SW_Requirements;

然后将该查询用作 ConcatRelated() 函数的源。

SELECT Contract_Requirements.*, 
ConcatRelated("SW_ReqID","Query1","[SW_Requirements.RootReq.Value]='" & [CR_ReqID] & "'") AS WhereUsed
FROM Contract_Requirements;

建议不要在命名约定中使用空格或标点符号/特殊字符。