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