Ms-access – How to count and order by unique entries

ms accesssubquery

I have this:

SELECT tblClient.[Printed Name], tblClient.[Address 1]
     , tblClient.[Address 2], tblClient.City, tblClient.State
     , tblClient.Zip, tblClient.Phone, tblClient.[Email Address]
FROM tblClient 
INNER JOIN tblCurrent 
    ON tblClient.[Client Code] = tblCurrent.Client
GROUP BY tblClient.[Printed Name], tblClient.[Address 1]
       , tblClient.[Address 2], tblClient.City, tblClient.State
       , tblClient.Zip, tblClient.Phone, tblClient.[Email Address]
       , tblCurrent.[Date Ordered]
HAVING (((tblCurrent.[Date Ordered])>#6/27/2013#))
ORDER BY tblClient.[Printed Name];

Now I need to get a count and sort by the number of times each unique "Printed Name" shows up.

I've looked all over but can't figure this out from other questions people have asked. I'm not usually the one at my company that has to mess with Access. It seems like this is probably a simple problem, but I'm noobier than noob.

Any help would be appreciated.

Best Answer

Try this

SELECT 
    tblClient.[Printed Name], 
    tblClient.[Address 1], 
    tblClient.[Address 2], 
    tblClient.City, 
    tblClient.State, 
    tblClient.Zip, 
    tblClient.Phone, 
    tblClient.[Email Address],
   (select count(*) 
    from tblCurrent 
    where tblCurrent.[Date Ordered]>#6/27/2013# 
      and tblClient.[Client Code] = tblCurrent.Client
   ) AS Qty
FROM tblClient 
ORDER BY Qty desc;
Related Question