nested select/union select without referenced table

[Bernhard Meier]

This statement works fine if not nested or used in a union query:

select 0 as [id], "select record" as [text].

Used this way:

select 0 as [id], "select record" as [text]
union all
select tbl1.id, tbl1.text from tbl1

i get an error message saying "the query must be based on one table at
least" (Access 2000, German version).

The union query is intended to be used in a combobox rowsource. The combobox
is meant to allow record selection in a form.

My workaround so far is:

select first(0) as [id], first("select record") as [text] from tbl1
union all
select tbl1.id, tbl1.text from tbl1

The disadvantage: if relying on tbl1 in the first select part, the result
remains empty as long as tbl1 does not contain a record.
I would prefer a solution which returns the line
"0|select record"
even if tbl1 is empty.

Thanks for your suggestions, Bernhard

 

[Bernhard Meier]

This statement works fine if not nested or used in a union query:

select 0 as [id], 'select record'as [text].

Used this way:

select 0 as [id], 'select record' as [text]
union all
select tbl1.id, tbl1.text from tbl1

i get an error message saying 'the query must be based on one table at
least' (Access 2000, German version).

The union query is intended to be used in a combobox rowsource. The combobox
is meant to allow record selection in a form.

My workaround so far is:

select first(0) as [id], first('select record') as [text] from tbl1
union all
select tbl1.id, tbl1.text from tbl1

The disadvantage: if relying on tbl1 in the first select part, the result
remains empty as long as tbl1 does not contain a record.
I would prefer a solution which returns the line
'0|select record'
even if tbl1 is empty.

Thanks for your suggestions, Bernhard

 

[AccessVandal via AccessMonster.com]

Hi Bernhard,

select 0 as [id], "select record" as [text] from tbl1
union all
select tbl1.id, tbl1.textone from tbl1;

I added "from tbl1" , seems to work.

This statement works fine if not nested or used in a union query:

select 0 as [id], "select record" as [text].

Used this way:

select 0 as [id], "select record" as [text]
union all
select tbl1.id, tbl1.text from tbl1

i get an error message saying "the query must be based on one table at
least" (Access 2000, German version).

The union query is intended to be used in a combobox rowsource. The combobox
is meant to allow record selection in a form.

My workaround so far is:

select first(0) as [id], first("select record") as [text] from tbl1
union all
select tbl1.id, tbl1.text from tbl1

The disadvantage: if relying on tbl1 in the first select part, the result
remains empty as long as tbl1 does not contain a record.
I would prefer a solution which returns the line
"0|select record"
even if tbl1 is empty.

Thanks for your suggestions, Bernhard

 

[Bernhard Meier]

Thanks AccessVandal,

but as far as I can see, I still get an empty result out of the union query,
if tbl1 contains no record. Probably a matter of the Access/Jet Engine
version (4.0 on my machine)?
Thanks for the reserved word hint, I just created a quick sample...

Hi Bernhard,

select 0 as [id], "select record" as [text] from tbl1
union all
select tbl1.id, tbl1.textone from tbl1;

I added "from tbl1" , seems to work.

This statement works fine if not nested or used in a union query:

select 0 as [id], "select record" as [text].

Used this way:

select 0 as [id], "select record" as [text]
union all
select tbl1.id, tbl1.text from tbl1

i get an error message saying "the query must be based on one table at
least" (Access 2000, German version).

The union query is intended to be used in a combobox rowsource. The combobox
is meant to allow record selection in a form.

My workaround so far is:

select first(0) as [id], first("select record") as [text] from tbl1
union all
select tbl1.id, tbl1.text from tbl1

The disadvantage: if relying on tbl1 in the first select part, the result
remains empty as long as tbl1 does not contain a record.
I would prefer a solution which returns the line
"0|select record"
even if tbl1 is empty.

Thanks for your suggestions, Bernhard

 

[Andy Hull]

Hi Bernhard

You don't have to use the same table in both selects so you could use a
table that you know will always have rows or create your own 1 row table for
just this purpose.

For the 1 row table...

select 0 as [id], 'Select Record' as [text] from tblSelectMsg
union all
select tbl1.id, tbl1.text from tbl1

hth

Andy Hull

 

[Bernhard Meier]

Thanks Andy!

As well, i could store the statement

'select 0 as [id], 'Select Record' as [text]'

as a query and refer to this query.

I still hope there is a solution that does not require to create an
additional database object such as a table or query.

Hi Bernhard

You don't have to use the same table in both selects so you could use a
table that you know will always have rows or create your own 1 row table for
just this purpose.

For the 1 row table...

select 0 as [id], 'Select Record' as [text] from tblSelectMsg
union all
select tbl1.id, tbl1.text from tbl1

