UNION query

[Yam84]

Hello

I am trying to UNION two queries in hopes to produce the information
that I need.
The Rule: If an employee has an incident, their whole department will
not be eligible to receive leave for 2 months...until the first day of
the 3rd month. If during this time period they transfer to a new
department, they still carry the restriction of their old department.
So if an employee in dept X (who is on probation) transfers to dept Y
in January, that employee, although dept Y is not restricted, they are
still restricted because the dept they came from is on restriction
until April 1st. This is the issue I have been dealing with. Trying
to keep an employee restricted.

The first query determines all employees who were in the department
that has an incident associated with it.
SELECT distinct Q2.empID, Q2.deptID, Q2.empName
FROM Q1 INNER JOIN Q2 ON Q1.deptID = Q2.deptID
WHERE (((Q2.deptID) In (SELECT Q1.DEPTid FROM Q1)));

The next query determines all employees who were in the departments,
including people who have transferred within the two month period. I
am having trouble constructing this query.


Here is my table structure:
Employee: empID, empNo, empName
1, 12345, Thomas Johnson
2, 67890, Tom Mantia,
3, 54321, JohnTest

Dept: deptID, dept
1, Engineering
2, Yard
3, Sandblast

DeptTrans: deptTransID, deptID, empID, transDt, transEndDt
1, 1, 1, 1.1.09, Null
2, 2, 1, 12.1.08, 12.31.08
3, 3, 2, 1.2.09, Null
4, 3, 3, 1.3.09, Null
A Null value in the transEndDt indicates the current department of the
employee
(One employee may have transferred departments many times)

(One employee may be assigned vacation time many times)

VacationTime: vacaID, empID, empEarnedTime, vacaTimeAwardDt
1, 1, 8, 2.1.09
2, 2, 0, Null
3, 3, 4, 2.1.09

(One employee maybe have many incidents)

EmpIncidents: incidentID, empID, incidentTypeID, incidentDt
1, 1, 2, 12.2.08
2, 2, 2, 1.4.09

(One employee can have many types of incidents)

IncidentTypeId: incidentTypeID, incident (the only incidents that
allow an employee and his dept to lose time are types 2 and 3)
1, NonRecordable
2, Recordable
3, Lost-Time

I appreciate any input you can provide.

 

[Yam84]

Hi Dale,

I agree someone is harsh! But it's the higher-ups that are harsh...I
don't think their 'incentive' program offers much incentive.

I have had so many issues with this, I appreciate your input.

Initially I just generated the RestrictionEndDt by using DateSerial
(Year([incidentDt]),Month([incidentDt])+3,1). I planned to use this
date to compare to the current date to determine when the employee
would no longer be restricted.

Using your suggestion, would I be able to have an employee maintain
their restriction, even if they transferred to a new department?

 

[Beetle]

Why not add DeptID as a foreign key field in tblEmpIncidents, then
create a query of this table with two additional calculated fields. One
to inicate if the incident requires a restriction and another to indicate
the end date of the restriction. You could use criteria in this query to
have it return only records where the restriction end date is greater
than the current date. You would then see all Departments AND
Employees that are currently on restriction, regardless of whether
or not that Employee has since transferred to another Department.

The SQL might look like;

SELECT tblDept.Dept, [FirstName] & " " & [LastName] AS FullName,
tblEmpIncidents.IncidentID, tblIncidents.IncidentType,
tblEmpIncidents.IncidentDate, IIf([IncidentID]=1,"No","Yes") AS Restricted,
IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])) AS [End Date]
FROM tblIncidents INNER JOIN (tblEmployee INNER JOIN (tblDept INNER JOIN
tblEmpIncidents ON tblDept.DeptID = tblEmpIncidents.DeptID) ON
tblEmployee.EmpID = tblEmpIncidents.EmpID) ON tblincidents.IndidentID =
tblEmpIncidents.IncidentID
WHERE (IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])))>Date();

BTW - you should split your EmpName field into FirstName and LastName
as they are separate attributes. You can always concatenate them together
in a query for display purposes.

 

[Yam84]

Hi Beetle,

Thank you for your reply.
I was thinking about putting the deptID in the tblEmpIncidents that
would definitely allow me access to that data with out having to form
queries to get the data.

One to inicate if the incident requires a restriction and
another to indicate the end date of the restriction.

I have an IF statement that says if the incident is of type 1 (meaning
the incident is not one that causes a dept to lose time), Eligible,
else Ineligible. I also generate an eligibility date 2 months and the
first day of the 3rd month later.

Thank you for the empName tip. I will report back my findings.

Why not add DeptID as a foreign key field in tblEmpIncidents, then
create a query of this table with two additional calculated fields. One
to inicate if the incident requires a restriction and another to indicate
the end date of the restriction. You could use criteria in this query to
have it return only records where the restriction end date is greater
than the current date. You would then see all Departments AND
Employees that are currently on restriction, regardless of whether
or not that Employee has since transferred to another Department.

The SQL might look like;

SELECT tblDept.Dept, [FirstName] & " " & [LastName] AS FullName,
tblEmpIncidents.IncidentID, tblIncidents.IncidentType,
tblEmpIncidents.IncidentDate, IIf([IncidentID]=1,"No","Yes") AS Restricted,
IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])) AS [End Date]
FROM tblIncidents INNER JOIN (tblEmployee INNER JOIN (tblDept INNER JOIN
tblEmpIncidents ON tblDept.DeptID = tblEmpIncidents.DeptID) ON
tblEmployee.EmpID = tblEmpIncidents.EmpID) ON tblincidents.IndidentID =
tblEmpIncidents.IncidentID
WHERE (IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])))>Date();

