Discuss "Exercise Database" in the Microsoft Access forum.

Hello,

I am an Access newbie and this is my first Access post. I bought a couple of Access books a couple of months ago and have been reading through them. Access is an awesome database tool and I am looking forward to learning as much as I can about it.

Well, I thought I'd start with a database that was very easy and decided to create a database to log my workouts... but it seems that the books make Access appear much easier than it really is... Can someone look at my database attached to this post and tell me what they think of my table structure and relations and also help me figure out how to get the main table to appear on the top level of the form and subform I have created? I would also like to know how to prevent the Exercise form (not the subform) I am using from creating a new entry for an item if it is already in the table?

Attached Files

Exercise.zip (21.5 KB, 10 views)

Ok, so what are you wanting to happen here? It appears that the exercise is standard and that the sets are related to the exercises....

Are you wanting to open the form, it gives you a start date and start time, then you choose what exercise, and how many reps / weight?

I guess the question is, how do you want to log this?

I would like to open the form and type in the date time... etc from the tblMain, then select an exercise, then type in the set number, reps and weight used for every set I do for that exercise. Then when finished with that exercise, move on to another and input the sets, reps, weight for that one.

Right now I can't figure out how to get all these forms into one form correctly and I cannot keep the tblExercises from adding a new record for the same exercise if it is already in the table.

ok, do you want to enter in the number of reps and weights used manually? For example:
Actual Reps - weight
8 reps - 113 lbs
21 reps - 61 lbs
38 reps - 33 lbs

Or do you want that predetermined? For Example:
Rep Heavy - 10 reps - 100 lbs
Rep Med - 20 reps - 50 lbs
Rep Light - 40 reps - 25 lbs

Yes, I would like to enter the Sets, Reps and Weight manually because this always changes for me.

Hi,..

Welcome to the forums,..

My take on this is actually quite simple,.. the only relationship that needs to be directly set is the exercise id to the daily routines,

tbl_exercise
ExId
Exercise

tbl_fixed_sets
SetID
Reps
WeightUsed

tbl_main_routine
ID - relationship to EXid
Other fields,
RoutineSelected - relationship via combo to SetID

Now i've tried to include a few things in the sample for you,. queries, forms, macros and modules,. to have a look through.

Now you'll notice i've based all the forms on query's,. this is standard practice as tables hold no order,.

You can enter to exercises directly through the combo,. you will be prompted as to whether you wish to add a new one. The fixed sets is simply unbown on the main form. Its only purpose is visual.

Now also,. you wouldn't usually show a primary key auto number on a form, but for the sake of this "exercise",. i've left it on as i have no descripton on the fixed set names.

Edit,.. upload corrected

Attached Files

exercise.zip (44.9 KB, 11 views)

__________________
If i helped you, make me famous by clicking the

Thanks AOG123!

I am really going to study this example and see all the cool stuff I can learn from it. I would like the Main table with the date and time information in it to be the top form with the exercise selection underneath and come up clear when I select a new date and time. I would then like the set/rep form underneath Exercise and it to be empty when I select a new Exercise.

So let's say I do 3 sets of Bench press then I'll have:

Bench Press
Set1 Reps 10 Weight Used 200
Set2 Reps 7 Weight Used 220
Set3 Reps 5 Weight Used 250

Then I move onto Shoulder Press

Shoulder Press
Set1 Reps 10 Weight 60
Set 2 Reps 8 Weight 70
Set 3 Reps 7 Weight 80

So my sets will repeat with every different exercise everyday. I usually do 3 to 5 sets of one exercise then move onto another exercise for a total for 3 to 5 different exercises a workout and one workout per day. This is why my set ID can't be a primary key unless I put it in its own table, right? Also the exercise ID can't be the primary of the main table or I will be limited to the amount of dates I can use, correct? I am a little confused I guess being a newbie and all. Thanks

Have a look through,.. and see if you encounter any limitations,.

In regards to the set id being a primary key,. it can and really should be. Noticed how i called the "tbl_fixed_sets" and the set id is a primary.

This stops duplicate sets being entered in the fixed sets table.

But,.. it doesn't stop multiples of this id being entered into the tbl_main_routine.

If this does't help,.. come back,.. and we'll look at a work around.

Heh, I had one ready to post as well.... may as well post it too....

Here is the sample I had:

Attached Files

exerciseII.zip (22.8 KB, 12 views)

@sbenj,.. nice,.

Jcrisf,

You have allot of things to review here,.. please note the handy feature for dblclick on times.

With all the information you've received i urge you to look into how the samples were set up.

Thanks and best regards,