Help w/query to retrieve latest pay increase

Please help a beginner.....


I have a table that holds employee name and all pay increases, bonuses and awards given to them while employeed with the company. So, there will be more than one entry in the table for each employee and pay type(pay increases, bonuses and awards).

I have 3 entries for an employees that has 3 payincrease. I have to show the current salary on the report and the last payincrease date and amount. I have the current salary calculation correct--yes!! I cannot get the latest payincrease date [B]and[B] amount of increase given.

In know I can use Max(emp_pay_dt) AS pay_date to get the latest pay increase date but I cannot seem to get the corresponding emp_pay_amt for that date. I tried using the EXISTS clause but I got an error on my query. I tried using the pay_date in a WHERE and I got an error that I couldn't do that.

thanks in advance for your help,

spearssc

what about something like this

thanks Memnoch!!! I added your idea to my query and it works perfectly....


Here the query just in case it can help someone else....


SELECT Employee_Info.Emp_ID, Employee_Info.Emp_FName, Employee_Info.Emp_MI, Employee_Info.Emp_LName, Employee_Info.emp_init_sal+(SELECT sum(emp_pay_amt) FROM Employee_Pay_Info WHERE employee_pay_info.emp_id = Employee_info.emp_id and Employee_Pay_Info.Emp_Pay_Type_ID=1) AS Expr1, (SELECT max(Employee_Pay_Info.Emp_Pay_DT) from employee_Pay_Info where Employee_Pay_Info.Emp_ID=Employee_info.emp_id) AS pay_date, (SELECT Employee_Pay_Info.Emp_Pay_Amt FROM employee_Pay_Info WHERE Employee_Pay_Info.Emp_Pay_DT = (SELECT max(Employee_Pay_Info.Emp_Pay_DT) from employee_Pay_Info where Employee_Pay_Info.Emp_ID=Employee_info.emp_id)) AS Expr2
FROM Employee_Info INNER JOIN Employee_Pay_Info ON Employee_Info.Emp_ID = Employee_Pay_Info.Emp_ID
WHERE (((Employee_Info.Emp_ID)=[Forms]![frmEmployeeSelect]![EmpName]) AND ((Employee_Pay_Info.Emp_Pay_Type_ID)=1));