How to Benchmark The Best Month Sales

[jasonman]

Please help. The query below sums sales per month in the entire Table.

SELECT Sum([GOLF SHOP OPERATIONS].CartFees) AS SumOfCartFees, Format([GOLF SHOP OPERATIONS].TheDate,"mm-yyyy") AS MonthYear
FROM [GOLF SHOP OPERATIONS]
GROUP BY Format([GOLF SHOP OPERATIONS].TheDate,"mm-yyyy");

How can I adjust the query so only the "best" month in sales is returned.

For example the query mentioned above returns these results:

SumOfCartFees MonthYear
$14,449.85 01-2010
$12,809.68 02-2010


$41,427.82 03-2010
$56,632.40 04-2009
$60,047.73 04-2010
$59,614.77 05-2009
$67,517.04 05-2010
$65,264.06 06-2009
$62,817.18 06-2010
$75,238.79 07-2009
$59,629.86 07-2010
$63,801.46 08-2009
$75,207.17 08-2010
$58,586.41 09-2009
$48,897.65 09-2010
$49,762.43 10-2009
$35,063.78 10-2010
$39,276.54 11-2009
$17,433.62 12-2009

However, I ONLY want it to return.

SumOfCartFees MonthYear
$75,238.79 07-2009

Anyone know how to do this? Thanks.

[ajetrumpet]

try:

SELECT TOP 1 

Sum([GOLF SHOP OPERATIONS].CartFees) AS SumOfCartFees, Format([GOLF SHOP

OPERATIONS].TheDate,"mm-yyyy") AS MonthYear

FROM [GOLF SHOP OPERATIONS]

GROUP BY Format([GOLF SHOP OPERATIONS].TheDate,"mm-yyyy")

ORDER BY Sum([GOLF SHOP OPERATIONS].CartFees) AS SumOfCartFees, Format([GOLF SHOP

OPERATIONS].TheDate,"mm-yyyy") DESC;

[jasonman]

Thanks. I couldn't get the query you suggested to work without an error. But it led me to this one, which seems to work. Thanks for the help.

SELECT TOP 1
Sum([GOLF SHOP OPERATIONS].CartFees) AS SumOfCartFees, Format([GOLF SHOP OPERATIONS].TheDate,"mm-yyyy") AS MonthYear
FROM [GOLF SHOP OPERATIONS]
GROUP BY Format([GOLF SHOP OPERATIONS].TheDate,"mm-yyyy")
ORDER BY Sum([GOLF SHOP OPERATIONS].CartFees) DESC;

Which returns:

SumOfCartFees MonthYear
$75,238.79 07-2009

Thanks again.

[RuralGuy]

Are you ready to follow the link in my sig and mark this thread as Solved?

[ajetrumpet]

I did it Allen. I think it's safe now