I am fascinated by subqueries and keep finding things on-line that I didn't know would work. Some of the really elegant subqueries I've created have turned out to be reall performance problems. Others work fine and result in an all-in-one solutions that seems "smarter" and mean less components to manage.
I found one today in a post on the Access team blog. Here's one of the queries from the article:
SELECT C.Color, Sum(C.Value) AS Total, T2.N FROM (SELECT T.Color, Count(T.Color) AS N FROM (SELECT DISTINCT Color, Count(*) AS N FROM tblColors GROUP BY Color) AS T GROUP BY T.Color) AS T2 INNER JOIN tblColors AS C ON T2.Color = C.Color GROUP BY C.Color, T2.N;
This is for count of distinct items. See the blog post for more.