Merging 3 tables/queries using MS Access Union Que

问题:

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!

回答1:

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.

回答2:

You need a semicolon (;) to end the query. See below:

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;