Nz function not showing all records


D

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
 
Ad

Advertisements

M

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
 
Ad

Advertisements

D

dwaynesworld

Works like a charm...Thanks!!!

Michel Walsh said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top