multi-table query

[ladybug via AccessMonster.com]

I have a table that has Employee Information. I have another table that
tracks License Information for the employees. The fields in this table
include dates for: when application is turned in, when application is mailed,
and when license is received. There is a third table that tracks if an
Employee is on a Leave of Absence. If they are on a Leave, the Employee ID
along with the type of leave, begin date for leave, and then when they return
an end date will be stored in this table.

I am trying to create a report that lists employee license information and
state if they are currently out on Leave or not.

I tried to create a query that had the expression IIF(IsDate([EndDate]) =
True,"No","Yes")
The problem that I am now coming across is if the employee has never had a
Leave of Absence then they are not showing up at all.

Can someone help me with a solution?

 

[Joan Wild]

Modify the join line betwee Employee and Leave of Absence.

In the query design, double click the join line between these two tables and
choose the appropriate type of join - either right or left (reading the
description should indicate which)

 

[ladybug via AccessMonster.com]

I tried that, but the code won't work. When the code is added all entries
become a Yes no matter if they are on a leave or not.

Modify the join line betwee Employee and Leave of Absence.

In the query design, double click the join line between these two tables and
choose the appropriate type of join - either right or left (reading the
description should indicate which)

I have a table that has Employee Information. I have another table
that tracks License Information for the employees. The fields in

[quoted text clipped - 18 lines]

Message posted via AccessMonster.com

 

[Joan Wild]

Go to the SQL view of your query (View, SQL) and copy and paste the SQL
statement here.

--
Joan Wild
Microsoft Access MVP

I tried that, but the code won't work. When the code is added all
entries become a Yes no matter if they are on a leave or not.

Modify the join line betwee Employee and Leave of Absence.

In the query design, double click the join line between these two
tables and choose the appropriate type of join - either right or
left (reading the description should indicate which)

I have a table that has Employee Information. I have another table
that tracks License Information for the employees. The fields in

[quoted text clipped - 18 lines]

Message posted via AccessMonster.com

 

[ladybug via AccessMonster.com]

SELECT tblEmployeeDetail.chrEmployeeFirstName, tblEmployeeDetail.
chrEmployeeLastName, tblEmployeeDetail_1.chrEmployeeFirstName,
tblEmployeeDetail_1.chrEmployeeLastName, tblLicense.License, tblLicense.
ExpDate, IIf(IsDate([tblLeaveofAbsence]![EndDate])=True,"No","Yes") AS Expr1
FROM (((tblEmployeeDetail INNER JOIN tblLicense ON tblEmployeeDetail.
chrUserID = tblLicense.chrUserID) INNER JOIN tblLicenseRenewal ON
tblEmployeeDetail.chrUserID = tblLicenseRenewal.chrUserID) INNER JOIN (
(tblEmployeeSupervisorJctn INNER JOIN tblSupervisor_Manager ON
tblEmployeeSupervisorJctn.chrSupervisorID = tblSupervisor_Manager.
chrSupervisorID) INNER JOIN tblEmployeeDetail AS tblEmployeeDetail_1 ON
tblSupervisor_Manager.chrManagerID = tblEmployeeDetail_1.chrUserID) ON
tblEmployeeDetail.chrUserID = tblEmployeeSupervisorJctn.chrEmployeeUserID)
INNER JOIN tblLeaveofAbsence ON tblEmployeeDetail.chrUserID =
tblLeaveofAbsence.chrUserID
WHERE (((tblLicenseRenewal.TurninApp) Is Null) AND ((tblLicenseRenewal.
RenewalPeriod)=[Enter Renewal Period]) AND ((tblEmployeeSupervisorJctn.
dtmEndDate) Is Null) AND ((tblSupervisor_Manager.dtmEndDate) Is Null));

Go to the SQL view of your query (View, SQL) and copy and paste the SQL
statement here.

I tried that, but the code won't work. When the code is added all
entries become a Yes no matter if they are on a leave or not.

[quoted text clipped - 13 lines]

 

[Joan Wild]

