title: Average of a distinct count by day in Access
body: <p>I'm trying to get an average of a distinct count per day out of an Access 2010 database and nothing I do seems to be working. I've tried using a subquery but I can't figure out how to link it to the main one. The table has a row for every office visit, like this:</p>
  
  <pre><code>    patient_id, visit_year, visit_date, dow, doctor_id
      12345, 2012, 3/5/12, Monday, 987
      12567, 2012, 3/5/12, Monday, 986
      12789, 2012, 3/6/12, Tuesday, 987
  </code></pre>
  
  <p>I need to get the average number of doctors available per day of week where year = 2012. In my head this should work but it doesn't: 
  Select dow, AVG(COUNT(DISTINCT(doctor_id))) AS AvgDocsInOffice
  From visits
  WHERE visit_year = 2012
  GROUP BY dow </p>
  
  <p>I'm trying to get to this output:</p>
  
  <pre><code>    DOW, AvgDocsInOffice
      Monday, 5
      Tuesday, 6
      Wednesday, 4
  </code></pre>
  
  <p>Any ideas? Unfortunately I'm stuck doing this in Access.</p>