Showing Null Sums in a Selection Query 1

[rcoutts]

I have a table the lists employees, e.g.,

   Mary
   Joe
   Sue
   Brian
   Beth

And a second table that lists how many hours they each worked that week, e.g.,

   Brian   8
   Sue     8
   Brian   4
   Joe     7
   Sue     3
   Brian   5

I want a Query to Sum the hours for the week. So, I created a simple Select Query that links the employees of the first table to the second and sums the results, e.g.,

   Brian   17
   Sue     11
   Joe      7

Problem is, how can I also list the names in my query which have Null hours for the week? My Query is being put into a Report and I want to list all the employees, regardless of whether the worked for the week or not. E.g.,

   Brian   17
   Sue     11
   Joe      7
   Mary     0
   Beth     0

I'd like to be able to do this in design view since I don't know SQL very well, but if I have to use SQL, that's fine too.

Thanks!
Rich

 

[JimHorton]

Rich,
In the 'Field' row of the query grid, put Iff( IsNull([hours]),0,[hours])
--Jim

 

[tlbroadbent]

The correct way to handle this is with an Left Join of the Employees table to the Hours table. Change the following to match your table and object names.

Select e.Name, sum(h.Hours) As TotHrs
From Employees As e Left Join Hours as h
On e.Name=h.Name

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Databaseguy]

The problem with Null is it propagates. Null * var = Null. Null + var = Null. Null somewhere near a filed = Null.

Properly join the fileds as Terry mentioned. Try to construct your data entry form to not allow Nulls. Maybe even make the underlying table not allow nulls. (Required = Yes) As a last resort, look into the NZ function.
Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[tlbroadbent]

Tyrone,

Thanks. My post was rather incomplete. I appreciate your help in pointing that out. Here is the complete query.

Select e.Name, Sum(IsNull(h.Hours,0)) As TotHrs
From Employees As e Left Join Hours as h
On e.Name=h.Name
Group By e.name Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.