Query employees have null data or having Data?

[Guest]

This query gives me the who has had action between a given date in a given
[Situation] but what I really need is all the employees names having data in
the "situation" or not having data. SELECT Employees.[Last Name],
Employees.[First Name], Count(EmployeeProfile.Situation) AS CountOfSituation,
Employees.[Hire Date], Employees.[Home Phone], Employees.CareerType,
Employees.Craft, Employees.Position, [Last Name] & ", " & [first Name] AS
Expr1, Employees.NotCurrentEmply, EmployeeProfile.ActionDate,
EmployeeProfile.Situation
FROM EmployeeProfile RIGHT JOIN Employees ON EmployeeProfile.SSiNumber =
Employees.SSI
GROUP BY Employees.[Last Name], Employees.[First Name], Employees.[Hire
Date], Employees.[Home Phone], Employees.CareerType, Employees.Craft,
Employees.Position, [Last Name] & ", " & [first Name],
Employees.NotCurrentEmply, EmployeeProfile.ActionDate,
EmployeeProfile.Situation
HAVING (((Employees.Craft)="CITY CARRIER" Or (Employees.Craft)="RURAL
CARRIER") AND ((Employees.Position)<>"958") AND
((Employees.NotCurrentEmply)=No) AND ((EmployeeProfile.ActionDate) Between
[ActionDate Start] And [ActionDate End]) AND
((EmployeeProfile.Situation)=[Action Request]))
ORDER BY Employees.[Last Name];
Employees.NotCurrentEmply, EmployeeProfile.ActionDate,
EmployeeProfile.Situation
HAVING (((Employees.Craft)="CITY CARRIER" Or (Employees.Craft)="RURAL
CARRIER") AND ((Employees.Position)<>"958") AND
((Employees.NotCurrentEmply)=No) AND ((EmployeeProfile.ActionDate) Between
[ActionDate Start] And [ActionDate End]) AND
((EmployeeProfile.Situation)=[Action Request]))
ORDER BY Employees.[Last Name];

 

[John Spencer]

It appears that you will need to split the query into two parts. Applying
criteria against the EmployeeProfile negates the Right Join.

Part one would get EmployeeProfile records that meet your criteria for
Employee Profile records.

Then you would join that to the employees table to get the remaining
columns.

Save the following query as qEmpWithAction
SELECT EmployeeProfile.SSiNumber,
ActionDate, Situation
FROM EmployeeProfile
WHERE EmployeeProfile.ActionDate Between [ActionDate Start] And [ActionDate
End]
AND EmployeeProfile.Situation=[Action Request]

Then your second query would be something like

SELECT Employees.[Last Name]
, Employees.[First Name]
, Count(qEmpWithAction.Situation) AS CountOfSituation
, Employees.[Hire Date]
, Employees.[Home Phone], Employees.CareerType
, Employees.Craft, Employees.Position
, [Last Name] & ", " & [first Name] AS Expr1
, Employees.NotCurrentEmply
, qEmpWithAction.ActionDate
, qEmpWithAction.Situation

FROM qEmpWithAction RIGHT JOIN Employees
ON qEmpWithAction.SSiNumber = Employees.SSI

WHERE (Employees.Craft="CITY CARRIER"
Or Employees.Craft="RURAL CARRIER")
AND Employees.Position<>"958"
AND Employees.NotCurrentEmply=No


GROUP BY Employees.[Last Name], Employees.[First Name], Employees.[Hire
Date], Employees.[Home Phone], Employees.CareerType, Employees.Craft,
Employees.Position, [Last Name] & ", " & [first Name],
Employees.NotCurrentEmply, qEmpWithAction.ActionDate,
qEmpWithAction.Situation
ORDER BY Employees.[Last Name];

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

This query gives me the who has had action between a given date in a given
[Situation] but what I really need is all the employees names having data
in
the "situation" or not having data. SELECT Employees.[Last Name],
Employees.[First Name], Count(EmployeeProfile.Situation) AS
CountOfSituation,
Employees.[Hire Date], Employees.[Home Phone], Employees.CareerType,
Employees.Craft, Employees.Position, [Last Name] & ", " & [first Name] AS
Expr1, Employees.NotCurrentEmply, EmployeeProfile.ActionDate,
EmployeeProfile.Situation
FROM EmployeeProfile RIGHT JOIN Employees ON EmployeeProfile.SSiNumber =
Employees.SSI
GROUP BY Employees.[Last Name], Employees.[First Name], Employees.[Hire
Date], Employees.[Home Phone], Employees.CareerType, Employees.Craft,
Employees.Position, [Last Name] & ", " & [first Name],
Employees.NotCurrentEmply, EmployeeProfile.ActionDate,
EmployeeProfile.Situation
HAVING (((Employees.Craft)="CITY CARRIER" Or (Employees.Craft)="RURAL
CARRIER") AND ((Employees.Position)<>"958") AND
((Employees.NotCurrentEmply)=No) AND ((EmployeeProfile.ActionDate) Between
[ActionDate Start] And [ActionDate End]) AND
((EmployeeProfile.Situation)=[Action Request]))
ORDER BY Employees.[Last Name];
Employees.NotCurrentEmply, EmployeeProfile.ActionDate,
EmployeeProfile.Situation
HAVING (((Employees.Craft)="CITY CARRIER" Or (Employees.Craft)="RURAL
CARRIER") AND ((Employees.Position)<>"958") AND
((Employees.NotCurrentEmply)=No) AND ((EmployeeProfile.ActionDate) Between
[ActionDate Start] And [ActionDate End]) AND
((EmployeeProfile.Situation)=[Action Request]))
ORDER BY Employees.[Last Name];