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.

Collette

See #3486799 – Scheffler

Is using the same table quicker than creating a single-row dummy table, and referencing that? You show using the same TableA for both parts of the query, but if TableA is large, is it more efficient to use a dedicated dummy table? – Collette

You can choose any table that is guaranteed not to be deleted. However, I do not think a very large table is suitable for a combobox, so this would seem to be somewhat theoretical. – Scheffler

It's not the output to the combo box which will be large as there are WHERE clauses based on other material, but the table may be in excess of 1000 entries. Would this impact performance when using the Union simply to return my constants? – Collette

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

Jealousy

You may need to cast your ID as a string value to get this to work, I'm not sure how MSAccess handles that. – Jealousy

I receive an error stating "Query input must contain at least one table or query". I have the Row Source pointing to 'Query2' which contains your above syntax. I've tried simplifying my code and I cannot get this syntax to work for Access? I've tried it straight from the Query2 window as well (datasheet view throws the same error) – Collette

@StuckAtWork: That's unfortunate, and one more reason not to confuse Access for a real database system. I have updated my answer. – Jealousy

Jet allows you to SELECT 0, "All" but not when Union'd. You (apparently) must provide a dummy table, which is the same as many DB systems. The only limit with Jet is that it doesn't have a built in dummy table, and you must create a single-row dummy table with appropriate data types. I was just hoping someone knew of the dummy table, but the more I look into it, there isn't one. – Collette

There is no need to create a new table. This is a standard problem in MS Access form design and has a common solution. BTW Access is not a database, it generally uses the Jet/ACE database, but not always. – Scheffler

@Remou, so if there is not a need to create a new table, can we expect a working answer from you? You mention this is a standard problem, what is the solution? – Jealousy

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.

Teel

As of Access 365 MSO, you don't need "Top 1". SELECT 'ALL', 'SHOW ALL' FROM tblItems; is sufficient – Hereunto

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

Avoid

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'

Champagne

I think Access needs a table after the 'SHOW ALL'.. that's more the syntax I needed. It throws an error of "No table or query in query". I've seen other DBs use things like "SYS.DUMMY" type thing as the table name for the constant values, but can't find it for access. – Collette

@Collette Please see my link above. – Scheffler

Answer 5

Mind you, the following only works if there are any rows in the table, if you've got an empty table, it won't work.

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

Leicestershire