Union Query - Spaces in SQL

[funkykayli]

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.

 

[Joe4]

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:

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!)

 

[funkykayli]

Wow! Thank you so much!!! I am very new to Access but this group always helps me out a bunch!

 

[funkykayli]

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]

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:

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!

 

[funkykayli]

Easy enough! Thanks for the tip...that's great!!!!!!!