Questions

calculate expiration dates in Access

Tags:
+
0 Votes
Locked

calculate expiration dates in Access

chocolatte2
Hi,
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

    no way to tell from here.

    but

    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

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

    Serviced
    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.

    HtHs

  • +
    0 Votes
    Tony Hopkinson

    no way to tell from here.

    but

    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

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

    Serviced
    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.

    HtHs