The first thing you'll need to do is introduce a means of returning every day
over a period of time as these data are not included in the table as it
stands. This means creating a table Calendar with a date/time column calDate
which as one row for every day over a period of time. As it happens I have
written a function to do this, so paste this into a standard module in the
database:
Public Function MakeCalendar_DAO(strtable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)
' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)
Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long
Set dbs = CurrentDb
' does table exist? If so get user confirmation to delete it
For Each tdf In dbs.TableDefs
If tdf.Name = strtable Then
If MsgBox("Replace existing table: " & _
strtable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strtable
dbs.Execute strSQL
Exit For
Else
Exit Function
End If
End If
Next tdf
' create new table
strSQL = "CREATE TABLE " & strtable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
dbs.Execute strSQL
' refresh database window
Application.RefreshDatabaseWindow
If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strtable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strtable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If
End Function
Make sure you have a reference to the Microsoft DAO Object Library (Tools |
References on the VBA menu bar – select the one with the highest version
number). If you've put the function in a new module save the module under a
different name from the function e.g. mdlCalendar.
To create and fill the table call the function from the debug window (aka
immediate window) – press Ctrl-G to open this. To create a calendar from
2005 to 2015 say, with all days of the week, enter:
MakeCalendar_DAO "Calendar",#2005-01-01#,#2015-12-31#,0
and press Enter.
You can now create a query which joins the Calendar table to your table,
using a LEFT OUTER JOIN so that all dates are returned even if there are no
absences on a day. The query would be grouped by the date and reason and
would count the number per date/reason.
This query cannot be designed in design view however as it joins the tables
on the calendar date falling between the start and end dates, which cannot be
represented in design view. So its necessary to create it in SQL view. To
do this open the query designed and switch to SQL view and then paste in the
following, and change each instance of YourTable in the SQL statement to your
real table name. If the table name has spaces or other special characters in
it wrap the name in square brackets, e.g. [Your Table]
PARAMETERS [Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT calDate, Reason, COUNT(SID) AS NumberAbsent
FROM Calendar LEFT JOIN YourTable
ON (Calendar.calDate BETWEEN YourTable.StartDate
AND YourTable.EndDate)
WHERE calDate BETWEEN [Enter start date:]
AND [Enter end date:]
GROUP BY caldate, Reason;
When you run this query it will prompt you for a start and end date, and then
return rows for all dates within the range. Once you are satisfied that the
query is working correctly its then simply a case of creating a report based
on the query, which you can do using the built in Report Wizard. When you
open the report it will prompt for the start and end dates in the same way as
when opening the query on which it is based.
Ken Sheridan
Stafford, England
