MS SQL Left Outer Join - too few records

I can see the concept has been asked before re: left outer joins, but I just can't get it connected in my brain. I'll try to strike a balance between the technical table structures and describing the issue. (BTW, I'm experiencing the same issue creating queries in MS Access and creating views in MSSQL...)

I have a set of employees (TM_EMPS, TM_EMP_INFO, DEPTS) that need to be printed on a monthly timesheet whether they have taken leave (TM_LEAVE_HISTORY, TM_CALENDAR, TM_LEAVE_LOOKUP) or not. I'll specify a period (TM_CALENDAR.CAL_PERIOD) to reduce the result set to only this pay period, but I only get records back for employees who have requested leave.

I tried creating two queries in Access (which return the correct respective recordsets: all employees in query1 and leave records for the month in query2), and the result is still just the emps who have requested leave.

Here's the view created in MSSQL Server:

SELECT     TOP 100 PERCENT dbo.TM_EMP_INFO.EMP_ID, dbo.TM_EMP_INFO.EMP_LAST, dbo.TM_EMP_INFO.EMP_FIRST, dbo.TM_EMP_INFO.EMP_LOC,
                      dbo.DEPTS.DEPT_NAME, dbo.TM_CALENDAR.CAL_PERIOD, dbo.TM_LEAVE_HISTORY.LV_DATE
FROM         dbo.TM_EMP_INFO INNER JOIN
                      dbo.DEPTS ON dbo.TM_EMP_INFO.EMP_LOC = dbo.DEPTS.DEPT_CODE INNER JOIN
                      dbo.TM_EMPS ON dbo.TM_EMP_INFO.EMP_ID = dbo.TM_EMPS.TM_EMP_ID LEFT OUTER JOIN
                      dbo.TM_LEAVE_LOOKUP INNER JOIN
                      dbo.TM_LEAVE_HISTORY ON dbo.TM_LEAVE_LOOKUP.TM_LV_CODE = dbo.TM_LEAVE_HISTORY.LV_TYPE INNER JOIN
                      dbo.TM_CALENDAR ON dbo.TM_LEAVE_HISTORY.LV_DATE = dbo.TM_CALENDAR.CAL_DATE ON
                      dbo.TM_EMP_INFO.EMP_ID = dbo.TM_LEAVE_HISTORY.LV_EMP_ID
WHERE     (dbo.TM_EMPS.TM_TIMESHEET = 1) AND (dbo.TM_LEAVE_LOOKUP.TM_LV_DISPLAY = 1) AND (dbo.TM_CALENDAR.CAL_PERIOD = 200709)
ORDER BY dbo.DEPTS.DEPT_NAME, dbo.TM_EMP_INFO.EMP_LAST, dbo.TM_EMP_INFO.EMP_FIRST

I read someplace else about specifying criteria for WHERE vs ON... not sure if that's the right direction. Let me know if you need more info (table structures, etc.) Thanks! :) DerryStart Free Trial