Showing most recent record for each employee in a query

[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

 

[John Spencer]

Do you have some date field or other field that lets you determine the last
position the employee held? Assuming that you have a field named
PositionStartDate in tblEmployeePositions.

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)) AND
tblEmployeePositions.PositionStartDate =
(SELECT Max(Temp.PositionStartDate)
FROM tblEmployeePositions as Temp
WHERE Temp.EmployeeNumber = tblEmployeePositions.EmployeeNumber)
ORDER BY tblPositions.Department;

 

[CEV]

Thank You very much John. That did exactly what I wanted it to.

Thanks,

CEV

Do you have some date field or other field that lets you determine the
last position the employee held? Assuming that you have a field named
PositionStartDate in tblEmployeePositions.

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)) AND
tblEmployeePositions.PositionStartDate =
(SELECT Max(Temp.PositionStartDate)
FROM tblEmployeePositions as Temp
WHERE Temp.EmployeeNumber = tblEmployeePositions.EmployeeNumber)
ORDER BY tblPositions.Department;

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