Solved

# Access Query - Find Multiple "MAX" rows.

Posted on 2004-11-03
962 Views
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!!!
0
Question by:HuBBie36

LVL 11

Accepted Solution

Hi

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

Greetings
Sparab
0

Author Comment

Sparab,

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

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

0

LVL 1

Assisted Solution

Hi,
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

regards

Atheek
0

Author Comment

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

Author Comment

Atheek,

Here is what I have:

FROM   FindBackupPositions as emp1
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!
0

Author Comment

SUCCESS!

After a little research, I have found the asnwer:

FROM   FindBackupPositions as emp1
FROM    FindBackupPositions as emp2
WHERE  emp2.EmpNum=emp1.EmpNum )

Thank you both for your excellent assistance.
0

## Featured Post

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…
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…