title: MS Access Query to get multiple counts from the same field
body: <p>I'm querying imported data that has a date/time field that I can't format as date in the table. </p>
  
  <p>Sample:</p>
  
  <pre><code>Ticket     Name   Date
  INC000101  User1  9/5/2016 10:00:34AM
  INC000102  User2  9/5/2016 12:02:00PM
  INC000103  User1  9/7/2016 3:34:00PM
  INC000104  User2  10/1/2016 9:30:23AM
  INC000105  User1  10/5/2016 10:20:00AM
  INC000106  USer2  10/6/2016 4:56:00PM
  </code></pre>
  
  <p>I'm trying to get a count of how many tickets each user has per month. Because the Date field comes from the database as a text field, I can't seem to make that format as date/time so I use "left" to filter by month. This is what I've used to get a return on a single User item for the month of October.</p>
  
  <pre><code>SELECT COUNT(*)
    FROM 2016YTD
    WHERE [Name]='User1' AND left(Date,3) = '10/';
  </code></pre>
  
  <p>I would like to add counts for User2 through UserX per month so that I can get a count row or column for each the quantity of tickets for each user each month in one report. Everything I've tried won't save the query due to syntax errors in one form or another. I've tried variations of the following query help post as well without success. </p>
  
  <pre><code>SELECT a.distributor_id,
  (SELECT COUNT(*) FROM myTable WHERE level='personal' and distributor_id = a.distributor_id) as PersonalCount,
  (SELECT COUNT(*) FROM myTable WHERE level='exec' and distributor_id = a.distributor_id) as ExecCount,
  (SELECT COUNT(*) FROM myTable WHERE distributor_id = a.distributor_id) as TotalCount
  </code></pre>
  
  <p>FROM myTable a ;</p>
  
  <p>I'm sure the answer is staring at me, just not sure what at the moment. </p>
  
  <p>Thanks for reading</p>
  
  <p>John</p>