none
Problem with multiple UNION statements in SQL

    Question

  • Having difficulty using the UNION stament in the SQL view of Access 2007. I am recieving a "Data type mismatch in criteria expression" error when I perform unions that include more than two queries. I can union any combination of two queries together, but not any more than two at a time. For Example, I can do these operations:

    SELECT * FROM tableA

    UNION

    SELCT * FROM tableB

     

    SELECT * FROM tableB

    UNION

    SELECT * FROM tableC

     

    SELECT * FROM tableA

    UNION

    SELECT * FROM tableC

     

    but I can not do this operation:

     

    SELECT * FROM tableA

    UNION

    SELECT * FROM tableB

    UNION

    SELECT * FROM tableC

    • Edited by A0602 Monday, January 31, 2011 5:28 PM
    Monday, January 31, 2011 3:35 PM

Answers

  • After talking over the problem with a colleague of mine, I had some inspiration. Since each of my queries in dependant on the previous one, I end up re-runnning the same query just by referencing, which in turn, dirties the data I had previously.

    My solution to the problem was to perform the unions in the OPPOSITE order of the hierarchy structure. This way, I may call a query multiple times, but I never call it after I have stored the information.

    My colleague informs me this is a problem because of the way MS Access tries to make things efficient. He identifies it as a bug in the program.

    • Marked as answer by A0602 Monday, January 31, 2011 8:06 PM
    Monday, January 31, 2011 8:06 PM

All replies

  • hi,

    you can use multiple union queries in Access. You need to do this in the SQL View of your query. Use at least one explicit field list for the first select statement instead of the asterisk - the * char, e.g.

    SELECT field1, field2, field3 FROM table1
    UNION
    SELECT * FROM table2
    UNION
    SELECT * FROM table3

    For using the asterisk in the subsequent select statements your tables or referenced queries must return the same number of columns and the columns must be of the same data type or at least they must be implicit castable. Otherwise use field names, e.g.

    SELECT field1, field2, field3 FROM table1
    UNION
    SELECT field1, field2, field3 FROM query2
    UNION
    SELECT field3, field5, field2  FROM table3

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Monday, January 31, 2011 3:44 PM
  • I tried both methods, however I recieve the same error. All the tables have an identical number of fields (with identical field names) so I don't think there is a problem with the number of columns. I will double check the data types to make sure they are all compatible...EDIT* Yes they are.

    Appreciate the timely response btw.

    • Edited by A0602 Monday, January 31, 2011 4:05 PM Update
    Monday, January 31, 2011 4:00 PM
  • The error message you are getting would seem to imply that one or more of your SQL SELECT statements has a WHERE Condition.  Is that correct?  If so, you should look to the WHERE condition for your answer.  If not, you might want to post the actual SQL Statement so that we can get a better look at it.

    Bob Oxford


    Bob Oxford Software Wizards, Inc.
    Monday, January 31, 2011 5:27 PM
  • One of the queries I am trying to union does have a WHERE clause. This is in my qryOrgDepartment query (one additional hidden field). However I do not use this field for the union operation. All other queries are dependant on previous queries for there data (they use JOIN's in the FROM statement).

    SQL Statement for Unions:

    SELECT
    qryOrgDepartment.DeptID,
    qryOrgDepartment.DeptName,
    qryOrgDepartment.DivID,
    qryOrgDepartment.DivName,
    qryOrgDepartment.BranchID,
    qryOrgDepartment.BranchName,
    qryOrgDepartment.SectionID,
    qryOrgDepartment.SectionName,
    qryOrgDepartment.UnitID,
    qryOrgDepartment.UnitName,
    qryOrgDepartment.GroupID,
    qryOrgDepartment.GroupName,
    qryOrgDepartment.SubGroupID,
    qryOrgDepartment.SubGroupName,
    qryOrgDepartment.OtherGroupID,
    qryOrgDepartment.OtherGroupName,
    qryOrgDepartment.WORKENTITYID
    FROM qryOrgDepartment

    UNION ALL
    SELECT * FROM qryOrgDivision

    UNION ALL
    SELECT * FROM qryOrgBranch;


    Your questions have made me look much more closely at my SQL allowing me to understand it more, but I still don't see a solution here...

    Monday, January 31, 2011 5:51 PM
  • After talking over the problem with a colleague of mine, I had some inspiration. Since each of my queries in dependant on the previous one, I end up re-runnning the same query just by referencing, which in turn, dirties the data I had previously.

    My solution to the problem was to perform the unions in the OPPOSITE order of the hierarchy structure. This way, I may call a query multiple times, but I never call it after I have stored the information.

    My colleague informs me this is a problem because of the way MS Access tries to make things efficient. He identifies it as a bug in the program.

    • Marked as answer by A0602 Monday, January 31, 2011 8:06 PM
    Monday, January 31, 2011 8:06 PM
  • hi,

    it would be really nice, if you or your colleague can craft a repro. As I'm not sure, if it is a bug or just the normal, expected behaviour.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Monday, February 07, 2011 9:45 AM