SELECT tblEmployeeDetail.chrEmployeeFirstName,
tblEmployeeDetail.chrEmployeeLastName,
tblEmployeeDetail_1.chrEmployeeFirstName,
tblEmployeeDetail_1.chrEmployeeLastName, tblLicense.License,
tblLicense.ExpDate,
IIf(IsDate([tblLeaveofAbsence]![EndDate])=True,"No","Yes") AS Expr1
FROM (((tblEmployeeDetail INNER JOIN tblLicense ON
tblEmployeeDetail.chrUserID = tblLicense.chrUserID) INNER JOIN
tblLicenseRenewal ON tblEmployeeDetail.chrUserID =
tblLicenseRenewal.chrUserID) INNER JOIN ((tblEmployeeSupervisorJctn INNER
JOIN tblSupervisor_Manager ON tblEmployeeSupervisorJctn.chrSupervisorID =
tblSupervisor_Manager.chrSupervisorID) INNER JOIN tblEmployeeDetail AS
tblEmployeeDetail_1 ON tblSupervisor_Manager.chrManagerID =
tblEmployeeDetail_1.chrUserID) ON tblEmployeeDetail.chrUserID =
tblEmployeeSupervisorJctn.chrEmployeeUserID) LEFT JOIN tblLeaveofAbsence ON
tblEmployeeDetail.chrUserID = tblLeaveofAbsence.chrUserID
WHERE (((tblLicenseRenewal.TurninApp) Is Null) AND
((tblLicenseRenewal.RenewalPeriod)=[Enter Renewal Period]) AND
((tblEmployeeSupervisorJctn.dtmEndDate) Is Null) AND
((tblSupervisor_Manager.dtmEndDate) Is Null));


--
Joan Wild
Microsoft Access MVP

SELECT tblEmployeeDetail.chrEmployeeFirstName, tblEmployeeDetail.
chrEmployeeLastName, tblEmployeeDetail_1.chrEmployeeFirstName,
tblEmployeeDetail_1.chrEmployeeLastName, tblLicense.License,
tblLicense. ExpDate,
IIf(IsDate([tblLeaveofAbsence]![EndDate])=True,"No","Yes") AS Expr1
FROM (((tblEmployeeDetail INNER JOIN tblLicense ON tblEmployeeDetail.
chrUserID = tblLicense.chrUserID) INNER JOIN tblLicenseRenewal ON
tblEmployeeDetail.chrUserID = tblLicenseRenewal.chrUserID) INNER JOIN
( (tblEmployeeSupervisorJctn INNER JOIN tblSupervisor_Manager ON
tblEmployeeSupervisorJctn.chrSupervisorID = tblSupervisor_Manager.
chrSupervisorID) INNER JOIN tblEmployeeDetail AS tblEmployeeDetail_1
ON tblSupervisor_Manager.chrManagerID =
tblEmployeeDetail_1.chrUserID) ON tblEmployeeDetail.chrUserID =
tblEmployeeSupervisorJctn.chrEmployeeUserID) INNER JOIN
tblLeaveofAbsence ON tblEmployeeDetail.chrUserID =
tblLeaveofAbsence.chrUserID
WHERE (((tblLicenseRenewal.TurninApp) Is Null) AND
((tblLicenseRenewal. RenewalPeriod)=[Enter Renewal Period]) AND
((tblEmployeeSupervisorJctn. dtmEndDate) Is Null) AND
((tblSupervisor_Manager.dtmEndDate) Is Null));

Go to the SQL view of your query (View, SQL) and copy and paste the
SQL statement here.

I tried that, but the code won't work. When the code is added all
entries become a Yes no matter if they are on a leave or not.

[quoted text clipped - 13 lines]

 

[ladybug via AccessMonster.com]

I tried it, but it doesnt work. It only lists employees that have ever been
on a Leave of Absence. It should be listing everyone else with a no, but it
is not. I really appreciate your help with this. Any more suggestions would
be greatly appreciated!!

