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.
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?