I do like a challenge!
The first thing you'll need to do is introduce a means of returning every day
in the date range as these data are not included in the tables as they stand.
This means creating a table Calendar with a date/time column calDate which
has 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 put this in a new module save the module under a different
name from the function e.g. mdlCalendar.
To create and fill the module call the function from the debug window (aka
immediate window) – press Ctrl-G to open this. To create a calendar from
1990 to 2020 say, with all days of the week, enter:
MakeCalendar_DAO "Calendar",#01/01/1990#,#12/31/2020#,0
and press Enter.
Now create a query which returns all the days in a range defined by start and
end dates, and the number of people on grounds each day:
PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT [Enter start date:] AS DateFrom,
[Enter end date:] AS DateTo, caldate,
COUNT(*) AS NumberOnGrounds
FROM Calendar, Individuals INNER JOIN Census AS C1
ON Individuals.IndividualID=C1.InDividualID
WHERE C1.CensusCode In (6,7) AND C1.DateEffective =
(SELECT MAX(DateEffective)
FROM Census AS C2
WHERE C2.IndividualID = C1.IndividualID
AND DateEffective <= calDate)
AND caldate BETWEEN [Enter start date:] AND [Enter end date:]
GROUP BY [Enter start date:], [Enter end date:], caldate;
Save the query as qryCensusNumberOnGroundsPerDay. You'll notice that the
calendar table is not joined to either of the others. This returns what's
known as the Cartesian Product of the Calendar table and the result set of
the join of the other two tables, i.e. each row in calendar is joined to each
row returned by the join of the others.
Create another query based on this query, which counts the rows returned and
averages the numbers on grounds:
SELECT DateFrom, DateTo,
COUNT(*) As DaysInRange,
AVG(NumberOnGrounds) As AveragePerday
FROM qryCensusNumberOnGroundsPerDay
GROUP BY DateFrom, DateTo;
Open this final query. You don't have to open the
qryCensusNumberOnGroundsPerDay query, the final query just uses that as the
source, but you can open it independently if you wish to give the details per
day.
Ken Sheridan
Stafford, England
The fact that you cannot declare the parameter as DATETIME is the clue, I
think. It rather sounds like the DateEffective column in the table is a text
[quoted text clipped - 74 lines]
Issue was the DateEffective field in the table was text...I set the
input mask up and forgot to specify date/time...instead it was text.
Code works perfectly...thank you so much. Can't believe I made such a
newbie mistake.
My next endevor is average daily census for a date range...care to
take a shot?
I would need to know the census (total number of people on grounds)
for each day in the date range; then add them together and then divide
that number by the total number of days in the date range.
Average daily census is typically run for a month or year....
I really have no clue how to implement a loop with multiple variables
in SQL or VB. It would basically run the query you already wrote for
each day in the date range, store the count and add them all, then
divide by total number of days in date range.
Example:
Average daily census for 11/01/2009 to 11/03/2009
On 11/01/2009 census was 50
On 11/02/2009 census was 51
On 11/03/2009 census was 51
Total: 50+51+51 = 152
Number of days in date range: 3
Return Average daily census: 152 / 3 = 50.66
