聚合查询无结果致Access窗体空白的优化方案问询

我正在构建一个试题数据库，层级关系为试题→主题→模块，需要按主题或模块筛选试题，且同一模块下的试题不能重复。

目前用AggregateQuery（聚合查询）可实现无重复查询，但当查询无结果时（比如查询模块2），由于聚合查询不可更新，Access窗体直接显示空白。当前的解决办法是切换到可更新的UnaggregatedQuery，但这种方法过于繁琐，希望找到无需二次查询的空行赋值方案。

表结构 #

Question表（试题表） #

-----------------------------------------------------------------------------------------------------
|    question_id    |  question_number  |     paper_id      |      year_id      |    is_selected    |
-----------------------------------------------------------------------------------------------------
|                 1 |                 1 |                 1 |                 1 |               Yes |
-----------------------------------------------------------------------------------------------------
|                 2 |                 1 |                 1 |                 2 |               Yes |
-----------------------------------------------------------------------------------------------------
|                 3 |                 2 |                 1 |                 1 |               Yes |
-----------------------------------------------------------------------------------------------------

Topics表（主题表） #

---------------------------------------------------------------------------------
|     topic_id      |   Topic Number    |    Topic Name     |     module_id     |
---------------------------------------------------------------------------------
|                 1 | 1.1               | Topic 1.1         |                 1 |
---------------------------------------------------------------------------------
|                 2 | 1.2               | Topic 1.2         |                 1 |
---------------------------------------------------------------------------------
|                 3 | 2.1               | Topic 2.1         |                 2 |
---------------------------------------------------------------------------------
|                 4 | 2.2               | Topic 2.1         |                 2 |
---------------------------------------------------------------------------------

Question_has_topics表（试题-主题关联表） #

-------------------------------------------------------------
| question_has_topi |     topic_id      |    question_id    |
-------------------------------------------------------------
|                 1 |                 1 |                 1 |
-------------------------------------------------------------
|                 2 |                 2 |                 1 |
-------------------------------------------------------------

Modules表（模块表） #

-------------------------------------------------------------
|     module_id     |      number       |       title       |
-------------------------------------------------------------
|                 1 |                 1 | Module 1          |
-------------------------------------------------------------
|                 2 |                 2 | Module 2          |
-------------------------------------------------------------

现有查询与代码 #

AggregateQuery（聚合查询，去重） #

SELECT Question.question_id, First(Topics.topic_id) AS FirstOftopic_id, First(Module.module_id) AS FirstOfmodule_id
FROM ([Module] INNER JOIN Topics ON Module.module_id = Topics.module_id) INNER JOIN (Question INNER JOIN Question_has_topics ON Question.question_id = Question_has_topics.question_id) ON Topics.topic_id = Question_has_topics.topic_id
WHERE (((Module.module_id)=1))
GROUP BY Question.question_id
ORDER BY Question.question_id;

UnaggregatedQuery（非聚合查询，可更新） #

SELECT Question.question_id, Topics.topic_id, Module.module_id
FROM ([Module] INNER JOIN Topics ON Module.module_id = Topics.module_id) INNER JOIN (Question INNER JOIN Question_has_topics ON Question.question_id = Question_has_topics.question_id) ON Topics.topic_id = Question_has_topics.topic_id
WHERE (((Module.module_id)=2));

现有切换逻辑VBA代码 #

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("AggregateQuery")
If rs.RecordCount = 0 Then
    Me.RecordSource = "UnaggregatedQuery"
Else
    Me.RecordSource = "AggregateQuery"
End If

解决方案：无需二次查询的空行赋值方案 #

通过修改聚合查询，让它在无结果时返回一条空记录，同时保证有结果时正常去重，且窗体能识别空行（避免空白）。

方法1：用UNION ALL拼接空记录 #

修改AggregateQuery，将原查询与空记录查询组合，仅在原查询无结果时显示空记录：

SELECT Question.question_id, First(Topics.topic_id) AS FirstOftopic_id, First(Module.module_id) AS FirstOfmodule_id
FROM ([Module] INNER JOIN Topics ON Module.module_id = Topics.module_id) INNER JOIN (Question INNER JOIN Question_has_topics ON Question.question_id = Question_has_topics.question_id) ON Topics.topic_id = Question_has_topics.topic_id
WHERE (((Module.module_id)=[Forms]![你的窗体名]![模块筛选控件]))
GROUP BY Question.question_id
UNION ALL
SELECT Null AS question_id, Null AS FirstOftopic_id, [Forms]![你的窗体名]![模块筛选控件] AS FirstOfmodule_id
FROM (SELECT COUNT(*) AS cnt FROM Question WHERE EXISTS (SELECT 1 FROM Question_has_topics INNER JOIN Topics ON Question_has_topics.topic_id=Topics.topic_id WHERE Topics.module_id=[Forms]![你的窗体名]![模块筛选控件] AND Question_has_topics.question_id=Question.question_id)) AS sub
WHERE sub.cnt=0
ORDER BY question_id;

• 用[Forms]![你的窗体名]![模块筛选控件]动态获取筛选的模块ID，避免硬编码
• 子查询统计当前模块下的试题数量，为0时返回空记录
• 空记录保留模块ID，让窗体关联当前筛选模块，其他字段为Null

方法2：LEFT JOIN强制返回空行 #

从模块表出发做左关联，确保模块存在就返回至少一条记录：

SELECT 
    Q.question_id, 
    First(T.topic_id) AS FirstOftopic_id, 
    M.module_id AS FirstOfmodule_id
FROM 
    Module M
    LEFT JOIN (
        Topics T
        INNER JOIN (
            Question Q
            INNER JOIN Question_has_topics QHT ON Q.question_id = QHT.question_id
        ) ON T.topic_id = QHT.topic_id
    ) ON M.module_id = T.module_id
WHERE M.module_id=[Forms]![你的窗体名]![模块筛选控件]
GROUP BY Q.question_id, M.module_id
ORDER BY Q.question_id;

• 左关联确保模块无试题时也返回一条记录，question_id和topic_id为Null，module_id保留筛选值
• 有结果时仍按question_id分组去重，无结果时返回空行，Access窗体可正常识别

优化后的VBA逻辑 #

无需切换查询，直接绑定修改后的聚合查询：

' 绑定修改后的聚合查询
Me.RecordSource = "ModifiedAggregateQuery"
' 动态更新筛选时重新查询
Me.Requery

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