none
Change UNION query to non-UNION = "Syntax error in union query."

    Question

  • It seems that Access (2010) won't allow changing what was originally a stored UNION query to a non-UNION query (removing the UNION clause). I can neither complete this action in VBA using the CurrentDb.QueryDefs("qComposite").SQL nor manually in the query's stored SQL. It always results in the error, "Syntax error in union query."

    Is there a way around this issue? This query is used as a recordsource for a report that may or may not require a UNION based on user settings. Any info/suggestions would be appreciated.

    Monday, May 5, 2014 3:11 PM

Answers

  • Thanks for your reply. After struggling with this for hours, I just figured out the problem about two minutes ago. The problem is that I had parentheses around the non-union SQL statement, like this:

    (SELECT FieldName FROM Table1);

    Access interprets this as a UNION type of query statement, and therefore expects a UNION clause. I should have just posted my original query, as it is equally simple as the example you offered. I'm not brand new to this, but every now and then I come across something that leaves me baffled. Usually it turns out to be something simple, as is the case here. I'm sorry for the confusion, but I sincerely do appreciate your time.

    Monday, May 5, 2014 4:27 PM

All replies

  • Hi,

    Not sure I understand exactly what you tried to do. If you had a UNION query that looked like this:

    SELECT FieldName FROM Table1
    UNION
    SELECT FieldName FROM Table2

    Did you try to change it by just removing the UNION keyword and ended up with something like this?

    SELECT FieldName FROM Table1
    SELECT FieldName FROM Table2

    If so, that's not a valid SQL statement for a query. So, what exactly did you want in the non-UNION query?


    • Edited by .theDBguy Monday, May 5, 2014 3:21 PM
    Monday, May 5, 2014 3:20 PM
  • Please post the SQLs of your working Union Query and the attemped "non-Union" Query.

    Addendum:  Note that you should rarely need to use the Union Query if the Table Structure is correct.  Union Querare quite often used to overcome the fragmentation of the dataset of an Entity, e.g. using different Tables to store data for different subsets of the same Entity.  I advised this in your earlier thread

    MSDN Thread: Method for producing report with user-selected (via a form) subreports

    I urge you to read up on the articles whose links I posted in the earlier thread.  Union Queries are combersome and certainly inefficient compared with using a normal Query with the correct Table Structure.  Worse still, you will have to continually find / work out the harder and inefficcient methods / work-arounds just to complete things that should be easy with the correct Table Strcuture.  I wrote earlier that Access will work with you if the Table Structure is correct instead of against you with the incorrect Table Structure. 

      


    Van Dinh


    • Edited by Van Dinh Monday, May 5, 2014 3:42 PM Addendum
    Monday, May 5, 2014 3:22 PM
  • Hi,

    Not sure I understand exactly what you tried to do. If you had a UNION query that looked like this:

    SELECT FieldName FROM Table1
    UNION
    SELECT FieldName FROM Table2

    Did you try to change it by just removing the UNION keyword and ended up with something like this?

    SELECT FieldName FROM Table1
    SELECT FieldName FROM Table2

    If so, that's not a valid SQL statement for a query. So, what exactly did you want in the non-UNION query?


    What I'm doing goes from something like this:

    SELECT FieldName FROM Table1
    UNION
    SELECT FieldName FROM Table2;

    to this:

    SELECT FieldName FROM Table1;

    Access doesn't seem to like reverting back to a non-union type of query once it has been established as a union.

    Monday, May 5, 2014 3:49 PM
  • Access doesn't seem to like reverting back to a non-union type of query once it has been established as a union.

    Hi,

    I'm sorry but I don't understand the above statement. How exactly did you try to convert the UNION query back to a non-UNION query? I just tried it manually, and there was no problem. If you tried it using code, please post the code. Thanks.

    Monday, May 5, 2014 4:13 PM
  • Thanks for your reply. After struggling with this for hours, I just figured out the problem about two minutes ago. The problem is that I had parentheses around the non-union SQL statement, like this:

    (SELECT FieldName FROM Table1);

    Access interprets this as a UNION type of query statement, and therefore expects a UNION clause. I should have just posted my original query, as it is equally simple as the example you offered. I'm not brand new to this, but every now and then I come across something that leaves me baffled. Usually it turns out to be something simple, as is the case here. I'm sorry for the confusion, but I sincerely do appreciate your time.

    Monday, May 5, 2014 4:27 PM
  • Hi,

    Glad to hear you got it sorted out. It might still help to post the original SQL if not to just make sure you come out with the correct understanding of what you actually had. For example, the parens around an SQL statement is usually interpreted as a "Sub-Query" and not necessarily a UNION query.

    Good luck with your project.

    Monday, May 5, 2014 4:35 PM