Hi, Rodger.
I do not wish just to complicate things for you but I have just a few
thoughts to share with you relative to tracking these re-occuring activity
dates.
You may need to consider keeping a record of all of the dates on which each
item was checked. This process will require that you have a table of your
items and another Items Checked table. For this commentary, I will call the
table for the Items "tblItems" and I will call the table that will store the
dates when items were checked "tblItemsChecked".
As you would expect, the "tblItems" table would have all information related
to the Item, perhaps even inculding the next date the item is to be checked.
While it is this field is going to be a calculated field based on the nunber
of days at which the next checking of the item is to occur, this field will
be updated each time the item is checked and the checked date is recorded.
While we are on the subject of calculating the next date the item is to be
checked, you may also want to at least think about a couple of things about
the "90" day period that you are indicating for the next check date. Is
there any circumstances under which some items might need to be checked at a
different interval? If there is any possibility that this could occur, you
should add a field to the "tblItems" table that will have a default value of
90 for the increment for the next check date but also allowing users to
specify a different number of days between check dates for a specific item.
Also along this line, will the next check date be calculated from the
previously specified check date or will the next check date potentially be
calculated from the date on which the actual check of the item occured? I
know this shoulds trivial, but it is not. This can be very critical to just
how items are checked and how you and your users would be notified of the
need for the next check of the item. to complicate this even further, I had
one situation where it was decided that the calculation for the next check
could actually calculated from a specified date. I know that would be very
unsual but if it can occur, you have to plan your database to accomodate it.
The new table, "tblItemsChecked" table would potentially have at least the
following structure:
Field Name Type
ItemChkdId AutoNumber
ItemID Number (long Intger)
DateChkDue Date/Time
DateChkComp Date/Time
There would surly be more fields than this but this should give you the
general idea. If the checking of these items is performed by multiple
individuals then you may need another table for "inspectors" or what ever you
call them and then add another number type field to this table as a foreign
key link field to the inspectors table. (Just my thoughts)
As an explanation, the fields in the "tblItemsChecked" table provide the
folowing: an autonumber field to provide a specific record ID for each entry
as its primary key, I a long intger number type field as the Foreign Key
field to link to the "tblItems" table and two date fields.
When you have your data structrued correctly, you will then be able to
create one or more queries to be used by forms to display the Items that
would be expected to be checked within the next week or the next specific
number of days or within the next month, etc.
These are just some of the things that I had to consider as I developed a
database that provides users with the ability to send out notices of
activities that are expected at various intervals.
Sorry if I got to just rambling around, but hopefully some of my ramblings
will be of some assistance to you as you plan and develop you database and
the User Interface for it.
Good luck with your project.
