ConcatRelated ms access

Question

Hi I am currently working on a concat related formula so that I can use the concatenated rows to create an update query for a large set of data. I have the select distinct I have been working on so far below. I think there are some simple things I might be missing, but I do feel like the formula is mostly correct. I am getting an error saying the formula is wrong or too complicated. See the current table I am working with, the desired results after the select query is built and used to update the end table and my work so far. Any help is much appreciated, I am somewhat new to all this.

Failure table (used in select query)

RMA - - Shop_Order -- SN Received ----FD Code

101- - --1234567 ------- 1A234234 ------- NFF

101 - - -1234567 --------1A234234 -------Comp

1122334- - 101 ----------58349103 --------ALGN

The desire is to concatenate the like rows into one record based on the foreign key of RMA/Shop_Order/SN Received fields. So, in this case the first two records above would combine into one record and the FD Codes would combine into a field of "FD Codes" and display "NFF, Comp" Then I would create an update query based on those desired results to update a "Fault Status" field to define whether a return was Fault Found or No Fault Found. To be clear, I have no issues creating an update query. This is just the first time using concatrelated. Below is my attempt so far.

SELECT FailureOver90Days.RMA, FailureOver90Days.Shop_Order, FailureOver90Days.[SN Received], FailureOver90Days.record_time, 
ConcatRelated([FD Code],[FailureOver90Days],"RMA = " & [RMA] & "Shop_Order = " & [Shop_Order] & "[SN Received] =""" & [SN Received] & "") AS [FD Codes]

FROM FailureOver90Days

GROUP BY FailureOver90Days.RMA, FailureOver90Days.Shop_Order, FailureOver90Days.[SN Received], FailureOver90Days.record_time, ConcatRelated([FD Code],[FailureOver90Days],"RMA = " & [RMA] & "Shop_Order = " & [Shop_Order] & "[SN Received] =""" & [SN Received] & "")

HAVING (((FailureOver90Days.RMA) Is Not Null) AND ((FailureOver90Days.Shop_Order) Is Not Null) AND ((FailureOver90Days.[SN Received]) Is Not Null) AND ((FailureOver90Days.record_time) Between #1/2/2020# And #1/9/2020#));

Is this Allen Browne's ConcatRelated() function? Why first 2 fields reversed in third record? — June7, Jun 10 '20 at 17:52

June7 · Accepted Answer · 2023-02-07T16:22:13.397

Assuming this is Allen Browne's ConcatRelated function, the field name and table name must be passed as strings, that means wrapping in quote marks. Also need AND operator for the criteria argument.

SELECT FailureOver90Days.RMA, FailureOver90Days.Shop_Order, FailureOver90Days.[SN Received], FailureOver90Days.record_time, 
ConcatRelated("[FD Code]","[FailureOver90Days]","RMA = " & [RMA] & " AND Shop_Order = " & [Shop_Order] & " AND [SN Received] ='" & [SN Received] & "'") AS [FD Codes]
FROM FailureOver90Days
GROUP BY RMA, Shop_Order, [SN Received], record_time, 
ConcatRelated("[FD Code]","[FailureOver90Days]","RMA = " & [RMA] & " AND Shop_Order = " & [Shop_Order] & " AND [SN Received] ='" & [SN Received] & "'")
HAVING (((FailureOver90Days.RMA) Is Not Null) AND ((FailureOver90Days.Shop_Order) Is Not Null) 
AND ((FailureOver90Days.[SN Received]) Is Not Null) 
AND ((FailureOver90Days.record_time) Between #1/2/2020# And #1/9/2020#));

An old and common topic - here is one previous discussion ConcatRelated function in a query

ConcatRelated ms access

1 Answers1