Counting all active records over date range

[trout]

Hi all,



I am fairly inexperienced with Access, but have been learning a lot recently. Until now, I have been able to figure out my problems by searching around, but this time I'm not sure how to phrase my search well enough. Here is what I am trying to do.

Using a form with corresponding table, I am collecting the start date and end date for a particular activity. People may be active for several months. I want to be able to count everyone who is active for a given month (in other words, they are between the start and end dates). In the past, I have only done queries based on start date. Ideally, I would also like to be able to run reports showing the number of active records for each month over the year.

I'm not sure I explained that very well, but I would appreciate your thoughts.

Thanks!

[June7]

How are start and end dates saved - in two fields of same record? If you want to provide db for analysis, follow instructions at bottom of my post.

[Dal Jeanis]

Okay, so when you are looking for people who were active in date range X to Y, you want all people whose start date is before or on Y, and whose end date is either Null or greater than X. (If your database has anyone who ended before they started, so be it.)

Here's the where condition for the SQL.

WHERE ([MyTable].[StartDate] <= [MyEndDate])
AND ([MyTable].[EndDate] IS Null OR [MyTable].[EndDate] >= [MyStartDate])

NULLS are kind of funky. The last condition could also be rewritten as

AND NOT ([MyTable].[EndDate] < [MyStartDate])

[trout]

Okay, so when you are looking for people who were active in date range X to Y, you want all people whose start date is before or on Y, and whose end date is either Null or greater than X. (If your database has anyone who ended before they started, so be it.)

Here's the where condition for the SQL.

WHERE ([MyTable].[StartDate] <= [MyEndDate])
AND ([MyTable].[EndDate] IS Null OR [MyTable].[EndDate] >= [MyStartDate])

NULLS are kind of funky. The last condition could also be rewritten as

AND NOT ([MyTable].[EndDate] < [MyStartDate])

Thank you both for responding. The start and end dates are in two fields of the same record. Dal Jeanis, I think that is perfect. Is there a simple way for me to create a report that would give me the number for each month over a specified period?

[Dal Jeanis]

Depends on your definition of "simple", and whether you want to be able to change that specified period at will.

The brute force method would be to create a month table, with one record per month you want to see on the report, that looked something like this:

tblMonths
mthStartDt  Date
mthEndDt    Date

I would tend to just go ahead and load that table with a record for every month since a year before the data started and for the next ten years, so I didn't have to think about it.

Then, assuming your Volunteer record looked like this:

tblVolunteer
volID       PK
volStartDt  Date
volEndDt    Date

You would use a query like this

SELECT Format(TM.mthStartDt,"MMM YYYY"), Count(TV.volID)
FROM tblMonths AS TM, tblVolunteers AS TV
WHERE ((TV.volStartDt <= TM.mthEndDt)
    AND (TV.volEndDt IS Null OR TV.volEndDt >= TM.mthStartDt))
GROUP BY TM.mthStartDt
ORDER BY TM.mthStartDt Asc;

NOTE: Changed SQL to WHERE instead of INNER JOIN, because of inequalities.

And then you could filter that query based upon your desired period.

WHERE mthStartDt BETWEEN ctrlFirstMonthDate and ctrlLastMonthDate

By the way, your own posts will be easier to read if you quote just the parts of the prior posts that are necessary to your point.

[Dal Jeanis]

Here's the code to create and load the month table.

0) As always, back up your database before attempting this.

1) Create a new module, and paste this code in.

2) Change the constants at the top.
* dtFirstStart should be the first day of the first possible month on your database.
* dtFirstEnd should be the last day of that month.
* nbrOfMonths should be the number of months you want to load.

3) Then open the immediate window, type "Call CreateAndLoadMonths", and hit enter.

4) Review the table that has been created and see that it looks reasonable.

Option Compare Database
Option Explicit
Const dtFirstStart As Date = #1/1/2011#
Const dtFirstEnd As Date = #1/31/2011#
Const nbrOfMonths As Integer = 120

Sub CreateAndLoadMonths()
    Call CreateMonthTable
    Call LoadMonthDates
End Sub

Sub CreateMonthTable()
 
    Dim db As DAO.Database
    Dim myTable As DAO.TableDef
    Dim myField As DAO.Field
    Dim myProp As DAO.Property
 
    Set db = CurrentDb
 
    ' create the new tabledef
    Set myTable = db.CreateTableDef("tblMonths")
    
    ' add the two date fields to the tabledef
    With myTable
        .Fields.Append .CreateField("mthStartDt", dbDate)
        .Fields.Append .CreateField("mthEndDt", dbDate)
    End With
    
    ' create the table in the database using the tabledef
    db.TableDefs.Append myTable
    'update the format of the start date
    Set myField = myTable.Fields("mthStartDt")
    Set myProp = myField.CreateProperty("Format", dbText, "Short Date")
    myField.Properties.Append myProp
    'update the format of the end date
    Set myField = myTable.Fields("mthEndDt")
    Set myProp = myField.CreateProperty("Format", dbText, "Short Date")
    myField.Properties.Append myProp
    'Refresh the nav panel
    Application.RefreshDatabaseWindow
     
    ' explicitly kill the objects
    Set myProp = Nothing
    Set myField = Nothing
    Set myTable = Nothing
    Set db = Nothing
 
End Sub

Sub LoadMonthDates()

   Dim dtMthStart As Date
   Dim dtMthEnd As Date
   Dim intI As Integer
   Dim strSQL As String
   Dim db As DAO.Database
   Set db = CurrentDb

   ' kill all current records
   strSQL = "DELETE FROM tblMonths;"
   db.Execute (strSQL)

   ' add one record per month
   For intI = 0 To nbrOfMonths
      dtMthStart = DateAdd("m", intI, dtFirstStart)
      dtMthEnd = DateAdd("m", intI, dtFirstEnd)
      strSQL = "INSERT INTO tblMonths (mthStartDt, mthEndDt) " & _
       "VALUES ( #" & dtMthStart & "#, #" & dtMthEnd & "# );"
      db.Execute (strSQL)
   Next intI

   ' explicitly kill the objects
   Set db = Nothing

End Sub