UNION query question

[Mark Andrews]

If I want to make a simple UNION query in Access:

Select "All employees" as TheName
UNION
Select tblEmployee.TheName from tblEmployee

it doesn't let me do it like this?

Just trying to add an extra row (used as the rowsource for a combobox).

My sql server days are now confusing me on Access syntax,
Any help is appreciated,
Mark

 

[Sylvain Lafontaine]

With Access, a FROM statement is necessary for any Select query; so you'll
have to add a dummy table with a single line:

Select "All employees" as TheName
From TableWithASingleLine
UNION
Select tblEmployee.TheName from tblEmployee

Another solution would be to add a special line to the table tblEmployee
with the value "All Employees" as the employee's name.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Duane Hookom]

Actually the UNION query will remove duplicates so this should work:

SELECT "All employees" as TheName
FROM tblEmployee
UNION
SELECT TheName
FROM tblEmployee;

If you want "All Employees" to appear at the top of the list, you may want
to put some type of symbol or space in front of it.

SELECT " All employees" as TheName
FROM tblEmployee
UNION
SELECT TheName
FROM tblEmployee;

 

[Mark Andrews]

Thank you both for your help!

Actually the UNION query will remove duplicates so this should work:

SELECT "All employees" as TheName
FROM tblEmployee
UNION
SELECT TheName
FROM tblEmployee;

If you want "All Employees" to appear at the top of the list, you may want
to put some type of symbol or space in front of it.

SELECT " All employees" as TheName
FROM tblEmployee
UNION
SELECT TheName
FROM tblEmployee;

 

[Bob Quintal]

Actually the UNION query will remove duplicates so this should
work:

SELECT "All employees" as TheName
FROM tblEmployee
UNION
SELECT TheName
FROM tblEmployee;

If you want "All Employees" to appear at the top of the list, you
may want to put some type of symbol or space in front of it.

SELECT " All employees" as TheName
FROM tblEmployee
UNION
SELECT TheName
FROM tblEmployee;

You can also
SELECT "All employees" as TheName,
-1 as Sequence
FROM tblEmployee
UNION
SELECT TheName,
0 as Sequence
FROM tblEmployee
ORDER BY Sequence, TheName;

 

[Paul Shapiro]

Performance might be better if the sql avoids the duplicates with Select
Distinct, rather than using the Union to remove them. Union All means not to
remove duplicates during the union operation.
SELECT DISTINCT
"All employees" as TheName,
-1 as Sequence
FROM tblEmployee
UNION ALL
SELECT
TheName,
0 as Sequence
FROM tblEmployee
ORDER BY Sequence, TheName;

Or if you have a table that has guaranteed, fixed id values, you could use
something like:
SELECT
"All employees" as TheName,
-1 as Sequence
FROM tblStatusType
Where statusTypeID=1
UNION ALL
....

I often find it useful to include a SequenceNumber table to be used in
queries, with just a single ID field, containing numbers from 1 to N, where
N is however many is guaranteed to be more than enough for any query I
write. That would be a great table to Select From in this case, using Where
ID=1.

 

[Mark Andrews]

Thanks for really exploring every detail of my question! The answers
provided are very helpful.

Mark

Performance might be better if the sql avoids the duplicates with Select
Distinct, rather than using the Union to remove them. Union All means not
to remove duplicates during the union operation.
SELECT DISTINCT
"All employees" as TheName,
-1 as Sequence
FROM tblEmployee
UNION ALL
SELECT
TheName,
0 as Sequence
FROM tblEmployee
ORDER BY Sequence, TheName;

Or if you have a table that has guaranteed, fixed id values, you could use
something like:
SELECT
"All employees" as TheName,
-1 as Sequence
FROM tblStatusType
Where statusTypeID=1
UNION ALL
...

I often find it useful to include a SequenceNumber table to be used in
queries, with just a single ID field, containing numbers from 1 to N,
where N is however many is guaranteed to be more than enough for any query
I write. That would be a great table to Select From in this case, using
Where ID=1.

 

[Bob Quintal]

Performance might be better if the sql avoids the duplicates with
Select Distinct, rather than using the Union to remove them. Union
All means not to remove duplicates during the union operation.
SELECT DISTINCT
"All employees" as TheName,
-1 as Sequence
FROM tblEmployee
UNION ALL
SELECT
TheName,
0 as Sequence
FROM tblEmployee
ORDER BY Sequence, TheName;

The UNION keyword has two qualifiers, ALL and DISTINCT.
UNION used without any qualifier defaults to DISTINCT.

Duane and I both used UNION with the implied DISTINCT.

The SQL code you posted above uses Both ALL and DISTINCT. I don't
know if it will work because of that contradiction, but it would
surely be slower if it works.

Or if you have a table that has guaranteed, fixed id values, you
could use something like:
SELECT
"All employees" as TheName,
-1 as Sequence
FROM tblStatusType
Where statusTypeID=1
UNION ALL
...

I often find it useful to include a SequenceNumber table to be
used in queries, with just a single ID field, containing numbers
from 1 to N, where N is however many is guaranteed to be more than
enough for any query I write. That would be a great table to
Select From in this case, using Where ID=1.

That's just a waste of IO bandwidth to join to a table that doesn't
contribute to the result.