SELECT tblEmployeeDetail.chrEmployeeFirstName,
tblEmployeeDetail.chrEmployeeLastName,
tblEmployeeDetail_1.chrEmployeeFirstName,
tblEmployeeDetail_1.chrEmployeeLastName, tblLicense.License,
tblLicense.ExpDate,
IIf(IsDate([tblLeaveofAbsence]![EndDate])=True,"No","Yes") AS Expr1
FROM (((tblEmployeeDetail INNER JOIN tblLicense ON
tblEmployeeDetail.chrUserID = tblLicense.chrUserID) INNER JOIN
tblLicenseRenewal ON tblEmployeeDetail.chrUserID =
tblLicenseRenewal.chrUserID) INNER JOIN ((tblEmployeeSupervisorJctn INNER
JOIN tblSupervisor_Manager ON tblEmployeeSupervisorJctn.chrSupervisorID =
tblSupervisor_Manager.chrSupervisorID) INNER JOIN tblEmployeeDetail AS
tblEmployeeDetail_1 ON tblSupervisor_Manager.chrManagerID =
tblEmployeeDetail_1.chrUserID) ON tblEmployeeDetail.chrUserID =
tblEmployeeSupervisorJctn.chrEmployeeUserID) LEFT JOIN tblLeaveofAbsence ON
tblEmployeeDetail.chrUserID = tblLeaveofAbsence.chrUserID
WHERE (((tblLicenseRenewal.TurninApp) Is Null) AND
((tblLicenseRenewal.RenewalPeriod)=[Enter Renewal Period]) AND
((tblEmployeeSupervisorJctn.dtmEndDate) Is Null) AND
((tblSupervisor_Manager.dtmEndDate) Is Null));

SELECT tblEmployeeDetail.chrEmployeeFirstName, tblEmployeeDetail.
chrEmployeeLastName, tblEmployeeDetail_1.chrEmployeeFirstName,

[quoted text clipped - 29 lines]

Message posted via AccessMonster.com

 

[Joan Wild]

Try this stripped down version.

SELECT tblEmployeeDetail.chrEmployeeFirstName,
tblEmployeeDetail.chrEmployeeLastName, tblLicense.License,
tblLicense.ExpDate,
IIf(IsNull([tblLeaveofAbsence]![EndDate])=True,"No","Yes") AS Expr1
FROM (tblEmployeeDetail INNER JOIN tblLicense ON tblEmployeeDetail.chrUserID
= tblLicense.chrUserID) LEFT JOIN tblLeaveofAbsence ON
tblEmployeeDetail.chrUserID = tblLeaveofAbsence.chrUserID;

Does this work?

--
Joan Wild
Microsoft Access MVP

I tried it, but it doesnt work. It only lists employees that have
ever been on a Leave of Absence. It should be listing everyone else
with a no, but it is not. I really appreciate your help with this.
Any more suggestions would be greatly appreciated!!

SELECT tblEmployeeDetail.chrEmployeeFirstName,
tblEmployeeDetail.chrEmployeeLastName,
tblEmployeeDetail_1.chrEmployeeFirstName,
tblEmployeeDetail_1.chrEmployeeLastName, tblLicense.License,
tblLicense.ExpDate,
IIf(IsDate([tblLeaveofAbsence]![EndDate])=True,"No","Yes") AS Expr1
FROM (((tblEmployeeDetail INNER JOIN tblLicense ON
tblEmployeeDetail.chrUserID = tblLicense.chrUserID) INNER JOIN
tblLicenseRenewal ON tblEmployeeDetail.chrUserID =
tblLicenseRenewal.chrUserID) INNER JOIN ((tblEmployeeSupervisorJctn
INNER JOIN tblSupervisor_Manager ON
tblEmployeeSupervisorJctn.chrSupervisorID =
tblSupervisor_Manager.chrSupervisorID) INNER JOIN tblEmployeeDetail
AS tblEmployeeDetail_1 ON tblSupervisor_Manager.chrManagerID =
tblEmployeeDetail_1.chrUserID) ON tblEmployeeDetail.chrUserID =
tblEmployeeSupervisorJctn.chrEmployeeUserID) LEFT JOIN
tblLeaveofAbsence ON tblEmployeeDetail.chrUserID =
tblLeaveofAbsence.chrUserID
WHERE (((tblLicenseRenewal.TurninApp) Is Null) AND
((tblLicenseRenewal.RenewalPeriod)=[Enter Renewal Period]) AND
((tblEmployeeSupervisorJctn.dtmEndDate) Is Null) AND
((tblSupervisor_Manager.dtmEndDate) Is Null));

