MS Access中ConcatRelated函数在源表正常但查询中失效问题咨询

Let's break down why your ConcatRelated function stopped working when using Query1, and walk through two solid solutions to get it back on track.

Why the Problem Happens #

When you use ConcatRelated in a query that joins MainTable with Table2, the function's filter parameter ([CategoryNumber] = " & [CategoryNumber]) still targets the entire MainTable dataset—not just the rows filtered by your Table2 association. Additionally, if Query1 returns duplicate CategoryNumber rows (one per TextField entry), the function might either repeat results or throw errors due to ambiguous field references.

Solution 1: Embed the Table2 Filter Directly in ConcatRelated #

Modify the ConcatRelated call to explicitly only include rows linked to Table2 via the Tag field. This ensures you're only concatenating values from the categories you care about.

First, confirm Query1 is structured to pull relevant rows:

SELECT MainTable.CategoryNumber, MainTable.TextField
FROM MainTable
INNER JOIN Table2 ON MainTable.Tag = Table2.Tag;

Then, adjust your final query to use a filtered ConcatRelated and DISTINCT to avoid duplicate category entries:

SELECT 
    DISTINCT MainTable.CategoryNumber,
    ConcatRelated(
        "[TextField]", 
        "[MainTable]", 
        "[CategoryNumber] = " & MainTable.CategoryNumber & " AND EXISTS (SELECT 1 FROM Table2 WHERE Table2.Tag = MainTable.Tag)"
    ) AS ConcatenatedText
FROM MainTable
INNER JOIN Table2 ON MainTable.Tag = Table2.Tag;

• The EXISTS clause adds an extra layer to ensure only rows linked to Table2 are included in the concatenation.
• Use DISTINCT so each CategoryNumber only appears once with its merged text.

Solution 2: Pre-Filter Categories First (Cleaner Approach) #

If you prefer a more modular setup, first create a query to isolate just the CategoryNumbers you need from Table2's linked rows, then use that to drive the ConcatRelated function.

1. Create a new query (name it Query_TargetCategories) to get your filtered categories:

SELECT DISTINCT MainTable.CategoryNumber
FROM MainTable
INNER JOIN Table2 ON MainTable.Tag = Table2.Tag;

2. Build your final query using this pre-filtered list:

SELECT 
    Query_TargetCategories.CategoryNumber,
    ConcatRelated("[TextField]", "[MainTable]", "[CategoryNumber] = " & Query_TargetCategories.CategoryNumber) AS ConcatenatedText
FROM Query_TargetCategories;

This method is easier to debug because you first confirm exactly which categories are being processed before running the concatenation.

Quick Troubleshooting Checks #

• If CategoryNumber is a text field (not numeric), adjust the filter string to include single quotes:

  "[CategoryNumber] = '" & Query_TargetCategories.CategoryNumber & "'"
  

• Double-check that all table and field names in ConcatRelated match your actual schema (no typos!).
• Ensure the Allen Browne ConcatRelated function is properly saved in your Access database's standard module (not a form/report module).

内容的提问来源于stack exchange，提问作者YourNick