Query Join Help

[RedBean]

I have a table in the following format:

REGION  REP#  NAME  STATE  SALES
A       0031  Joe   MA     $10,000
A       0302  Mary  CA     $15,000
A       0063  Mike  TX     $15,000
B       0004  Bob   NY     $7,000
...

What I want to do is return for every region the person with the highest Sales and if there is a tie, then just return the person that has the higher REP#. So the results of the example would be:

REGION  REP#  NAME  STATE  SALES
A       0063  Mike  TX     $15,000
B       0004  Bob   NY     $7,000
...

I first made a query to find the max sales per region that returns:

REGION  MaxOfSALES
A       $15,000
B       $7,000
...

But I am not quire sure how to combine it with the original table that would return the person with the higher REP# in the case of multiple people having the same sales. I end up with results like this:

REGION  REP#  NAME  STATE  SALES
A       0302  Mary  CA     $15,000
A       0063  Mike  TX     $15,000
B       0004  Bob   NY     $7,000
...

How do I get is so Mike is the only one returned for region A? Any help would be greatly appreciated.

 

[nsukari]

To your first query add a third column for Max of Rep#. Now your query resilts should be

Region MaxOfSales MaxOfRep#
A $15,000.00 0302
B $7,000.00 0004

Here is the code for the join query:

SELECT Query1.Region, Query1.[MaxOfRep#], Reps.Name, Reps.State, Query1.MaxOfSales
FROM Reps RIGHT JOIN Query1 ON Reps.[Rep#] = Query1.[MaxOfRep#];

This code will produce this result:
Region MaxOfRep# Name State MaxOfSales
A 0302 Mary CA $15,000.00
B 0004 Bob NY $7,000.00

 

[RedBean]

Thank you very much, nsukari. Your solution worked great!