tracking daily values and reporting each day at month's end

[MKM]

my client runs a halfway house and needs to track information regarding where
the recovering addicts are the three recovery phases. I move them from Phase
1 to Phase 2 and do record the date of the move. At month's end she wants a
report that shows, for each day of that month, that there were X addicts in
PH1, Y addicts in PH2 and Z addicts in PH3. For example, on Monday there are
9, 12 and 4 addicts in each of the three phases. On Tuesday 2 addicts move
up to PH2 and 1 addicts moves to PH3, so I need to show 7, 13 and 5. I can
query at any point to find the current number in each phase, but need the
historical data. The only thing I can think of is to run a query each day
that counts the number in each phase and stores that information to be used
later in a report. Surely, since I know that date an addict was moved , I
can iterate through the days of the month and see if that date is before the
move to PH2 date and then count? --
Monica K. Murphy

 

[Allen Browne]

Hi Monica. The answer to your question will depend on what tables you have.

I'm not sure a "move" table will handle all scenarios.
A person may not move from one phase to the next, e.g.:
- person drops out of the program;
- person goes to hospital (or elsewhere) between phases;
- person completes program (i.e. is not moving into anything else);
- person is permitted to start Stage 2 for special reasons, even though they
are still completing stage 1.

So, I suspect you need to manage the entry and exit of each stage
separately, even though the common scenario would be to finish one stage and
move on to the next. The tables would look something like this:

Phase table (one record for each phase)
PhaseID primary key

Person table (one record for each person):
PersonID primary key
Surname text
...

Enrol table (one record each time a person enters a phase):
EnrolID primary key
PersonID relates to Person.PersonID
PhaseID relates to Phase.PhaseID
EnrolDate Date/Time Required.
ExitDate Date/Time (Blank while current.)

Now, your question is how to generate a record for each date between
EnrolDate and ExitDate, for each person. The dates have to come from
somewhere, so you need a table of dates.

1. Create a table with one field named TheDate.
Mark it as primary key.
Save it with the name tblDate.

2. Use the code below to populate it with all the dates you need. For
example you could put in the next 15 years of dates like this:
? MakeDates(#1/1/2006#, #12/31/2021#)

3. Create a query that uses both the Enrol table and tblDate, with no line
joining them. Called a Cartesian Product, this generates every possible
combination of records between the 2 tables.

4. Drag TheDate from tblDate to the grid.
In the Criteria row beneath this field, enter:
Between [Enrol].[EnrolDate] And (Nz([Enrol].[ExitDate], Date())

This limits the query output, so you just get a record for every date
between the enrol and exit dates. Where the ExitDate is null (i.e. the
person is still enrolled), the query gives every date from enrolment to
today.

Here is the code to populate the date table programmatically, so you don't
have to type in all the dates yourself:

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

 

[MKM]

thanks for the code; I'll try that and let you know how it turns out.
--
Monica K. Murphy

Hi Monica. The answer to your question will depend on what tables you have.

I'm not sure a "move" table will handle all scenarios.
A person may not move from one phase to the next, e.g.:
- person drops out of the program;
- person goes to hospital (or elsewhere) between phases;
- person completes program (i.e. is not moving into anything else);
- person is permitted to start Stage 2 for special reasons, even though they
are still completing stage 1.

So, I suspect you need to manage the entry and exit of each stage
separately, even though the common scenario would be to finish one stage and
move on to the next. The tables would look something like this:

Phase table (one record for each phase)
PhaseID primary key

Person table (one record for each person):
PersonID primary key
Surname text
...

Enrol table (one record each time a person enters a phase):
EnrolID primary key
PersonID relates to Person.PersonID
PhaseID relates to Phase.PhaseID
EnrolDate Date/Time Required.
ExitDate Date/Time (Blank while current.)

Now, your question is how to generate a record for each date between
EnrolDate and ExitDate, for each person. The dates have to come from
somewhere, so you need a table of dates.

1. Create a table with one field named TheDate.
Mark it as primary key.
Save it with the name tblDate.

2. Use the code below to populate it with all the dates you need. For
example you could put in the next 15 years of dates like this:
? MakeDates(#1/1/2006#, #12/31/2021#)

3. Create a query that uses both the Enrol table and tblDate, with no line
joining them. Called a Cartesian Product, this generates every possible
combination of records between the 2 tables.

4. Drag TheDate from tblDate to the grid.
In the Criteria row beneath this field, enter:
Between [Enrol].[EnrolDate] And (Nz([Enrol].[ExitDate], Date())

This limits the query output, so you just get a record for every date
between the enrol and exit dates. Where the ExitDate is null (i.e. the
person is still enrolled), the query gives every date from enrolment to
today.

Here is the code to populate the date table programmatically, so you don't
have to type in all the dates yourself:

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

my client runs a halfway house and needs to track information regarding
where
the recovering addicts are the three recovery phases. I move them from
Phase
1 to Phase 2 and do record the date of the move. At month's end she wants
a
report that shows, for each day of that month, that there were X addicts
in
PH1, Y addicts in PH2 and Z addicts in PH3. For example, on Monday there
are
9, 12 and 4 addicts in each of the three phases. On Tuesday 2 addicts
move
up to PH2 and 1 addicts moves to PH3, so I need to show 7, 13 and 5. I
can
query at any point to find the current number in each phase, but need the
historical data. The only thing I can think of is to run a query each day
that counts the number in each phase and stores that information to be
used
later in a report. Surely, since I know that date an addict was moved , I
can iterate through the days of the month and see if that date is before
the
move to PH2 date and then count? --
Monica K. Murphy