使用MS Access Union查询合并3个表/查询 [英] Merging 3 tables/queries using MS Access Union Query

问题描述

我建立了一个MySQL数据库来存储帐单付款.我办公室的每个人都拥有MS Access，因此我正在使用MS Access构建前端数据库报告工具，并链接到后端的MySQL表.

I have built a MySQL database to store bill payments. Everyone in my office has MS Access, so I am building a front-end database reporting tool using MS Access and linking to the MySQL tables on backend.

我创建了一些引用MySQL表的Access查询，进行了一些操作，现在想将三个查询(具有相同的表结构)合并回一个可以建立报表的查询.

I have created some Access queries that reference the MySQL tables, done some manipulation, and now want to merge three queries (with the same table structure) back into one that I can build my report on.

通过我的研究( article1 ， article2 等)，我发现我需要一个Union查询.我可以很好地合并2个表，但是当我尝试合并3个表时，查询将无法执行.我已经分别对每个组合(1-2、1-3、2-3)和任何一对组合测试了Union查询.我试图了解我可能在做错什么，以便将第3个查询合并到单个Union中.您能提供任何建议吗?

Through my research (article1, article2, and others) , I have found that a Union query is what I need. I can union 2 tables just fine but when I try to union the 3rd, the query fails to execute. I have tested the Union query on each combination individually, (1-2, 1-3, 2-3) and any pair works. I am trying to understand what I might be doing wrong in order to incorporate the 3rd query into a single Union. Can you offer any suggestions?

表1 = A 表2 = B 表3 = C

Table 1 = A Table 2 = B Table 3 = C

SELECT A.Year, A.BillingQuarter, A.Name, A.ObjectCode, A.Amount
FROM A

UNION  ALL SELECT B.Year, B.BillingQuarter, B.Name, B.ObjectCode, B.Amount
FROM B

UNION ALL SELECT C.Year, C.BillingQuarter, C.Name, C.ObjectCode, C.Amount
FROM C

;

*更新* 将每个查询导出到独立表后，我能够运行一个3表UNION ALL查询并将它们合并在一起.因此，问题显然出在我对UNION 3查询的尝试中，而不是在3个表中.有想法吗?

* UPDATE * After exporting each query to standalone tables, I was able to run a 3-table UNION ALL query and merge them together. So the problem clearly lies in my attempt to UNION 3 queries, not in 3 tables. Thoughts?

谢谢！

推荐答案

我无法在Access中对其进行测试，但它在SQL Server中有效，请选择前两个表，并以UNION作为派生表，然后选择UNION表C和派生表.

I can't test this in Access but it works in SQL Server, Select the first two tables with a UNION as a derived table then UNION table C and the derived table.

SELECT Year, BillingQuarter, Name, ObjectCode, Amount FROM
    (SELECT Year, BillingQuarter, Name, ObjectCode, Amount FROM @A
    UNION ALL
    SELECT Year, BillingQuarter, Name, ObjectCode, Amount FROM B)
    AS Derived
    UNION ALL
    SELECT Year, BillingQuarter, Name, ObjectCode, Amount FROM C

可能值得研究一下架构设计/关系，以了解是否可以避免这种情况.

It may be worth looking at the schema design / relationships to see if this can be avoided.

这篇关于使用MS Access Union查询合并3个表/查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！