How do I add extra row to Union query results?

#1

I want to query data from a table and add an extra row to the
results. This is what I have so far:

SELECT ResidentID, LastName & ', ' & FirstName & ' ' & MiddleName AS
ResidentName, Status FROM tblDemographics WHERE (Status = 'Active')
UNION ALL SELECT 0, 'N/A', 'Active '
ORDER BY LastName & ', ' & FirstName & ' ' & MiddleName;

I want a list of names returned and the extra row "0, N/A, Active".
(Just realized that I don't really need Status in the query)

When I try to execute this I get the error message "Query input must
contain at least one table or query".

How do I correct this? thanks for any help or advice.

#2

Hi,
SELECT syntax needs FROM clause. Pls find below modified code.

Quote:

SELECT ResidentID, LastName & ', ' & FirstName & ' ' & MiddleName AS
ResidentName, Status FROM tblDemographics WHERE (Status = 'Active')
UNION *ALL SELECT 0, 'N/A', 'Active ' FROM tblDemographics
ORDER BY LastName & ', ' & FirstName & ' ' & MiddleName;

Regards,
Sebastian

#3

emanning wrote:

Quote:

I want to query data from a table and add an extra row to the
results. This is what I have so far:

SELECT ResidentID, LastName & ', ' & FirstName & ' ' & MiddleName AS
ResidentName, Status FROM tblDemographics WHERE (Status = 'Active')
UNION ALL SELECT 0, 'N/A', 'Active '
ORDER BY LastName & ', ' & FirstName & ' ' & MiddleName;

I want a list of names returned and the extra row "0, N/A, Active".
(Just realized that I don't really need Status in the query)

When I try to execute this I get the error message "Query input must
contain at least one table or query".

How do I correct this?
By specifying one table or query name in a FROM clause. In this case I would

simply do:

....
UNION ALL
SELECT TOP 1 0, 'N/A', 'Active ' FROM tblDemographics
....

There's nothing that says you actually need to return data from the table
you are querying. As you can see, you can return calculated fields and
nothing else.

David Fenton pointed out to me a while ago that the FROM clause is no longer
required in Jet and he is correct, but there are a couple situations where
one is required and you have found one of them: union queries.

#4

On Apr 28, 2:25*pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:

Quote:

emanning wrote:
I want to query data from a table and add an extra row to the
results. *This is what I have so far:

SELECT ResidentID, LastName & ', ' & FirstName & ' ' & MiddleName AS
ResidentName, Status FROM tblDemographics WHERE (Status = 'Active')
UNION *ALL SELECT 0, 'N/A', 'Active '
ORDER BY LastName & ', ' & FirstName & ' ' & MiddleName;

I want a list of names returned and the extra row "0, N/A, Active".
(Just realized that I don't really need Status in the query)

When I try to execute this I get the error message "Query input must
contain at least one table or query".

How do I correct this?

By specifying one table or query name in a FROM clause. In this case I would
simply do:

...
UNION *ALL
SELECT TOP 1 0, 'N/A', 'Active ' FROM tblDemographics
...

There's nothing that says you actually need to return data from the table
you are querying. As you can see, you can return calculated fields and
nothing else.

David Fenton pointed out to me a while ago that the FROM clause is no longer
required in Jet and he is correct, but there are a couple situations where
one is required and you have found one of them: union queries.

Thanks for your replies. I took out the redundant Status column and
tweaked it a little. It works just fine:

SELECT ResidentID, LastName & ', ' & FirstName & ' ' & MiddleName AS
ResidentName FROM tblDemographics WHERE (Status = 'Active')
UNION SELECT 0, 'N/A' FROM tblDemographics
ORDER BY ResidentName

#5

emanning wrote:

Quote:

Thanks for your replies. I took out the redundant Status column and
tweaked it a little. It works just fine:

SELECT ResidentID, LastName & ', ' & FirstName & ' ' & MiddleName AS
ResidentName FROM tblDemographics WHERE (Status = 'Active')
UNION SELECT 0, 'N/A' FROM tblDemographics
ORDER BY ResidentName

Really? Without the "Top 1"? I would expect there to be as many rows
containing 0,N/A as those returned from the first query, perhaps more since
there is no WHERE clause in the second query. Are you sure you did not use
"SELECT Top 1 0, 'N/A' FROM tblDemographics"?

#6

emanning wrote:

Quote:

SELECT ResidentID, LastName & ', ' & FirstName & ' ' & MiddleName AS
ResidentName FROM tblDemographics WHERE (Status = 'Active')
UNION SELECT 0, 'N/A' FROM tblDemographics
ORDER BY ResidentName

Oh wait, I see now - you left out the "ALL" keyword so the duplicates
returned by the second query were eliminated.
From a performance standpoint, if tblDemographics contains a lot of records,
I would prefer to use the " UNION ALL SELECT Top 1 ... " version since you
aren't forcing the query engine to eliminate duplicate results.

#7

On Apr 28, 2:46*pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:

Quote:

emanning wrote:

SELECT ResidentID, LastName & ', ' & FirstName & ' ' & MiddleName AS
ResidentName FROM tblDemographics WHERE (Status = 'Active')
UNION SELECT 0, 'N/A' FROM tblDemographics
ORDER BY ResidentName

Oh wait, I see now - you left out the "ALL" keyword so the duplicates
returned by the second query were eliminated.
From a performance standpoint, if tblDemographics contains a lot of records,
I would prefer to use the " UNION ALL SELECT Top 1 ... " version since you
aren't forcing the query engine to eliminate duplicate results.

Yep, I had to take out the "ALL" otherwise I'd have umpteen duplicates
of N/A. Thanks for the idea of "Top 1". I can see where that would
be a better way.

#8

emanning wrote:

Quote:

On Apr 28, 2:46*pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
emanning wrote:

SELECT ResidentID, LastName & ', ' & FirstName & ' ' & MiddleName AS
ResidentName FROM tblDemographics WHERE (Status = 'Active')
UNION SELECT 0, 'N/A' FROM tblDemographics
ORDER BY ResidentName

Oh wait, I see now - you left out the "ALL" keyword so the duplicates
returned by the second query were eliminated.
From a performance standpoint, if tblDemographics contains a lot of records,
I would prefer to use the " UNION ALL SELECT Top 1 ... " version since you
aren't forcing the query engine to eliminate duplicate results.

Yep, I had to take out the "ALL" otherwise I'd have umpteen duplicates
of N/A. Thanks for the idea of "Top 1". I can see where that would
be a better way.

I prefer to use a one row utility table
. . .
UNION ALL SELECT 0, 'N/A' FROM tblOneRow
and avoid the sort and duplicate removal required by TOP 1
even if it is optimized by an index.

--
Marsh

#9

Marshall Barton wrote:

Quote:

emanning wrote:

On Apr 28, 2:46 pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
emanning wrote:

SELECT ResidentID, LastName & ', ' & FirstName & ' ' & MiddleName
AS ResidentName FROM tblDemographics WHERE (Status = 'Active')
UNION SELECT 0, 'N/A' FROM tblDemographics
ORDER BY ResidentName

Oh wait, I see now - you left out the "ALL" keyword so the
duplicates returned by the second query were eliminated.
From a performance standpoint, if tblDemographics contains a lot of
records, I would prefer to use the " UNION ALL SELECT Top 1 ... "
version since you aren't forcing the query engine to eliminate
duplicate results.

Yep, I had to take out the "ALL" otherwise I'd have umpteen
duplicates of N/A. Thanks for the idea of "Top 1". I can see where
that would be a better way.

I prefer to use a one row utility table
. . .
UNION ALL SELECT 0, 'N/A' FROM tblOneRow
and avoid the sort and duplicate removal required by TOP 1
even if it is optimized by an index.

Point, although I think TOP is better optimized than that - at least it is
in SQL Server.

#10

emanning <emanning (AT) kumc (DOT) edu> wrote in
news:17783eb3-ee8b-4fe6-9417-f3257b228cac (AT) f2g2000yqf (DOT) googlegroups.com
:

Quote:

I want to query data from a table and add an extra row to the
results.

Sounds to me like you want to create a report.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How do I add extra row to Union query results?

comp.databases.ms-access comp.databases.ms-access

How do I add extra row to Union query results? - 04-28-2011 , 12:30 PM

Re: How do I add extra row to Union query results? - 04-28-2011 , 02:17 PM

Re: How do I add extra row to Union query results? - 04-28-2011 , 02:25 PM

Re: How do I add extra row to Union query results? - 04-28-2011 , 02:30 PM

Re: How do I add extra row to Union query results? - 04-28-2011 , 02:41 PM

Re: How do I add extra row to Union query results? - 04-28-2011 , 02:46 PM

Re: How do I add extra row to Union query results? - 04-28-2011 , 02:56 PM

Re: How do I add extra row to Union query results? - 04-28-2011 , 10:25 PM

Re: How do I add extra row to Union query results? - 04-29-2011 , 05:52 AM

Re: How do I add extra row to Union query results? - 04-30-2011 , 01:17 PM