Union Query not returning second select results

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

hragapeAsked:
Who is Participating?
 
nico5038Commented:
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
 
GRayLCommented:
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
 
hragapeAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
GRayLCommented:
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
 
hragapeAuthor Commented:
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
 
GRayLCommented:

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
 
GRayLCommented:
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
 
hragapeAuthor Commented:
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
 
GRayLCommented:
OK, but I am going to try to get some 'fresh' eyes on this.
0
 
nico5038Commented:
Did you try to save both queries and create the UNION like:

select * from qrySaved1
UNION ALL
select * from qrySaved2;

Nic;o)
0
 
hragapeAuthor Commented:

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
 
GRayLCommented:
Hi Nico:  any comment about including/not including the False argument in an Access query using the IIF() function?
0
 
hragapeAuthor Commented:
Thank you! I should have known it would be something simple.
0
 
GRayLCommented:
Good catch.  I've been looking at this thing for over a week!
0
 
nico5038Commented:
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
 
nico5038Commented:
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
 
GRayLCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.