title: Having trouble using count() in Access SQL query
body: <p>Using Access 2007.</p>
  
  <p>I have a table Inventory with the following fields:</p>
  
  <p>ContainerID
  FolderID</p>
  
  <p>My goal is to find out which FolderIDs correspond to more than one ContainerID, and what the ContainerIDs are for these records.  I thought the best way to handle this would be doing a query that returns ContainerID, FolderID, and a count of each FolderID, so that I could sort that list to put the records with count>1 on top.  I tried this with:</p>
  
  <pre><code>select ContainerID,FolderID,count(FolderID) from (select distinct * from Inventory);
  </code></pre>
  
  <p>but Access gave me an error message: "You tried to execute a query that does not include the specified expression 'ContainerID' as part of an aggregate function."</p>
  
  <p>How can I get the results I'm looking for?</p>
  
  <p>EDIT:
  I'm trying all of your solutions but all of them keep getting a lot of duplicate rows.  I just tried to filter them out with DISTINCT but for some reason that froze up Access.  I have to go home so I'll check these out again tomorrow morning.  Thanks for offering suggestions.</p>