Access Not Showing Up Records for Rt Join or Inner Join

  • Thread starter Thread starter Ker
  • Start date Start date
K

Ker

I am trying to list all my employee Names from the tblEmployee Table
and only matching records from SubJobTable. My relationship says all
records from tblEmployee and only those from SubJobTable where they
are equal. I have read lots of threads but none that help solve this
one. It looks a little complex on the select because I'm doing dates
per customer request. The query works great except I don't get all
employee Names.

Thanks to anyone who will look at it.

SELECT
tblEmployee.EmployeeStatus, tblEmployee.EmployeeName,
SubJobTable.Date, Format$([SubJobTable.Date],"ddd") AS [Day],
Format$([SubJobTable.Date],"Short Date",0,0) AS Fordate,
SubJobTable.GLAcct, (([JobCalculation]+[LoadUpCalculation]+[TravelCalculation]+[PlantMatPUDelCalculation]+[DesignCalculation]+[AdminCalculation]+[ShopCalculation]+[NurseryCalculation]+[OtherCalculation]-[LunchToBeDeducted])/60)
AS TotalWithBenefits,
(([JobCalculation]+[LoadUpCalculation]+[TravelCalculation]+[PlantMatPUDelCalculation]+[DesignCalculation]+[AdminCalculation]+[ShopCalculation]+[NurseryCalculation]+[OtherCalculation]-[LunchToBeDeducted])/60)
AS Total, ([JobCalculation]+[LoadUpCalculation]+[TravelCalculation]+[PlantMatPUDelCalculation]+[DesignCalculation])
AS TotalBillable, ([AdminCalculation]+[ShopCalculation]+[NurseryCalculation]+[OtherCalculation])
AS TotalNonBillable,
IIf(IsNull(Sum(DateDiff("n",[JobStart],[JobEnd]))),0,(Sum(DateDiff("n",[JobStart],[JobEnd]))))
AS JobCalculation, IIf(IsNull(Sum(DateDiff("n",[LoadUpStart],[LoadUpEnd]))),0,(Sum(DateDiff("n",[LoadUpStart],[LoadUpEnd]))))
AS LoadUpCalculation, ([TravelFromCalculation]+[TravelToCalculation])
AS TravelCalculation,
IIf(IsNull(Sum(DateDiff("n",[PlantMatPUDelStart],[PlantMatPUDelEnd]))),0,(Sum(DateDiff("n",[PlantMatPUDelStart],[PlantMatPUDelEnd]))))
AS PlantMatPUDelCalculation,
IIf(IsNull(Sum(DateDiff("n",[DesignStart],[DesignEnd]))),0,(Sum(DateDiff("n",[DesignStart],[DesignEnd]))))
AS DesignCalculation,
IIf(IsNull(Sum(DateDiff("n",[TravelFromStart],[TravelFromEnd]))),0,(Sum(DateDiff("n",[TravelFromStart],[TravelFromEnd]))))
AS TravelFromCalculation,
IIf(IsNull(Sum(DateDiff("n",[TravelToStart],[TravelToEnd]))),0,(Sum(DateDiff("n",[TravelToStart],[TravelToEnd]))))
AS TravelToCalculation,
IIf(IsNull(Sum(DateDiff("n",[AdminStart],[AdminEnd]))),0,(Sum(DateDiff("n",[AdminStart],[AdminEnd]))))
AS AdminCalculation,
IIf(IsNull(Sum(DateDiff("n",[ShopStart],[ShopEnd]))),0,(Sum(DateDiff("n",[ShopStart],[ShopEnd]))))
AS ShopCalculation, IIf(IsNull(Sum(DateDiff("n",[NurseryStart],[NurseryEnd]))),0,(Sum(DateDiff("n",[NurseryStart],[NurseryEnd]))))
AS NurseryCalculation,
IIf(IsNull(Sum(DateDiff("n",[OtherStart],[OtherEnd]))),0,(Sum(DateDiff("n",[OtherStart],[OtherEnd]))))
AS OtherCalculation, IIf(([JobName]="Non-Job
Related"),[LunchToBeDeducted],0) AS LunchCalculationNonJob,
IIf(([JobName]="Non-Job Related"),0,[LunchToBeDeducted]) AS
LunchCalculationJob, SubJobTable.LunchToBeDeducted
FROM
SubJobTable
RIGHT JOIN
tblEmployee ON SubJobTable.EmployeeName = tblEmployee.EmployeeName
GROUP BY
tblEmployee.EmployeeStatus, tblEmployee.EmployeeName,
SubJobTable.Date, Format$([SubJobTable.Date],"ddd"),
Format$([SubJobTable.Date],"Short Date",0,0), SubJobTable.GLAcct,
IIf(([JobName]="Non-Job Related"),[LunchToBeDeducted],0),
IIf(([JobName]="Non-Job Related"),0,[LunchToBeDeducted]),
SubJobTable.LunchToBeDeducted
HAVING (((tblEmployee.EmployeeStatus)="clerical" Or
(tblEmployee.EmployeeStatus) Is Null) AND ((SubJobTable.Date) Between
[forms]![frmDateRange]![StartDate] And
[forms]![frmDateRange]![EndDate]))
ORDER BY SubJobTable.Date;
 
HAVING (((tblEmployee.EmployeeStatus)="clerical" Or
(tblEmployee.EmployeeStatus) Is Null) AND ((SubJobTable.Date) Between
[forms]![frmDateRange]![StartDate] And
[forms]![frmDateRange]![EndDate]))
ORDER BY SubJobTable.Date;
Click to expand...

The reason you're not seeing all employees is that you are
specifically restricting the SubJobTable date to a date range. If
there is no record for an employee that date field will be NULL, and
will fail this test.

Change the word HAVING to WHERE (so the records are selected BEFORE
the totalling gets done, and add a criterion

OR SubJobTable.[Date] IS NULL
 
Thank you so much! It worked perfectly!

John Vinson said:
HAVING (((tblEmployee.EmployeeStatus)="clerical" Or
(tblEmployee.EmployeeStatus) Is Null) AND ((SubJobTable.Date) Between
[forms]![frmDateRange]![StartDate] And
[forms]![frmDateRange]![EndDate]))
ORDER BY SubJobTable.Date;
Click to expand...

The reason you're not seeing all employees is that you are
specifically restricting the SubJobTable date to a date range. If
there is no record for an employee that date field will be NULL, and
will fail this test.

Change the word HAVING to WHERE (so the records are selected BEFORE
the totalling gets done, and add a criterion

OR SubJobTable.[Date] IS NULL
Click to expand...
 
Back
Top