使用 MS Access Union Query 合并 3 个表/查询
我建立了一个 MySQL 数据库来存储账单支付。我办公室里的每个人都拥有 MS Access,因此我正在使用 MS Access 构建前端数据库报告工具并链接到后端的 MySQL 表。
我创建了一些引用 MySQL 表的 Access 查询,完成了一些操作,现在想要将三个查询(具有相同的表结构)合并回一个我可以构建报告的查询。
通过我的研究(文章1, Article2 等),我发现 Union 查询正是我所需要的。我可以很好地合并 2 个表,但是当我尝试合并第三个表时,查询无法执行。我已经单独测试了每个组合的联合查询(1-2、1-3、2-3),任何组合都有效。我试图了解我可能做错了什么,以便将第三个查询合并到一个联合中。您能提供一些建议吗?
表 1 = A 表 2 = B 表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 个表中。想法?
谢谢!
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.
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.
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?
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
;
* 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?
Thanks!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我无法在 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.
It may be worth looking at the schema design / relationships to see if this can be avoided.
您需要一个分号 (;) 来结束查询。见下文:
You need a semicolon (;) to end the query. See below: