Access Query - Find Multiple "MAX" rows.
Posted on 2004-11-03
I have a query that is returning three things:
1. Employee Numbers - EmpNum
2. Position Numbers - PositionNum
3. Position Pay Scale - PayScale
Each employee has up to 5 positions but each position has a different pay scale (ranging from $2 to $8). The "MAX" pay is considered their "Base Position" and the remainders are their "Backup Positions".
I need a query (or multiple queries) to handle this:
1. Query the Employee Positions table and find only those rows with the MAX PayScale (based on Employee Numbers). In other words, only return those rows with the max dollar value.
2. Take the remainder and move them to a work table.
My ultimate goal here is to create a form where one can go in, change around positions, and then have a report spit off the total wage of the individual. As positions change often due to training, so do the wages and base positions.
Please let me know if you need further clarification. Thanks!!!