Relational tables - again

[BarbT]

Having posted a couple of weeks ago, I have spent a while checking out YouTube videos of Access 2007 and now have a better idea of tables! However, I am still struggling with just what basic info to include in each one if I want to set up relationships.

I will have one database with about 6 tables in it:

1 table will contain student info - name, address, DoB etc.



Then there will be a table that show their fitness results and height, weight etc at 4 different times throughout the year - so I assume 4 tables for this?

Then 1 table for attendance at all the activity sessions and finally,

1 table for attendance at holiday activity session.

What I still don't really understand is what common info I need to include in each one, in order to set up relationships? I assume that I will have a 1 to many relationships: 1 student info linked to their many fitness measurements? Please can someone advise me just what I need to include in order to link the tables together.
It is perhaps not as difficult as it seems to me, but I cannot see where I am going with it at the moment.
Many thanks for what is proving to be a headache!

[RuralGuy]

Typically we would put the StudentID PrimaryKey field (hopefully an AutoNumber field) in the other tables as a ForeignKey field to establish the relationship.

[Access_Blaster]

Arvin Meyer has a student database here . It could serve as a model.

[ssanfu]

Having posted a couple of weeks ago, I have spent a while checking out YouTube videos of Access 2007 and now have a better idea of tables!

Also check out a tutorial by Crystal Long : http://www.accessmvp.com/Strive4Peace/

However, I am still struggling with just what basic info to include in each one if I want to set up relationships.

I will have one database with about 6 tables in it:

1 table will contain student info - name, address, DoB etc.

Then there will be a table that show their fitness results and height, weight etc at 4 different times throughout the year - so I assume 4 tables for this?

Then 1 table for attendance at all the activity sessions and finally,

1 table for attendance at holiday activity session.

What I still don't really understand is what common info I need to include in each one, in order to set up relationships? I assume that I will have a 1 to many relationships: 1 student info linked to their many fitness measurements? Please can someone advise me just what I need to include in order to link the tables together.
It is perhaps not as difficult as it seems to me, but I cannot see where I am going with it at the moment.
Many thanks for what is proving to be a headache!

Not knowing what data you are trying to capture, I would start out with something like:

table = STUDENTS

Field Name - Type
------------------------------
StudentID - Autonumber (PK)
LName - Text
FName - Text
Address1 - Text
Address2 - Text
City - Text
State - Text
Zip - Text
DOB - Date/time
etc

table = FITNESS
Field Name - Type
------------------------------
FitnessID - Autonumber
StudentID_FK - Long (foreign key to student)
FitnessDate - Date/Time
Height - Single
Weight - Single
etc


table = ATTENDANCE
Field Name - Type
------------------------------
AttendanceID - Autonumber
StudentID_FK - Long (foreign key to student tbl)
SessionType_FK - Long (foreign key to session tbl)
AttendDate - Date/Time
Note - Text (or Memo type)
etc

table = SESSIONS
Field Name - Type
------------------------------
SessionID - Autonumber
Description - Text

[BarbT]

Thank you to you all for your help. It has given me a much better idea of how to go about this and I now need to get on with it! I need to look at foreign keys now as I am not quite sure about those - I may be back! You are all doing a great job so a very big thank you again.

[RuralGuy]

Barb,
Do you want to follow the link in my sig and mark this thread as Solved to close it out?

[BarbT]

Thanks to all.