BTW - you should split your EmpName field into FirstName and LastName
as they are separate attributes. You can always concatenate them together
in a query for display purposes.

--
_________

Sean Bailey

Hello

I am trying to UNION two queries in hopes to produce the information
that I need.
The Rule: If an employee has an incident, their whole department will
not be eligible to receive leave for 2 months...until the first day of
the 3rd month.  If during this time period they transfer to a new
department, they still carry the restriction of their old department.
So if an employee in dept X (who is on probation) transfers to dept Y
in January, that employee, although dept Y is not restricted, they are
still restricted because the dept they came from is on restriction
until April 1st.  This is the issue I have been dealing with. Trying
to keep an employee restricted.

The first query determines all employees who were in the department
that has an incident associated with it.
SELECT distinct Q2.empID, Q2.deptID, Q2.empName
FROM Q1 INNER JOIN Q2 ON Q1.deptID = Q2.deptID
WHERE (((Q2.deptID) In (SELECT Q1.DEPTid FROM Q1)));

The next query determines all employees who were in the departments,
including people who have transferred within the two month period.  I
am having trouble constructing this query.

Here is my table structure:
Employee: empID, empNo, empName
1, 12345, Thomas Johnson
2, 67890, Tom Mantia,
3, 54321, JohnTest

Dept: deptID, dept
1, Engineering
2, Yard
3, Sandblast

DeptTrans: deptTransID, deptID, empID, transDt, transEndDt
1, 1, 1, 1.1.09, Null
2, 2, 1, 12.1.08, 12.31.08
3, 3, 2, 1.2.09, Null
4, 3, 3, 1.3.09, Null
A Null value in the transEndDt indicates the current department of the
employee
(One employee may have transferred departments many times)

(One employee may be assigned vacation time many times)

VacationTime: vacaID, empID, empEarnedTime, vacaTimeAwardDt
1, 1, 8, 2.1.09
2, 2, 0, Null
3, 3, 4, 2.1.09

(One employee maybe have many incidents)

EmpIncidents: incidentID, empID, incidentTypeID, incidentDt
1, 1, 2, 12.2.08
2, 2, 2, 1.4.09

(One employee can have many types of incidents)

IncidentTypeId:  incidentTypeID, incident (the only incidents that
allow an employee and his dept to lose time are types 2 and 3)
1, NonRecordable
2, Recordable
3, Lost-Time

I appreciate any input you can provide.

 

[KARL DEWEY]

Try this --
SELECT EmpIncident.empID, EmpIncident.incidentTypeID,
EmpIncident.incidentDt, DeptTrans.deptID, DeptTrans_1.empID, Employee.empNo,
Employee.empName
FROM ((EmpIncident INNER JOIN DeptTrans ON EmpIncident.empID =
DeptTrans.empID) INNER JOIN DeptTrans AS DeptTrans_1 ON DeptTrans.deptID =
DeptTrans_1.deptID) INNER JOIN Employee ON DeptTrans_1.empID = Employee.empID
WHERE (((EmpIncident.incidentTypeID)="2" Or
(EmpIncident.incidentTypeID)="3") AND
((EmpIncident.incidentDt)>=DateAdd("m",-2,Date()) And
(EmpIncident.incidentDt)>=[DeptTrans].[transDt] And
(EmpIncident.incidentDt)<=IIf([DeptTrans].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans].[transEndDt]) And
(EmpIncident.incidentDt)>=[DeptTrans_1].[transDt] And
(EmpIncident.incidentDt)<=IIf([DeptTrans_1].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans_1].[transEndDt])));

Hi Beetle,

Thank you for your reply.
I was thinking about putting the deptID in the tblEmpIncidents that
would definitely allow me access to that data with out having to form
queries to get the data.

One to inicate if the incident requires a restriction and
another to indicate the end date of the restriction.

I have an IF statement that says if the incident is of type 1 (meaning
the incident is not one that causes a dept to lose time), Eligible,
else Ineligible. I also generate an eligibility date 2 months and the
first day of the 3rd month later.

Thank you for the empName tip. I will report back my findings.

Why not add DeptID as a foreign key field in tblEmpIncidents, then
create a query of this table with two additional calculated fields. One
to inicate if the incident requires a restriction and another to indicate
the end date of the restriction. You could use criteria in this query to
have it return only records where the restriction end date is greater
than the current date. You would then see all Departments AND
Employees that are currently on restriction, regardless of whether
or not that Employee has since transferred to another Department.

The SQL might look like;

SELECT tblDept.Dept, [FirstName] & " " & [LastName] AS FullName,
tblEmpIncidents.IncidentID, tblIncidents.IncidentType,
tblEmpIncidents.IncidentDate, IIf([IncidentID]=1,"No","Yes") AS Restricted,
IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])) AS [End Date]
FROM tblIncidents INNER JOIN (tblEmployee INNER JOIN (tblDept INNER JOIN
tblEmpIncidents ON tblDept.DeptID = tblEmpIncidents.DeptID) ON
tblEmployee.EmpID = tblEmpIncidents.EmpID) ON tblincidents.IndidentID =
tblEmpIncidents.IncidentID
WHERE (IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])))>Date();

BTW - you should split your EmpName field into FirstName and LastName
as they are separate attributes. You can always concatenate them together
in a query for display purposes.

--
_________

