MS Access - Aggregate functions and max

Question

I have this query, where I am trying to get max age of a retail store seller(There's multiple towns), and show multiple if there's multiple people with the same (max)age. I am using Microsoft Access 2010. Here is the query:

SELECT Linnad.Linn, Myyjad.Nimi, Max(Myyjad.Vanus) As Vanus
FROM Linnad INNER JOIN Myyjad ON Linnad.LinnID = Myyjad.LinnID
GROUP BY Linnad.Linn, Myyjad.Nimi
ORDER BY Linnad.Linn;

The problem is, it seems to ignore the MAX, and just shows all of the values, and I can't remove the group by Myyjad.Nimi, because it gives me an error that aggregate function not included for Myyjad.Nimi.

And the output should be: Town - Name - Max(Age)

Also, Linn = Town, Nimi = Name and the Vanus = Age.

Answer 1

I think this may be what your looking for:

SELECT L.Linn, M.Nimi, M.Vanus
FROM Linnad As L,
(
SELECT M2.LinnID, M2.Nimi, M2.Vanus
FROM Myyjad As M2
WHERE M2.Vanus = (SELECT Max(Z.Vanus) FROM Myyjad As Z WHERE Z.LinnID = M2.LinnID)
) As M
WHERE M.LinnID = L.LinnID

This performs a sub-select to get a list of the Linn ID's with all Nimi's showing the maximum Vanus, then we link this sub-select back to the Linnad table via the LinnID.