Solved

# Current wage and previous wage in the same query

Posted on 2011-10-31
281 Views
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
Question by:Lawrence Salvucci

LVL 39

Expert Comment

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

LVL 119

Expert Comment

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

LVL 119

Accepted Solution

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

LVL 1

Author Closing Comment

Thank you very much!!!
0

## Featured Post

### Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.