HuBBie36
asked on
Access Query - Find Multiple "MAX" rows.
Hello all,
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!!!
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!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello again all,
OK.. I have it working about 95%. Just one simple thing is stopping me from achieving success. I have two queries creating two tables. One with the employee’s base position (works perfectly thanks to Sparab) and one with the employees backup positions.
My issue is that the employee's base position is being entered into the backup table. Is there an easy way to edit the query to say "DO NOT INCLUDE THE MAX PAY ROW"? For example, if employee 1234 has positions 1,2,3,4 and position 1 is the "Base Position" then I want it to forget about position 1 and include 2,3,4, into the backup table. Currently it is including all of them.
Here is my SQL:
SELECT EmpPositions.EmpNum, EmpPositions.PositionNum, Bands.BandBasePay, Bands.BandAdditionalPay
FROM (Bands INNER JOIN Positions ON Bands.BandID = Positions.PositionBand) INNER JOIN EmpPositions ON Positions.PositionNum = EmpPositions.PositionNum
GROUP BY EmpPositions.EmpNum, EmpPositions.PositionNum, Bands.BandBasePay, Bands.BandAdditionalPay;
To clarify, I need to select ALL BUT the MAX BASEPAY (Bands.BandBasePay).
Thanks!!
OK.. I have it working about 95%. Just one simple thing is stopping me from achieving success. I have two queries creating two tables. One with the employee’s base position (works perfectly thanks to Sparab) and one with the employees backup positions.
My issue is that the employee's base position is being entered into the backup table. Is there an easy way to edit the query to say "DO NOT INCLUDE THE MAX PAY ROW"? For example, if employee 1234 has positions 1,2,3,4 and position 1 is the "Base Position" then I want it to forget about position 1 and include 2,3,4, into the backup table. Currently it is including all of them.
Here is my SQL:
SELECT EmpPositions.EmpNum, EmpPositions.PositionNum, Bands.BandBasePay, Bands.BandAdditionalPay
FROM (Bands INNER JOIN Positions ON Bands.BandID = Positions.PositionBand) INNER JOIN EmpPositions ON Positions.PositionNum = EmpPositions.PositionNum
GROUP BY EmpPositions.EmpNum, EmpPositions.PositionNum, Bands.BandBasePay, Bands.BandAdditionalPay;
To clarify, I need to select ALL BUT the MAX BASEPAY (Bands.BandBasePay).
Thanks!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Atheek,
Im not sure if that is how you format it. Could you please be more clear while including the correct values?!?
Again they are 'EmpNum', 'PossistionNum' (not posnum), and 'BandBasePay' (not payscale)
Thanks!!
Im not sure if that is how you format it. Could you please be more clear while including the correct values?!?
Again they are 'EmpNum', 'PossistionNum' (not posnum), and 'BandBasePay' (not payscale)
Thanks!!
ASKER
Atheek,
Here is what I have:
SELECT EmpNum, PositionNum, BandAdditionalPay
FROM FindBackupPositions as emp1
WHERE BandAdditionalPay = (SELECT max(BandAdditionalPay)
FROM FindBackupPositions as emp2
WHERE emp2.EmpNum=emp1.EmpNum )
It works great but it is the exact opposit to what I am looking for. This return all of the maximum values and I need it to return all BUT the maximum values. Can I change that "WHERE BandAdditionalPay = (SELECT " to say "Does not equal" somehow?!?
Thanks!
Here is what I have:
SELECT EmpNum, PositionNum, BandAdditionalPay
FROM FindBackupPositions as emp1
WHERE BandAdditionalPay = (SELECT max(BandAdditionalPay)
FROM FindBackupPositions as emp2
WHERE emp2.EmpNum=emp1.EmpNum )
It works great but it is the exact opposit to what I am looking for. This return all of the maximum values and I need it to return all BUT the maximum values. Can I change that "WHERE BandAdditionalPay = (SELECT " to say "Does not equal" somehow?!?
Thanks!
ASKER
SUCCESS!
After a little research, I have found the asnwer:
SELECT EmpNum, PositionNum, BandAdditionalPay
FROM FindBackupPositions as emp1
WHERE BandAdditionalPay<>(SELECT
FROM FindBackupPositions as emp2
WHERE emp2.EmpNum=emp1.EmpNum )
Thank you both for your excellent assistance.
After a little research, I have found the asnwer:
SELECT EmpNum, PositionNum, BandAdditionalPay
FROM FindBackupPositions as emp1
WHERE BandAdditionalPay<>(SELECT
FROM FindBackupPositions as emp2
WHERE emp2.EmpNum=emp1.EmpNum )
Thank you both for your excellent assistance.
ASKER
This is excellent. However not exactly what I require.
#1. Works perfectly except for the case that I have to also include the Position Number. So, I would have the 'EmpNum', 'MaxPay', and 'PositionNum'. If I have only the first two, it works great, however if I add the 'PositionNum' to the query I am back to it returning all the fields. I need a way to say "Only return the rows that have the MAXIMUM PayScale number".
#2. I could simply use this query I suppose for my reporting purposes so I would now require a second table that contains all the OTHER positions and EXCLUDING those listed in the #1 query.
Please let me know if this makes sense.
Thanks!