Updating date fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form linked to a table for item servicings. There are 4 different
servicings at diferent frequencies, The Field names are: [Completed1] (date),
[Period1](text), [Due1](date), [Completed2], [Period2], [Due2], etc. When
[Completed1] is updated [Due1] is calculated with the simple formula ...
Due1:[Completed1]+[Period1] ... and so on with the other servicings.
My problem is this
If a higher service is performed eg (Bay Service 2) this will affect the due
date for Bay Service 1 because it is an in depth service that actually
incorporates the lower service. It may sound silly, however the effect is
that when a higher service is preformed the lower service is deemed to have
been performed as well. This makes the completed date the same. So, when
completed2 date field is updated I need Completed1 updated but still be able
to update the lower service myself and the subsequent calculation be
performed.
It must be possible, but can it be done by a simple boofhead like me? or
would it require heavy coding?
 
Bill, what you describe makes perfect sense for many kinds of maintenance,
but the implementation is not as simple as you suggest.

The repeating fields that you describe (Completed1, Completed2, etc.) are an
absolute no-no for relational design. It must be done with a related table.

The nesting of services (e.g. the "B" service satisifes all aspects of the
"A" service also), so somehing you have to teach the database about. With
some kind of maintenance, these "services" need to actually be broken down
into components, e.g. a "B" service includes changing a component (such an
an oil pump), but if that component has already been changed, then this
doesn't need doing. So, there's a table that defines each component of each
service. For each component, there can also be multiple criteria, e.g.
"every 10000 miles, or every 200 engine hours, or every 3 months, which ever
comes first."

So, you end up with tables like this:
- ServiceType table (one record for each kind of service)
- Aspect table (one record for each component that makes up the serice)
- AspectFreq table (one record for each requirement of when an aspect of a
serivce must occur)
- ServiceAspect table (one record for each aspect covered by a service
type.)
- Vehicle table (one record for each thing that needs servicing)
- Job table (one record for each time a vehicle is maintained.)
- JobAspect table (one record for each aspect conducted in the job)

If Job has a foreign key to ServiceType, in can indicate what type of
service was conducted. In the form where you enter jobs, you can then
execute an Append query statement in the AfterInsert event of the form, to
add all the aspects in the ServiceAspect table into the JobApect table. The
user can then add, edit, or delete those items according to what was
actually done. (This also means the aspects are correctly maintained for
past records, even if you change what is normally covered in a service
later, by altering the ServiceAspect table.)

The actual code that determines what aspects require service next and when
is also interesting stuff to write. It requires a bit of fuzzy logic, based
on the recent runnning patterns of that vehicle to predict when it is likely
to clock up the distance or hours or date for that aspect, and then select
the mininum of the anticipates dates and return that.

HTH

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

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

Bill Neilsen said:
I have a form linked to a table for item servicings. There are 4 different
servicings at diferent frequencies, The Field names are: [Completed1]
(date),
[Period1](text), [Due1](date), [Completed2], [Period2], [Due2], etc. When
[Completed1] is updated [Due1] is calculated with the simple formula ...
Due1:[Completed1]+[Period1] ... and so on with the other servicings.
My problem is this
If a higher service is performed eg (Bay Service 2) this will affect the
due
date for Bay Service 1 because it is an in depth service that actually
incorporates the lower service. It may sound silly, however the effect is
that when a higher service is preformed the lower service is deemed to
have
been performed as well. This makes the completed date the same. So, when
completed2 date field is updated I need Completed1 updated but still be
able
to update the lower service myself and the subsequent calculation be
performed.
It must be possible, but can it be done by a simple boofhead like me? or
would it require heavy coding?
Click to expand...
 
Thanks mate, I think I'll just keep doing it manually. It's way above my head.

Allen Browne said:
Bill, what you describe makes perfect sense for many kinds of maintenance,
but the implementation is not as simple as you suggest.

The repeating fields that you describe (Completed1, Completed2, etc.) are an
absolute no-no for relational design. It must be done with a related table.

The nesting of services (e.g. the "B" service satisifes all aspects of the
"A" service also), so somehing you have to teach the database about. With
some kind of maintenance, these "services" need to actually be broken down
into components, e.g. a "B" service includes changing a component (such an
an oil pump), but if that component has already been changed, then this
doesn't need doing. So, there's a table that defines each component of each
service. For each component, there can also be multiple criteria, e.g.
"every 10000 miles, or every 200 engine hours, or every 3 months, which ever
comes first."

So, you end up with tables like this:
- ServiceType table (one record for each kind of service)
- Aspect table (one record for each component that makes up the serice)
- AspectFreq table (one record for each requirement of when an aspect of a
serivce must occur)
- ServiceAspect table (one record for each aspect covered by a service
type.)
- Vehicle table (one record for each thing that needs servicing)
- Job table (one record for each time a vehicle is maintained.)
- JobAspect table (one record for each aspect conducted in the job)

If Job has a foreign key to ServiceType, in can indicate what type of
service was conducted. In the form where you enter jobs, you can then
execute an Append query statement in the AfterInsert event of the form, to
add all the aspects in the ServiceAspect table into the JobApect table. The
user can then add, edit, or delete those items according to what was
actually done. (This also means the aspects are correctly maintained for
past records, even if you change what is normally covered in a service
later, by altering the ServiceAspect table.)

The actual code that determines what aspects require service next and when
is also interesting stuff to write. It requires a bit of fuzzy logic, based
on the recent runnning patterns of that vehicle to predict when it is likely
to clock up the distance or hours or date for that aspect, and then select
the mininum of the anticipates dates and return that.

HTH

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

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

Bill Neilsen said:
I have a form linked to a table for item servicings. There are 4 different
servicings at diferent frequencies, The Field names are: [Completed1]
(date),
[Period1](text), [Due1](date), [Completed2], [Period2], [Due2], etc. When
[Completed1] is updated [Due1] is calculated with the simple formula ...
Due1:[Completed1]+[Period1] ... and so on with the other servicings.
My problem is this
If a higher service is performed eg (Bay Service 2) this will affect the
due
date for Bay Service 1 because it is an in depth service that actually
incorporates the lower service. It may sound silly, however the effect is
that when a higher service is preformed the lower service is deemed to
have
been performed as well. This makes the completed date the same. So, when
completed2 date field is updated I need Completed1 updated but still be
able
to update the lower service myself and the subsequent calculation be
performed.
It must be possible, but can it be done by a simple boofhead like me? or
would it require heavy coding?
Click to expand...
Click to expand...
 
Back
Top