Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Union Query not returning second select results

Posted on 2008-06-19
17
Medium Priority
?
973 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
17 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 21842102
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
ID: 21876402
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
ID: 21876555
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:hragape
ID: 21877270
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
ID: 21879045

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
ID: 21879063
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
ID: 21883797
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
ID: 21885302
OK, but I am going to try to get some 'fresh' eyes on this.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 21885567
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
ID: 21886081

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
ID: 21886231
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 500 total points
ID: 21886361
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
ID: 31468929
Thank you! I should have known it would be something simple.
0
 
LVL 44

Expert Comment

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

Expert Comment

by:nico5038
ID: 21886427
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
ID: 21886483
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
ID: 21887051
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

722 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