Solved

Union Query not returning second select results

Posted on 2008-06-19
17
924 Views
Last Modified: 2012-06-27
I have a Union query that will not return  duplicate records. If I run each select statement by itsel,f I get a record with a que_rpt_Schedule_s1.ID (first field) of 11605. In the union query I only get one record with 11605.  Each select statement is using a join on a different field in the second table.

Any help would be appreciated
SELECT que_rpt_Schedule_s1.ID, ref_dates_week.ID, ref_dates_week.StartofWeek, ref_dates_week.EndofWeek, que_rpt_Schedule_s1.startdate, 

IIf([startdate] Between [startofweek] And [endofweek],1) AS S1_IN,

 que_rpt_Schedule_s1.s2StartDate, IIf([s2startdate] Between [startofweek] And [endofweek],1) AS S2_IN

FROM ref_dates_week 

LEFT JOIN que_rpt_Schedule_s1 ON ref_dates_week.ID = que_rpt_Schedule_s1.Startweek;
 

UNION ALL
 

SELECT que_rpt_Schedule_s1.ID, ref_dates_week.ID, ref_dates_week.StartofWeek, ref_dates_week.EndofWeek, que_rpt_Schedule_s1.startdate, 

IIf([startdate] Between [startofweek] And [endofweek],1) AS S1_IN,

 que_rpt_Schedule_s1.s2StartDate, IIf([s2startdate] Between [startofweek] And [endofweek],1) AS S2_IN

FROM ref_dates_week 

LEFT JOIN que_rpt_Schedule_s1 ON ref_dates_week.ID = que_rpt_Schedule_s1.S2_Startweek;

Open in new window

0
Comment
Question by:hragape
  • 8
  • 5
  • 4
17 Comments
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
In the iif statements in both SELECT statements, you have not include the FALSE criteria,  ie. what is supposed to happen if the [startdte] is not between the two  test dates?
0
 

Author Comment

by:hragape
Comment Utility
I am sorry for not replying earlier but had to leave the office for medical reasons.

Nothing needs to happen if it is false. I am using the "1"  to mark the records so that I can perform summary calculations later.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
I do not believe the false condition of a IIF() function is optional, but then Jet, the db engine in Access is sometimes too lazy about raising flags .  If you do not want anything to happen, try inserting ,Null after each of the four ,1's in your query as in:

IIf([startdate] Between [startofweek] And [endofweek],1,Null)

Does that change anything?
0
 

Author Comment

by:hragape
Comment Utility
I still get the same results. I also tried using 0 instead of Null.

The funny thing is that the select staments work by themselves. They just don't work as part of the union. Only the first select works regardless of which statement I put first.

I'm wondering if this has somnething to do with the way that I built the source tables.

That is good to know about the db engine not always raising errors.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility

SELECT 

b.ID, 

a.ID, 

a.StartofWeek, 

a.EndofWeek, 

b.startdate, 

IIf([startdate] Between [startofweek] And [endofweek],1) AS S1_IN,

b.s2StartDate, 

IIf([s2startdate] Between [startofweek] And [endofweek],1) AS S2_IN

FROM ref_dates_week a

LEFT JOIN que_rpt_Schedule_s1 b

ON a.ID = b.Startweek;

 

UNION ALL
 

SELECT 

d.ID, 

c.ID, 

c.StartofWeek, 

c.EndofWeek, 

d.startdate, 

IIf([startdate] Between [startofweek] And [endofweek],1),

 qd.s2StartDate, 

IIf([s2startdate] Between [startofweek] And [endofweek],1) 

FROM ref_dates_week c 

LEFT JOIN que_rpt_Schedule_s1 d

ON c.ID = d.S2_Startweek;
 

This is how I like to lay out the query when I'm having difficulty troubleshooting - using aliases a and b make it a lot easier to spot errors.  The first thing I noticed is that the fields in IIf() statements are not qualified with their table names if they are not parameters.  
 

Remember, a UNION query takes the field names and datatypes from the first SELECT query so there is not point in providing the AS <fldName> in the subsequent SELECT query(s).  Does the query as I have modified it in this snippet above run?

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
I should have added the nulls:

