How to show records even if the value is null

[bikefixxer]

I'm a beginner with Access and am using the 2007 version on XP. I've created a database that keeps track of employee hours where I work. Everything has worked fine until we recently hired someone new. I have a subquery that checks for the amount of overtime hours someone has between a specified date range, and the new employee doesn't show up at all. I'm thinking it's because no hours information has been entered in because he was new, so the query is returning a null value or something. I've tried to use an iif statement so that if it's null it returns a zero but that didn't work; however, I may have not had proper syntax or something. Anyway, here's the query:

SELECT [Overtime Hours Query].EmployeeID, Employees.LastName, Employees.FirstName, Sum([Overtime Hours Query].TotalOTHoursWk) AS TotalOTHoursPP
FROM Employees LEFT JOIN [Overtime Hours Query] ON Employees.ID=[Overtime Hours Query].EmployeeID
WHERE ((([Overtime Hours Query].LastWeekDay) Between [Forms]![Date Range].[StartDate] And [Forms]![Date Range].[EndDate]))
GROUP BY [Overtime Hours Query].EmployeeID, Employees.LastName, Employees.FirstName
ORDER BY Employees.LastName, Employees.FirstName;

If anyone can help me figure out a way to return all employees regardless if they have hours information or not it would really help. All I need it to say is zero for them if there's nothing there. The new employee shows up at later dates when the information is there but not when he barely started.

Thanks!

[Stewart Ross]

Hi, and Welcome to Bytes!

Well done for using a left-join with your tables - that's a good start. The left join means that all of the employee records will be included, subject to whatever WHERE clause you are using.

You will need to make sure you are referring to the fields from the correct side of the join, however - so substitute the employee table ID for the overtime hours employee ID currently listed, for example. You will also need to alter the WHERE clause and the Group By clause - the WHERE clause as it stands will prevent you from seeing any of the records which have no overtime hours, because the WHERE selects only those which are between certain overtime dates.

A revised version to try is listed below.

