You should not use Date as a field name as it can give you problems.
Try these two queries --
UNTESTED --- UNTESTED --
qryEmpAllDate --
SELECT Employee_PunchTime.EmpID, Employee_PunchTime.[Date] AS AllDate
FROM Employee_PunchTime, Holidays
GROUP BY Employee_PunchTime.EmpID, Employee_PunchTime.[Date]
UNION SELECT Employee_PunchTime.EmpID, Holidays.[Date]
FROM Employee_PunchTime, Holidays
GROUP BY Employee_PunchTime.EmpID, Holidays.[Date];

SELECT qryEmpAllDate.EmpID, Employee_PunchTime.[Time-in],
Employee_PunchTime.[Time-out], qryEmpAllDate.AllDate, [Paid Holiday]
FROM (qryEmpAllDate LEFT JOIN Employee_PunchTime ON qryEmpAllDate.EmpID =
Employee_PunchTime.EmpID AND qryEmpAllDate.AllDate =
Employee_PunchTime.[Date]) LEFT JOIN Holidays.Holiday ON
qryEmpAllDate.AllDate = Holidays.[Date];

--
Build a little, test a little.


"Keith" wrote:

I have two tables - Employee_PunchTime table and Holidays Table. The
PunchTime table contains three fields representing EmployeeID, Time-in and
Time-out; and the Holiday table contains two fields representing name of
holiday and the date.

If the payroll period is 5/31/2010 (Mon) thru 6/4/2010 (Fri) and 5/31/2010
is a paid holiday, I need to create a query that shows punched time and paid
holiday on a separate column.

On the paid holiday, there would obviously be no Time-In and Time-Out record.

It will look something like this:

EmpID Date Time-in Time-out Paid Holiday
------- ---- -------- --------- ------------
1001 5/31/10 Memorial Day
1001 6/01/10 9:00 am 6:00 pm
1001 6/02/10 9:00 am 6:00 pm
1001 6/03/10 9:00 am 6:00 pm
1001 6/02/10 9:00 am 6:00 pm

Any suggestions?

Thank you.

Keith