Lawrence Salvucci
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?
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?
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]) /[CurrentW age] AS PercentIncrease
FROM tblWages AS W
GROUP BY W.EmpID;
SELECT W.EmpID, (select Max([WageDate]) From tblWages T where T.WageDate< dmax("WageDate","tblWages"
FROM tblWages AS W
GROUP BY W.EmpID;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much!!!
DBWage.mdb