2 queries OK by themselves, FAIL in Union

[mikes]

I have 2 separate queries, which effectively are the same except they
draw data from separate tables. Both tables are (design-wise)
identical, only the data is different. for each query, there are 2
tables with a standard LEFT JOIN. One field of the query is calculated,
looking for a NULL in one table, and then using a field from the second
table in that case. One query looks like this:

PARAMETERS [Month] Text ( 255 );
SELECT GR_CUSTOMER.CST_FIRSTNAME, IIf(GR_CUSTOMER!CST_LASTNAME Is Not
Null,GR_CUSTOMER!CST_LASTNAME,GR_FAMILY!FAM_FAMILY NAME) AS LASTNAME,
GR_CUSTOMER.CST_BIRTHDATE, GR_FAMILY.FAM_ADDRESS1,
GR_FAMILY.FAM_ADDRESS2, GR_FAMILY.FAM_CITY, GR_FAMILY.FAM_STATE,
GR_FAMILY.FAM_ZIPCODE
FROM GR_CUSTOMER INNER JOIN GR_FAMILY ON GR_CUSTOMER.FAMILY_ID =
GR_FAMILY.FAMILY_ID
WHERE (((MonthName(DatePart("m",[CST_BIRTHDATE]),1))=[Month]));

We'll call this query QUERY1. This query executes PERFECTLY. The second
query (we'll call it QUERY2) is identical, except for substituting the
tables names where appropriate.

However, when I try to creat a UNION query, like
Table QUERY1 UNION Table QUERY2

the calculated field, "LASTNAME", is EMPTY!

I have attempted to use the Nz() function in place of the IIF()
statement (it behaves the same way). If I use any other non-calculated
field for the ALIAS field "LASTNAME", the full UNION query works fine
as well!

Any thoughts as to what might cause such a problem with the ALIAS on a
calculated field?

Thanks in advance,
Mike

[Allen Browne]

Mike, I'm not sure why it's not working correctly but see if these
suggestions solve the problem:

1. Try creating one query, instead of UNIONing 2 queries each with their own
parameters.

2. Use Nz() rather than the IIf() statement.

3. Take the month number as the parameter.

You will end up with something like this:

PARAMETERS [MonthNumber] Short;
SELECT GR_CUSTOMER.CST_FIRSTNAME,
Nz(GR_CUSTOMER.CST_LASTNAME, GR_FAMILY.FAM_FAMILYNAME) AS LASTNAME,
GR_CUSTOMER.CST_BIRTHDATE, GR_FAMILY.FAM_ADDRESS1,
GR_FAMILY.FAM_ADDRESS2, GR_FAMILY.FAM_CITY, GR_FAMILY.FAM_STATE,
GR_FAMILY.FAM_ZIPCODE
FROM GR_CUSTOMER INNER JOIN GR_FAMILY
ON GR_CUSTOMER.FAMILY_ID = GR_FAMILY.FAMILY_ID
WHERE Month([CST_BIRTHDATE]) = MonthNumber
UNION
SELECT Table2.Firstname, ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<mi***@vmsmailingservices.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

I have 2 separate queries, which effectively are the same except they
draw data from separate tables. Both tables are (design-wise)
identical, only the data is different. for each query, there are 2
tables with a standard LEFT JOIN. One field of the query is calculated,
looking for a NULL in one table, and then using a field from the second
table in that case. One query looks like this:

PARAMETERS [Month] Text ( 255 );
SELECT GR_CUSTOMER.CST_FIRSTNAME, IIf(GR_CUSTOMER!CST_LASTNAME Is Not
Null,GR_CUSTOMER!CST_LASTNAME,GR_FAMILY!FAM_FAMILY NAME) AS LASTNAME,
GR_CUSTOMER.CST_BIRTHDATE, GR_FAMILY.FAM_ADDRESS1,
GR_FAMILY.FAM_ADDRESS2, GR_FAMILY.FAM_CITY, GR_FAMILY.FAM_STATE,
GR_FAMILY.FAM_ZIPCODE
FROM GR_CUSTOMER INNER JOIN GR_FAMILY ON GR_CUSTOMER.FAMILY_ID =
GR_FAMILY.FAMILY_ID
WHERE (((MonthName(DatePart("m",[CST_BIRTHDATE]),1))=[Month]));

We'll call this query QUERY1. This query executes PERFECTLY. The second
query (we'll call it QUERY2) is identical, except for substituting the
tables names where appropriate.

However, when I try to creat a UNION query, like
Table QUERY1 UNION Table QUERY2

the calculated field, "LASTNAME", is EMPTY!

I have attempted to use the Nz() function in place of the IIF()
statement (it behaves the same way). If I use any other non-calculated
field for the ALIAS field "LASTNAME", the full UNION query works fine
as well!

Any thoughts as to what might cause such a problem with the ALIAS on a
calculated field?

Thanks in advance,
Mike

[Lyle Fairfield]

mi***@vmsmailingservices.com wrote in news:1135636101.010614.85020
@g44g2000cwa.googlegroups.com:

I have 2 separate queries, which effectively are the same except they
draw data from separate tables. Both tables are (design-wise)
identical, only the data is different. for each query, there are 2
tables with a standard LEFT JOIN. One field of the query is calculated,
looking for a NULL in one table, and then using a field from the second
table in that case. One query looks like this:

PARAMETERS [Month] Text ( 255 );
SELECT GR_CUSTOMER.CST_FIRSTNAME, IIf(GR_CUSTOMER!CST_LASTNAME Is Not
Null,GR_CUSTOMER!CST_LASTNAME,GR_FAMILY!FAM_FAMILY NAME) AS LASTNAME,
GR_CUSTOMER.CST_BIRTHDATE, GR_FAMILY.FAM_ADDRESS1,
GR_FAMILY.FAM_ADDRESS2, GR_FAMILY.FAM_CITY, GR_FAMILY.FAM_STATE,
GR_FAMILY.FAM_ZIPCODE
FROM GR_CUSTOMER INNER JOIN GR_FAMILY ON GR_CUSTOMER.FAMILY_ID =
GR_FAMILY.FAMILY_ID
WHERE (((MonthName(DatePart("m",[CST_BIRTHDATE]),1))=[Month]));

We'll call this query QUERY1. This query executes PERFECTLY. The second
query (we'll call it QUERY2) is identical, except for substituting the
tables names where appropriate.

However, when I try to creat a UNION query, like
Table QUERY1 UNION Table QUERY2

the calculated field, "LASTNAME", is EMPTY!

I have attempted to use the Nz() function in place of the IIF()
statement (it behaves the same way). If I use any other non-calculated
field for the ALIAS field "LASTNAME", the full UNION query works fine
as well!

Any thoughts as to what might cause such a problem with the ALIAS on a
calculated field?

Thanks in advance,
Mike

What could it be?

I have read about "Table QUERY1 UNION Table QUERY2" but I've never heard
of anyone actually using it. Maybe I've led a sheltered life, but since
TTBOMK there is not a large body of knowledge about this special form of
the UNION statement, the first thing I would do when faced with an
unexplained problem involving it, is to discontinue its use.

If the Table QUERY1 UNION Table QUERY2 special form of the UNION
statement is golden then what else? Ninety-nine and 44/100 ths per cent
of the query problems that are posted here are syntax problems. I
understand that you are sure there is no problem with your second query.
But to satisfy our curiousity, perhaps you could post it anyway?

Is there something else to grasp at? When a UNION query has field name
differences those of the first query rule. Did you call the calculated
field the same name in both the queries? No, I don't know how this could
affect anything but I'm reaching....
--
Lyle Fairfield

[Bob Quintal]

mi***@vmsmailingservices.com wrote in
news:11*********************@g44g2000cwa.googlegro ups.com:

I have 2 separate queries, which effectively are the same
except they draw data from separate tables. Both tables are
(design-wise) identical, only the data is different. for each
query, there are 2 tables with a standard LEFT JOIN. One field
of the query is calculated, looking for a NULL in one table,
and then using a field from the second table in that case. One
query looks like this:

PARAMETERS [Month] Text ( 255 );
SELECT GR_CUSTOMER.CST_FIRSTNAME, IIf(GR_CUSTOMER!CST_LASTNAME
Is Not Null,GR_CUSTOMER!CST_LASTNAME,GR_FAMILY!FAM_FAMILY NAME)
AS LASTNAME, GR_CUSTOMER.CST_BIRTHDATE,
GR_FAMILY.FAM_ADDRESS1, GR_FAMILY.FAM_ADDRESS2,
GR_FAMILY.FAM_CITY, GR_FAMILY.FAM_STATE, GR_FAMILY.FAM_ZIPCODE
FROM GR_CUSTOMER INNER JOIN GR_FAMILY ON GR_CUSTOMER.FAMILY_ID
= GR_FAMILY.FAMILY_ID
WHERE
(((MonthName(DatePart("m",[CST_BIRTHDATE]),1))=[Month]));

We'll call this query QUERY1. This query executes PERFECTLY.
The second query (we'll call it QUERY2) is identical, except
for substituting the tables names where appropriate.

However, when I try to creat a UNION query, like
Table QUERY1 UNION Table QUERY2

the calculated field, "LASTNAME", is EMPTY!

I have attempted to use the Nz() function in place of the
IIF() statement (it behaves the same way). If I use any other
non-calculated field for the ALIAS field "LASTNAME", the full
UNION query works fine as well!

Any thoughts as to what might cause such a problem with the
ALIAS on a calculated field?

Thanks in advance,
Mike

Why are you using bangs(!) instead of dots (.) in the iif
statement?
This may work with the normal query, but fail when trying to
union the two queries.

--
Bob Quintal

PA is y I've altered my email address.