projecting due dates

[Judy]

My database has an Annual Review Due field with quarters (1st, 2nd, etc.). I
need to produce a weekly report showing a 60-day projections of items that
are coming due. Should I have a date range in my annual review due field, or
leave it as is? I don't know how to write a formula for my projections so I
don't have to write it each week with real dates in it.

Any help will be greatly appreciated. Thanks, Judy

 

[Klatuu]

Sorry, Judy, but I don't think I fully understand your question.
You say the Annual Review Due field is a number from 1 to 4 that tells in
which quarter the review is due? Or is it actually 1st, 2nd, 3rd, 4th?

Now, the report runs weekly and you want to project which reviews will have
to be done in the next 60 days. Well, based on the above information, that
cannot be accurately determined.

If you can be a bit more specific on the business rules as to which data are
to be included, perhaps we can offer some specific recommendations.

 

[Judy]

Maybe I have the wrong kind of data in my "due date" field; I can change it
if I need to. I have many documents written; on an annual basis each chapter
needs to go through an annual review. i need to report to the document
owner's that their chapter is coming up due for review. I need to give them
a 60 day notice/warning that the due date for review is approaching.

I originally put in the due date field for each chapter whether they were
due the 1st, 2nd, 3rd, or 4th quarter (1st, 2nd, 3rd, 4th is what is entered
in that field for each chapter). Do I need to change the 1st, 2nd, etc to a
specific date like, Jan 1, Apr 1, Jul 1, Oct 1 to represent the beginning of
each quarter? I need to run this report weekly to give to the chapter owners.

 

[Piet Linden]

Maybe I have the wrong kind of data in my "due date" field; I can change it
if I need to. I have many documents written; on an annual basis each chapter
needs to go through an annual review. i need to report to the document
owner's that their chapter is coming up due for review.  I need to givethem
a 60 day notice/warning that the due date for review is approaching.

I originally put in the due date field for each chapter whether they were
due the 1st, 2nd, 3rd, or 4th quarter (1st, 2nd, 3rd, 4th is what is entered
in that field for each chapter). Do I need to change the 1st, 2nd, etc toa
specific date like, Jan 1, Apr 1, Jul 1, Oct 1 to represent the beginningof
each quarter? I need to run this report weekly to give to the chapter owners.

Maybe it's just me. I'm still not clear as to the structure of the
relevant tables in your database.

There are:
- Documents
- Chapters
- Reviews

Each document contains one or more chapters. Each chapter can be
reviewed zero or more times. Right?

Document---(1,M)---Chapter----(1,M)---Review

If this structure is correct, then if you include the ReviewDate in
the Review table, then this should be trivial.

 

[Klatuu]

I think it would be better if you changed the data type to a date.

Since you will want to determine just the month and day, but not the year in
your query, you will need to do the comparison based on the current year.
Here is a formula that will strip out the year and replace it with the
current year for comparison:

WHERE DateSerial(Year(Date), Month([ReviewDAte]), Day([ReviewDate]) BETWEEN
Date AND DateAdd("d", 60, Date)

This will return all records between today and 60 days from today.