Sean Bailey

Hello

I am trying to UNION two queries in hopes to produce the information
that I need.
The Rule: If an employee has an incident, their whole department will
not be eligible to receive leave for 2 months...until the first day of
the 3rd month. If during this time period they transfer to a new
department, they still carry the restriction of their old department.
So if an employee in dept X (who is on probation) transfers to dept Y
in January, that employee, although dept Y is not restricted, they are
still restricted because the dept they came from is on restriction
until April 1st. This is the issue I have been dealing with. Trying
to keep an employee restricted.

The first query determines all employees who were in the department
that has an incident associated with it.
SELECT distinct Q2.empID, Q2.deptID, Q2.empName
FROM Q1 INNER JOIN Q2 ON Q1.deptID = Q2.deptID
WHERE (((Q2.deptID) In (SELECT Q1.DEPTid FROM Q1)));

The next query determines all employees who were in the departments,
including people who have transferred within the two month period. I
am having trouble constructing this query.

Here is my table structure:
Employee: empID, empNo, empName
1, 12345, Thomas Johnson
2, 67890, Tom Mantia,
3, 54321, JohnTest

Dept: deptID, dept
1, Engineering
2, Yard
3, Sandblast

DeptTrans: deptTransID, deptID, empID, transDt, transEndDt
1, 1, 1, 1.1.09, Null
2, 2, 1, 12.1.08, 12.31.08
3, 3, 2, 1.2.09, Null
4, 3, 3, 1.3.09, Null
A Null value in the transEndDt indicates the current department of the
employee
(One employee may have transferred departments many times)

(One employee may be assigned vacation time many times)

VacationTime: vacaID, empID, empEarnedTime, vacaTimeAwardDt
1, 1, 8, 2.1.09
2, 2, 0, Null
3, 3, 4, 2.1.09

(One employee maybe have many incidents)

EmpIncidents: incidentID, empID, incidentTypeID, incidentDt
1, 1, 2, 12.2.08
2, 2, 2, 1.4.09

(One employee can have many types of incidents)

IncidentTypeId: incidentTypeID, incident (the only incidents that
allow an employee and his dept to lose time are types 2 and 3)
1, NonRecordable
2, Recordable
3, Lost-Time

I appreciate any input you can provide.

 

[Yam84]

Hi Karl, I am getting an error on EmpIncidents.empID, in the first
field of the select clause. Syntax error in JOIN operation

SELECT EmpIncidents.empID, EmpIncidents.incidentTypeID,
EmpIncidents.incidentDt, DeptTrans.deptID, DeptTrans_1.empID,
Employee.empNo, Employee.empName
FROM ((EmpIncidents INNER JOIN DeptTrans ON EmpIncident.empID =
DeptTrans.empID) INNER JOIN DeptTrans AS DeptTrans_1 ON
DeptTrans.deptID =
DeptTrans_1.deptID) INNER JOIN Employee ON DeptTrans_1.empID =
Employee.empID
WHERE (((EmpIncidents.incidentTypeID)=2
Or (EmpIncidents.incidentTypeID)=3)
AND ((EmpIncidents.incidentDt)>=DateAdd("m",-2,Date())
And (EmpIncidents.incidentDt)>=[DeptTrans].[transDt]
And (EmpIncidents.incidentDt)<=IIf([DeptTrans].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans].[transEndDt])
And (EmpIncidents.incidentDt)>=[DeptTrans_1].[transDt]
And (EmpIncidents.incidentDt)<=IIf([DeptTrans_1].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans_1].[transEndDt])));

Try this --
SELECT EmpIncident.empID, EmpIncident.incidentTypeID,
EmpIncident.incidentDt, DeptTrans.deptID, DeptTrans_1.empID, Employee.empNo,
Employee.empName
FROM ((EmpIncident INNER JOIN DeptTrans ON EmpIncident.empID =
DeptTrans.empID) INNER JOIN DeptTrans AS DeptTrans_1 ON DeptTrans.deptID =
DeptTrans_1.deptID) INNER JOIN Employee ON DeptTrans_1.empID = Employee..empID
WHERE (((EmpIncident.incidentTypeID)="2" Or
(EmpIncident.incidentTypeID)="3") AND
((EmpIncident.incidentDt)>=DateAdd("m",-2,Date()) And
(EmpIncident.incidentDt)>=[DeptTrans].[transDt] And
(EmpIncident.incidentDt)<=IIf([DeptTrans].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans].[transEndDt]) And
(EmpIncident.incidentDt)>=[DeptTrans_1].[transDt] And
(EmpIncident.incidentDt)<=IIf([DeptTrans_1].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans_1].[transEndDt])));

Hi Beetle,

Thank you for your reply.
I was thinking about putting the deptID in the tblEmpIncidents that
would definitely allow me access to that data with out having to form
queries to get the data.
I have an IF statement that says if the incident is of type 1 (meaning
the incident is not one that causes a dept to lose time), Eligible,
else Ineligible.  I also generate an eligibility date 2 months and the
first day of the 3rd month later.

Thank you for the empName tip.  I will report back my findings.

