It should be a simple query I've done it on SQL Server db's before but it doesn't work for Access. All I need is the top 10 first names that occur with a last name of X. I am unable to order by the 'itCount' variable so I don't get the most common names. Here is my query hopefully you can help me. Thanks, Luke

SELECT TOP 10 t1.sFName AS sTemp, (SELECT count(*) FROM tblData t2 WHERE t1.sFName = t2.sFName And t2.sSurname Like 'H*') AS itCount
FROM tblData AS t1
WHERE 0=0 And t1.sSurname Like 'H*'
GROUP BY t1.sFName;
'''I would like to add 'ORDER BY itCount' here but it throws an error

i believe you are getting the error because in your query, itCount is an alias for the subquery, i.e. derived table

could be wrong; wouldn't be the first time

the GROUP BY seems unnecessary in that construction, too

luckily your query can be rewritten in a more straightforward manner

you'll want to sort descending, too

d'oh, I guess I did overcomplicate that. Your query works with one small modification. You can't Order By an alias so you have to change the clause to "order by count(*) desc"

Thanks again

select top 10
sFName as sTemp
, count(*) as itCount
from tblData
where sSurname like 'H*'
group
by sFName
order
by count(*) desc