Union Query button weird error? (1 Viewer)

[mloucel]

Hello all..

Has anyone run into an issue with the union query button where no matter how you add the tables when you click on the button generates a blank page then if you try to run it, of course gives you an error: Invalid SQL statement, expected [etc]

I thought it was my computer, but it does the same in my laptop, YES I KNOW LEARN THE SQL STATEMENT FOR THE JOIN, got it already did, but still that does not take away the fact that now 3 of my computers have the same behavior so coincidence of am I doing something wrong???

Process:
Create query design
add both tables [both are identical, 1 is the backup of old data]
click on the Union query button.
poof a blank screen show..

I'm using Office 365, V2306, Build 16529.20126, on windows 11

 

[Josef P.]

Try this approach:
Create a union query without knowing SQL:

1. insert your first table to create a "normal" select query
2. insert an alias for the table
3. select your required fields
4. change to SQL view
   you will see something like this:
   select T.Field1, T.Field2 from YourTable as T
5. copy this SQL statement into the clipboard
6. click on the union query button
7. insert the SQL statment
8. write UNION ALL (or only UNION ... difference see online docu) under it
9. Paste the copied SQL statement again
10. Rename the table name (not the alias) with the name of the 2nd table

Result:

select T.Field1, T.Field2 from Table1 as T
UNION ALL
select T.Field1, T.Field2 from Table2 as T

 

[isladogs]

@mloucel
Your approach is indeed wrong.
The method you described adding two tables without a join doesn't generate a union.
You are in fact making a Cartesian join query which is a totally different thing. For more info, see

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

www.isladogs.co.uk

Union queries together with Passthrough and Data Definition are SQL only. They cannot be created or edited in design view.
When you click any of those buttons, a new blank SQL view window is created ready for the query SQL to be entered

To make a UNION query, first make a valid SELECT query then change to SQL view and edit the SQL e.g. using the approach described above

 

[Josef P.]

[OT]
Forgot again to stop by Colin's website first.

 

[mloucel]

@mloucel
Your approach is indeed wrong.
The method you described adding two tables without a join doesn't generate a union.
You are in fact making a Cartesian join query which is a totally different thing. For more info, see

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

www.isladogs.co.uk

Union queries together with Passthrough and Data Definition are SQL only. They cannot be created or edited in design view.
When you click any of those buttons, a new blank SQL view window is created ready for the query SQL to be entered

To make a UNION query, first make a valid SELECT query then change to SQL view and edit the SQL e.g. using the approach described above

Got it.. Thanks, I thought it was going to generate the SQL for me.

 

[mloucel]

Got it.. Thanks, I thought it was going to generate the SQL for me.

AND I added your website and YT to my repertoire