Why not add DeptID as a foreign key field in tblEmpIncidents, then
create a query of this table with two additional calculated fields. One
to inicate if the incident requires a restriction and another to indicate
the end date of the restriction. You could use criteria in this queryto
have it return only records where the restriction end date is greater
than the current date. You would then see all Departments AND
Employees that are currently on restriction, regardless of whether
or not that Employee has since transferred to another Department.
The SQL might look like;
SELECT tblDept.Dept, [FirstName] & " " & [LastName] AS FullName,
tblEmpIncidents.IncidentID, tblIncidents.IncidentType,
tblEmpIncidents.IncidentDate, IIf([IncidentID]=1,"No","Yes") AS Restricted,
IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])) AS [End Date]
FROM tblIncidents INNER JOIN (tblEmployee INNER JOIN (tblDept INNER JOIN
tblEmpIncidents ON tblDept.DeptID = tblEmpIncidents.DeptID) ON
tblEmployee.EmpID = tblEmpIncidents.EmpID) ON tblincidents.IndidentID =
tblEmpIncidents.IncidentID
WHERE (IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])))>Date();
BTW - you should split your EmpName field into FirstName and LastName
as they are separate attributes. You can always concatenate them together
in a query for display purposes.
--
_________
Sean Bailey
:
Hello
I am trying to UNION two queries in hopes to produce the information
that I need.
The Rule: If an employee has an incident, their whole department will
not be eligible to receive leave for 2 months...until the first dayof
the 3rd month.  If during this time period they transfer to a new
department, they still carry the restriction of their old department.
So if an employee in dept X (who is on probation) transfers to deptY
in January, that employee, although dept Y is not restricted, they are
still restricted because the dept they came from is on restriction
until April 1st.  This is the issue I have been dealing with. Trying
to keep an employee restricted.
The first query determines all employees who were in the department
that has an incident associated with it.
SELECT distinct Q2.empID, Q2.deptID, Q2.empName
FROM Q1 INNER JOIN Q2 ON Q1.deptID = Q2.deptID
WHERE (((Q2.deptID) In (SELECT Q1.DEPTid FROM Q1)));
The next query determines all employees who were in the departments,
including people who have transferred within the two month period.  I
am having trouble constructing this query.
Here is my table structure:
Employee: empID, empNo, empName
1, 12345, Thomas Johnson
2, 67890, Tom Mantia,
3, 54321, JohnTest
Dept: deptID, dept
1, Engineering
2, Yard
3, Sandblast
DeptTrans: deptTransID, deptID, empID, transDt, transEndDt
1, 1, 1, 1.1.09, Null
2, 2, 1, 12.1.08, 12.31.08
3, 3, 2, 1.2.09, Null
4, 3, 3, 1.3.09, Null
A Null value in the transEndDt indicates the current department of the
employee
(One employee may have transferred departments many times)
(One employee may be assigned vacation time many times)
VacationTime: vacaID, empID, empEarnedTime, vacaTimeAwardDt
1, 1, 8, 2.1.09
2, 2, 0, Null
3, 3, 4, 2.1.09
(One employee maybe have many incidents)
EmpIncidents: incidentID, empID, incidentTypeID, incidentDt
1, 1, 2, 12.2.08
2, 2, 2, 1.4.09
(One employee can have many types of incidents)
IncidentTypeId:  incidentTypeID, incident (the only incidents that
allow an employee and his dept to lose time are types 2 and 3)
1, NonRecordable
2, Recordable
3, Lost-Time
I appreciate any input you can provide.

 

[Yam84]

Hi Karl,

I am not sure what that query was supposed to return exactly.

SELECT EmpIncidents.empID, EmpIncidents.incidentTypeID,
EmpIncidents.incidentDt, DeptTrans.deptID, Depttrans_1.empID,
Employee.empNo, Employee.empName, *
FROM Employee INNER JOIN ((DeptTrans INNER JOIN DeptTrans AS
Depttrans_1 ON DeptTrans.deptID = Depttrans_1.deptID) INNER JOIN
EmpIncidents ON Depttrans_1.empID = EmpIncidents.empID) ON
(Depttrans_1.empID = Employee.empID) AND (Employee.empID =
EmpIncidents.empID)
WHERE (((EmpIncidents.incidentTypeID) In (2,3)
AND ((EmpIncidents.incidentDt)>=DateAdd("m",-2,Date())
And (EmpIncidents.incidentDt)>=[DeptTrans].[transDt]
And (EmpIncidents.incidentDt)<=IIf([DeptTrans].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans].[transEndDt])
And (EmpIncidents.incidentDt)>=[DeptTrans_1].[transDt]
And (EmpIncidents.incidentDt)<=IIf([DeptTrans_1].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans_1].[transEndDt]))))

Try this --
SELECT EmpIncident.empID, EmpIncident.incidentTypeID,
EmpIncident.incidentDt, DeptTrans.deptID, DeptTrans_1.empID, Employee.empNo,
Employee.empName
FROM ((EmpIncident INNER JOIN DeptTrans ON EmpIncident.empID =
DeptTrans.empID) INNER JOIN DeptTrans AS DeptTrans_1 ON DeptTrans.deptID =
DeptTrans_1.deptID) INNER JOIN Employee ON DeptTrans_1.empID = Employee..empID
WHERE (((EmpIncident.incidentTypeID)="2" Or
(EmpIncident.incidentTypeID)="3") AND
((EmpIncident.incidentDt)>=DateAdd("m",-2,Date()) And
(EmpIncident.incidentDt)>=[DeptTrans].[transDt] And
(EmpIncident.incidentDt)<=IIf([DeptTrans].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans].[transEndDt]) And
(EmpIncident.incidentDt)>=[DeptTrans_1].[transDt] And
(EmpIncident.incidentDt)<=IIf([DeptTrans_1].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans_1].[transEndDt])));

Hi Beetle,

