Creating Tables in Access 2007

[leighkhonig]

I'm currently taking an Access DB course and I need to set up a db from scratch. I am a little confused and overwhelmed as to how to set up the different tables. My database ideas was to base it off of what information is gathered at a Fish and Game office for wildlife. I have two tables so far:


Table 1: has all the demographic information and the person id is the primary key
Table 2: has hunt trips, including ticket number, if successful, male or female etc.
Now I'm not sure how to set up the other tables and to have them link successfully. For table 3, I was thinking of having the measurements if a successful kill for sheep, moose, or goat. My question is what would be my foreign or primary key to link the information up to the hunter and the ticket number?

Thank you so much for any help!

[June7]

table of Hunters
HunterID (primary key)
Name
Address
etc

table of Tickets
TicketNo (primary key)
HunterID (foreign key)
Species
Can include fields for measurements in this table, this would mean blank fields if no kill for a ticket, but makes data entry simple. Otherwise have another table that will have record only if kill for the ticket. The foreign key will be the TicketNo.

Concerned with only the 3 species? Need to consider how to structure the measurement fields. Not all the species have the same measurement data. Besides height and weight, there are measurements for the horns. Moose have rack spread/points/palm width & length/circumference, sheep (Dall) has spread/curl/4 circumferences, goat has spread/length/4 circumferences. How much info do you want to record?

One solution for kill data is multiple records:
table of Measurements
TicketID (foreign key)
Measurement (number)
Type (sex, height, weight, spread, points, curl, length, circumference)
Side (left, right) will be blank for the records for height, weight, sex - no system is perfect
Alternative would be number field for each type of measurement. Would mean a lot of blank fields because not every measurement applies to all species but again easier for data entry. Also calculations, such as a height to weight ratio, would be easier.