I am trying to do a database for a rescue groups adoptions. I know I'm doing something wrong:
We have 3 tables: Adoptions, Animals, and People.
The people just has names with contact information.
The animals has the animals info: name, age, color, breed, all that.
The adoptions has info on who adopted and surrendered the animal.
What I wanted to do was create a query for finished adoptions and unfinished. Since some people adopt more than one animal, I have in the adoptions animal1, animal2, animal3, and animal4.
I wanted to be able to do reports or queries with who adopted or surrendered along with names of animals and ages, sex, color. But I can't add the sex or color - the query just doesn't work then.
I don't know how to get it to work. I basically want it to reference the animals' record and pull the age/color/sex for the right animal....but I'm completely lost as to how to do this if I have it set up the way it is.
First table fields:
AdoptionID (primary key)
Animal1
Animal2
Animal3
Animal4 (these all refer to the animal table)
Adopter
Source (these both refer to the people table)
DateOfAdoption
FinishedAdoption (checkmark y/n)
2nd Table fields:
Person (primary key)
CellPhone
HomePhone
WorkPhone
Address
City
State
Zipcode
3rd Table fields:
Animal
Age
Sex
Color
LikesOthers
LikesDogs
LikesCats
Food
ComesWithCage
LitterTrained
MedicalCondition
Vaccinated
VaxDate
If anyone can help me I'd be very grateful!