need help using the max function in aggregate query (1 Viewer)

[martinr]

I have a list of product sales for various regions.
How do I write a query to only retrieve the record with the highest value in a region, but include the region and product code in the results?

For example, If I run an aggregate query using the max function I still get the record for region:
SELECT Stats.ProdCode, Max(Stats.Sales) AS MaxOfSales, Stats.Region
FROM Stats
GROUP BY Stats.ProdCode, Stats.Region;

I realise that this returns the record for each region because the 'Group By' is applied to that field, but if I remove it then that field is not going to be available in the results(???)

what I'm looking for is a result like (in table format of course);
ProdCode: AX23
MaxOfSales: 1,5022
Region: WestA2

 

[jdraw]

Here is a sample based on your post -my table name is different.

SELECT tblStatsProdSales.ProdCode
, Max(tblStatsProdSales.Sales) AS MaxOfSales
, tblStatsProdSales.Region
FROM tblStatsProdSales
WHERE
 (((tblStatsProdSales.[sales])=(select Max( sales) from  tblStatsProdSales as x
where x.region = tblStatsProdSales.region)))
GROUP BY   ProdCode, Region;

I have attached a jpg of the table data and the query result.
Good luck.

 

[martinr]

thanks jdraw, it seems to work Ok, but for some reason not all records (ProdCode)
are being returned in the query result... ie; some ProdCodes are missing from the results, even though they have data in the table...

 

[jdraw]

Can you post a copy of the database, or your table design and some sample data?