title: Use Access SQL to do a grouped ranking
body: <p>How do I rank salespeople by # customers <em>grouped by department</em> (with ties included)?</p>
  
  <p>For example, given this table, I want to create the Rank column on the right. How should I do this in Access?
  <PRE>
  SalesPerson Dept #Customers Rank
  Bill        DeptA     20    1
  Ted         DeptA     30    2
  Jane        DeptA     40    3
  Bill        DeptB     50    1
  Mary        DeptB     60    2
  </PRE></p>
  
  <p>I already know how to do a simple ranking with this SQL code. But I don't know how to rework this to accept grouping.</p>
  
  <pre><code>Select Count(*) from [Tbl] Where [#Customers] &lt;  [Tblx]![#Customers] )+1
  </code></pre>
  
  <p>Also, there's plenty of answers for this using SQL Server's Rank() function, but I need to do this in Access.  Suggestions, please?</p>