Showing most recent record for each employee in a query

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


.

Relevant Pages

  • Re: Record listed multiple times
    ... that query is cuerrently pulling the EmpDept field from teh Employee table. ... The entries in question in my query are Kasey Brooker-sessionID 900, ... FROM (tblParticipants INNER JOIN tblParticipantTrx ON (tblParticipants.EmpID ...
    (microsoft.public.access.queries)
  • Re: Two Queries
    ... you can't use a subquery that reguires square braces embedded in the subquery. ... Since your field and table names meet the requirements you can simply paste the subquery into the query. ... tblEmployees LEFT JOIN Query13 ... ... FROM tblJobNames INNER JOIN ((tblEmployees ...
    (microsoft.public.access.queries)
  • Re: "Stacking Queries"
    ... you could use a Union query. ... appointment fields from tblEmployees. ... if every appointment an employee has is entered, ...
    (microsoft.public.access.queries)
  • Re: Show count if zero
    ... >> second query is based on the first query, ... >> qryEmployeeAuditCount.EmpAuditDate FROM tblEmployees LEFT JOIN ... >> about the employee audits, filtered by the starting and ending date ... >> parameters, and grouped by employee ID, audit name, and audit date. ...
    (microsoft.public.access.queries)
  • Re: Two Queries
    ... Query 13 altogether and replace it with code? ... tblEmployees LEFT JOIN Query13 ... ... FROM tblJobNames INNER JOIN ((tblEmployees ...
    (microsoft.public.access.queries)