hth

Andy Hull

This statement works fine if not nested or used in a union query:

select 0 as [id], 'select record'as [text].

Used this way:

select 0 as [id], 'select record' as [text]
union all
select tbl1.id, tbl1.text from tbl1

i get an error message saying 'the query must be based on one table at
least' (Access 2000, German version).

The union query is intended to be used in a combobox rowsource. The combobox
is meant to allow record selection in a form.

My workaround so far is:

select first(0) as [id], first('select record') as [text] from tbl1
union all
select tbl1.id, tbl1.text from tbl1

The disadvantage: if relying on tbl1 in the first select part, the result
remains empty as long as tbl1 does not contain a record.
I would prefer a solution which returns the line
'0|select record'
even if tbl1 is empty.

Thanks for your suggestions, Bernhard

 

[John Spencer]

If you have a known table with only a few values in it then use that table.
I usually have at least on "lookup" table that has only 3 or 4 records.
Remember that UNION strips out duplicate values and in a Combobox it makes
no sense to have duplicate values - in other words don't use UNION ALL.
UNION is a bit slower than UNION ALL, however unless you have a really large
set of data you will never see the performance difference.

SELECT tbl1.id, tbl1.text from tbl1
UNION
SELECT 0 , 'Select Record'
FROM SmallLookupTable
Order By id

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks Andy!

As well, i could store the statement

'select 0 as [id], 'Select Record' as [text]'

as a query and refer to this query.

I still hope there is a solution that does not require to create an
additional database object such as a table or query.

Hi Bernhard

You don't have to use the same table in both selects so you could use a
table that you know will always have rows or create your own 1 row table
for
just this purpose.

For the 1 row table...

select 0 as [id], 'Select Record' as [text] from tblSelectMsg
union all
select tbl1.id, tbl1.text from tbl1

hth

Andy Hull

This statement works fine if not nested or used in a union query:

select 0 as [id], 'select record'as [text].

Used this way:

select 0 as [id], 'select record' as [text]
union all
select tbl1.id, tbl1.text from tbl1

i get an error message saying 'the query must be based on one table at
least' (Access 2000, German version).

The union query is intended to be used in a combobox rowsource. The
combobox
is meant to allow record selection in a form.

My workaround so far is:

select first(0) as [id], first('select record') as [text] from tbl1
union all
select tbl1.id, tbl1.text from tbl1

The disadvantage: if relying on tbl1 in the first select part, the
result
remains empty as long as tbl1 does not contain a record.
I would prefer a solution which returns the line
'0|select record'
even if tbl1 is empty.

Thanks for your suggestions, Bernhard

 

[Bernhard Meier]

Thank you John,

I am going to refer to an existing table.

I did not recognize the sense of the UNION statements "ALL" option and used
the aggregate function to prevent duplicates. Thanks for this hint as well.

If you have a known table with only a few values in it then use that table.
I usually have at least on "lookup" table that has only 3 or 4 records.
Remember that UNION strips out duplicate values and in a Combobox it makes
no sense to have duplicate values - in other words don't use UNION ALL.
UNION is a bit slower than UNION ALL, however unless you have a really large
set of data you will never see the performance difference.

SELECT tbl1.id, tbl1.text from tbl1
UNION
SELECT 0 , 'Select Record'
FROM SmallLookupTable
Order By id

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks Andy!

As well, i could store the statement

'select 0 as [id], 'Select Record' as [text]'

as a query and refer to this query.

I still hope there is a solution that does not require to create an
additional database object such as a table or query.

Hi Bernhard

You don't have to use the same table in both selects so you could use a
table that you know will always have rows or create your own 1 row table
for
just this purpose.

For the 1 row table...

select 0 as [id], 'Select Record' as [text] from tblSelectMsg
union all
select tbl1.id, tbl1.text from tbl1

hth

Andy Hull


:

This statement works fine if not nested or used in a union query:

select 0 as [id], 'select record'as [text].

Used this way:

select 0 as [id], 'select record' as [text]
union all
select tbl1.id, tbl1.text from tbl1

i get an error message saying 'the query must be based on one table at
least' (Access 2000, German version).

The union query is intended to be used in a combobox rowsource. The
combobox
is meant to allow record selection in a form.

My workaround so far is:

select first(0) as [id], first('select record') as [text] from tbl1
union all
select tbl1.id, tbl1.text from tbl1

The disadvantage: if relying on tbl1 in the first select part, the
result
remains empty as long as tbl1 does not contain a record.
I would prefer a solution which returns the line
'0|select record'
even if tbl1 is empty.

Thanks for your suggestions, Bernhard