By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,548 Members | 1,134 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,548 IT Pros & Developers. It's quick & easy.

How to show records even if the value is null

P: 9
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT [Overtime Hours Query].EmployeeID, Employees.LastName, Employees.FirstName, Sum([Overtime Hours Query].TotalOTHoursWk) AS TotalOTHoursPP
  2. FROM Employees LEFT JOIN [Overtime Hours Query] ON Employees.ID=[Overtime Hours Query].EmployeeID
  3. WHERE ((([Overtime Hours Query].LastWeekDay) Between [Forms]![Date Range].[StartDate] And [Forms]![Date Range].[EndDate]))
  4. GROUP BY [Overtime Hours Query].EmployeeID, Employees.LastName, Employees.FirstName
  5. 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.

Sep 2 '08 #1
Share this Question
Share on Google+
11 Replies

Expert Mod 2.5K+
P: 2,545
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Employees.ID, Employees.LastName, Employees.FirstName, Sum([Overtime Hours Query].TotalOTHoursWk) AS TotalOTHoursPP
  2. FROM Employees LEFT JOIN [Overtime Hours Query] ON Employees.ID=[Overtime Hours Query].EmployeeID
  3. WHERE (((([Overtime Hours Query].LastWeekDay) Between [Forms]![Date Range].[StartDate] And [Forms]![Date Range].[EndDate])) Or Isnull([Overtime Hours Query].[EmployeeID])
  4. GROUP BY Employees.ID, Employees.LastName, Employees.FirstName
  5. ORDER BY Employees.LastName, Employees.FirstName;
Sep 2 '08 #2

P: 9

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.
Sep 2 '08 #3

Expert Mod 2.5K+
P: 2,545
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.

Sep 3 '08 #4

P: 9
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!!

Expand|Select|Wrap|Line Numbers
  1. SELECT Employees.ID, Employees.LastName, Employees.FirstName, Sum([Overtime Hours Query].TotalOTHoursWk) AS TotalOTHoursPP
  2. FROM Employees LEFT JOIN [Overtime Hours Query] ON Employees.ID=[Overtime Hours Query].EmployeeID
  3. WHERE ((([Overtime Hours Query].LastWeekDay) Between Forms![Date Range].StartDate And Forms![Date Range].EndDate)) Or (((IsNull([Overtime Hours Query].EmployeeID))<>False))
  4. GROUP BY Employees.ID, Employees.LastName, Employees.FirstName
  5. ORDER BY Employees.LastName, Employees.FirstName;
Sep 4 '08 #5

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. 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.

Sep 4 '08 #6

Expert 2.5K+
P: 2,653
Post deleted. Don't mind please.
Sep 4 '08 #7

P: 9
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!!

Sep 4 '08 #8

Expert 100+
P: 1,134
you could try it this way
Expand|Select|Wrap|Line Numbers
  1. SELECT Employees.ID, 
  2.           Employees.LastName, 
  3.           Employees.FirstName, 
  4.           a.TotalOTHoursPP
  5. FROM Employees 
  7. (     SELECT EmployeeID,sum(TotalOTHoursWk) as TotalOTHoursPP
  8.       FROM [Overtime Hours Query] 
  9.       WHERE LastWeekDay Between Forms![Date Range].StartDate 
  10.          AND Forms![Date Range].EndDate
  11.       GROUP BY EmployeeID
  12. )a
  13. ON Employees.ID=a.EmployeeID
  14. ORDER BY Employees.LastName, Employees.FirstName;
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 :)
Sep 4 '08 #9

P: 9
That makes sense and that also worked!! Thank you very much for your help!
Sep 8 '08 #10

P: 1
thnks that helped a lot,i was running in maze otherwise.
May 26 '10 #11

Expert Mod 15k+
P: 31,662
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT   Employees.ID
  2.        , Employees.LastName
  3.        , Employees.FirstName
  4.        , Sum([Overtime Hours Query].TotalOTHoursWk) AS TotalOTHoursPP
  6. FROM     Employees LEFT JOIN
  7.          [Overtime Hours Query]
  8.   ON     Employees.ID=[Overtime Hours Query].EmployeeID
  10. WHERE    ([Overtime Hours Query].LastWeekDay Between [Forms]![Date Range].[StartDate] And [Forms]![Date Range].[EndDate])
  11.    OR    ([Overtime Hours Query].[EmployeeID] Is Null)
  13. GROUP BY Employees.ID
  14.        , Employees.LastName
  15.        , Employees.FirstName
  17. ORDER BY Employees.LastName
  18.        , 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).
May 26 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.