How to concatenate multiple rows in Access involving a Link?

Question

I have the following problem concerning my Access Database:

I have 3 tables which are tblComponents, tblErrors, and linkComponentsErrors. This is a many to many relationships which means one component can have many errors and one error can have many components. These a are linked with their primaryKey (tblComponents.componentID and tblErrors.errorID) in the linkComponentsErrors. Both tables also have a field for their name

tblComponents / tblErrors / linkComponentsErrors

|compID|compname|        |errID|errname|        |compID|errID| 
+------------+           +------------+         +------------+
| 1 | HDD-300 |          | 1 | E101 |           | 1 | 1 |
+------------+           +------------+         +------------+
| 2 | SSD-100 |          | 2 | E404 |           | 1 | 2 |
+------------+           +------------+         +------------+
| 3 | CPU-i7 |           | 3 | E123 |           | 2 | 2 |
                                                +------------+
                                                | 2 | 3 |
                                                +------------+
                                                | 3 | 3 |

For the concatenation, I use the VBA-Code by Allen Browne

When I do this Query with the concatenation

SELECT tblComponents.compName, ConcatRelated("errorID", "linkComponentsErrors", "compID = " &[linkComponentsErrors].[compID]) FROM tblComponents;

I get this:

    |compName|Expr| 
    +------------+
    | HDD-300 | 1, 2 |
    +------------+
    | SSD-100 | 2, 3 |
    +------------+
    | CPU-i7 | 3 |
    +------------+

But what I want is this:

    |compName|Expr| 
    +------------+
    | HDD-300 | E101, E404 |
    +------------+
    | SSD-100 | E404, E123 |
    +------------+
    | CPU-i7 | E123 |
    +------------+

I am lost in the SQL Syntax in the Concat function with this join to the name of the error. I would be very happy if you know a solution to this problem.

Looks like you should just have to change `ErrorID` (`errID`?) for `ErrName` — Darren Bartrup-Cook, Jun 30 '20 at 12:11
But where do I say that the errName belongs to the tblErrors? The method doesn't know where the errName comes from. — fundi, Jun 30 '20 at 12:22

score 2 · Accepted Answer · answered Jun 30 '20 at 12:25

2

You can use my DJoin function for this:

SELECT 
    tblComponents.compname, 
    DJoin(
        "errname",
        "SELECT compID, errname 
            FROM linkComponentsErrors 
            INNER JOIN tblErrors ON linkComponentsErrors.errID = tblErrors.errID",
        "compID = " & [tblComponents].[compID] & "",
        ", ") AS errnames
FROM 
    tblComponents 
INNER JOIN 
    linkComponentsErrors ON tblComponents.compID = linkComponentsErrors.compID
GROUP BY 
    tblComponents.compname, 
    DJoin(
        "errname",
        "SELECT compID, errname 
            FROM linkComponentsErrors 
            INNER JOIN tblErrors ON linkComponentsErrors.errID = tblErrors.errID",
        "compID = " & [tblComponents].[compID] & "",
        ", "), 
    tblComponents.compID
ORDER BY 
    tblComponents.compID;

Output:

answered Jun 30 '20 at 12:25

Gustav

53,498
7
29
55

Oh wow, thank you very much, this really helped. But what do I do when I have a fourth table like tblCategories which has the same relationship to tblComponents like the tblErrors? Do I just need to repeat this, make a selection adding with another DJoin(), an InnerJoin and the the DJoin() in the Group by with the tblCategories? – fundi Jun 30 '20 at 13:02
That may be a bit difficult, as `DJoin` only holds one collection, thus this should hold the categories as well. Could be done by using a compound key, I guess. Or you could make two queries and output the results to two temp tables, then combine these for the final result. You may have to open a new question with reference to this, listing the additional input (categories) and the expected result. – Gustav Jun 30 '20 at 13:12

score 1 · Answer 2 · answered Jul 01 '20 at 09:24

If you don't want to use DJoin, you can do two queries and use the ConcatRelated function by Allen Browne:

A helping query "Hilfsabfrage" that does the join with the link:

SELECT tblErrors.errName, tblErrors.errID, linkComponentsErrors.CompID FROM tblErrors INNER JOIN linkComponentsErrors ON tblErrors.errID = linkComponentsErrors.errID

And the main query:

SELECT tblComponents.compName, ConcatRelated("errName", "Hilfsabfrage", "compID = " & [compID]) FROM tblComponents

Don't do this in your main query: SELECT tblComponents.compName, ConcatRelated("errName", "Hilfsabfrage", "compID = " &[Hilfsabfrage].[compID]) FROM tblComponents . The query has do be without the [Hilfsabfrage].

You can also put more than one ConcatRelated in your query.

How to concatenate multiple rows in Access involving a Link?

2 Answers2

Linked

Related