Maintenance database

[George]

Dear friends,

I am in the process of developing a database to keep tracks of customers
buying equipment and then maintain this equipment every 6 months.

I will create a table for my customers, a table for the equipment and then
link those tables (one customer to many equipment). I will need to record
the installation date and then using this date to have reminders for
maintenance (every 6 months) - I believe that I will need a third table to
record the actual maintenance but I am not sure how to do that. Also I will
then need reminders for following maintenance:

e.g. equipment1 was installed on 1/1/2008
I will need a reminder (perhaps by the end of May) that this equipment1
needs maintenance.
After maintaining the equipment1 I must record the maintenance date, so to
have another reminder 5-6 months after).
On the reminders I don't need to see the maintained equipment.

Any help will be highly appreciated.

Thanking you in advance,


GeorgeCY

 

[Golfinray]

I would start by inserting some date/time fields in your new maintenance
table. You could use a form to enter the dates into the table. Have a date
field for each point in the maintenance schedule. You could then query using
datediff to find the difference in the dates (in days.) at those intervals,
you will know service needs to be done. Use
datediff("d",[yourfirstdate],[yourseconddate]) If you run into problems doing
this, post back.

 

[John W. Vinson]

Dear friends,

I am in the process of developing a database to keep tracks of customers
buying equipment and then maintain this equipment every 6 months.

I will create a table for my customers, a table for the equipment and then
link those tables (one customer to many equipment). I will need to record
the installation date and then using this date to have reminders for
maintenance (every 6 months) - I believe that I will need a third table to
record the actual maintenance but I am not sure how to do that. Also I will
then need reminders for following maintenance:

e.g. equipment1 was installed on 1/1/2008
I will need a reminder (perhaps by the end of May) that this equipment1
needs maintenance.
After maintaining the equipment1 I must record the maintenance date, so to
have another reminder 5-6 months after).
On the reminders I don't need to see the maintained equipment.

Any help will be highly appreciated.

Thanking you in advance,


GeorgeCY

I'll have to disagree with Golfinray here. Having multiple fields for multiple
maintenance dates is NOT a good idea. Each item will have zero, one, two or
many maintenance operations; the proper way to handle this is to have two
tables in a one to many relationship:

Equipment
ItemID <primary key>
ItemType
CustomerID
InstallationDate
<other information about the equipment item as an object>

Maintenance
MaintenanceID <autonumber primary key>
ItemID <link to Equipment>
MaintDate <date/time, date maintenance was actually done>
<comments, other information about this maintenance activity>

You can easily calculate the due date using a Totals query selecting the Max
of MaintDate for each item, and using whatever information you want to see
from the maintenance, equipment, and customer tables.

 

[George]

Dear John, you are Great, thanks a lot.

I will do so, and then using the dateadd function I can easily create my
reminders.

Ο χÏήστης "John W. Vinson" έγγÏαψε: