MS Access Query to get multiple counts from the same field

I’m querying imported data that has a date/time field that I can’t format as date in the table.

Sample:

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

​x
 
Ticket     Name   DateINC000101  User1  9/5/2016 10:00:34AMINC000102  User2  9/5/2016 12:02:00PMINC000103  User1  9/7/2016 3:34:00PMINC000104  User2  10/1/2016 9:30:23AMINC000105  User1  10/5/2016 10:20:00AMINC000106  USer2  10/6/2016 4:56:00PM​

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.

SELECT COUNT(*)
  FROM 2016YTD
  WHERE [Name]='User1' AND left(Date,3) = '10/';

 
SELECT COUNT(*)  FROM 2016YTD  WHERE [Name]='User1' AND left(Date,3) = '10/';​

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.

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

 
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​

FROM myTable a ;

I’m sure the answer is staring at me, just not sure what at the moment.

Thanks for reading

John

Answer

This is only an answer to your first question about how to deal with dates that are stored in text fields.

To get the count for all users for every month you can do:

SELECT [Name], Format([Date],'mmmm') AS Month, COUNT(*) as Count
  FROM 2016YTD
  GROUP BY [Name], Format([Date],'mmmm')

 
SELECT [Name], Format([Date],'mmmm') AS Month, COUNT(*) as Count  FROM 2016YTD  GROUP BY [Name], Format([Date],'mmmm')​

A text field containing a date that is always in the same format can be treated as a date with Format() so Format([Date],’mmmm’) returns the full month name for each date.

Advertisement

Answer