Union query doesn't return all expected records

[projecttoday]

I have 2 queries and when I make a union of them, I don't get all the records from the 2nd query as I do when I run it by itself. How is this possible? The only thing I can think of is I'm using Date/Time fields but only selecting on the date. But I don't think that should matter. The union query was created by copying and pasting the other 2 queries. Anyone know?

Query 1:

SELECT tblHoursworked.starttime, tblHoursworked.endtime, tblHoursworked.typeid, IIf(typeid=1,((DateDiff("n",[starttime],[endtime]) - ((60 * tblHoursworked.lunchtimehrs) + tblHoursworked.lunchtimemins))/60),0) AS Col1amount, IIf(typeid=2,((DateDiff("n",[starttime],[endtime]) - ((60 * tblHoursworked.lunchtimehrs) + tblHoursworked.lunchtimemins))/60),0) AS Col2amount, IIf(typeid=3,((DateDiff("n",[starttime],[endtime]) - ((60 * tblHoursworked.lunchtimehrs) + tblHoursworked.lunchtimemins))/60),0) AS Col3amount, IIf(typeid=4,((DateDiff("n",[starttime],[endtime]) - ((60 * tblHoursworked.lunchtimehrs) + tblHoursworked.lunchtimemins))/60),0) AS Col4amount, tblHoursworked.personid, tblHoursworked.weekno
FROM tblHoursworked
WHERE (((tblHoursworked.starttime) Between [forms].[frmtimeprtsel].[txtstartdate] And [forms].[frmtimeprtsel].[txtenddate]) AND ((tblHoursworked.personid)=[forms].[frmtimeprtsel].[txtpersonid]));

Query 2:

SELECT tblDates.starttime, tblDates.endtime, tblDates.typeid, tblDates.Col1amount, tblDates.Col2amount, tblDates.Col3amount, tblDates.Col4amount, tblDates.personid, tblDates.weekno
FROM tblDates
WHERE (((tblDates.starttime) Between [forms].[frmtimeprtsel].[txtstartdate] And [forms].[frmtimeprtsel].[txtenddate]) AND ((tblDates.personid)=[forms].[frmtimeprtsel].[txtpersonid]));

Query 3:

SELECT tblHoursworked.starttime, tblHoursworked.endtime, tblHoursworked.typeid, IIf(typeid=1,((DateDiff("n",[starttime],[endtime]) - ((60 * tblHoursworked.lunchtimehrs) + tblHoursworked.lunchtimemins))/60),0) AS Col1amount, IIf(typeid=2,((DateDiff("n",[starttime],[endtime]) - ((60 * tblHoursworked.lunchtimehrs) + tblHoursworked.lunchtimemins))/60),0) AS Col2amount, IIf(typeid=3,((DateDiff("n",[starttime],[endtime]) - ((60 * tblHoursworked.lunchtimehrs) + tblHoursworked.lunchtimemins))/60),0) AS Col3amount, IIf(typeid=4,((DateDiff("n",[starttime],[endtime]) - ((60 * tblHoursworked.lunchtimehrs) + tblHoursworked.lunchtimemins))/60),0) AS Col4amount, tblHoursworked.personid, tblHoursworked.weekno
FROM tblHoursworked
WHERE (((tblHoursworked.starttime) Between [forms].[frmtimeprtsel].[txtstartdate] And [forms].[frmtimeprtsel].[txtenddate]) AND ((tblHoursworked.personid)=[forms].[frmtimeprtsel].[txtpersonid]))
UNION SELECT tblDates.starttime, tblDates.endtime, tblDates.typeid, tblDates.Col1amount, tblDates.Col2amount, tblDates.Col3amount, tblDates.Col4amount, tblDates.personid, tblDates.weekno
FROM tblDates
WHERE (((tblDates.starttime) Between [forms].[frmtimeprtsel].[txtstartdate] And [forms].[frmtimeprtsel].[txtenddate]) AND ((tblDates.personid)=[forms].[frmtimeprtsel].[txtpersonid]));

 

[dhookom]

You won't get duplicates with UNION. Try replace UNION with UNION ALL.

Duane
Hook'D on Access
MS Access MVP

 

[projecttoday]

Will do. Thanks.