Thank you for your reply.
I was thinking about putting the deptID in the tblEmpIncidents that
would definitely allow me access to that data with out having to form
queries to get the data.
I have an IF statement that says if the incident is of type 1 (meaning
the incident is not one that causes a dept to lose time), Eligible,
else Ineligible.  I also generate an eligibility date 2 months and the
first day of the 3rd month later.

Thank you for the empName tip.  I will report back my findings.

Why not add DeptID as a foreign key field in tblEmpIncidents, then
create a query of this table with two additional calculated fields. One
to inicate if the incident requires a restriction and another to indicate
the end date of the restriction. You could use criteria in this queryto
have it return only records where the restriction end date is greater
than the current date. You would then see all Departments AND
Employees that are currently on restriction, regardless of whether
or not that Employee has since transferred to another Department.
The SQL might look like;
SELECT tblDept.Dept, [FirstName] & " " & [LastName] AS FullName,
tblEmpIncidents.IncidentID, tblIncidents.IncidentType,
tblEmpIncidents.IncidentDate, IIf([IncidentID]=1,"No","Yes") AS Restricted,
IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])) AS [End Date]
FROM tblIncidents INNER JOIN (tblEmployee INNER JOIN (tblDept INNER JOIN
tblEmpIncidents ON tblDept.DeptID = tblEmpIncidents.DeptID) ON
tblEmployee.EmpID = tblEmpIncidents.EmpID) ON tblincidents.IndidentID =
tblEmpIncidents.IncidentID
WHERE (IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])))>Date();
BTW - you should split your EmpName field into FirstName and LastName
as they are separate attributes. You can always concatenate them together
in a query for display purposes.
--
_________
Sean Bailey
:
Hello
I am trying to UNION two queries in hopes to produce the information
that I need.
The Rule: If an employee has an incident, their whole department will
not be eligible to receive leave for 2 months...until the first dayof
the 3rd month.  If during this time period they transfer to a new
department, they still carry the restriction of their old department.
So if an employee in dept X (who is on probation) transfers to deptY
in January, that employee, although dept Y is not restricted, they are
still restricted because the dept they came from is on restriction
until April 1st.  This is the issue I have been dealing with. Trying
to keep an employee restricted.
The first query determines all employees who were in the department
that has an incident associated with it.
SELECT distinct Q2.empID, Q2.deptID, Q2.empName
FROM Q1 INNER JOIN Q2 ON Q1.deptID = Q2.deptID
WHERE (((Q2.deptID) In (SELECT Q1.DEPTid FROM Q1)));
The next query determines all employees who were in the departments,
including people who have transferred within the two month period.  I
am having trouble constructing this query.
Here is my table structure:
Employee: empID, empNo, empName
1, 12345, Thomas Johnson
2, 67890, Tom Mantia,
3, 54321, JohnTest
Dept: deptID, dept
1, Engineering
2, Yard
3, Sandblast
DeptTrans: deptTransID, deptID, empID, transDt, transEndDt
1, 1, 1, 1.1.09, Null
2, 2, 1, 12.1.08, 12.31.08
3, 3, 2, 1.2.09, Null
4, 3, 3, 1.3.09, Null
A Null value in the transEndDt indicates the current department of the
employee
(One employee may have transferred departments many times)
(One employee may be assigned vacation time many times)
VacationTime: vacaID, empID, empEarnedTime, vacaTimeAwardDt
1, 1, 8, 2.1.09
2, 2, 0, Null
3, 3, 4, 2.1.09
(One employee maybe have many incidents)
EmpIncidents: incidentID, empID, incidentTypeID, incidentDt
1, 1, 2, 12.2.08
2, 2, 2, 1.4.09
(One employee can have many types of incidents)
IncidentTypeId:  incidentTypeID, incident (the only incidents that
allow an employee and his dept to lose time are types 2 and 3)
1, NonRecordable
2, Recordable
3, Lost-Time
I appreciate any input you can provide.

 

[KARL DEWEY]

Check and see if there is a hard return after ---
DeptTrans_1.deptID) INNER JOIN Employee ON DeptTrans_1.empID =

and before ---
Employee.empID
WHERE

It returns those not eligible for leave.
I moved the employees around more testing and added another incident as all
of yours were more than two months ago.

EmpIncident.empID incidentTypeID incidentDt deptID DeptTrans_1.empID empNo empName
1 2 2/15/2009 2 1 12345 Thomas Johnson
1 2 2/15/2009 2 3 54321 JohnTest

Hi Karl, I am getting an error on EmpIncidents.empID, in the first
field of the select clause. Syntax error in JOIN operation

SELECT EmpIncidents.empID, EmpIncidents.incidentTypeID,
EmpIncidents.incidentDt, DeptTrans.deptID, DeptTrans_1.empID,
Employee.empNo, Employee.empName
FROM ((EmpIncidents INNER JOIN DeptTrans ON EmpIncident.empID =
DeptTrans.empID) INNER JOIN DeptTrans AS DeptTrans_1 ON
DeptTrans.deptID =
DeptTrans_1.deptID) INNER JOIN Employee ON DeptTrans_1.empID =
Employee.empID
WHERE (((EmpIncidents.incidentTypeID)=2
Or (EmpIncidents.incidentTypeID)=3)
AND ((EmpIncidents.incidentDt)>=DateAdd("m",-2,Date())
And (EmpIncidents.incidentDt)>=[DeptTrans].[transDt]
And (EmpIncidents.incidentDt)<=IIf([DeptTrans].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans].[transEndDt])
And (EmpIncidents.incidentDt)>=[DeptTrans_1].[transDt]
And (EmpIncidents.incidentDt)<=IIf([DeptTrans_1].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans_1].[transEndDt])));

