query with nulls

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a db that has the following elements:

Tables:
Employee
Meeting_Hours
Training_Hours

The Meeting_Hours and Training_Hours have identical fields:
Date: short date
Hours: number

They are related to the Employee table using a 1-to-many.

I need to create a query that will sum the Meeting_Hours for a given
employee for the current month. If no entry is found for a given employee
for the current month then the sum should be 0. Can someone help me with
this?

I can get the data ok for the current month if a record exits, but I am not
sure how to display 0 when a record does not exist. I need this query for a
report that will show the monthly totals for each employee even if they have
logged 0 hours.

TIA

Jeff
 
Jeff said:
I have a db that has the following elements:

Tables:
Employee
Meeting_Hours
Training_Hours

The Meeting_Hours and Training_Hours have identical fields:
Date: short date
Hours: number

They are related to the Employee table using a 1-to-many.

I need to create a query that will sum the Meeting_Hours for a given
employee for the current month. If no entry is found for a given employee
for the current month then the sum should be 0. Can someone help me with
this?

I can get the data ok for the current month if a record exits, but I am not
sure how to display 0 when a record does not exist. I need this query for a
report that will show the monthly totals for each employee even if they have
logged 0 hours.
Click to expand...


You need to use both tables with a outer join to get all the
employees:

SELECT Employees.Employee,
Sum(Meetings.Meeting_Hours) As TotalMeeting,
Sum(Meetings.Training_Hours) As TotalTraining
FROM Employees LEFT JOIN Meetings
ON Employees.Employee = Meetings.Employee
GROUP BY Employees.Employee
 
Hi Marsh,

The tables are already outer left joined as you suggested. The problem
comes in when I try to limit the data to only show for a current month. I
may have not been clear enough on what I am trying to accomplish. Since the
meeting_hours table will contain data each month I need to be able to sum the
number of hours by employee for the current month. If a particular employee
has not entered in any meeting hours for the current month I need this to
display 0 hours. Does this make sense? The problem exists when I select to
only see the data for the current month.

TIA
 
The tables are already outer left joined as you suggested. The problem
comes in when I try to limit the data to only show for a current month. I
may have not been clear enough on what I am trying to accomplish. Since the
meeting_hours table will contain data each month I need to be able to sum the
number of hours by employee for the current month. If a particular employee
has not entered in any meeting hours for the current month I need this to
display 0 hours. Does this make sense? The problem exists when I select to
only see the data for the current month.
Click to expand...

Add a criterion

OR IS NULL

to the current-month criterion.

You might need to use a Query based on another Query - that is, create
a query with just the current month's data, and Left Join this query
to the Employee table.

John W. Vinson[MVP]
 
Hi John,

The "OR Is Null" does not work because some months contain will contain data
for an employee, so then the field is left blank again. I will try to use
your other suggestion of using two querys.

Thanks
 
Back
Top