SELECT tblEmployeeDetail.chrEmployeeFirstName, tblEmployeeDetail.
chrEmployeeLastName, tblEmployeeDetail_1.chrEmployeeFirstName,

[quoted text clipped - 29 lines]

Message posted via AccessMonster.com

 

[ladybug via AccessMonster.com]

No. It still shows everyone as a No, even though I have two people that are
out on Leave.

Try this stripped down version.

SELECT tblEmployeeDetail.chrEmployeeFirstName,
tblEmployeeDetail.chrEmployeeLastName, tblLicense.License,
tblLicense.ExpDate,
IIf(IsNull([tblLeaveofAbsence]![EndDate])=True,"No","Yes") AS Expr1
FROM (tblEmployeeDetail INNER JOIN tblLicense ON tblEmployeeDetail.chrUserID
= tblLicense.chrUserID) LEFT JOIN tblLeaveofAbsence ON
tblEmployeeDetail.chrUserID = tblLeaveofAbsence.chrUserID;

Does this work?

I tried it, but it doesnt work. It only lists employees that have
ever been on a Leave of Absence. It should be listing everyone else

[quoted text clipped - 33 lines]

Message posted via AccessMonster.com

 

[Joan Wild]

Double check your data; ensure that there is nothing in the EndDate field
for these two people. It works for me.

--
Joan Wild
Microsoft Access MVP

No. It still shows everyone as a No, even though I have two people
that are out on Leave.

Try this stripped down version.

SELECT tblEmployeeDetail.chrEmployeeFirstName,
tblEmployeeDetail.chrEmployeeLastName, tblLicense.License,
tblLicense.ExpDate,
IIf(IsNull([tblLeaveofAbsence]![EndDate])=True,"No","Yes") AS Expr1
FROM (tblEmployeeDetail INNER JOIN tblLicense ON
tblEmployeeDetail.chrUserID = tblLicense.chrUserID) LEFT JOIN
tblLeaveofAbsence ON tblEmployeeDetail.chrUserID =
tblLeaveofAbsence.chrUserID;

Does this work?

I tried it, but it doesnt work. It only lists employees that have
ever been on a Leave of Absence. It should be listing everyone else

[quoted text clipped - 33 lines]

Message posted via AccessMonster.com

 

[ladybug via AccessMonster.com]

This is what I have in the Leave of Absence table.

chrUserID Reason BeginDate EndDate
03301 SHORT TERM 08/08/2006
03616 LONG TERM 07/07/2006
03753 FMLA 07/07/2006
03828 FMLA 05/04/2006

Double check your data; ensure that there is nothing in the EndDate field
for these two people. It works for me.

No. It still shows everyone as a No, even though I have two people
that are out on Leave.

[quoted text clipped - 20 lines]

 

[Joan Wild]

Do the chrUserID 03301, 03616, 03753, and 03828 exist in the
tblEmployeeDetail table?

--
Joan Wild
Microsoft Access MVP

This is what I have in the Leave of Absence table.

chrUserID Reason BeginDate EndDate
03301 SHORT TERM 08/08/2006
03616 LONG TERM 07/07/2006
03753 FMLA 07/07/2006
03828 FMLA 05/04/2006

Double check your data; ensure that there is nothing in the EndDate
field for these two people. It works for me.

No. It still shows everyone as a No, even though I have two people
that are out on Leave.

[quoted text clipped - 20 lines]

 

[ladybug via AccessMonster.com]

Yes. In the Leave of Absence table chrUserID is a como box linked to the
chrUserID's in Employee Detail table.

Do the chrUserID 03301, 03616, 03753, and 03828 exist in the
tblEmployeeDetail table?

This is what I have in the Leave of Absence table.

[quoted text clipped - 15 lines]

 

[Joan Wild]

If you like, send me a zipped copy of the mdb.

Remove the obvious from my email address.


--
Joan Wild
Microsoft Access MVP

Yes. In the Leave of Absence table chrUserID is a como box linked to
the chrUserID's in Employee Detail table.

Do the chrUserID 03301, 03616, 03753, and 03828 exist in the
tblEmployeeDetail table?

This is what I have in the Leave of Absence table.

[quoted text clipped - 15 lines]