Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 967
  • Last Modified:

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!!!
  • 5
2 Solutions

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];

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


Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

   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.

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now