90 day increments

[Annette]

I have a start date for a client stored in a table. Each client needs
to be reviewed every 90 days (do not care about working days or
holidays -- just a straight 90 days). I would like a tickler report
that will show me which clients are to be reviewed in the 90 day
increment pattern. So say the starting date is 01/01/2008, this client
would show up in a tickler report for clients needing to be reviewed
in 3/31/2008 and then show up again in a report that for the month of
June (6/29/2008) and so on.

How can I get the report to continue to work in three years show which
time frame we are in for 90 day increments? I will always have to be
looking back at the start date and the 90 day increments.

Thanks in advance!

 

[Ron2006]

Before we attempt at describing a solution we need to know one other
piece of information.

Are you going to be running this search 7 times in a week?

For instance, if the date you are using as the base date is 5/3/2008
(last Monday) then the 90 day aniversary is 8/3/8 - a Sunday. If you
do NOT run the report on Sunday or effective as of Sunday 8/3 you will
not see that client's record - it will not be in any tickler report in
August.

That question needs an answer before a solution can be guessed at.

Ron

 

[KARL DEWEY]

More questions. Are you keeping a record of reviews? You could use the
[last review completion date] or [last scheduled review date] to calculate
the next one.
You would start by entering a record for all using initial dates and then
query to append a record per client for the next review due within 90 days.
Then a query to produce a report of all due reviews.

 

[John Spencer]

Perhaps something along the lines of the following. I can't test it, so I
don't really know if either of the solutions will work or not. I am almost
positive that you will have to tweak them a bit, but I think one of the
concepts might work for you.

SELECT Client
FROM SomeTable
WHERE (DateDiff("d",[StartDate], Date()) MOD 90) Between 60 and 90

Or an alternative might be
SELECT Client
FROM SomeTable
WHERE DateAdd("d",90*Int(DateDiff("d",[StartDate],Date())/3),StartDate)
BETWEEN DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0)

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Annette]

This report would be run once monthly. So I would run it the beginning
of May for the cases I need to review (hit their 90 day increment) in
June. It then would be run again in June to find cases that need to be
reviewed in July.

No, apparently there is no table that keeps track of the reviews. This
is a purchased database that I am trying to do ad hoc reporting on.

Thank you.