Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Union Query not returning second select results

Posted on 2008-06-19
17
Medium Priority
?
979 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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

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…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

783 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