Nz function not showing all records

[dwaynesworld]

I have a query where I would like for all employee records to display for
time taken. If the employee has not taken any time off, the record should
show "0". Here is my query:

SELECT tblEmployeeTimeOff2.Instructor, Sum(Nz([Hours Taken],0)) AS [Hrs
Taken], DateDiff("d",(forms!ParamForm!StartDate),(forms!ParamForm!EndDate))+1
AS TotalDays, forms!ParamForm!StartDate AS StartDate, forms!ParamForm!EndDate
AS EndDate
FROM tblEmployeeTimeOff2
WHERE (((tblEmployeeTimeOff2.Date) Between [forms]![ParamForm]![StartDate]
And [forms]![ParamForm]![EndDate]))
GROUP BY tblEmployeeTimeOff2.Instructor,
DateDiff("d",(forms!ParamForm!StartDate),(forms!ParamForm!EndDate))+1,
forms!ParamForm!StartDate, forms!ParamForm!EndDate;


The problem that I am getting is that the results only display the employees
that have entered time off for the period. Please help as I am desperate.
Thanks

 

[Michel Walsh]

Save that query, say name it q1. Then, have it with your table of all
employee, no dup, in a new query:


SELECT employee.employeeID, q1.*
FROM employee LEFT JOIN q1
ON employee.employeeID=q1.instructor




(I assumed the employee reference, in your actual query, is the field
instructor).





Hoping it may help,
Vanderghast, Access MVP

 

[dwaynesworld]

Works like a charm...Thanks!!!

Save that query, say name it q1. Then, have it with your table of all
employee, no dup, in a new query:


SELECT employee.employeeID, q1.*
FROM employee LEFT JOIN q1
ON employee.employeeID=q1.instructor




(I assumed the employee reference, in your actual query, is the field
instructor).





Hoping it may help,
Vanderghast, Access MVP

I have a query where I would like for all employee records to display for
time taken. If the employee has not taken any time off, the record should
show "0". Here is my query:

SELECT tblEmployeeTimeOff2.Instructor, Sum(Nz([Hours Taken],0)) AS [Hrs
Taken],
DateDiff("d",(forms!ParamForm!StartDate),(forms!ParamForm!EndDate))+1
AS TotalDays, forms!ParamForm!StartDate AS StartDate,
forms!ParamForm!EndDate
AS EndDate
FROM tblEmployeeTimeOff2
WHERE (((tblEmployeeTimeOff2.Date) Between [forms]![ParamForm]![StartDate]
And [forms]![ParamForm]![EndDate]))
GROUP BY tblEmployeeTimeOff2.Instructor,
DateDiff("d",(forms!ParamForm!StartDate),(forms!ParamForm!EndDate))+1,
forms!ParamForm!StartDate, forms!ParamForm!EndDate;


The problem that I am getting is that the results only display the
employees
that have entered time off for the period. Please help as I am desperate.
Thanks