Just can't figure it out (First Form w/ combos)

[rcfreed]

Hello, i'm pretty new to access. I've pretty much only used the pre-built forms and templates and finally decided to try to create one on my own. Like a first girlfriend, I just cant figure out what to do.

I'm basically making a Workout Logging database. Each workout consists of 6 exercise types, each exercise type consists of 6 exercises. I want to track a complete workout from one form. So far I have the following tables,

tblExerciseType
ID - PK
ExerciseType (Shoulders, Arms, etc..)

tblExerciseNames
ID
ExerciseName
ExerciseType_ID - FK

They are related one-to-many as many exercises fall under each type.

I'm trying to make a table to track all of. Right now it's

tblWorkout
ID
Date
Workout Type
Exercise Performed
Weight1
Rep1
Weight2
Rep2
Weight3
Rep3
Weight4
Rep4

I created a form with a combo-box to select the workout type. On the on change event a list box is updated with 6 randomly selected exercises that fall under the selection made in the type combo box.

What i'm trying to do is then make a form so I can enter the weigh and reps per each exercise but it seems I can only do one at a time.

I'm sure there may be a better way to organize the database but I just cant visualize it. Can someone either help me with the form or a think up a better way to do this.

 

[plog]

When you start numerating field names, its a sign that you need a new table. Instead of Weight1, Rep1, etc, you need to create a new table, I suggest WorkoutExcercises. It would look like this:

WorkoutExcercises
WorkoutID - Long integer, foreign key to tblWorkout
ExerciseID - Long integer, foreign key to tblExerciseNames
ExerciseWeight
ExerciseRep
ExerciseOrder

Then you remove all the fields from tblWorkout that have numbers after them and put them in that table. If the number is significant (e.g. it determines the order in which they should be performed) then you store it in the ExerciseOrder field. WorkoutExercises will have a many-to-one relationship with tblWorkout and tblExerciseNames.

Your form would be based on tblWorkout and have a continous subform based on WorkoutExercises.

Additionally, 'Date' is a poor choice for a field name--its a reserve word and will cause issues when writing code on donw the line. I suggest renaming it like 'WorkoutDate'.

 

[mikejaytlabustro]

Check this one (Attached Database 2007)