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:
[TABLE="width: 144"]
<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: #c5d9f1"]Make[/TD]
[TD="class: xl64, width: 64, bgcolor: #c5d9f1"]Colour[/TD]
[TD="class: xl65, width: 64, bgcolor: #c5d9f1"]Sales[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Ford[/TD]
[TD="bgcolor: transparent"]red[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Ford[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Holden[/TD]
[TD="bgcolor: transparent"]Red[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Holden[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Toyota[/TD]
[TD="bgcolor: transparent"]blue[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Toyota[/TD]
[TD="bgcolor: transparent"]red[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[/TR]
</TBODY>[/TABLE]

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.