Union Query - Spaces in SQL

funkykayli

Board Regular
Joined
Apr 25, 2007
Messages
183
I have two tables that I would like to take some of the data from and combine into a third table. I have started to write a union query however I think that SQL does not like spaces in field names. Here is my SQL

SELECT Claimant Name as [Claimant Name] FROM tbl_AIG
UNION SELECT
Name as [Claimant Name] FROM tbl_Liberty;

It is not selecting the first part "Claimant Name". Is this b/c of the space? The table is an imported excel file and that is how the column heading comes acrosss.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,935
Office Version
  1. 365
Platform
  1. Windows
Yes, you cannot have spaces in your field names. If you do, you must surround the names with the square brackets.

How about trying something like this:
Code:
SELECT [tbl_AIG].[Claimant Name] as Claimant_Name 
FROM [tbl_AIG]
UNION 
SELECT [tbl_Liberty].[Name] as Claimant_Name 
FROM [tbl_Liberty];
(it doesn't hurt to surround everything with square brackets!)
 
Last edited:

funkykayli

Board Regular
Joined
Apr 25, 2007
Messages
183
Wow! Thank you so much!!! I am very new to Access but this group always helps me out a bunch!
 

funkykayli

Board Regular
Joined
Apr 25, 2007
Messages
183
Ok so now for the second part. What if I want to include more fields?

SELECT [tbl_AIG].[Claimant Name] as [Claimant Name] FROM [tbl_AIG],
SELECT [tbl_AIG].[Loss Date] as [Loss Date] FROM [tbl_AIG]
UNION
SELECT [tbl_Liberty].[Name] as [Claimant Name] FROM [tbl_Liberty],
SELECT [tbl_Liberty].[Acc Date] as [Loss Date] FROM [tbl_Liberty];

I get an error with the above SQL that says "Syntax error in FROM clause". Do I need the from clause?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,935
Office Version
  1. 365
Platform
  1. Windows
You only have one SELECT ... FROM clause per record source (table/query), not one per field. Each field is separated by a comma.

So it would look like this:
Code:
SELECT [tbl_AIG].[Claimant Name] as [Claimant Name], [tbl_AIG].[Loss Date] as [Loss Date] 
FROM [tbl_AIG]
UNION
SELECT [tbl_Liberty].[Name] as [Claimant Name], [tbl_Liberty].[Acc Date] as [Loss Date] 
FROM [tbl_Liberty];

Here is a little secret to building UNION queries.

Build a simple Select query using query builder for each of the tables/queries. So create a simple one table query from tbl_AIG and add the two fields you want. Now, switch your query to SQL view, and copy and paste your code someplace (like Word or NotePad).

Now do the same with the other table/query. To combine these together in a Union query, simply take the two sections of SQL code and combine them together by dropping the semi-colon ( ; ) from the first one and sticking the word UNION between them. Then just paste this code into a SQL View window for a new query, and PRESTO, you have your code without having to write any of the SQL code yourself!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top