Microsoft Access - Left Outer Join Crosstab Query Question

Question

I'm trying to run a crosstab query showing all Employees from EMP.NEW table with matching records from the UPTO table. The results should return 80 employees but only returns 68. It isn't returning results for employees with no data in the UPTO table. Please help.

TRANSFORM Count(UPTO.SEG_CODE) AS CountOfSEG_CODE

SELECT [First] & " " & [Last] AS Name2

FROM EMP_NEW LEFT JOIN UPTO ON EMP_NEW.[EmpID without preceding zeros] = UPTO.EMP_ID

WHERE (((EMP_NEW.Dept)="Customer Care") AND ((EMP_NEW.Title)<>"Customer Care Manager") AND ((UPTO.NOM_DATE) Between #10/1/2015# And #9/30/2016#))

GROUP BY [First] & " " & [Last]

PIVOT Format([NOM_DATE],"mmm") In ("Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep");

Answer 1

I just tried to remove the criteria from the UPTO table and still only returns 68 records.

Answer 2

There are 146 records total in the EMP_New table. The criteria I applied to the EMP_NEW name field should return 87 records. Of the 87 employees 68 have records in the UPTO table. I want the crosstab query to return all 87 employees in the row field with a count of SEG_CODE from the UPTO table. It is only returning 68 records. The employees it isn't including are those that don't have data in the UPTO table. It is like the left outer join isn't working.

Answer 3

The fact that you have a criterion on NOM_DATE is causing the join to be effectively an INNER JOIN - if there is no record in UPTO, then the NOM_DATE field will be NULL, which is not "between" anything. Try changing the criterion to

(UPTO.NOM_DATE Between #10/1/2015# And #9/30/2016# OR UPTO.NOM_DATE IS NULL)

If you want the query to work next year, you may want to consider replacing the literal dates; e.g.

(UPTO.NOM_DATE >= DateSerial(Year(Date()) - 1, 10, 1) And UPTO.NOM_DATE < DateSerial(Year(Date()), 10,

1. OR UPTO.NOM_DATE IS NULL)

Answer 4

It is returning all the records because you put a criteria from the UPTO table. That negates the LEFT JOIN making it an INNER JOIN.

Answer 5

Hi. The result of the query is limited because you have applied some criteria to the query. How many records are in the EMP_NEW table?