Need to sum with empty values

[J.J.]

My current formula below works fine except it does not return a zero value
where there are no entries ie. no HoursWorked. I tried using Nz or IFf
IsNull etc but keep comming up with either a syntax error or else the blank
hrs are not reported on. I am doing something wrong but after two days I
haven't been able to find how to fix it and none of the other current posts
have helped.

SELECT Sum(TrackHours.HoursWorked) AS MnthSum, TrackHours.EmployeeID
FROM TrackHours
WHERE (dates fall within paremeters etc.)
GROUP BY TrackHours.EmployeeID;

Could someone show how to return zero hours for these poor forgotten and
unemployed souls. Thank you

 

[John W. Vinson]

My current formula below works fine except it does not return a zero value
where there are no entries ie. no HoursWorked. I tried using Nz or IFf
IsNull etc but keep comming up with either a syntax error or else the blank
hrs are not reported on. I am doing something wrong but after two days I
haven't been able to find how to fix it and none of the other current posts
have helped.

SELECT Sum(TrackHours.HoursWorked) AS MnthSum, TrackHours.EmployeeID
FROM TrackHours
WHERE (dates fall within paremeters etc.)
GROUP BY TrackHours.EmployeeID;

Could someone show how to return zero hours for these poor forgotten and
unemployed souls. Thank you

You'll need to join the Employee table to pick up EmployeeID's for the lazy
slackers:

SELECT Sum(TrackHours.HoursWorked) AS MnthSum, Employees.EmployeeID
FROM Employees LEFT JOIN TrackHours
ON TrackHours.EmployeeID = Employees.EmployeeID
WHERE (dates fall within paremeters etc.)
GROUP BY Employees.EmployeeID;

 

[J.J.]

Thank you for the quick response. However, your example does not seem to
work. I do have an employee table but I dumbed down my question in the post
for fear someone would tell me to track on employees instead which would lead
to further explanations. Anyway, I am actually tracking on workorders hence
WorkOrderID in the TrackHours table and not employeeID. The TrackHours table
has the WorkDates and the HoursWorked. As there may be several workorders
for each employee, the TrackHours table is joined to the WorkOrder table via
the WorkOrderID. My working query really is:
SELECT Sum(TrackHours.HoursWorked) AS MnthSum, TrackHours.WorkOrderID
FROM TrackHours
WHERE (dates fall within paremeters etc.)
GROUP BY TrackHours.WorkOrderID;

All of this is really nothing, so I simply substituted WorkOrder.WorkOrderID
from your example of employees.EmployeeID. and WorkOrder for employees. When
I ran it, I got an error "data type mismatch in criteria expression." I
believe that the criteria is the where clause filtering on dates which did
work fine.

Somehow, I need to get HoursWorked to show a zero whenever there is nothing
recorded In the Trackhours table eventhough there is a WorkOrderID in the
WorkOrder table

 

[J.J.]

Oops: Error should read " Syntax Error in From Clause"

 

[J.J.]

Please disregard last error corection message. my son tried to tinker with
the code.

 

[John W. Vinson]

Thank you for the quick response. However, your example does not seem to
work. I do have an employee table but I dumbed down my question in the post
for fear someone would tell me to track on employees instead which would lead
to further explanations. Anyway, I am actually tracking on workorders hence
WorkOrderID in the TrackHours table and not employeeID. The TrackHours table
has the WorkDates and the HoursWorked. As there may be several workorders
for each employee, the TrackHours table is joined to the WorkOrder table via
the WorkOrderID. My working query really is:
SELECT Sum(TrackHours.HoursWorked) AS MnthSum, TrackHours.WorkOrderID
FROM TrackHours
WHERE (dates fall within paremeters etc.)
GROUP BY TrackHours.WorkOrderID;

All of this is really nothing, so I simply substituted WorkOrder.WorkOrderID
from your example of employees.EmployeeID. and WorkOrder for employees. When
I ran it, I got an error "data type mismatch in criteria expression." I
believe that the criteria is the where clause filtering on dates which did
work fine.

Somehow, I need to get HoursWorked to show a zero whenever there is nothing
recorded In the Trackhours table eventhough there is a WorkOrderID in the
WorkOrder table

Then you need to display and group by WorkOrder.WorkOrderID (which always
exists for an existing workorder) rather than TrackHours.WorkorderID (which
might not exist). If the dates in the WHERE clause are in the TrackHours
table, you also need to add an OR IS NULL to the criteria.

 

[J.J.]

Almost have it now!
Following your example below I had already grouped by WorkOrder.WorkOrderID.
I thought you had it figured out for a moment with the OR IS NULL clause
thinking that the date parameters would be ignored when no hours were
recorded in the TrackHours table but unfortunately the data type mismatch
error re-appeared. This leads me to believe that while we are close, the
syntax isn't quite right. Right and inner joins do work but, of course,
simply ignore our poor unemployed. The date parameter is only a problem in
the left join. There are no dates in the WordOrder table and so this has me
thinking. Is there a method to somehow implement the Where clause only for
the right table [TrackHours]?

 

[John Spencer]

You will need to use a subquery in the FROM clause to solve this

SELECT Sum(Hours.HoursWorked) AS MnthSum
, Employees.EmployeeID
FROM Employees LEFT JOIN
(SELECT HoursWorked, EmployeeID FROM TrackHours
WHERE (dates fall within paremeters etc.) ) as Hours
ON Employees.EmployeeID = Hours.EmployeeID
GROUP BY Employees.EmployeeID

If you require square brackets in the subquery, then Access will force
you to do this with two queries. Your first query will be your existing
query saved as a nmae query - say QHoursWorked

SELECT Sum(TrackHours.HoursWorked) AS MnthSum, TrackHours.EmployeeID
FROM TrackHours
WHERE (dates fall within paremeters etc.)
GROUP BY TrackHours.EmployeeID

Now build a query Left Joining the above query to your employees table.
That might look something like the following.

SELECT E.EmployeeID, Nz(q.MnthSum,0) as MnthHours
FROM EmployeesTable as E LEFT JOIN QHoursWorked as Q
ON E.EmployeeID = Q.EmployeeID

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Almost have it now!
Following your example below I had already grouped by WorkOrder.WorkOrderID.
I thought you had it figured out for a moment with the OR IS NULL clause
thinking that the date parameters would be ignored when no hours were
recorded in the TrackHours table but unfortunately the data type mismatch
error re-appeared. This leads me to believe that while we are close, the
syntax isn't quite right. Right and inner joins do work but, of course,
simply ignore our poor unemployed. The date parameter is only a problem in
the left join. There are no dates in the WordOrder table and so this has me
thinking. Is there a method to somehow implement the Where clause only for
the right table [TrackHours]?

Please post your actual SQL. Yes, you'll need to apply the criteria only to
the table containing the date fields (since that's where the *fields* are!),
but you'll need to get the parentheses right; you haven't posted the structure
of the tables or the WHERE clause you've tried so I can't advise.

 

[J.J.]

Thank you so much both of you John Spencer and John Vinson.
John Vinson, thank you for putting me partially on the right track with the
left join. In the end John Spencer's first example did exactly what I wanted
it to do, although it took a few moments to figure out exactly how this piece
of crafty code works. After I converted the code to my real example, I simply
slide a Nz around the Sum function to return my zero's and that was it.
Great stuff guys! Again thank you very much!