Try this --
SELECT EmpIncident.empID, EmpIncident.incidentTypeID,
EmpIncident.incidentDt, DeptTrans.deptID, DeptTrans_1.empID, Employee.empNo,
Employee.empName
FROM ((EmpIncident INNER JOIN DeptTrans ON EmpIncident.empID =
DeptTrans.empID) INNER JOIN DeptTrans AS DeptTrans_1 ON DeptTrans.deptID =
DeptTrans_1.deptID) INNER JOIN Employee ON DeptTrans_1.empID = Employee..empID
WHERE (((EmpIncident.incidentTypeID)="2" Or
(EmpIncident.incidentTypeID)="3") AND
((EmpIncident.incidentDt)>=DateAdd("m",-2,Date()) And
(EmpIncident.incidentDt)>=[DeptTrans].[transDt] And
(EmpIncident.incidentDt)<=IIf([DeptTrans].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans].[transEndDt]) And
(EmpIncident.incidentDt)>=[DeptTrans_1].[transDt] And
(EmpIncident.incidentDt)<=IIf([DeptTrans_1].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans_1].[transEndDt])));

Hi Beetle,

Thank you for your reply.
I was thinking about putting the deptID in the tblEmpIncidents that
would definitely allow me access to that data with out having to form
queries to get the data.
I have an IF statement that says if the incident is of type 1 (meaning
the incident is not one that causes a dept to lose time), Eligible,
else Ineligible. I also generate an eligibility date 2 months and the
first day of the 3rd month later.

Thank you for the empName tip. I will report back my findings.

Why not add DeptID as a foreign key field in tblEmpIncidents, then
create a query of this table with two additional calculated fields. One
to inicate if the incident requires a restriction and another to indicate
the end date of the restriction. You could use criteria in this query to
have it return only records where the restriction end date is greater
than the current date. You would then see all Departments AND
Employees that are currently on restriction, regardless of whether
or not that Employee has since transferred to another Department.

The SQL might look like;

SELECT tblDept.Dept, [FirstName] & " " & [LastName] AS FullName,
tblEmpIncidents.IncidentID, tblIncidents.IncidentType,
tblEmpIncidents.IncidentDate, IIf([IncidentID]=1,"No","Yes") AS Restricted,
IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])) AS [End Date]
FROM tblIncidents INNER JOIN (tblEmployee INNER JOIN (tblDept INNER JOIN
tblEmpIncidents ON tblDept.DeptID = tblEmpIncidents.DeptID) ON
tblEmployee.EmpID = tblEmpIncidents.EmpID) ON tblincidents.IndidentID =
tblEmpIncidents.IncidentID
WHERE (IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])))>Date();

BTW - you should split your EmpName field into FirstName and LastName
as they are separate attributes. You can always concatenate them together
in a query for display purposes.
Sean Bailey

:
Hello

I am trying to UNION two queries in hopes to produce the information
that I need.
The Rule: If an employee has an incident, their whole department will
not be eligible to receive leave for 2 months...until the first day of
the 3rd month. If during this time period they transfer to a new
department, they still carry the restriction of their old department.
So if an employee in dept X (who is on probation) transfers to dept Y
in January, that employee, although dept Y is not restricted, they are
still restricted because the dept they came from is on restriction
until April 1st. This is the issue I have been dealing with. Trying
to keep an employee restricted.

The first query determines all employees who were in the department
that has an incident associated with it.
SELECT distinct Q2.empID, Q2.deptID, Q2.empName
FROM Q1 INNER JOIN Q2 ON Q1.deptID = Q2.deptID
WHERE (((Q2.deptID) In (SELECT Q1.DEPTid FROM Q1)));

The next query determines all employees who were in the departments,
including people who have transferred within the two month period. I
am having trouble constructing this query.

Here is my table structure:
Employee: empID, empNo, empName
1, 12345, Thomas Johnson
2, 67890, Tom Mantia,
3, 54321, JohnTest

Dept: deptID, dept
1, Engineering
2, Yard
3, Sandblast

DeptTrans: deptTransID, deptID, empID, transDt, transEndDt
1, 1, 1, 1.1.09, Null
2, 2, 1, 12.1.08, 12.31.08
3, 3, 2, 1.2.09, Null
4, 3, 3, 1.3.09, Null
A Null value in the transEndDt indicates the current department of the
employee
(One employee may have transferred departments many times)

(One employee may be assigned vacation time many times)

VacationTime: vacaID, empID, empEarnedTime, vacaTimeAwardDt
1, 1, 8, 2.1.09
2, 2, 0, Null
3, 3, 4, 2.1.09

(One employee maybe have many incidents)

EmpIncidents: incidentID, empID, incidentTypeID, incidentDt
1, 1, 2, 12.2.08
2, 2, 2, 1.4.09

(One employee can have many types of incidents)

IncidentTypeId: incidentTypeID, incident (the only incidents that
allow an employee and his dept to lose time are types 2 and 3)
1, NonRecordable
2, Recordable
3, Lost-Time

I appreciate any input you can provide.

 

[Yam84]

Karl,

I'm not sure what you mean by a hard return...I checked both places
you suggested, but I can't get past that JOIN error on SELECT
EmpIncident. Here is teh exact code I have:

