Adding a Constant Row Result to SQL Query - MS Access

Question

Say I have a table "tblItems":

*ID*    |     *Name*
1       |   First Item
2       |  Second Item

and I want this to be populated in a drop-down in a form. How would I add a row:

ALL     |    SHOW ALL

to become

*ID*    |     *Name*
1       |   First Item
2       |  Second Item
ALL     |    SHOW ALL

with one query to place in the Row Source for the combo box? I haven't been able to find Access syntax to do this..

AFAIK, I need syntax similar to

SELECT ID, Name FROM tblItems
UNION
SELECT 0, "All" FROM SOME.SYSTEM.DUMMY.TABLE

what I can't find is Access' version of that dummy table. I don't really want to have a separate table just to store one row for one form... but from what I've been reading I may have to.

Answer 1

you could do something like this:

select ID, Name
from tblItems
union all
select 'ALL', 'SHOW ALL'

if you always wanted it to show up on the bottom, you'd have to get a bit more complicated.

Per comments, I realized that Access does not support a SELECT statement without a FROM clause, which is annoying. A workaround would be to create a table tblAll (syntax may require modification):

create table tblAll(ID varchar(15), Name varchar(30));
insert into tblAll(ID, Name) values ('ALL', 'SHOW ALL');

then you can do:

select ID, Name
from tblAll
union all
select str(ID) as ID, Name
from tblItems

Answer 2

What I do is use a SELECT TOP 1 statement and use the existing table name, so it looks like this:

SELECT ID, Name FROM tblItems
UNION
SELECT TOP 1 'ALL', 'SHOW ALL' FROM tblItems

That will give you the single row along with the selection from your existing table. You could use any table you want for the TOP 1 row.

Answer 3

Just want to fix Jeremy Holovacs' answer to work in MS Access. This way you can use the same table and not have to create a dummy table.

SELECT ID, Name
FROM tblItems
UNION ALL
SELECT TOP 1 'ALL', 'SHOW ALL' 
FROM tblItems

Answer 4

You can use UNION ALL, however, you have a mismatch between the constants and the columns ("ALL" is not an integer). You might do something like:

select ID, NAME from tblItems
union all
select 0, 'SHOW ALL'

On the application side, interpret ID of 0 as "SHOW ALL". Or, convert ID to a string.

select str(ID), NAME from tblItems
union all
select 'ALL', 'SHOW ALL'