C
CEV
I am working with the following Query:
SELECT tblEmployees.LastName, tblEmployees.FirstName,
tblPositions.Department, tblEmployeePositions.Status,
tblEmployeePositions.PositionHours,
tblEmployeePositions.PositionNumber, tblEmployees.DateofHire,
tblEmployees.DateLeftAgency
FROM tblPositions INNER JOIN (tblEmployees INNER JOIN
tblEmployeePositions ON tblEmployees.EmployeeNumber =
tblEmployeePositions.EmployeeNumber) ON
tblPositions.PositionNumber =
tblEmployeePositions.PositionNumber
WHERE (((tblEmployees.DateLeftAgency) Between
[Forms]![frmSelectDateRange]![txtBeginDate] And
[Forms]![frmSelectDateRange]![txtEndDate]) AND
((tblEmployees.Terminated)=Yes))
ORDER BY tblPositions.Department;
I have a form based on the table tblEmployees. Within this form I
have a subform displaying in table view the info from the table
tblEmployeePositions for the Employee selected in the main form.
The above Query shows me info for everyone that was terminated
for the dates I specify. The problem is that over a period of
time an employee may have had more then one position. This Query
is showing me info for every position that employee has ever had. I
only want it to show me the latest position held by each employee that is
called up by the query. How can I do
this?
Thank You for your help,
CEV
SELECT tblEmployees.LastName, tblEmployees.FirstName,
tblPositions.Department, tblEmployeePositions.Status,
tblEmployeePositions.PositionHours,
tblEmployeePositions.PositionNumber, tblEmployees.DateofHire,
tblEmployees.DateLeftAgency
FROM tblPositions INNER JOIN (tblEmployees INNER JOIN
tblEmployeePositions ON tblEmployees.EmployeeNumber =
tblEmployeePositions.EmployeeNumber) ON
tblPositions.PositionNumber =
tblEmployeePositions.PositionNumber
WHERE (((tblEmployees.DateLeftAgency) Between
[Forms]![frmSelectDateRange]![txtBeginDate] And
[Forms]![frmSelectDateRange]![txtEndDate]) AND
((tblEmployees.Terminated)=Yes))
ORDER BY tblPositions.Department;
I have a form based on the table tblEmployees. Within this form I
have a subform displaying in table view the info from the table
tblEmployeePositions for the Employee selected in the main form.
The above Query shows me info for everyone that was terminated
for the dates I specify. The problem is that over a period of
time an employee may have had more then one position. This Query
is showing me info for every position that employee has ever had. I
only want it to show me the latest position held by each employee that is
called up by the query. How can I do
this?
Thank You for your help,
CEV