StuckAtWork StuckAtWork - 3 months ago 23
SQL Question

Adding a Constant Row Result to SQL Query - MS Access

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

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