Question about union queries

J

John

I would like to make a list or combo box based off a table, with the addition
of a single line that doen't exist in any table.

What I'm trying:

SELECT [DeptNo], [DeptName] FROM tDepartments
UNION SELECT "*" AS [DeptNo], "All Deptarments" as [DeptName];

Obviously, this isn't working because there is no FROM clause in the union.
Is there a way to accomplish this?

Thanks in advance,
John
 
J

John Spencer (MVP)

Sure just add any table (preferably a short one) to your query. The UNION
clause will cause any duplicate values to be dropped. However, the less records
it has to throw out the better.

This should work for you.

SELECT [DeptNo], [DeptName] FROM tDepartments
UNION
SELECT "*", "All Departments"
FROM tDepartments
 
G

George Nicholson

You need to specify a 2nd FROM table. However, since you are using Aliases
and supplying the actual values for the 2 fields, you could probably pretty
much use any existing table/query name.

SELECT [DeptNo], [DeptName] FROM tDepartments
UNION SELECT "*" AS [DeptNo], "All Deptarments" as [DeptName] FROM
tDepartments;

HTH,
 
S

Sylvain Lafontaine

The second table in the UNION doesn't need to be the same as the first one.
Personally, I use an extra table with a single ID and a single record in it
that I have called MyUniqueTable.

I don't remember if an empty table (with no record) will work, too.

S. L.

John Spencer (MVP) said:
Sure just add any table (preferably a short one) to your query. The UNION
clause will cause any duplicate values to be dropped. However, the less
records
it has to throw out the better.

This should work for you.

SELECT [DeptNo], [DeptName] FROM tDepartments
UNION
SELECT "*", "All Departments"
FROM tDepartments

I would like to make a list or combo box based off a table, with the
addition
of a single line that doen't exist in any table.

What I'm trying:

SELECT [DeptNo], [DeptName] FROM tDepartments
UNION SELECT "*" AS [DeptNo], "All Deptarments" as [DeptName];

Obviously, this isn't working because there is no FROM clause in the
union.
Is there a way to accomplish this?

Thanks in advance,
John
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

WHICH is what I meant by "just add any table". I guess your explanation does
make that a bit clearer. Thanks for the clarification.

Sylvain said:
The second table in the UNION doesn't need to be the same as the first one.
Personally, I use an extra table with a single ID and a single record in it
that I have called MyUniqueTable.

I don't remember if an empty table (with no record) will work, too.

S. L.

John Spencer (MVP) said:
Sure just add any table (preferably a short one) to your query. The UNION
clause will cause any duplicate values to be dropped. However, the less
records
it has to throw out the better.

This should work for you.

SELECT [DeptNo], [DeptName] FROM tDepartments
UNION
SELECT "*", "All Departments"
FROM tDepartments

I would like to make a list or combo box based off a table, with the
addition
of a single line that doen't exist in any table.

What I'm trying:

SELECT [DeptNo], [DeptName] FROM tDepartments
UNION SELECT "*" AS [DeptNo], "All Deptarments" as [DeptName];

Obviously, this isn't working because there is no FROM clause in the
union.
Is there a way to accomplish this?

Thanks in advance,
John
Click to expand...
Click to expand...
Click to expand...
 
J

John Vinson

The second table in the UNION doesn't need to be the same as the first one.
Personally, I use an extra table with a single ID and a single record in it
that I have called MyUniqueTable.
Click to expand...

Back when I was working with Oracle, I got used to Oracle providing a
system table named Dual, with only one record and no fields. It's
handy when you want to do something like this - "SELECT "*" FROM
Dual;" returns just one record with an asterisk.

John W. Vinson[MVP]
 
Top