Exercise app

[JoeCruse]

I'm posting this in the Sample Database Section, so that anyone that wants to use/modify this for their own purposes can. Note that on open, the main page has 3 labels that act like web page links. These are labels that I've coded to act like command buttons, and to look like Internet links. I've put together what code is in this with help from this website and a couple of others, and if I did not reference it in the code, I'll state here that I had a LOT of help with it by using these forums.

There is a form for weight lifting workouts. If you're using this for tracking gym clients, you'll need to add to the underlying main query of this form. It will need gym client field(s) or something, so that in the workout form, you can set up a combo box to choose a gym client. I set up 2 sychronized subforms on this main workout form. One keeps the exercises used, using a combobox list to choose exercises. I left my list in this, and it is set up to let you add new exercises to the list through code on the "Not IN List" property of the combobox. The other subform is linked to the exercises subform. In it, you put sets, reps, and weight used. To do this for each workout, click on an exercise in the exercise subform, and then either "Alt-Tab" to go to the sets/reps subform or just click directly on the sets/reps subform. Fill in the data for the first exercise. To record the data for the next exercise, go back to the exercises subform and click on the next esercise you recorded. The sets/reps subform "should go blank, and is ready for you to enter the data on the next exercise. You may not like this format for tracking this stuff, but I really liked doing it this way for tracking progress on weightlifting. It will be fairly easy to make queries for charting reports, if you want to visually track progress.

There is a Cardio Form that can be opened from the main page. It's pretty simple, actually, and I'm going to try to set up something better later on, but this WILL capture the basics for you. Again, you'll need to edit it to let you enter the client identification for each recorded cardio workout, if you're using this for gym clients.

Change the colors up or whatever you want to do. I pulled out all data except for a list of exercises, and pulled out linked images to keep me or this board from getting into trouble over using images taken off a couple of lifting equipment web sites. I hope it's useful to someone. If you can improve it, do me a favor and let me know what you did; I might like it and change my version

 

[DCXtreme]

Hi there, thanks so much for this again.

How would I go about getting the form to sort the workouts by membership number, is it as simple as adding MembershipNo as a forgien key in the workout table?

I've editied the rest of the form to get what I need from it, I have an add exercises form and so on, its just the membershipNo thing I am having problems with, any advice?

 

[JoeCruse]

I would think so. The main w/o table, right now, only has the PK (autonumber), date of workout, and details. Add in the gym member from your Membership table, as an FK. Add that to the main w/o form's underling query. In the main w/o form, add a combo box, and makes its ROW source a query based on the Membership table. It will only store the number field, but will show, in your combo box, the member's name. You would be able to do a simple search on this combo box anytime you need. It should also be pretty easy to build queries and reports that show a members progress in the gym. You can make the form sort by member by doing the sorting in the form's underlying query. If it were me, I'd probably leave it to sort by date, and then just filter for the member, if you wanted to have a quick look on a member without pulling a report.

Same thing on the Cardio form.

By the way, I put a datasheet form in there to look at and add to the list of exercises, but I neglected to put a button or link on the other forms for it. There IS an "Add an exercise" form in there for the times you try to type on in the workout form, and it's not in the list. It comes up by the "NotInList" property of that combobox. I tested it, and it seemed to work well.

Good luck with this AND with the gym business.

 

[DCXtreme]

Thanks very much, starting my own business has been stressful to say the least, I am starting to regret cutting corners by desgning this datbase.

I have set up the MembershipNo as a FKEY and added it to the query also, I've set "Customer" table to be the control source and have said to store the data in the "membershipNo" fkey field.

Seems to be working it records the membershipNo now in the table, I just need to find out a way to sort by member easily (I have other people using this in the gym)

Any ideas?

thanks again.

 

[JoeCruse]

If you want the Workout form to be sorted by member, have that done in the underlying query by telling the member field to order either ascending or descending. You CAN order by in the form itself, by clicking on the field and picking the sort by option you want from the toolbar.

I would rather let the form be sorted by its PK (invisible in the form). You can "Filter by Form" in the form itself, if members want to see history. If it were me, I'd make a report that, when a member is given at the prompt, shows a workout history of some kind. I'm going to set something like that up for me at some point.

