Relating One Field in a Table to Multiple Fields in another Table

[Shizbart]

MS Access 97
Beginner/Moderate Level User

I am trying to create a Database to track Workouts in MS Access 97.

I have one Table named Workouts that contains the following Fields: Workout Code (e.g: U100), WorkoutActivity (e.g.: Bench Press: Flat Bench Straightbar) (note: this is the Primary Key of this Table), and Target Area (e.g.: Upper Body / Chest).

A second Table named Workout_Log contains the following Fields: Date, Set, WorkoutActivity, WorkoutActivity2..., Reps, and Weight..

I have created a Relationship between Table: Workouts: WorkoutActivity and Table: Workout_Log: WorkoutActivity, WorkoutActivity2. I've done this with the intent to automatically fill the Workout Code and the Target Area fields (derived from Table: Workouts) in on a new Form I'm creating relative to multiple WorkoutActivities (i.e.: WorkoutActivity, WorkoutActivity2...). The Form is being used to log multiple Workout Activities into a single Workout Session.

I have been successful in making the Form automatically popuate the Workout Code and Target Area fields based on selection from the WorkoutActivity field, but I am confused as to how to make the additional WorkoutActivity2 fields automatically drive the contents of additional Workout Code and Target Area fields.

Example:

Date: 09/19/06
Session: AM

Set Workout Code Workout Activity Target Area Reps Weight
1 U100 Bench Press Chest 8 180
2 <pending selection>

How do I cause the Workout Code & Target Area on this Form for Line #2 to automatically populate with "U101" and "Upper Chest" if I were to select "Bench Press: Incline" from the pending selection drop down box illustrated above? The way I have the Form set up now, the drop down does not trigger an autofill.

Thank you,
Shizbart

[jkantner]

MS Access 97
Beginner/Moderate Level User

I am trying to create a Database to track Workouts in MS Access 97.

I have one Table named Workouts that contains the following Fields: Workout Code (e.g: U100), WorkoutActivity (e.g.: Bench Press: Flat Bench Straightbar) (note: this is the Primary Key of this Table), and Target Area (e.g.: Upper Body / Chest).

A second Table named Workout_Log contains the following Fields: Date, Set, WorkoutActivity, WorkoutActivity2..., Reps, and Weight..

I have created a Relationship between Table: Workouts: WorkoutActivity and Table: Workout_Log: WorkoutActivity, WorkoutActivity2. I've done this with the intent to automatically fill the Workout Code and the Target Area fields (derived from Table: Workouts) in on a new Form I'm creating relative to multiple WorkoutActivities (i.e.: WorkoutActivity, WorkoutActivity2...). The Form is being used to log multiple Workout Activities into a single Workout Session.

I have been successful in making the Form automatically popuate the Workout Code and Target Area fields based on selection from the WorkoutActivity field, but I am confused as to how to make the additional WorkoutActivity2 fields automatically drive the contents of additional Workout Code and Target Area fields.

Example:

Date: 09/19/06
Session: AM

Set Workout Code Workout Activity Target Area Reps Weight
1 U100 Bench Press Chest 8 180
2 <pending selection>

How do I cause the Workout Code & Target Area on this Form for Line #2 to automatically populate with "U101" and "Upper Chest" if I were to select "Bench Press: Incline" from the pending selection drop down box illustrated above? The way I have the Form set up now, the drop down does not trigger an autofill.

Thank you,
Shizbart

-------------------------------------
You don't need the field work activity 2.
For each activity, you just create a new record. The date and set are enough to make the information unique. You can then report your acitivy based on grouping by date or set. If you have someone that does an activity more than once a day, you may want to also include a field for session number.
If you plan to use this log for different people, you will need another table with a list of the people and a unique id for each one. Just to start numbering them at one would work, or Access's field type 'autonumber' will ensure you don't use the same number over again.
Your acitiviy_log table will then also need to include the unique id of the person whose acitivity you are recording.
To maintain the activity_log, you would need a form and subform. The controlling form would be the people table and the subform would be the activity_log.
Microsoft Access web site has more info on how to create a form with a subform: http://office.microsoft.com/en-us/assistance/HP051878031033.aspx

jkantner