Add to union query

[Netherby]

Hi,

I have a union query that combines two sets of names together.

It works well and my SQL is as follows

SELECT tblName.LastName
FROM tblName UNION SELECT tblName1.LastName
FROM tblName1

I'd now like to add the name 'Smith' to my query result and have tried the following and several variations with no success.

SELECT tblName.LastName
FROM tblName UNION SELECT tblName1.LastName
FROM tblName1 UNION SELECT 'Smith'

Could someone please tell me the correct syntax.

Thanks.

 

[Golom]

Your last SELECT doesn't correspond to the

SELECT field FROM table

format required for a SELECT statement

Try

SELECT tblName.LastName FROM tblName 

UNION 

SELECT tblName1.LastName FROM tblName1 

UNION 

SELECT TOP 1 'Smith' From tblName

 

[dhookom]

You shouldn't even need the TOP 1 since union queries return unique records unless you use UNION ALL.

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

Quite right Duane.

I guess the old adage is true.

Memory is the second thing to go ... and I don't remember what the first thing is.

 

[Netherby]

Thanks guys. It works perfectly without Top 1.