复杂数据库中的 ConcatRelated() - 解析问题 [英] ConcatRelated() in complex database - parsing problems

问题描述

我的任务是分别在多表查询或报告中连接值.我找到了 Allen Browne 的 ConcatRelated() 函数，它似乎在做我想要的.但是，我无法让它发挥作用.我知道在 stackoverflow 上有很多关于这个主题的不同帖子，但我已经阅读了将近 30 篇，但没有一篇真正解决了我的问题.

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:

关系图的图片.

我需要做的是输出来自 TEST_1 的所有不同值以及它们在 TEST_4 中的各自实现.所以通常情况下，我会做一个查询，从 TEST_1 中获取所有值及其在 TEST_4 中的(间接)关联值.我已经这样做了，它工作正常.但是我们为 TEST_1 中的每个条目获得了多行，我们想要的是每个 TEST_1 一行，其中来自 TEST_4 的值连接在一起成为一行.在实际数据库中还有更多的表，如 TEST_4，但我想一旦我让它只为那个表工作，我就没有问题了.问题是我无法让 where 子句正常工作，使用该函数的查询有些工作，但它将 TEST_4 的每个可能值放入每一行，而不仅仅是与 TEST_4 中的行相关联的值.

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.

我试过用常规查询来做，但没有用.然后我尝试了 ConcatRelated() 函数.我看到它将 TEST_4 的所有可能值输出到连接字段中，而不仅仅是相关字段，所以我认为查询可能不会检查 TEST_1 中的每条记录.然后我尝试为此构建一个嵌套的 for 循环，这可能是不必要的，因为该函数应该能够使用 where 子句自行完成此操作.

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.

我创建了一个表格，其中包含了我想在报告中显示的 TEST_1 中的值以及 TEST_4 中的相关值.第一个问题:我应该确保这个表通过内连接连接吗?

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

注意 where 子句中的字段是文本字段！

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

查询目前如下:

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.

按原样查询，我得到 Johanna/Linz/a,b,c 而不是 Johanna/Linz/a

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

如果它确实有效(例如，如果我不使用 where 子句)，我会得到 TEST_4 的每个条目，这是一个不需要的结果.我想问一下查询或函数本身是否存在语法错误或其他错误，或者是否所有这些都是

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

我的一位同事发现了这个错误.共享它，以防将来有人遇到同样的问题.我在 ConcatRelated() 中所做的如下:

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],"",",") 

这是错误的.该函数不能引用外部表.而不是 TEST_1.Name 它需要是 conc_Table.Name.我认为 where 字段在从自身获取值时无法更新 where 子句，但它可以.因此 where 子句在循环时会动态更新，循环遍历 conc_table.Name 中的记录并将当前记录添加到 where 子句中.正是我需要的.我希望这是可以理解的，如果不能，请给我留言.

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.

推荐答案

既没有数据样本也没有查询，这并不容易.

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

但是，如果(因为)您的输出列出了多个相同的记录，请将该查询用作新查询中的源，您可以在其中应用 distinct 或 group by.然后将此查询用作您连接/加入字段的查询中的源.

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.

此外，如果您有很多记录，您可能会受益于我的 DJoin 功能，这里有完整的解释和记录:

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

从表或查询的一个字段中加入(连接)值

如果您没有帐户，请浏览链接:阅读全文.

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

代码也在 GitHub 上:VBA.DJoin

这篇关于复杂数据库中的 ConcatRelated() - 解析问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！