If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Updating date fields
I have a form linked to a table for item servicings. There are 4 different
servicings at diferent frequencies, The Field names a [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? |
#2
|
|||
|
|||
Updating date fields
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Bill Neilsen" wrote in message ... I have a form linked to a table for item servicings. There are 4 different servicings at diferent frequencies, The Field names a [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? |
#3
|
|||
|
|||
Updating date fields
Thanks mate, I think I'll just keep doing it manually. It's way above my head.
"Allen Browne" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Bill Neilsen" wrote in message ... I have a form linked to a table for item servicings. There are 4 different servicings at diferent frequencies, The Field names a [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? |
Thread Tools | |
Display Modes | |
|
|