Link to home
Start Free TrialLog in
Avatar of Lawrence Salvucci
Lawrence SalvucciFlag for United States of America

asked on

Current wage and previous wage in the same query

I have a table called tblWages where we store all the wages for our employees. The table is structured as follows:

WageID - PK
EmpID - FK
Wage - Wage Amount
WageDate - Date of wage increase

What I need to do is create a query by employee that will list in 1 column their current wage, which would be the most recent wage increase by the date in the tblWages for that employee. Then in the next column I need to show the last wage increase prior to their current wage.

For example:

Emp ID    Wage Date       Wage     Wage Date    Wage      %increase (calculated field)
123         1/1/10               $18.00    1/1/11           $19.00    5.5%

Now employees might get increases more than once per year so there could be 2 wage increases for the same year. So I would need to show the most recent one and the one prior to that.

How can I create a query for this?
Avatar of als315
als315
Flag of Russian Federation image

It is possible to do it in 3 queries. You can, of course combine them into one, result will be same, but it will be difficult to check and change it

DBWage.mdb
try this query

SELECT W.EmpID, (select Max([WageDate]) From tblWages T where T.WageDate< dmax("WageDate","tblWages","EmpID=" & T.[EmpID]) And T.EmpID=W.EmpID) AS PrevWageDate, (select Max([Wage]) From tblWages T where T.WageDate< dmax("WageDate","tblWages","EmpID=" & T.[EmpID]) And T.EmpID=W.EmpID) AS PrevWage, Last(W.WageDate) AS LastWageDate, Last(W.Wage) AS CurrentWage, ([CurrentWage]-[PrevWage])/[CurrentWage] AS PercentIncrease
FROM tblWages AS W
GROUP BY W.EmpID;
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lawrence Salvucci

ASKER

Thank you very much!!!