Return last record

[Guest]

I have a query that needs to pull the last wage information on seperated
employees. I have a form that one can choose from all past employees listed
and it is supposed, emphasis on supposed to, to pull up that person
seperation reason and last pay, etc.

The query is pulling off of three tables, tblEmployees, tblResignation, and
tblWages. The EmployeeID is the primary key on all of these or part of it
since the wage table has the EmployeeID and DateofChange as the primary key.
What the query needs to pull is the last name and first name and date of hire
from the tblEmployee, the resignation date, reason, and if eligible for
rehire from tblresignation, and all the information that is associated with
the last dateofchange in the tblWage, so basically their last day rate, night
rate, title and status. Do I need to usa a SelectDistinct here?? I know the
last function does not work since that will return whatever it feels like,
working sometimes and other times not.

 

[John Spencer]

One method would be to use a subquery in the where clause

SELECT *
FROM (tblEmployee INNER JOIN tblResignation
ON tbleEmployee.EmployeeID = TblResignation.EmployeeID)
INNER JOIN tblWages On TblEmployee.EmployeeID = TblWages.EmployeeID
WHERE tblWages.DateOfChange =
(SELECT Max(W.DateOfChange)
FROM tblWages as W
WHERE W.EmployeeID = TblEmployee.EmployeeID)

 

[Guest]

Thanks. I tried this and it worked to a degree. For some reason though it
is pulling more than just the Employee.ID I'm entering for the parameter.
It's pulling up 15 different employees even though I'm entering 2050 as the
Employee.ID. When I get this working right, the Employee.ID will be pulled
from the dropdown list on the form PastEmployeeSearch.

Any clue as to why it is giving me more than the parameter I entered? Here
is the SQL, although it is pretty much exactly what you typed:

SELECT *
FROM (tblEmployees INNER JOIN tblWage ON tblEmployees.EmployeeID =
tblWage.EmployeeID) INNER JOIN tblResignationTable ON tblEmployees.EmployeeID
= tblResignationTable.EmployeeID
WHERE (((tblWage.DateofChange)=(Select Max(W.DateofChange)
FROM tblWage as W
Where W.EmployeeID=tblEmployee.EmployeeID)));

 

[John Spencer]

Maybe I'm missing something.

WHERE are you limiting this to employeeid of 2050? By the way, is
EmployeeId a number field or a text field?

You should be limiting the employee in the MAIN query.

SELECT *
FROM (tblEmployees INNER JOIN tblWage ON tblEmployees.EmployeeID =
tblWage.EmployeeID) INNER JOIN tblResignationTable ON
tblEmployees.EmployeeID
= tblResignationTable.EmployeeID
WHERE (((tblWage.DateofChange)=(Select Max(W.DateofChange)
FROM tblWage as W
Where W.EmployeeID=tblEmployee.EmployeeID)))
AND TblEmployee.EmployeeID = 2050

Assuming that EmployeeID is a number field. If you get an error 13 - Type
mismatch, try
AND TblEmployee.EmployeeID = "2050"
as the last line.

 

[Guest]

I wasn't at first, but realized what had happened. When I copied that
example you gave, I switched everything but one item to the exact field name.
I had one tblEmployees as TblEmployees.EmployeeID, so it was asking for that
parameter. Anyway I think it will work now. The EmployeeID is a text field
but I will be using this to pull that


(((tblEmployees.EmployeeID)=[Forms]![PastEmployeeSearch]![Employee])) in the
AND statement.

Thanks for the help..when I get some time..I"m going to put that in and see
if all that works.