将其中具有多个值的多行连接到MS Access中的单行 [英] Concatenating multiple rows, with multiple values in it, into single line in MS Access

问题描述

我正在尝试创建简单的需求管理数据库.基本上我有2张桌子，如下所示:

I am trying to create simple requirements management database. Basically I have 2 tables like below:

具有2列的Contract_requirements:

Contract_requirements with 2 columns:

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

SW_requirements

SW_requirements

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

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

And I would like to write query to receive such a table:

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

通过"RootReq"列关联合同要求"和软件要求"表

Tables "Contract requirements" and "SW requirements" are in relation via column "RootReq"

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

Ive tried to implement code from 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中遇到错误

but I get error in Access

错误3831:不能在WHERE或HAVING子句中使用多值字段'RootReq'"

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

你们能帮助我使它正常工作吗? 预先感谢

Could you guys help me to make this working? Thanks in advance

推荐答案

构建查询，将多值字段元素扩展为单个记录.

Build a query that expands the multi-value field elements to individual records.

Query1

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

然后使用该查询作为ConcatRelated()函数的源.

Then use that query as source for ConcatRelated() function.

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

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

Advise not to use spaces nor punctuation/special characters in naming convention.

这篇关于将其中具有多个值的多行连接到MS Access中的单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！