SELECT 

b.ID, 

a.ID, 

a.StartofWeek, 

a.EndofWeek, 

b.startdate, 

IIf([startdate] Between [startofweek] And [endofweek],1,Null) AS S1_IN,

b.s2StartDate, 

IIf([s2startdate] Between [startofweek] And [endofweek],1,Null) AS S2_IN

FROM ref_dates_week a

LEFT JOIN que_rpt_Schedule_s1 b

ON a.ID = b.Startweek;

 

UNION ALL

 

SELECT 

d.ID, 

c.ID, 

c.StartofWeek, 

c.EndofWeek, 

d.startdate, 

IIf([startdate] Between [startofweek] And [endofweek],1,Null),

 qd.s2StartDate, 

IIf([s2startdate] Between [startofweek] And [endofweek],1,Null) 

FROM ref_dates_week c 

LEFT JOIN que_rpt_Schedule_s1 d

ON c.ID = d.S2_Startweek;

 

Open in new window

0
 

Author Comment

by:hragape
Comment Utility
I actually lay out my sql the same way but Acess seems to condense the code whenever I leave the query. I am not seing anything in your code different then mine. I did paste your code and I am still having the same issue.

Thanks for you help but I think that I will  try to create a work around. The functionality is not worth spending too much more time on.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
OK, but I am going to try to get some 'fresh' eyes on this.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Did you try to save both queries and create the UNION like:

select * from qrySaved1
UNION ALL
select * from qrySaved2;

Nic;o)
0
 

Author Comment

by:hragape
Comment Utility

Nico5038,
I had tried to execute the union the way you suggested and I got the same results. This is the strangest problem. I have used the union functionality many times and I have not had this problem. I am thinking that it must have something to do with the relationships between the tables.

Thanks for the suggestion
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Hi Nico:  any comment about including/not including the False argument in an Access query using the IIF() function?
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 125 total points
Comment Utility
The False isn't needed, but there's another flaw, the intermediate ";" after the first query, hragape try:

SELECT que_rpt_Schedule_s1.ID, ref_dates_week.ID, ref_dates_week.StartofWeek, ref_dates_week.EndofWeek, que_rpt_Schedule_s1.startdate,
IIf([startdate] Between [startofweek] And [endofweek],1) AS S1_IN,
 que_rpt_Schedule_s1.s2StartDate, IIf([s2startdate] Between [startofweek] And [endofweek],1) AS S2_IN
FROM ref_dates_week
LEFT JOIN que_rpt_Schedule_s1 ON ref_dates_week.ID = que_rpt_Schedule_s1.Startweek
 
UNION ALL
 
SELECT que_rpt_Schedule_s1.ID, ref_dates_week.ID, ref_dates_week.StartofWeek, ref_dates_week.EndofWeek, que_rpt_Schedule_s1.startdate,
IIf([startdate] Between [startofweek] And [endofweek],1) AS S1_IN,
 que_rpt_Schedule_s1.s2StartDate, IIf([s2startdate] Between [startofweek] And [endofweek],1) AS S2_IN
FROM ref_dates_week
LEFT JOIN que_rpt_Schedule_s1 ON ref_dates_week.ID = que_rpt_Schedule_s1.S2_Startweek;

Nic;o)
0
 

Author Closing Comment

by:hragape
Comment Utility
Thank you! I should have known it would be something simple.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Good catch.  I've been looking at this thing for over a week!
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Hmm, my test query works flawlessly with the UNION ALL with and without the intermediate ";" , so I assume it's something in the data when the previous tip doesn't work.
Can you create a sample .accdb with (part of) the tables and the query and attach that to the comment so GRayL and I can have a look?

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
As an old veteran expert you should know it's always in the details Ray <LOL>
Still puzzled why the ";" doesn't interfere for me in the accdb database, from the "old" .mdb databases I know you get a message when there's something behind the final ";".

OK, glad it's now working hragape, success with your application !

Nic;o)
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
I knew the devil was in the details, and I knew a 'fresh set of eyes' was the answer.  Thanks, Nico and good luck with the application hragape.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now