SELECT EmpIncidents.empID, EmpIncidents.incidentTypeID,
EmpIncidents.incidentDt, DeptTrans.deptID, DeptTrans_1.empID,
Employee.empNo, Employee.empName
FROM ((EmpIncidents INNER JOIN DeptTrans ON EmpIncident.empID =
DeptTrans.empID) INNER JOIN DeptTrans AS DeptTrans_1 ON
DeptTrans.deptID = DeptTrans_1.deptID) INNER JOIN Employee ON
DeptTrans_1.empID = Employee.empID WHERE
(((EmpIncidents.incidentTypeID)=2
Or (EmpIncidents.incidentTypeID)=3)
AND ((EmpIncidents.incidentDt)>=DateAdd("m",-2,Date())
And (EmpIncidents.incidentDt)>=[DeptTrans].[transDt]
And (EmpIncidents.incidentDt)<=IIf([DeptTrans].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans].[transEndDt])
And (EmpIncidents.incidentDt)>=[DeptTrans_1].[transDt]
And (EmpIncidents.incidentDt)<=IIf([DeptTrans_1].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans_1].[transEndDt])));

Check and see if there is a hard return after    ---
DeptTrans_1.deptID) INNER JOIN Employee ON DeptTrans_1.empID =

and before ---
Employee.empID
WHERE

It returns those not eligible for leave.
I moved the employees around more testing and added another incident as all
of yours were more than two months ago.

EmpIncident.empID       incidentTypeID  incidentDt      deptID  DeptTrans_1.empID       empNo   empName
1       2       2/15/2009       2       1      12345   Thomas Johnson
1       2       2/15/2009       2       3      54321   JohnTest

Hi Karl, I am getting an error on EmpIncidents.empID, in the first
field of the select clause. Syntax error in JOIN operation

SELECT EmpIncidents.empID, EmpIncidents.incidentTypeID,
EmpIncidents.incidentDt, DeptTrans.deptID, DeptTrans_1.empID,
Employee.empNo, Employee.empName
FROM ((EmpIncidents INNER JOIN DeptTrans ON EmpIncident.empID =
DeptTrans.empID) INNER JOIN DeptTrans AS DeptTrans_1 ON
DeptTrans.deptID =
DeptTrans_1.deptID) INNER JOIN Employee ON DeptTrans_1.empID =
Employee.empID
WHERE (((EmpIncidents.incidentTypeID)=2
Or (EmpIncidents.incidentTypeID)=3)
AND ((EmpIncidents.incidentDt)>=DateAdd("m",-2,Date())
And (EmpIncidents.incidentDt)>=[DeptTrans].[transDt]
And (EmpIncidents.incidentDt)<=IIf([DeptTrans].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans].[transEndDt])
And (EmpIncidents.incidentDt)>=[DeptTrans_1].[transDt]
And (EmpIncidents.incidentDt)<=IIf([DeptTrans_1].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans_1].[transEndDt])));

