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!

RyanKDalton
  • 1,271
  • 3
  • 14
  • 30
  • Can you elaborate on "fails to execute?" Do you get an error? Does the query timeout? – Joe Stefanelli Jan 07 '11 at 19:58
  • Can you try using UNION only rather than UNION ALL? – PMC Jan 07 '11 at 20:04
  • 1
    @Joe- Good question. Specifically I get an "ODBC--call failed" (I am linking to MySQL via ODBC) and the error "You have an error in your SQL syntax; check the manual for the right syntax to use near UNION ALL..." – RyanKDalton Jan 07 '11 at 20:04
  • @Paul- Nope, same error when using UNION or UNION ALL. – RyanKDalton Jan 07 '11 at 20:06
  • This may be way off but I remember something about Access requiring UNION ALL then UNION for subsequent – PMC Jan 07 '11 at 20:08
  • Total stab in the dark: Does enclosing each query in parens make a difference? `(SELECT...FROM A) UNION ALL (SELECT...FROM B) UNION ALL (SELECT...FROM C)` – Joe Stefanelli Jan 07 '11 at 20:15
  • @Paul- Good suggestion but does not work. @Joe- Great thought, and enclosing the select statements in () still works if using 2 tables, but does not affect the success once the third is included. – RyanKDalton Jan 07 '11 at 20:21
  • 1
    There is no Access/Jet/ACE restriction on UNION vs. UNION AL as Paul McCowat speculates. UNION eliminates dupes, UNION ALL does not. You should always use UNION ALL if you know for a fact that the records returned by each of your SELECTs do not overlap, as it will be much faster. That is, use UNION ALL by preference, and only use UNION when you definitely know you have to eliminate dupes. – David-W-Fenton Jan 08 '11 at 03:22
  • Is the above SQL the real thing? And is A, B or C a view? There is simply no reason why you shouldn't be able to UNION three SELECT statements from a MySQL data source (I just tested). – David-W-Fenton Jan 08 '11 at 03:25

2 Answers2

2

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.

PMC
  • 4,698
  • 3
  • 37
  • 57
  • Paul, thanks for testing and posting this code snippet. I have no doubt that it works in SQL Server, but the limited Access SQL capabilities will not accept this. It comes back with a "An action query cannot be used as a row source". I will continue to investigate other options, and maybe just move it over to MySQL native. As far as looking at the design, I agree. This need came out of a previously implemented design where instead of assigning the bill to 1 entity, they could apply the bill to a "generic" entity and that cost was equally distributed among up to 49 entities. – RyanKDalton Jan 07 '11 at 22:42
  • Er, the error you're reporting suggest you're trying to use a DML query in a SELECT, which you cannot do. That is, nothing in Paul's SQL is a DML statement, so cannot possibly produce that error, so it suggest that what you're using is quite different. – David-W-Fenton Jan 08 '11 at 03:24
0

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;
sparkitny
  • 1,503
  • 4
  • 18
  • 23
Steven Dey
  • 1