Nested Aggragate Function in MS Access

[evillestat]

Hello,

I'm a student working on a problem in MS Access 2003 SP3 on Windows XP, and no matter where I look online I cannot find any help. My book also doesn't help me.

Essentially I need to do the statement:

Max(Sum([SaleItem].[Quantity]*[SaleItem].[SalePrice]))

Though, as you know Access will not allow this. I do not understand how I can achieve this, as the code below will display this:

State SumOfSales
TX 142.30
CA 122.50
MS 32.10

However, what I am really after is simply:

State SumOfSales
TX 142.30


I need the program to calculate the sum of the sales, then I need the max of the sums to be the only thing shown.

Any and all help is appreciated. I'm simply desperate for a solution though no one I speak with seems to know of any.

SELECT City.State, Sum([SaleItem].[Quantity]*[SaleItem].[SalePrice]) AS SumOfSales
FROM ((Customer INNER JOIN Sale ON Customer.Custom erID = Sale.CustomerID ) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID ) INNER JOIN City ON Customer.CityID = City.CityID
WHERE (((Sale.SaleDat e) Like '7*'))
GROUP BY City.State;

Thanks for your reading and time.

[FishVal]

Hi, there.

Jet SQL really doesn't support nested aggregate functions.
But the same may be usually achieved by splitting it into two queries - i.e. one calculates sum of groups, other based on the first calculates average of sums.

In you particular case you don't need even that. Just sort the query you've posted by sum in descending order and get the first row only using TOP predicate.

Regards,
Fish

[evillestat]

Thank you kindly, that worked perfectly!

[FishVal]

You are welcome.

Best regards,
Fish