sql - ConcatRelated() in complex database - parsing problems

Question

I have been tasked with concatenating values in a multi-table query or report, respectively. I have found Allen Browne's ConcatRelated() function, which appears to be doing what I want it to. I just can't get it to work, however. I know there are lots of different posts on this topic on stackoverflow, but I have read close to 30 of them and none solved my problem really.

For better understanding, the database looks similar to this:

Picture of relationship graph.

What I need to do is output all distinct values from TEST_1 with their respective realizations in TEST_4. So normally, I'd do a query that gets all values from TEST_1 and their (indirectly) associated values in TEST_4. I have already done that, it works fine. But we get multple lines for each entry in TEST_1, what we want is one line for each TEST_1 with the values from TEST_4 concatenated together into one row. There's many more tables like TEST_4 in the actual DB, but I figure I'll have no problem doing this once I get this to work just for that table. The problem is that I just can not get the where clause to work properly, the query with the function somewhat works but it puts every possible value of TEST_4 into every row, NOT just the values assocciated with the row in TEST_4.

I have tried doing it with regular queries, which doesn't work. I then tried the ConcatRelated() function. I saw that it outputs ALL possible values of TEST_4 into the concatenated field, not just the related ones, so I thought maybe the query doesn't check every record in TEST_1. I then tried to build a nested for loop for this, which is probably unnecessary, as the function should be able to do this on its own with the where clause.

I have created a table that combines the values from TEST_1 I want to display in the report and the associated values from TEST_4. The first question: Should I make sure that this table in joined by inner joins?

The query with the function will run on this combined table. This is the Table I will run the query on: Table

Note that the fields in the where clause are text fields!

The query is currently as follows:

SELECT ConcatRelated("Kategorie","conc_Table","TEST_1.Name ="  &  conc_Table.[Name],"",",") AS Expr1, conc_Table.Name, conc_Table.Ort
FROM conc_Table;

With this query and this combination of quotes, I get an "ambiguous name" error, but the error messages will vary depending on the combination of quotation marks.

With the query as it is, I get Johanna/Linz/a,b,c instead of Johanna/Linz/a

If it does work (for example if I leave the where clause out) I get every entry of TEST_4, an undesired result. I wanted to ask whether there is a syntax error or some other error in the query or function itself or whether all of this is

EDIT: A colleague of mine found the mistake. Sharing it in case anyone in the future has the same problem. What I did in ConcatRelated() is the following:

ConcatRelated("Kategorie","conc_Table","TEST_1.Name ="  &  conc_Table.[Name],"",",") 

This is wrong. The function can't refer to an outside table. instead of TEST_1.Name it needs to be conc_Table.Name. I thought the where field couldn't update the where clause when it gets the values from itself, but it can. So the where clause gets dynamically updated when it loops, looping through the records in conc_table.Name and adding the current record to the where clause. Exactly what I needed. I hope this is understandable, if not, send me a message.

Answer 1

With neither a data sample nor your query, it is not easy.

However, if (as) your output lists multiple identical records, use the query as source in a new query where you either apply distinct or group by. Then use this query as source in yet a query whery you concat/join a field.

Also, if you have many records, you may benefit from my DJoin function, fully explained and documented here:

Join (concat) values from one field from a table or query

If you don't have an account, browse for the link: Read the full article.

Code is also on GitHub: VBA.DJoin