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!!!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Here is your solution

Table is a table where your data is located
1.   SELECT empNum, Max(PayScale) AS MaxOfPayScale
FROM Table
SAve the above query as Query1
Now i am updating table <test> with the maximum values.
2.   UPDATE Query1 INNER JOIN test ON Query1.EmpNum = test.EmpNum SET test.PayScale = [test]![MaxOfPayScale];


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HuBBie36Author Commented:

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.

HuBBie36Author Commented:
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).


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

   Try this

SELECT empNum,  posnum,payscale as MaxPayScale
FROM   Table as emp1
WHERE  MaxPayScale=(SELECT max(payscale)              
                                 FROM   Table as emp2
                                 WHERE  emp2.empNum=emp1.empNum )

// if this doesnt work replace the '=' with IN


HuBBie36Author Commented:

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)

HuBBie36Author Commented:

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

HuBBie36Author Commented:

After a little research, I have found the asnwer:

SELECT EmpNum, PositionNum, BandAdditionalPay
FROM   FindBackupPositions as emp1
WHERE   BandAdditionalPay<>(SELECT max(BandAdditionalPay)              
                                 FROM    FindBackupPositions as emp2
                                 WHERE  emp2.EmpNum=emp1.EmpNum )

Thank you both for your excellent assistance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.