Minimum function with multiple categories

[FredMcStaire]

I would like to find, for each car make, what colour has the maximum sales. So it would return Ford, Blue, 2; Holden, Blue,5; Toyota, red,11.
( this is a dummy set of data, in the real set make and colour each have upwards of 50 unique values)
a data set as follows:

Make	Colour	Sales
Ford	red	1
Ford	Blue	2
Holden	Red	3
Holden	Blue	5
Toyota	blue	7
Toyota	red	11

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

Any advice on whether this is possible with a single query without VBA?

Thanks

 

[Joe4]

If you want to do it in a Single Query, you will need to write the SQL code directly instead of using the Query Builder, as it involves a Nested Query (you could use the Query Builder if you are doing it in two steps/queries).

Here is what that SQL Code might look like:

SELECT CarsTable.Make, CarsTable.Colour,CarsTable.Sales
FROM CarsTable
INNER JOIN
(SELECT CarsTable.Make, Max(CarsTable.Sales) AS MaxOfSales
FROM CarsTable
GROUP BY CarsTable.Make) as X
ON CarsTable.Make=X.Make
AND CarsTable.Sales=X.MaxOfSales;

Basically, you are just first performing an Aggregate Query to find the maximum count for each Make, then linking that back to your original table to get the Colour associated with that Make/Sales combination.

 

[FredMcStaire]

Thanks!
Exactly what I was after.