473,854 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

2 queries OK by themselves, FAIL in Union

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_CUSTOME R!CST_LASTNAME, GR_FAMILY!FAM_F AMILYNAME) AS LASTNAME,
GR_CUSTOMER.CST _BIRTHDATE, GR_FAMILY.FAM_A DDRESS1,
GR_FAMILY.FAM_A DDRESS2, GR_FAMILY.FAM_C ITY, GR_FAMILY.FAM_S TATE,
GR_FAMILY.FAM_Z IPCODE
FROM GR_CUSTOMER INNER JOIN GR_FAMILY ON GR_CUSTOMER.FAM ILY_ID =
GR_FAMILY.FAMIL Y_ID
WHERE (((MonthName(Da tePart("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

Dec 26 '05 #1
3 2227
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_F AMILYNAME) AS LASTNAME,
GR_CUSTOMER.CST _BIRTHDATE, GR_FAMILY.FAM_A DDRESS1,
GR_FAMILY.FAM_A DDRESS2, GR_FAMILY.FAM_C ITY, GR_FAMILY.FAM_S TATE,
GR_FAMILY.FAM_Z IPCODE
FROM GR_CUSTOMER INNER JOIN GR_FAMILY
ON GR_CUSTOMER.FAM ILY_ID = GR_FAMILY.FAMIL Y_ID
WHERE Month([CST_BIRTHDATE]) = MonthNumber
UNION
SELECT Table2.Firstnam e, ...

--
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***@vmsmaili ngservices.com> wrote in message
news:11******** *************@g 44g2000cwa.goog legroups.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_CUSTOME R!CST_LASTNAME, GR_FAMILY!FAM_F AMILYNAME) AS LASTNAME,
GR_CUSTOMER.CST _BIRTHDATE, GR_FAMILY.FAM_A DDRESS1,
GR_FAMILY.FAM_A DDRESS2, GR_FAMILY.FAM_C ITY, GR_FAMILY.FAM_S TATE,
GR_FAMILY.FAM_Z IPCODE
FROM GR_CUSTOMER INNER JOIN GR_FAMILY ON GR_CUSTOMER.FAM ILY_ID =
GR_FAMILY.FAMIL Y_ID
WHERE (((MonthName(Da tePart("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

Dec 27 '05 #2
mi***@vmsmailin gservices.com wrote in news:1135636101 .010614.85020
@g44g2000cwa.go oglegroups.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_CUSTOME R!CST_LASTNAME, GR_FAMILY!FAM_F AMILYNAME) AS LASTNAME,
GR_CUSTOMER.CST _BIRTHDATE, GR_FAMILY.FAM_A DDRESS1,
GR_FAMILY.FAM_A DDRESS2, GR_FAMILY.FAM_C ITY, GR_FAMILY.FAM_S TATE,
GR_FAMILY.FAM_Z IPCODE
FROM GR_CUSTOMER INNER JOIN GR_FAMILY ON GR_CUSTOMER.FAM ILY_ID =
GR_FAMILY.FAMIL Y_ID
WHERE (((MonthName(Da tePart("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
Dec 27 '05 #3
mi***@vmsmailin gservices.com wrote in
news:11******** *************@g 44g2000cwa.goog legroups.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_CUSTOME R!CST_LASTNAME, GR_FAMILY!FAM_F AMILYNAME)
AS LASTNAME, GR_CUSTOMER.CST _BIRTHDATE,
GR_FAMILY.FAM_A DDRESS1, GR_FAMILY.FAM_A DDRESS2,
GR_FAMILY.FAM_C ITY, GR_FAMILY.FAM_S TATE, GR_FAMILY.FAM_Z IPCODE
FROM GR_CUSTOMER INNER JOIN GR_FAMILY ON GR_CUSTOMER.FAM ILY_ID
= GR_FAMILY.FAMIL Y_ID
WHERE
(((MonthName(Da tePart("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.
Dec 27 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3838
by: Fred Zuckerman | last post by:
I have a union query joining two other queries, each with 62 fields. Interestingly, when I open the query it has the correct number of 2850 records. But if I then set a filter (using filter by form), when I apply the filter I get only 206 records, when I should get 208. If I then remove the filter I get 2850 records again. But if I then click on "apply filter" again I get only 186 records. Each time I remove the filter I get the...
9
4368
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my predecessor, I hasten to add) so that each day it creates a copy of the record for each company, changes the date to today's date, and prompts the user for any changes of ratings on that day. The resulting data table grows by approx 600 records per...
6
2042
by: Richard Holliingsworth | last post by:
Hello: Thanks for reading this. I would like to build a report (Access 2002) that uses SQL queries to count records in specific groups. I've tried several methods and so far no luck. Could someone please point me in the right direction on this one. It
2
2463
by: Aaron Haspel | last post by:
Greetings Access gurus. I have clients with Access databases in the field that I need to update -- new tables, new indices, new queries, the works. Since these clients may have only the Access runtime, automation isn't an option for me; I need to rely on DDL (through Oledb at the moment but if necessary I could use something else). My DDL works fine with the exception of views (queries) that have union clauses. Any DDL of the form ...
12
2919
by: Susan Bricker | last post by:
For those of you who have been following my posts - they all pertain to a Dog Competition Organization's Database. There are three classes that the dogs can participate: NOVICE, OPEN, and UTILITY. I want to produce a report of the top 10 average scores for each class for each year.
1
2953
by: Alex Satrapa | last post by:
Is there any (simple? har!) way to optimise a particular SQL query? At this stage, I'm more interested in making the query more readable, so I've started hunting down references about relational theory to see if I can use relational algebra to manipulate the query into a "shorter" form (I believe the mathematical term is "simplify"). Since I've got somewhere between two days and two lifetimes' worth of reading ahead of me, would it be...
5
5069
by: BillCo | last post by:
I've encountered a problem while using ADO to save query objects. Union queries created normally (via the interface) appear in adox catelog.procedures rather than catelog.views. This is reasonably well documented and I can live with it. The below seems to be the only way to add queries to a database using ADO: Set cmd = New ADODB.Command cmd.CommandType = adCmdText
5
6330
by: marshmallowww | last post by:
I have an Access 2000 mde application which uses ADO and pass through queries to communicate with SQL Server 7, 2000 or 2005. Some of my customers, especially those with SQL Server 2005, have had pass-through queries fail due to intermittent connection failures. I can easily restablish a connection for ADO. My problem is with pass-through queries.
36
2395
by: CK | last post by:
How do I write a set based query? I have a groupSets table with fields setId, idField, datasource, nameField, prefix, active Data: 1,someIDfield, someTable, someField, pre1, 1 2,someotherIDfield, someTable, someotherField, pre2, 1 3,somethirdIDfield, someTable, somethirdField, pre3, 1 4,somefourthIDfield, someotherTable, somefourthField, pre4, 1 I need to generate records in another table by constructing queries from the data in groups...
0
9750
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11024
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10672
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10744
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9510
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7909
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7077
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5738
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4550
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.