Max Query to return one row only

[nerthol]

Hello All,

I have searched for this and failed.:banghead:

I have a query which is a max sum of sales for several sales reps. (If that makes sense?)

Sales Rep, Total Sales
1, £100
2, £200
3, £500
4, £50
5, £150

I need to create a report which delivers the sales rep with the most sales and only that sales rep

e.g.

3, £500

Any help with this would be much appreciated.

 

[boblarson]

Using that query you have created, then you would use

Select [Sales Rep], Max([Total Sales])
From [QueryNameHere]
Group By [Sales Rep]

 

[John Big Booty]

This article may also give you some pointers.

 

[nerthol]

Thank you very much both of you!

I kept getting all of the 5 records returning and not just the one I needed. So from the pointers I used:

SELECT TOP 1 table1.[Sales Rep], Sum(table1.[Total Sales]) AS SumOfTotal
FROM table1
GROUP BY table1.[Sales Rep]
ORDER BY Sum(table1.[Total Sales]) DESC;


Using Access 2010, sorted decending and max records 1 in the property sheet.

Seems to work.

Can you see any problems with using this?

Thanks for the quick replies.

 

[mdlueck]

Greetings nerthol,

I was going to suggest a:

SELECT TOP 1
ORDER BY

type solution. It appears you have come up with that already. I will take your word for requiring the GROUP BY clause. Looks like you are ranking sales reps or something, yes?

 

[nerthol]

Hi

You are right grouping is not needed.

Noticed that after I posted.

Thanks for helping out.

Yes ranking sales reps for most sales per month.

Greetings nerthol,

I was going to suggest a:

SELECT TOP 1
ORDER BY

type solution. It appears you have come up with that already. I will take your word for requiring the GROUP BY clause. Looks like you are ranking sales reps or something, yes?

 

[mdlueck]

You are right grouping is not needed.

Noticed that after I posted.

rrrrr???? Are you certain that the query results are correct without the grouping?

Grouping and the Sum() function will work together, doing the Sum() for each group (sales rep) and then the ORDER BY will fire after the GROUP BY sending the biggest value to the top of the result set.

You best audit the query CaReFuLlY!

 

[boblarson]

rrrrr???? Are you certain that the query results are correct without the grouping?

Looking back on the original post, I can see that grouping would not be needed. In fact, it would not work if it were. My bad.

Top 1 would work.