Right now, if I want to see what I need to be doing for today's workout, I scroll backwards to the previous like workout. For you to do this for a member, filter by form on the member field, and then scroll the the workout you want to see.

If I ever get around to setting up some graphs for showing progress, I'll add them to this.

 

[DCXtreme]

I have been able to get a workout to be linked to a member number.

I have a question for you Joe if I may be so bold..

I want to be able to add cardio workouts too, and also select these on the workout form, the only problem is instead of sets and reps I would need time / distance / intensity. Any clue how I could set this up?

I also need a query which will allow the user of the database to bring up a report showing all the workouts for a user within a 2 week period from the day the query is run, is this possible?

Thanks again.

Jon

 

[JoeCruse]

Hi Jon! I hope your new gym is doing well. I imagine you're pretty busy.

I would copy the 2 tables that hold the weight exercises performed and the sets/weight/reps and paste them as blank tables, renaming them for Cardio exercises and Cardio data. I probably would not rework the sets/weight/reps table, because a lot of people do a cardio session with the time/distance/intensity setup you are looking for. If you had folks doing HIIT style cardio, where they are almost performing sets of varying intensity, the current format would work though.

I would probably just copy the 2 tables, as I noted, though. Relate them with the main w/o table, just like weight training tables are. You could add a field to the main table that stores whether the workout is weights or cardio. Have a control on the workout form that you choose either weights or cardio. Depending on which workout is selected, the subforms to enter the workout data become visible. Or you can have a second workout form for cardio that is a copy of the weights form, but has the 2 subforms for cardio instead of weights. The main table underneath both the weights and cardio main form could still be the main w/o table. You would need to add all your cardio exercises into your exercise table, which is no big deal to do. I would keep only one main table to log in any workouts, and use a field in the table to designate weights or cardio (or yoga or martial arts, if you branch out later). If you use 2 separate forms, you can have the On Open event of the form to tell the table to store the record as weights or cardio, and not have to manually tell it.

It's definitely possible to make a query showing your gym rats a history of their workouts. You can make the history as long as you want. Start by adding the main w/o table to the query, and then the exercise used table and sets/reps/weights table. Pick what fields you need in the report, and set your criteria in the date field for how far back you wnat to report. How you set this up depends on how much you want to show or what you want to emphasize in the data. You'll have to play with this to get exactly what you want your people to see. You can also build charts from the queries, to give them visual signs of their progress, or lack of. This would work for both weights and cardio.

 

[DCXtreme]

What I wanted to do in that aspect you talked about last was print out a 2 week plan for a customer if they wanted me to set one up for them, obviously the exercises and weights would get heavier as the weeks progress, this could then be printed off and kept by them for reference.

As for everything else, you explained it very clearly! I will give it a go when I get some free time after work tonight, I really need to employ some more staff!

 

[JoeCruse]

Jon,

Okay, you want a projected workout plan for 2 weeks for a gym member, detailing a setup of exercises, sets, reps, and weight.

Hmmm, that's a bit complicated. Say you scheduled a person to do squats/presses/calf raises/leg raises in workout A and deadlifts/rows/chins/crunches in workout B, and had them do this as A on Monday, B on Thursday. You "might" have them increase 10lb each week on squats and deadlifts, but probably not on presses, as that would be a bigger % weight increase for a much smaller muscle group. I assume you'd tell Access where to look in their log to base the next 2 weeks off of. You'd have to make some FUNKY code to calculate expected weight increases for workouts. OR, you might tell Access to use a % increase by doing this in a query. That "might" work.
I'll have to think on this one. For now, you could go with a blank form, from Word or Excel, that has a blank table that you fill in with exercises and expected weights to be used.

Are you going to base the 2 week projection on a %-base increase from current levels, if you had to do it by hand? In other words, if I'm squatting 315LB (I wish!), you might project me to increase the weight by 5% at each subsequent workout. I've never dealt with it this way. I usually look at the previous w/o and decide what I'll do today based on the poundage lifted last time, and the notes on how the last workout went.

 

[DCXtreme]

