Fill query column with my own values

[Jean]

Hello

I have a query that takes information from the column of a table, very
simply:

SELECT NAME
FROM tblPeople;

I want to add another column to my query, but I just want the rows to
contain one value, say 0, and I want to specify that value in the SQL
.... how do I do that? I recall seeing how one does it somewhere...and
without referring to a table with this value...

The point of doing this, is so that I can do a union query, and I need
to make the two participating queries have equal nubers of columns.

Please help!

[Wayne Morgan]

Yes, it is possible. First, don't use a reserved word (i.e. Name) for a
field name. It could cause you problems if you aren't very careful.

SELECT Name, 0 As MyField
FROM tblPeople;

I have done this with UnionQueries to set up a "tag" field so that I could
tell which original query the record came from when looking at the Union
Query.

--
Wayne Morgan
MS Access MVP
"Jean" <je**********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hello

I have a query that takes information from the column of a table, very
simply:

SELECT NAME
FROM tblPeople;

I want to add another column to my query, but I just want the rows to
contain one value, say 0, and I want to specify that value in the SQL
... how do I do that? I recall seeing how one does it somewhere...and
without referring to a table with this value...

The point of doing this, is so that I can do a union query, and I need
to make the two participating queries have equal nubers of columns.

Please help!

[Keith]

"Jean" <je**********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hello

I have a query that takes information from the column of a table, very
simply:

SELECT NAME
FROM tblPeople;

I want to add another column to my query, but I just want the rows to
contain one value, say 0, and I want to specify that value in the SQL
... how do I do that?

SELECT NAME, "0" AS MyField
FROM tblPeople;

Regards,
Keith.
www.keithwilby.com

[ken]

OK...I might be able to answer this!

how about like this:

SELECT tblPeople.NAME, 0 AS MyColumn
FROM tblPeople;

If you want to do this in design view, then in Field cell of the query
type in "MyColumn:0" with out the quotes ofcourse and that will do it
as well. Go to SQL view and you should see something similar to above...

[Jean]

Great stuff, thanks guys!

Your solutions helped me solve my problem. now for Step 2:
I have the union query now, with two columns as follows:

PName Amount
----- ------
AXL 0
LIS 3
BCA 2
BCA 0
THE 1
CHR 0
CHR 5
.... ....

When there are two rows for a PName, e.g. for BCA, I want the next
query (or even better within the union query) to select the PName that
is not null.
I.e. the final result will look like this:

PName Amount
----- ------
AXL 0
LIS 3
BCA 2
THE 1
CHR 5
.... ....

[Jean]

I've tried this so far, the union query as a subquery:

SELECT DISTINCT PNAME, AMOUNT FROM (Select PNAME, AMOUNT FROM
qryByDept UNION Select PNAME, AMOUNT FROM qryBySpecDept);

No luck...yet

[jimfortune]

Jean wrote:

I've tried this so far, the union query as a subquery:

SELECT DISTINCT PNAME, AMOUNT FROM (Select PNAME, AMOUNT FROM
qryByDept UNION Select PNAME, AMOUNT FROM qryBySpecDept);

No luck...yet

I after reading your post I somehow came up with:

SELECT DISTINCT PName, Nz(IIf((SELECT COUNT([PName]) FROM
tblPName)=1,[PName],(SELECT FIRST(A.Amount) FROM tblPName AS A WHERE
A.Amount <> 0 AND A.PName = tblPName.PName)), 0) AS Amount FROM
tblPName;

It produced:

PName Amount
AXL 0
BCA 2
CHR 5
LIS 3
THE 1

I also saw other ways to do it. I don't know if you care that it
orders the results by PName. The way it works is somewhat inefficient.
For each PName, if that PName is unique in tblPName use its Amount else
use the first non-zero Amount it finds with that PName. Then the
distinct part takes care of the duplicate lines.

James A. Fortune

[Jean]

Hi James,

Thanks for the reply, that just did the trick.

Regards,
Jean