Try this --
SELECT EmpIncident.empID, EmpIncident.incidentTypeID,
EmpIncident.incidentDt, DeptTrans.deptID, DeptTrans_1.empID, Employee..empNo,
Employee.empName
FROM ((EmpIncident INNER JOIN DeptTrans ON EmpIncident.empID =
DeptTrans.empID) INNER JOIN DeptTrans AS DeptTrans_1 ON DeptTrans.deptID =
DeptTrans_1.deptID) INNER JOIN Employee ON DeptTrans_1.empID = Employee..empID
WHERE (((EmpIncident.incidentTypeID)="2" Or
(EmpIncident.incidentTypeID)="3") AND
((EmpIncident.incidentDt)>=DateAdd("m",-2,Date()) And
(EmpIncident.incidentDt)>=[DeptTrans].[transDt] And
(EmpIncident.incidentDt)<=IIf([DeptTrans].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans].[transEndDt]) And
(EmpIncident.incidentDt)>=[DeptTrans_1].[transDt] And
(EmpIncident.incidentDt)<=IIf([DeptTrans_1].[transEndDt] Is
Null,#12/31/9999#,[DeptTrans_1].[transEndDt])));
:
Hi Beetle,
Thank you for your reply.
I was thinking about putting the deptID in the tblEmpIncidents that
would definitely allow me access to that data with out having to form
queries to get the data.

One to inicate if the incident requires a restriction and
another to indicate the end date of the restriction.

I have an IF statement that says if the incident is of type 1 (meaning
the incident is not one that causes a dept to lose time), Eligible,
else Ineligible.  I also generate an eligibility date 2 months and the
first day of the 3rd month later.
Thank you for the empName tip.  I will report back my findings.
Why not add DeptID as a foreign key field in tblEmpIncidents, then
create a query of this table with two additional calculated fields. One
to inicate if the incident requires a restriction and another to indicate
the end date of the restriction. You could use criteria in this query to
have it return only records where the restriction end date is greater
than the current date. You would then see all Departments AND
Employees that are currently on restriction, regardless of whether
or not that Employee has since transferred to another Department.
The SQL might look like;
SELECT tblDept.Dept, [FirstName] & " " & [LastName] AS FullName,
tblEmpIncidents.IncidentID, tblIncidents.IncidentType,
tblEmpIncidents.IncidentDate, IIf([IncidentID]=1,"No","Yes") ASRestricted,
IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])) AS [End Date]
FROM tblIncidents INNER JOIN (tblEmployee INNER JOIN (tblDept INNER JOIN
tblEmpIncidents ON tblDept.DeptID = tblEmpIncidents.DeptID) ON
tblEmployee.EmpID = tblEmpIncidents.EmpID) ON tblincidents.IndidentID =
tblEmpIncidents.IncidentID
WHERE (IIf([IncidentID]=1,Null,DateAdd("m",2,[IncidentDate])))>Date();
BTW - you should split your EmpName field into FirstName and LastName
as they are separate attributes. You can always concatenate them together
in a query for display purposes.
--
_________
Sean Bailey
:
Hello
I am trying to UNION two queries in hopes to produce the information
that I need.
The Rule: If an employee has an incident, their whole department will
not be eligible to receive leave for 2 months...until the firstday of
the 3rd month.  If during this time period they transfer to anew
department, they still carry the restriction of their old department.
So if an employee in dept X (who is on probation) transfers to dept Y
in January, that employee, although dept Y is not restricted, they are
still restricted because the dept they came from is on restriction
until April 1st.  This is the issue I have been dealing with.Trying
to keep an employee restricted.
The first query determines all employees who were in the department
that has an incident associated with it.
SELECT distinct Q2.empID, Q2.deptID, Q2.empName
FROM Q1 INNER JOIN Q2 ON Q1.deptID = Q2.deptID
WHERE (((Q2.deptID) In (SELECT Q1.DEPTid FROM Q1)));
The next query determines all employees who were in the departments,
including people who have transferred within the two month period.  I
am having trouble constructing this query.
Here is my table structure:
Employee: empID, empNo, empName
1, 12345, Thomas Johnson
2, 67890, Tom Mantia,
3, 54321, JohnTest
Dept: deptID, dept
1, Engineering
2, Yard
3, Sandblast
DeptTrans: deptTransID, deptID, empID, transDt, transEndDt
1, 1, 1, 1.1.09, Null
2, 2, 1, 12.1.08, 12.31.08
3, 3, 2, 1.2.09, Null
4, 3, 3, 1.3.09, Null
A Null value in the transEndDt indicates the current departmentof the
employee
(One employee may have transferred departments many times)
(One employee may be assigned vacation time many times)
VacationTime: vacaID, empID, empEarnedTime, vacaTimeAwardDt
1, 1, 8, 2.1.09
2, 2, 0, Null
3, 3, 4, 2.1.09
(One employee maybe have many incidents)
EmpIncidents: incidentID, empID, incidentTypeID, incidentDt
1, 1, 2, 12.2.08
2, 2, 2, 1.4.09
(One employee can have many types of incidents)
IncidentTypeId:  incidentTypeID, incident (the only incidentsthat
allow an employee and his dept to lose time are types 2 and 3)
1, NonRecordable
2, Recordable
3, Lost-Time
I appreciate any input you can provide.

 

[KARL DEWEY]

I'm not sure what you mean by a hard return...I checked both places you
suggested,
I only suggested one place - after & before - or - between them.
A hard return is what you get when you press the ENTER key as opposed to a
word wrap. They both look alike but the hard return has two charaters that
are not visible - Chr(13) & Chr(10).

You can try taking it apart to find the trouble. Make a copy. In design
view delete the table displayed in the right most position. Run to see if
you get errors other than a prompt. Keep removing the right most tables
until you get no errors.
Then starting over remove any criteria assoicated with that table until you
find what is causing the problem.

 

[Dale Fye]

Absolutely, because the restriction would be tied to the EmpID, not their
department. The only reason I added the IncidentID to the table was for
tracability back to the incident that caused the restriction.

Dale

Hi Dale,

I agree someone is harsh! But it's the higher-ups that are harsh...I
don't think their 'incentive' program offers much incentive.

I have had so many issues with this, I appreciate your input.

Initially I just generated the RestrictionEndDt by using DateSerial
(Year([incidentDt]),Month([incidentDt])+3,1). I planned to use this
date to compare to the current date to determine when the employee
would no longer be restricted.

Using your suggestion, would I be able to have an employee maintain
their restriction, even if they transferred to a new department?

 

[Yam84]

I like that idea. I have added deptID to the incident table. When an
incident occurs, the user will have to input the deptID. I need to
filter out employees who are in a department that has had an incident,
regardless of whether they are the employee in that department who
received the incident. I tried joining them on deptID, but that did
not produce a correct list.

Absolutely, because the restriction would be tied to the EmpID, not their
department. The only reason I added the IncidentID to the table was for
tracability back to the incident that caused the restriction.

Dale

Hi Dale,

I agree someone is harsh! But it's the higher-ups that are harsh...I
don't think their 'incentive' program offers much incentive.

I have had so many issues with this, I appreciate your input.

Initially I just generated the RestrictionEndDt by using DateSerial
(Year([incidentDt]),Month([incidentDt])+3,1). I planned to use this
date to compare to the current date to determine when the employee
would no longer be restricted.

Using your suggestion, would I be able to have an employee maintain
their restriction, even if they transferred to a new department?

You are some kind of harsh (if someone in the department has an incident,
the
whole department is ineligible for leave for 2 months)!

Based on your description, I think I would add a table to your database
that
tracks Restrictions (RestrictionID, IncidentID, EmpID, RestrictStart,
RestrictEnd).

This way, when an incident occurs that warrants restriction, you can write
a
record to the table for each employeed that is in that department, with
the
restriction start and end dates)

Then, it is easy to identify when an individual was on restriction by
looking at that table and filtering on the EmpID. This would be much
simpler
than the convoluted query you are trying to create.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.