ms-access queries

Question

I never really did ms access queries, but today I need one. I have 2 tables, models and orders. From first table one 2 fields are of interest here: number and color, from second only number which can only be equal to values of number from table "models" . What I need is to select most frequent color. In mysql that would be something like

    SELECT models.color, orders.number  FROM  models
    INNER JOIN orders ON (orders.number =models.number) 
    group by color
    order by count(color) desc limit 1

But in ms-access that doesn't seem to work How do you write query to do same thing in ms-access?

Answer 1

All you need is to get rid of limit and use Top 1 instead:

SELECT Top 1 models.color  FROM  models
INNER JOIN orders ON (orders.number =models.number) 
group by color
order by count(color) Desc

However, there is a caveat for Top n, in MS Access, Top n will return matches, so if several items have the same count, all will be returned. If this does not suit, you can work around this by ordering by a unique id as well:

order by count(color) desc, OrderID

Note also that number is a reserved word.

Answer 2

try this:

SELECT TOP 1 models.color, COUNT(orders.number)  FROM  models
INNER JOIN orders ON (orders.number=models.number) 
GROUP BY models.color
ORDER BY 2 desc