Adding a constant string result to an SQL query - MS Access

Question

Say I have a table called "tblItems":

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

and I want it to be populated in the drop-down list on the form. How to add a line:

 ALL | SHOW ALL 

to become

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

with one request to place on the Row Source for the combo box? I could not find the access syntax for this.

AFAIK, I need a syntax similar to

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

what I cannot find is the Access version of this dummy table. I really don't want to have a separate table just to hold one row for one form ... but from what I read, I might have to.

Answer 1

you can do something like this:

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

If you always wanted it to appear below, you would have to get a little complicated.

In the comments, I realized that Access does not support the SELECT without the FROM , which is annoying. A tblAll would be to create the tblAll table (the syntax may require a change):

 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

I use the SELECT TOP 1 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 

This will give you one row along with a selection from your existing table. You can use any table for the TOP 1 row.

Answer 3

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

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

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

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

Answer 4

I just want to correct the answer of Jeremy Golovach to work in MS Access. This way you can use the same table, rather than create a dummy table.

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

Answer 5

Remember that the following only works if there are any rows in the table, if you have an empty table, this will not work.

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