Animal Shelter Medical tracking/scheduling help

[chronister]

Hello all,



Hope everyone is doing well and I am hoping one of you kind souls can help me get moving in the right direction on this.

I am creating a Shelter Manager for a local humane society I volunteer at (so no money is involved here). I have a nice ms access system I have built that has animals (lots of data), volunteers, adopters, medical records, a do not adopt list, reports and more and all is going well and I can track pretty well everything that happens from the intake of an animal, to going out the door (adoption, rescue, reclaim etc)

I have a table of Medical treatments (fields are: [Treatment Type Code], [Treatment Name], [Treatment Description] ) that are used right now to simply record in another table when a treatment is completed and is stored as basically a [Datefield], [animalID], [Treatment Type Code], [Notes].

I am trying to wrap my head around the following problem / task. These procedures / treatments will need to be scheduled from time to time. E.g. Flea treatments need to be scheduled every 30 days for dogs and cats (set 1 time and is perpetual), all dogs get specific shots within 7 days of their intake date or maybe a specific animal needs to be assigned medicines XY and Z 3 times per day for 7 days.

I kind of have an idea of how to break things down to be able to store them in the DB, and then create VBA logic on them, but I am not able to truly wrap my brain around it and come up with a DB structure that I can then use to create logic on.

I figure it will be [scheduleProcedureID] (PK), [Treatment Type Code] (FK), [target] (animalID, cats, dogs, all) but from there I cannot figure out whether to store a frequency, a repeat pattern a duration or what combination it would be. Should I maybe store it as [perDay], [repeatFrequency], [totalTreatments] (continuous / fixed num).

I am sure I an come up with some VBA to determine who needs what once I have the schedule setup. I want them to be able to (as non computer folks) add a treatment, set it's time/frequency/duration, and assign it to either all cats, all dogs, all animals. Some things are going to be a specific 1 time (or multi-time) treatment and others are just in the schedule. e.g. flea treatments, heartworm protections, all dogs coming in must get parvo and bordetella immediately. Cats must receive med1 and med2 right away (if they meet qualifications X and Y) and then again 2 weeks later.

So that is my dilemma. I am not asking anyone to do it for me, and it is a complicated (in my opinion) project. I am proficient at MS access and am new to VBA, but am rocking and rolling creating custom code. I understand how things work, just may not be the most optimum.

Thanks in advance to all who take time to read and offer advice.

Nate

[June7]

Do you want all animals to have the same frequency for the same procedure?

If yes, then have a field in the Treatments table for the frequency (number) and another field for unit (monthly, yearly, etc) unless you want to calculate every frequency in the same unit.

[chronister]

Some treatments are going to be scheduled for every dog or cat that comes in. Others will be scheduled as needed. I need to be able to store flexibly, the information.

One schedule may be
Flea treatment - one time Every 30 days continually - Applies to all dogs (another for cats... several in this format)

and then turn around and schedule

Coccidia Treatment - twice daily for 7 days - applies to animalID 43 (Spot)

XXX Treatment - 1 time repeated every 14 days for 2 total treatments (related to intake date (when the animal got to the shelter)) applies to all cats


So essentially it needs to have a variable daily dosage (1 time, twice etc), repeat frequency (daily, every X days/weeks/), total treatments (not required in all situations).

To give a little bg and detail, I have an animals table, which cross references a medical procedures table. This medical procedures table pulls from the Medical treatments table (list of treatments / procedures the shelter does). I aim to store what procedures are being done to the animal (done) and set schedule rules to treatments and allow the system to give a report as to what animal is due for what treatment based on the schedule. It will hold base schedules that apply to all animals (flea treatments, heartworm prevention) and are perpetual. It will also hold specific schedules if one animal is on a new medicine regimen.

I will eventually create VBA logic to cross reference the animals medical history (what has been done), and the med schedule (trying to create it now) and determine what animals are due for treatments. I hope this makes sense. I am not sure how to make it generic enough to work with all types of scheduling.

Nate

[June7]

Quite a dilemma. There would be so many combinations of standard treatments and individualized treatments. Every time you needed to set up a new individualized treatment would have to create a record in tblTreatments and select that record ID for saving in junction table that relates animals with treatments.