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?
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
forgot the percent format


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, Format(([CurrentWage]-[PrevWage])/[CurrentWage],"Percent") AS PercentIncrease
FROM tblWages AS W
GROUP BY W.EmpID;
0
 
als315Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
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;
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you very much!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.