When working with date ranges a Calendar table is very useful for this sort
of thing. A calendar table is in its simplest form just a table of all dates
over a period, say from 01/01/2000 to 12/31/2010. You can create one easily
by serially filling a column with dates in Excel and then importing it into
Access. I've created a function to do it in Access, however. Just paste the
following code into a standard module in your database:
Public Function MakeCalendar(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 cmd As ADODB.Command
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection
' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tbl = cat(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
cmd.CommandText = strSQL
cmd.Execute
Else
Set cmd = Nothing
Exit Function
End If
End If
On Error GoTo 0
' create new table
strSQL = "CREATE TABLE " & strTable & _
"(dayNum LONG, calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
cmd.CommandText = strSQL
cmd.Execute
If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(dayNum, calDate) " & _
"VALUES(" & lngDayNum & ", #" & Format(dtmDate, "mm/dd/yyyy")
& "#)"
cmd.CommandText = strSQL
cmd.Execute
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
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(dayNum, calDate)
" & _
"VALUES(" & lngDayNum & ", #" & Format(dtmDate,
"mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
End If
Next varDay
Next dtmDate
End If
Set cmd = Nothing
End Function
This function allows you to specify all or selected days of the week in the
calendar. It also numbers the rows which can be very useful when working
with a calendar of just Mondays to Fridays say. To create a table named
Calendar from 01/01/2000 to 12/31/2010 with all days of the week you'd call
it like so:
MakeCalendar "Calendar", #01/01/2000#, #31/12/2010#, 0
You then join it to your existing table, so if we assume a table Programs
with columns ProgramID, StartDate, EndDate a query to count the days in March
2006 for program 42 would go like this:
SELECT COUNT(*)
FROM Calendar INNER JOIN Programs
ON Calendar.CalDate <= Programs.Startdate
AND Calendar.CalDate <= Programs.EndDate
WHERE YEAR(CalDate) = 2006
AND MONTH(CalDate) = 3
AND ProgramID = 42;
To return the people who spent time in the program in any month would depend
on how the people data relates to the Programs table, but the basic principle
would be the same, i.e. you'd join the Programs and calendar tables and
restrict the result set to the month I question. You'd probably also join
something like a ProgramParticipation table and People table, the former
modelling the relationship between People and Programs. I could probably be
more explicit in this point if you could post details of your tables and how
they relate to each other.
Ken Sheridan
Stafford, England
