calculate expiration dates in Access

0 Votes

calculate expiration dates in Access

I'm working with Access 2007. I'm working on a database used to store info about my pets health record. Every time my pet goes to the vet, I record what was done. Some dates 1 service was done, other dates several things were done. My problem is calculating the due dates for some services, like vaccinations. For example, if only a Rabies vaccine was given on 1/1/2007, I get the due date of 1/1/2008 using DateAdd which is correct but I also get a due date of 1/1/2008 for all the other services even though they were done on a different date. How can I get Access to pull the most recent date for a service and calculate the due date for that service only?
The form is laid out with a main form that has 3 subforms:
Main Form = frmPetInfoMain
Subform = sfrmMedicalInfo
Subform = sfrmExpiration
Subform = sfrmMedication

A date and service are entered in sfrmMedicalInfo. I want Access to take the most recent date a service was done, find how many months ahead to make the due date (listed in a separate table), and put the due date in sfrmExpiration.
Any suggestions would be appreciated.
Thanks, Charlotte
0 Votes
Tony Hopkinson
Collapse -

no way to tell from here.


If you had

ServiceName ServiceCompleted ServiceDue
Rabies 1/1/2007 1/1/2008
Diptheria 6/2/2007 6/2/2008

The only way they are all getting updated, is if you forgot Where ServiceName = 'Rabies'

Personally I'd change the design

ServiceID ServiceName ServiceFrequency
1 Rabies 12 (months)
2 Diptheria 6

PetID ServiceID ServiceDone

and then just calculate the expiry date, that way you don't have to go updating loads of stuff, if you go in a month later, or find out now it's more than once a year now.

Also if you make Serviced have a key of the above three fields, you get a real history instead of just one actual date.