Search code examples
sqlms-accessdummy-data

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.


Solution

  • 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