Table design help needed 2

[Dophia]

Hi Everyone:

I need help with the design of table(s) to track the health of shelter animals on a week by week basis for three weeks.

I have a table with the animal's ID number and other data. How would I add table(s) to record the data on a form which has the same criteria data on a three week basis? If the animal goes home, there may not be a 2nd week assessment.

For instance:

Admission: How is the animal's coat, teeth, etc.
Week 1: How is the animal's coat, teeth, etc
Week 2: How is the animal's coat, teeth, etc.

I also want to be able to call up which animal's should have a 2nd week assessment.

Initially, I set up 4 tables, but I cannot link them properly.
Table 1: tblAssessment
Table 2: tblAdmission_Assessment
Table 3: tblAssessment_Wk1
Table 4: tblAssessment_Wk2

Any guidance would be appreciated.

Sophia

 

[dhookom]

I think you are headed in the wrong direction. Consider downloading and reviewing the Employee Evaluation sample at

http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4.

Duane
Hook'D on Access
MS Access MVP

 

[fneily]

A must read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You would not have separate tables for assessments. Violates normalization.

Your questions are of the type "How is ...". Are the answers going to be descriptive, or is there a check list? Obviously matters to build the tables.

 

[Dophia]

Thanks for your advise. I looked at both suggestions, but what is confusing me is that the "assessemnt" is taken at three points in time....1) On admission, 2) After 1 week and 3) After 2 weeks. That is the reason that I was considering creating four tables, since I wanted them to be treated as seperate and not a continuation of the first one. At any time I want to see which assessment(s) have not yet been done and also to compare the assessments taken on a specific animal. Also, to alert staff that the assessment is due to be taken.

To answer Fneily,...there is a checklist of health factors which is taken each time. There is about 25 items that should be checked each time. So, to compare it to the Employee Evaluation db, it is more structured in the times that the assessments must be taken.

Any further advise would be appreciated.

Thanks, Sophia

 

[fneily]

How about this:
tblAnimal
AnimalID
Name
Street
City
Etc.

tblAssessment
AssessID
AnimalID
DateOfAssessment
AssessorName

tblCriteria
CriterID
AssessID
Ears
Paws
etc.

So tblCriteria contains your checklist items.
An animal is connected to tblAssessment which is connected to tblCriteris. One-to-many then one-to-many.

To see how many assessments were done on an animal, simply run a query to count AnimalID in tblAssessment.

tblCriteria is sort of a history table so you can compare various assessments against each other.

One main form, a subform with AnimalID has a dropdown, another subform off the first subform showing criteria.

Notice for tblcriteria I created criterID. Get it. Animal, critter? Database humor.

 

[dhookom]

I would create my normalized tables like:
[tt][blue]
tblAnimals
===============
aniAniID autonumber primary key
aniName
aniATyID links to tblAnimalTypes.atyATyID
aniAdmitDate

tblAnimalTypes
===============
atyATyID autonumber primary key
atyAnimalType (dog, cat, llama,...)

tblAssessAttributes
======================
asaAsAID autonumber primary key
asaAssessment (fur, teeth, eyes, claws, ...)

tblAniTypeAssess (links types to attributes)
========================
ataATAID autonumber primary key
ataATyID links to tblAnimalTypes.atyATyID
ataAsAID links to tblAssessAttributes.asaAsAID

tblAssessments
==================
asmAsmID autonumber primary key
asmAniID links to tblAnimals.aniAniID
asmAssessDate
asmAssessor

tblAssessmentDetails
===================
asdAsDID autonumber primary key
asdAsmID links to tblAssessments.asmAsmID
asdAsAID links to tblAssessAttributes.asaAsAID
asdEval stores results of assessment
[/blue][/tt]

I might even include a table of possible evaluations based on the assessment attribute.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

fneily,
Although I appreciate the humor of critters, not all critters would have the same criteria. I would try to avoid using Ears, Paws, etc as field names since not all critters have paws and I am not sure about ears. Also, while I doubt you would actually create a field named "Name", you might want to use "CritterName" ;-)

It might be that my structure is too normalized for some shelters but I wouldn't create this application any other way.

Duane
Hook'D on Access
MS Access MVP

 

[fneily]

Should we also consider which classification to use? The Carolus Linnaeus, Darwinian common descent, or Molecular systematic? eg. scales and feathers are the same on the gene level.

Yeah, Name shouldn't been changed. Oops.

 

[Dophia]

Thank you both for your great suggestions! I guess I was trying to skip a step and incorporate the timing of the assessments into the tables.

Any improvements for how the 2nd and 3rd assessment should be created?
For instance, when an animal is admitted, I can have the Admission Assessment linked to the admittance form. Then should I create a query which shows the due dates of the 2nd assessment, based on 7 days after the Admission Assessment? And then another query for the 3rd assessment, based on 7 days after the second assessment?

Thanks for your help,
Sophia

 

[dhookom]

A totals/group by query on tblAssessments and tblAnimals should be able to give you the count of assessments as well as the Max() date. You can then determine when the next assessment should be scheduled.

Duane
Hook'D on Access
MS Access MVP