I wouldnt be looking at code to do that really.

I would just do a query to tell access to pull out all workouts for a certain member within a 2 week period (i kept the date field in).

So basically the % thing would not be needed, I may just print out weekly ones which they pick up monday, and judging by their performance on the past week create 3 new workouts for them with increased intensity to see how they do.

I'm actually having some trouble incorporating Cardio into the workout form, I've created a table for it and a query, but i'm having some problems getting my head round the logistics of it, I have added a exercisetype field to exercises table to split between cardio and strength too.

I've attached the database, if you have time it'd be great if you could give me your thoughts on it.

 

[DCXtreme]

Have people just been stealing my exercise database?

14 views and not one reply!

Joe I could really do with your help here I think I've messed things up big time, attached my *attempt* at a cardio/strength form.

 

[JoeCruse]

Jon,

lucky for you, today at work was a bit slow. Gives me a little time to practice Access skills .

I've attached an example of what I was thinking about, to incorporate cardio into the form. I put another subform on the workout form that can be used for cardio work. I renamed a table and some fields to help hold this line of thought (i.e. workoutexerciseID instead of workoutliftID). The 2 subforms for capturing all workout data are linked to the subform for listing exercises (no longer just lifts) for a workout event. This minimizes the forms needed. The Time field in the new subform is a Long Integer field, which is meant for you to enter elapsed times in total seconds, with 2 decimal places. There is no special Access designation to keep time in a table in a mm:ss:hundredths of a second format. Do a search on "milliseconds" and you'll find some info on this. This means a little work for you to convert minutes to total seconds, but...
I'll probably put this into my own tracker at home.

I looked at your other stuff. Your Health table and Measurement table are both not normalized. You've set them up like a spreadsheet. Turn them both into tables that have a normal structure.

Good luck!

ps. where is your gym?

 

[DCXtreme]

THanks again Joe thats great! exactly what I wanted, unfortunatly I cant seem to attach it to a membership number now like I did before, this is my FINAL problem, honest! then I can put it to use starting this weekend.

The gym is in pendine, west wales!

Attached my "attempt" at incorporating membership

 

[JoeCruse]

Jon,

sounds like you're a bit far to go get in a workout for me .
The reason the member field didn't work was because something was screwy with the query. I changed the query, and left out the Member table; you don't nned it here. I also changed your Member table. You really need the member number to be autonumber. Saves you having to make one up. I had the member field (combo box) on the form use a row source that gives you the member's full name, sorted by last name. If you end up getting members with the exact same names, you can always add the actual ID number in the concatenation of the name, to give you a visual demarcation between the members of same name, on the form.

See what you think.

 

[DCXtreme]

Thanks again Joe!

I am having the worst kind of trouble trying to get a report on the workouts for a certain member, it just shows workouts for exeryone, and only shows the first exercise.

Any ideas anyone, the query is called tblWorkoutQuery

 

[JoeCruse]

Jon,

let's take this out of this thread. I should have already done so. This thread is really only meant to post people's sample apps, not go into lengthy discussion on them. I'll post elsewhere. Apologies to the mods.

 

[Mickster]

Thanks again Joe!

I am having the worst kind of trouble trying to get a report on the workouts for a certain member, it just shows workouts for exeryone, and only shows the first exercise.

Any ideas anyone, the query is called tblWorkoutQuery

Could you possibly post this using Access 97. That is the only version I have running.

Thanks

 

[JoeCruse]

Mickster,

here it is in 97. I converted it with Database Tools in Access 2002. It warned me that there are some references that will have to be checked on in the Modules section, to make sure they are all accounted for , for the code this app is running. I don't have 97 version of Access, so you are on your own in getting this to run properly in your Access version, unless someone here can help you.

Good luck, and I hope it works well for you.

 

[Mickster]

Mickster,

here it is in 97. I converted it with Database Tools in Access 2002. It warned me that there are some references that will have to be checked on in the Modules section, to make sure they are all accounted for , for the code this app is running. I don't have 97 version of Access, so you are on your own in getting this to run properly in your Access version, unless someone here can help you.

Good luck, and I hope it works well for you.

Thanks Joe