Query (count) Number of Absences

[michellecahal]

I have a table with DriverName, DateOfAbsence, AbsenceReason as fields. I want to count the number of absences each driver has by month. Would I need a "Month" field?

 

[plog]

Nope, you never store what you can calculate with the data you already have. You would use the Month() function: http://www.techonthenet.com/access/functions/date/month.php

Your query would look like this:

SELECT Month(DateOfAbsence) AS AbsenceMonth, Year(DateOfAbsence) AS AbsenceYear, DriverName, COUNT(DriverName) AS Absences
FROM YourTableNameHere 
GROUP BY Month(DateOfAbsence), Year(DateOfAbsence), DriverName 
ORDER BY Year(DateOfAbsence), Month(DateOfAbsence), DriverName;

Be sure to replace 'YourTableNameHere' with the name of your table.

 

[plog]

To run the query I wrote, copy the code above, create a new query, instead of design view go into SQL view and paste the code. Then replace 'YourTableNameHere' with the name of the table all your data is in. Run that query and you should have what you need.