[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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?
0
Lawrence Salvucci
Asked:
Lawrence Salvucci
  • 2
1 Solution
 
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
 
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
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you very much!!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now