Union Query Problem

[Bill Sturdevant]

I have a table that may or may not have any records in
it. I need to do a Union Query, but the Union Query only
works if there is at least one record in the table:

SELECT ID, Field1 From Table1
UNION SELECT 0 as ID, "<ZeroValue>" as Field1 FROM Table1
ORDER BY Field1;

When there is at least one record in Table1 I get the
following:

ID Field1
0 <ZeroValue>
-238726 Value from record 1

BUT, when table1 has no records, I get no results. What I
need to get is the following:
ID Field1
0 <ZeroValue>


How do I do this?

I have discovered that if I change the query to the
following it works:
SELECT ID, Field1 From Table1
UNION SELECT 0 as ID, "<ZeroValue>" as Field1 FROM
AnyOtherTableWithRecords
ORDER BY Field1;

But I do not like this because how can I know that
AnyOtherTableWithRecords might someday have 0 records.

 

[Jeff Boyce]

Bill

Could you describe a bit more what business need you have to show a row when
none exists?

 

[Bill Sturdevant]

Yes.

As an example, I have a project record. In that record, I
need to select the application the project is being
executed for. So I use a lookup with an appropriate
rowsource. But, occasionally, if a project is NOT
associatecd with an application, I have to be able to
select "<No Application>". I cannot have an entry in the
application table of "<No Application>", so I use a union
query to combine the list of applications with "<No
Application" which has an ID of 0 as specified in the
union query. In this way, in the project record, I either
have a bonafide application ID or 0, which tells me there
is no application, as opposed to telling me that someone
has not yet picked an application.

 

[Jeff Boyce]

Bill

So, I'm still a little confused. From your original post, it sounded like
you could have a table of "?applications" that could be empty. Are you not
using the same table of applications for every project? How could you have
none (an empty table)?

I guess I don't understand enough about your data model yet...

 

[Guest]

Jeff,

This is a large multi-database system with many users.
The application table is full of apps, and the apps are
grouped by Portfolio.

Under normal circumstances, you would enter projects and
the combo box would find some applications within your
portfolio and union query the "<No Application>" to that
list.

But supposed that a new portfolio has been created, and no
apps are currently assigned to it. But a user has to
enter a "No Application" project record in that
portfolio. The combo box would eventually have
applications unioned with "<No Application>" but until the
appliations are entered, it must show only "<No
Application>".

I know I could do a query of the apps in the portfolio and
if there are none, change the rowsource of the combo box
to only show "<No Application>", but I would like to avoid
doing the extra query if I can avoid it.

Bill

 

[Gary Walter]

Hi Bill,

Use a table that will *always* have a record
in your second SELECT stmt since it does
not matter which table it is.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter

 

[Jeff Boyce]

Bill

I took a run at it here and got the same result you describe. If there's
nothing in the table, the UNION with <No record> doesn't work.

Tell me again why you don't want to include an actual row in the table that
says <no application>? Wouldn't that be a lot easier than all these work
arounds?

 

[Bill Sturdevant]

The main reason is that I would have to go back and touch
all my existing queries to add a criteria of: