Access Query - Find Multiple "MAX" rows.

Posted on 2004-11-03
Last Modified: 2012-06-27
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!!!
Question by:HuBBie36
    LVL 11

    Accepted Solution


    Here is your solution

    Table is a table where your data is located
    1.   SELECT empNum, Max(PayScale) AS MaxOfPayScale
    FROM Table
    GROUP BY EmpNum;
    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];


    Author Comment


    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.


    Author Comment

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


    LVL 1

    Assisted Solution

       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



    Author Comment


    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)


    Author Comment


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


    Author Comment


    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now