SELECT Employees.ID, Employees.LastName, Employees.FirstName, Sum([Overtime Hours Query].TotalOTHoursWk) AS TotalOTHoursPP
FROM Employees LEFT JOIN [Overtime Hours Query] ON Employees.ID=[Overtime Hours Query].EmployeeID
WHERE (((([Overtime Hours Query].LastWeekDay) Between [Forms]![Date Range].[StartDate] And [Forms]![Date Range].[EndDate])) Or Isnull([Overtime Hours Query].[EmployeeID])
GROUP BY Employees.ID, Employees.LastName, Employees.FirstName
ORDER BY Employees.LastName, Employees.FirstName;

-Stewart

[bikefixxer]

Stewart,

Thanks for the reply. What you're saying totally makes sense. I made the improvements though and there is no change to the result. In the design view I noticed that it said "< > False" under criteria. I changed that to true and then the new employee showed up but it also added up everyone else's overtime hours from the beginning. Any other ideas?

Thanks for the help! I appreciate it.

[Stewart Ross]

Hi. Could you post the actual SQL for your query as it is now, please? I don't know what field the '<> false' criterion was being applied to as it was not present in the original SQL posted, so it may be that the SQL in your query is not quite what I expect it to be. Once this is reviewed I'll post back with suggestions for overcoming the current problem.

-Stewart

[bikefixxer]

Sorry about not being clear on that. I've pasted the new code below. The '<> False' thing didn't show up at first. I noticed it when I went into the design view after changing the query. After I changed it to true in the design view is when it started showing up in the sql query view. Thanks again for your help!!

SELECT Employees.ID, Employees.LastName, Employees.FirstName, Sum([Overtime Hours Query].TotalOTHoursWk) AS TotalOTHoursPP
FROM Employees LEFT JOIN [Overtime Hours Query] ON Employees.ID=[Overtime Hours Query].EmployeeID
WHERE ((([Overtime Hours Query].LastWeekDay) Between Forms![Date Range].StartDate And Forms![Date Range].EndDate)) Or (((IsNull([Overtime Hours Query].EmployeeID))<>False))
GROUP BY Employees.ID, Employees.LastName, Employees.FirstName
ORDER BY Employees.LastName, Employees.FirstName;

[Stewart Ross]

Hi. I think the date issue is actually a different one, and it may well relate to comparing real dates in your table to strings representing dates in your form's textboxes. Working with dates can be less obvious than it might seem, because dates are stored internally as numbers representing the number of days from a particular reference date, and textboxes which are unbound simply store string values. They may look the same, but they are stored entirely differently - and BETWEEN or other comparative operators can fail when faced with comparing an internal date value to a string representing a date, in part because of what Access does to try to convert from one to the other.

To make sure that the date comparison is correct I have used the date conversion function CDate in the revised WHERE clause below for you to try:

WHERE ([Overtime Hours Query].LastWeekDay) Between CDate(Forms![Date Range].StartDate) And CDate(Forms![Date Range].EndDate)) Or (IsNull([Overtime Hours Query].EmployeeID = True)

Let me know how you get on.

-Stewart

[FishVal]

Post deleted. Don't mind please.

[bikefixxer]

Hi. The query returned the same values as it has before, so still no dice.

However, I found that by entering some days into the hours table for the employee and just entering zero hours he now shows up. Normally there's no information there at all, which is causing the problem, but if I put in those earlier dates all seems to be well. So, it's not the ideal solution but it works for now. If you want to keep trying to help me out that would be great but if not it'd be okay too. I'm sure this isn't the only flaw to this database since I only half know what I'm doing, so I won't be too upset if it's not perfect.

Thanks for all the help though, I appreciate it!!

-Tyson

[Delerna]

you could try it this way
[code=sql]
SELECT Employees.ID,
Employees.LastN ame,
Employees.First Name,
a.TotalOTHoursP P
FROM Employees
LEFT JOIN
( SELECT EmployeeID,sum( TotalOTHoursWk) as TotalOTHoursPP
FROM [Overtime Hours Query]
WHERE LastWeekDay Between Forms![Date Range].StartDate
AND Forms![Date Range].EndDate
GROUP BY EmployeeID
)a
ON Employees.ID=a. EmployeeID
ORDER BY Employees.LastN ame, Employees.First Name;
[/code]
I think thats correct. I hope I didnt miss anything

By moving the WHERE clause into a subquery it should stop it from disallowing the LEFT JOIN to select all employee records and ony the matching records in [Overtime Hours Query]

I hope that sentence makes sense :)

[bikefixxer]

That makes sense and that also worked!! Thank you very much for your help!

[itssti]

thnks that helped a lot,i was running in maze otherwise.

[NeoPa]

I've looked at Stewart's earlier SQL and I see no obvious issue with it (except maybe that the SQL engine isn't very happy dealing with the IsNull() function rather than its inbuilt comparison Is Null.

Would you mind trying the very slightly amended SQL below on your data and letting us know the results.

SELECT   Employees.ID
       , Employees.LastName
       , Employees.FirstName
       , Sum([Overtime Hours Query].TotalOTHoursWk) AS TotalOTHoursPP

FROM     Employees LEFT JOIN
         [Overtime Hours Query]
  ON     Employees.ID=[Overtime Hours Query].EmployeeID

WHERE    ([Overtime Hours Query].LastWeekDay Between [Forms]![Date Range].[StartDate] And [Forms]![Date Range].[EndDate])
   OR    ([Overtime Hours Query].[EmployeeID] Is Null)

GROUP BY Employees.ID
       , Employees.LastName
       , Employees.FirstName

ORDER BY Employees.LastName
       , Employees.FirstName

PS. I know Delerna's provided a set of SQL that does work, but I'm still interested to know why Stewart's doesn't. I see no obvious reason why the more complicated (subqueries etc) version should be necessary (and I do like to understand what I'm looking at).