help with tables

(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name, address etc.
Exercises Table...ExerciseID(pk), exercise name, description etc
Workout Table...WorkoutID(pk), ClientID, ExerciseID, workout date, Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client (combo box), type
in the workout date, then create a workout. I want to save this workout
specific to the client name. Where do i begin?

Thanks!

Create a form bound to the Client table, where you can enter the client's
information.

Create a form (to become a subform), in Continuous Form view, with a combo
box for the ExerciseID, a text box for the date, etc. Cut the labels from
the controls, and put them into the Form Header section. Then arrange the
boxes side-by-side, so the whole detail section is only about 0.2" tall. Set
the form's Default View to Continuous form. Save.

Now add this as a subform on your continuous form. The LinkMasterFields and
LinkChildFields properties will be set to ClientID. Consequently, the
subform shows the records relevant to the client in the main form, and you
can add as many rows as you want for that client.

So you can jump to a particular client's record, use the combo wizard to add
another unbound box to the top of your main form for selecting the client.
Alternatively, use the code in this link:
http://allenbrowne.com/ser-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LTOSH" wrote in message
...
(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name, address etc.
Exercises Table...ExerciseID(pk), exercise name, description etc
Workout Table...WorkoutID(pk), ClientID, ExerciseID, workout date,
Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client (combo box),
type
in the workout date, then create a workout. I want to save this workout
specific to the client name. Where do i begin?

Thanks!

a note on your tables design: recommend you remove the Exercise Name field
from the Workout table. that value is already stored in the Exercises table,
which is linked to the Workout table by the ExerciseID foreign key field, so
storing it again violates normalization rules. and if the Description field
in the Workout table is the same as the Description field in the Exercises
table, then remove it from the Workout table too, for the same reason. and,
btw, recommend you remove any spaces from your fieldnames - Exercise name
should be ExerciseName; for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

once you've made the correction(s) above, suggest the following for your
data entry forms: create a form bound to the Client table, SingleForm view,
i'll call it frmClients. create another form bound to the Workout table,
Datasheet view, i'll call it sfrmWorkouts. in sfrmWorkouts, add a combobox
control, and set the ControlSource to ExerciseID; set the RowSource to a SQL
statement, as

SELECT ExerciseID, ExerciseName FROM Exercises;

set the following combobox properties, as

ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
LimitToList: Yes

when you look at the combobox droplist in Form view (Datasheet view, in this
case), you'll see only the exercise name, but when you pick an exercise from
the list, the ExerciseID will be saved in the field in the Workout table, as
it should be.

add sfrmWorkouts to the frmClients, as a subform. set the subform's
LinkChildFields property to ClientID (which refers to the foreign key field
in the Workout table), and set the LinkMasterFields property to ClientID
(which refers to the primary key field in the Client table).

so now you can open the mainform (frmClients), and add a new client record
or go to an existing client record, and then - in the subform - choose an
exercise from the combobox droplist and enter the date, sets, reps, and
weight; enter as many exercise records as you want for each client.

hth


"LTOSH" wrote in message
...
(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name, address etc.
Exercises Table...ExerciseID(pk), exercise name, description etc
Workout Table...WorkoutID(pk), ClientID, ExerciseID, workout date,
Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client (combo box),
type
in the workout date, then create a workout. I want to save this workout
specific to the client name. Where do i begin?

Thanks!

thanks allen and tina for guidance on my tables...i will get to work on them
probably tomorrow. i may be bck for further help.
thanks again!!

"tina" wrote:

a note on your tables design: recommend you remove the Exercise Name field
from the Workout table. that value is already stored in the Exercises table,
which is linked to the Workout table by the ExerciseID foreign key field, so
storing it again violates normalization rules. and if the Description field
in the Workout table is the same as the Description field in the Exercises
table, then remove it from the Workout table too, for the same reason. and,
btw, recommend you remove any spaces from your fieldnames - Exercise name
should be ExerciseName; for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

once you've made the correction(s) above, suggest the following for your
data entry forms: create a form bound to the Client table, SingleForm view,
i'll call it frmClients. create another form bound to the Workout table,
Datasheet view, i'll call it sfrmWorkouts. in sfrmWorkouts, add a combobox
control, and set the ControlSource to ExerciseID; set the RowSource to a SQL
statement, as

SELECT ExerciseID, ExerciseName FROM Exercises;

set the following combobox properties, as

ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
LimitToList: Yes

when you look at the combobox droplist in Form view (Datasheet view, in this
case), you'll see only the exercise name, but when you pick an exercise from
the list, the ExerciseID will be saved in the field in the Workout table, as
it should be.

add sfrmWorkouts to the frmClients, as a subform. set the subform's
LinkChildFields property to ClientID (which refers to the foreign key field
in the Workout table), and set the LinkMasterFields property to ClientID
(which refers to the primary key field in the Client table).

so now you can open the mainform (frmClients), and add a new client record
or go to an existing client record, and then - in the subform - choose an
exercise from the combobox droplist and enter the date, sets, reps, and
weight; enter as many exercise records as you want for each client.

hth


"LTOSH" wrote in message
...
(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name, address etc.
Exercises Table...ExerciseID(pk), exercise name, description etc
Workout Table...WorkoutID(pk), ClientID, ExerciseID, workout date,
Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client (combo box),
type
in the workout date, then create a workout. I want to save this workout
specific to the client name. Where do i begin?

Thanks!

you're welcome


"LTOSH" wrote in message
...
thanks allen and tina for guidance on my tables...i will get to work on
them
probably tomorrow. i may be bck for further help.
thanks again!!

"tina" wrote:

a note on your tables design: recommend you remove the Exercise Name
field
from the Workout table. that value is already stored in the Exercises
table,
which is linked to the Workout table by the ExerciseID foreign key
field, so
storing it again violates normalization rules. and if the Description
field
in the Workout table is the same as the Description field in the
Exercises
table, then remove it from the Workout table too, for the same reason.
and,
btw, recommend you remove any spaces from your fieldnames - Exercise
name
should be ExerciseName; for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

once you've made the correction(s) above, suggest the following for your
data entry forms: create a form bound to the Client table, SingleForm
view,
i'll call it frmClients. create another form bound to the Workout table,
Datasheet view, i'll call it sfrmWorkouts. in sfrmWorkouts, add a
combobox
control, and set the ControlSource to ExerciseID; set the RowSource to a
SQL
statement, as

SELECT ExerciseID, ExerciseName FROM Exercises;

set the following combobox properties, as

ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
LimitToList: Yes

when you look at the combobox droplist in Form view (Datasheet view, in
this
case), you'll see only the exercise name, but when you pick an exercise
from
the list, the ExerciseID will be saved in the field in the Workout
table, as
it should be.

add sfrmWorkouts to the frmClients, as a subform. set the subform's
LinkChildFields property to ClientID (which refers to the foreign key
field
in the Workout table), and set the LinkMasterFields property to ClientID
(which refers to the primary key field in the Client table).

so now you can open the mainform (frmClients), and add a new client
record
or go to an existing client record, and then - in the subform - choose
an
exercise from the combobox droplist and enter the date, sets, reps, and
weight; enter as many exercise records as you want for each client.

hth


"LTOSH" wrote in message
...
(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name, address etc.
Exercises Table...ExerciseID(pk), exercise name, description etc
Workout Table...WorkoutID(pk), ClientID, ExerciseID, workout date,
Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client (combo box),
type
in the workout date, then create a workout. I want to save this
workout
specific to the client name. Where do i begin?

Thanks!

Tina, I could use a little more help.

I have made the changes as you suggested and looks great so far but I am not
sure where to put my workout date. In my subform I have done as you
suggested below. However, if I put my workout date in this subform and make
it a continous form it repeats the workout date too. I want to create a
Workout for a specific client and a specific date. then add multiple
exercises for that date. Hope this makes sense.

I have a command button on a "switchboard" that opens my frmClient. When I
Click this button I want to open a new form, how do i tell it to do that?

I then want to be able to EDIT that particular workout later if needed by
creating a command button called "edit workout". when i click this command
button i want it to ask me for which client(list box), then whick workout
date (list box).

Thanks so much for all help!
LTOSH

"tina" wrote:

you're welcome


"LTOSH" wrote in message
...
thanks allen and tina for guidance on my tables...i will get to work on
them
probably tomorrow. i may be bck for further help.
thanks again!!

"tina" wrote:

a note on your tables design: recommend you remove the Exercise Name
field
from the Workout table. that value is already stored in the Exercises
table,
which is linked to the Workout table by the ExerciseID foreign key
field, so
storing it again violates normalization rules. and if the Description
field
in the Workout table is the same as the Description field in the
Exercises
table, then remove it from the Workout table too, for the same reason.
and,
btw, recommend you remove any spaces from your fieldnames - Exercise
name
should be ExerciseName; for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

once you've made the correction(s) above, suggest the following for your
data entry forms: create a form bound to the Client table, SingleForm
view,
i'll call it frmClients. create another form bound to the Workout table,
Datasheet view, i'll call it sfrmWorkouts. in sfrmWorkouts, add a
combobox
control, and set the ControlSource to ExerciseID; set the RowSource to a
SQL
statement, as

SELECT ExerciseID, ExerciseName FROM Exercises;

set the following combobox properties, as

ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
LimitToList: Yes

when you look at the combobox droplist in Form view (Datasheet view, in
this
case), you'll see only the exercise name, but when you pick an exercise
from
the list, the ExerciseID will be saved in the field in the Workout
table, as
it should be.

add sfrmWorkouts to the frmClients, as a subform. set the subform's
LinkChildFields property to ClientID (which refers to the foreign key
field
in the Workout table), and set the LinkMasterFields property to ClientID
(which refers to the primary key field in the Client table).

so now you can open the mainform (frmClients), and add a new client
record
or go to an existing client record, and then - in the subform - choose
an
exercise from the combobox droplist and enter the date, sets, reps, and
weight; enter as many exercise records as you want for each client.

hth


"LTOSH" wrote in message
...
(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name, address etc.
Exercises Table...ExerciseID(pk), exercise name, description etc
Workout Table...WorkoutID(pk), ClientID, ExerciseID, workout date,
Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client (combo box),
type
in the workout date, then create a workout. I want to save this
workout
specific to the client name. Where do i begin?

Thanks!

well, you could split the Workout table into a Workout table with a child
WorkoutExercises table - but i can't see much sense in that if the Workout
table's only common field will be the workout date field. you'd still have
the same number of fields in the child table, just replacing the date field
with a foreign key field linking back to the Workout table. it just adds an
unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an unbound
textbox control to frmClients, i'll call it txtWorkoutDate. enter the date
you want to see/add workout records for. add the txtWorkoutDate to the
subform control's LinkMasterFields property, and add the workout date field
name (from the workouts table) to the LinkChildFields property. by changing
the date in the textbox, you'll change the records returned by the subform.
also, you can add a Default value to the date control in the subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my frmClient. When
I
Click this button I want to open a new form, how do i tell it to do that?

the command button opens frmClient. what "new form" do you want to open?
instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout later if needed by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any existing
workout record anytime you want, as well as adding a workout record anytime
you want. if instead you want a form simply to add/edit client records, and
a separate form to add/edit workout records for specific clients/dates, you
can do that pretty much as you proposed - with an unbound listbox with
RowSource based on the clients table, and an unbound listbox of dates with
RowSource based on a SELECT DISTINCT query of the date field of the workout
table, and filtering the workouts form's RecordSource with the values chosen
in those two controls. though having a listbox of dates might be somewhat
trickier from the standpoint of entering new records in the form with a date
that was not yet in the date listbox - you'd want to requery the listbox at
some point, and some issues might arise; i'd have to work through it with a
test setup to really discover the ins and outs of making it work.

hth


"LTOSH" wrote in message
...
Tina, I could use a little more help.

I have made the changes as you suggested and looks great so far but I am
not
sure where to put my workout date. In my subform I have done as you
suggested below. However, if I put my workout date in this subform and
make
it a continous form it repeats the workout date too. I want to create a
Workout for a specific client and a specific date. then add multiple
exercises for that date. Hope this makes sense.

I have a command button on a "switchboard" that opens my frmClient. When
I
Click this button I want to open a new form, how do i tell it to do that?

I then want to be able to EDIT that particular workout later if needed by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick workout
date (list box).

Thanks so much for all help!
LTOSH

"tina" wrote:

you're welcome


"LTOSH" wrote in message
...
thanks allen and tina for guidance on my tables...i will get to work
on
them
probably tomorrow. i may be bck for further help.
thanks again!!

"tina" wrote:

a note on your tables design: recommend you remove the Exercise
Name
field
from the Workout table. that value is already stored in the
Exercises
table,
which is linked to the Workout table by the ExerciseID foreign key
field, so
storing it again violates normalization rules. and if the
Description
field
in the Workout table is the same as the Description field in the
Exercises
table, then remove it from the Workout table too, for the same
reason.
and,
btw, recommend you remove any spaces from your fieldnames - Exercise
name
should be ExerciseName; for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

once you've made the correction(s) above, suggest the following for
your
data entry forms: create a form bound to the Client table,
SingleForm
view,
i'll call it frmClients. create another form bound to the Workout
table,
Datasheet view, i'll call it sfrmWorkouts. in sfrmWorkouts, add a
combobox
control, and set the ControlSource to ExerciseID; set the RowSource
to a
SQL
statement, as

SELECT ExerciseID, ExerciseName FROM Exercises;

set the following combobox properties, as

ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
LimitToList: Yes

when you look at the combobox droplist in Form view (Datasheet view,
in
this
case), you'll see only the exercise name, but when you pick an
exercise
from
the list, the ExerciseID will be saved in the field in the Workout
table, as
it should be.

add sfrmWorkouts to the frmClients, as a subform. set the subform's
LinkChildFields property to ClientID (which refers to the foreign
key
field
in the Workout table), and set the LinkMasterFields property to
ClientID
(which refers to the primary key field in the Client table).

so now you can open the mainform (frmClients), and add a new client
record
or go to an existing client record, and then - in the subform -
choose
an
exercise from the combobox droplist and enter the date, sets, reps,
and
weight; enter as many exercise records as you want for each client.

hth


"LTOSH" wrote in message
...
(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name, address etc.
Exercises Table...ExerciseID(pk), exercise name, description etc
Workout Table...WorkoutID(pk), ClientID, ExerciseID, workout date,
Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client (combo
box),
type
in the workout date, then create a workout. I want to save this
workout
specific to the client name. Where do i begin?

Thanks!

when i say add "new form", i mean when i click on the button it opens a blank
frmClient...ready for new information....right now when i click my command
button and it opens the frmClient it has the information that was previously
entered.

i have been playing around some waiting for a response on last post and
wondered about adding on the frmClient a tab control (labeled Day 1, Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click Day 1 tab, then
select exercises etc for that day...Click Day 2 select exercises for that
day. i have set up 2 days but on Day 2 it shows the exercise information
from Day 1. Is this a possiblility to make this set up easier?

I'm just learning here...what are your suggestions?
Thanks!

"tina" wrote:

well, you could split the Workout table into a Workout table with a child
WorkoutExercises table - but i can't see much sense in that if the Workout
table's only common field will be the workout date field. you'd still have
the same number of fields in the child table, just replacing the date field
with a foreign key field linking back to the Workout table. it just adds an
unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an unbound
textbox control to frmClients, i'll call it txtWorkoutDate. enter the date
you want to see/add workout records for. add the txtWorkoutDate to the
subform control's LinkMasterFields property, and add the workout date field
name (from the workouts table) to the LinkChildFields property. by changing
the date in the textbox, you'll change the records returned by the subform.
also, you can add a Default value to the date control in the subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my frmClient. When
I
Click this button I want to open a new form, how do i tell it to do that?

the command button opens frmClient. what "new form" do you want to open?
instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout later if needed by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any existing
workout record anytime you want, as well as adding a workout record anytime
you want. if instead you want a form simply to add/edit client records, and
a separate form to add/edit workout records for specific clients/dates, you
can do that pretty much as you proposed - with an unbound listbox with
RowSource based on the clients table, and an unbound listbox of dates with
RowSource based on a SELECT DISTINCT query of the date field of the workout
table, and filtering the workouts form's RecordSource with the values chosen
in those two controls. though having a listbox of dates might be somewhat
trickier from the standpoint of entering new records in the form with a date
that was not yet in the date listbox - you'd want to requery the listbox at
some point, and some issues might arise; i'd have to work through it with a
test setup to really discover the ins and outs of making it work.

hth


"LTOSH" wrote in message
...
Tina, I could use a little more help.

I have made the changes as you suggested and looks great so far but I am
not
sure where to put my workout date. In my subform I have done as you
suggested below. However, if I put my workout date in this subform and
make
it a continous form it repeats the workout date too. I want to create a
Workout for a specific client and a specific date. then add multiple
exercises for that date. Hope this makes sense.

I have a command button on a "switchboard" that opens my frmClient. When
I
Click this button I want to open a new form, how do i tell it to do that?

I then want to be able to EDIT that particular workout later if needed by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick workout
date (list box).

Thanks so much for all help!
LTOSH

"tina" wrote:

you're welcome


"LTOSH" wrote in message
...
thanks allen and tina for guidance on my tables...i will get to work
on
them
probably tomorrow. i may be bck for further help.
thanks again!!

"tina" wrote:

a note on your tables design: recommend you remove the Exercise
Name
field
from the Workout table. that value is already stored in the
Exercises
table,
which is linked to the Workout table by the ExerciseID foreign key
field, so
storing it again violates normalization rules. and if the
Description
field
in the Workout table is the same as the Description field in the
Exercises
table, then remove it from the Workout table too, for the same
reason.
and,
btw, recommend you remove any spaces from your fieldnames - Exercise
name
should be ExerciseName; for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

once you've made the correction(s) above, suggest the following for
your
data entry forms: create a form bound to the Client table,
SingleForm
view,
i'll call it frmClients. create another form bound to the Workout
table,
Datasheet view, i'll call it sfrmWorkouts. in sfrmWorkouts, add a
combobox
control, and set the ControlSource to ExerciseID; set the RowSource
to a
SQL
statement, as

SELECT ExerciseID, ExerciseName FROM Exercises;

set the following combobox properties, as

ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
LimitToList: Yes

when you look at the combobox droplist in Form view (Datasheet view,
in
this
case), you'll see only the exercise name, but when you pick an
exercise
from
the list, the ExerciseID will be saved in the field in the Workout
table, as
it should be.

add sfrmWorkouts to the frmClients, as a subform. set the subform's
LinkChildFields property to ClientID (which refers to the foreign
key
field
in the Workout table), and set the LinkMasterFields property to
ClientID
(which refers to the primary key field in the Client table).

so now you can open the mainform (frmClients), and add a new client
record
or go to an existing client record, and then - in the subform -
choose
an
exercise from the combobox droplist and enter the date, sets, reps,
and
weight; enter as many exercise records as you want for each client.

hth


"LTOSH" wrote in message
...
(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name, address etc.
Exercises Table...ExerciseID(pk), exercise name, description etc
Workout Table...WorkoutID(pk), ClientID, ExerciseID, workout date,
Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client (combo
box),
type
in the workout date, then create a workout. I want to save this
workout
specific to the client name. Where do i begin?

Thanks!

comments inline.

"LTOSH" wrote in message
...
when i say add "new form", i mean when i click on the button it opens a
blank
frmClient...ready for new information....right now when i click my command
button and it opens the frmClient it has the information that was
previously
entered.

you can add code to frmClients Load event procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew


i have been playing around some waiting for a response on last post and
wondered about adding on the frmClient a tab control (labeled Day 1, Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click Day 1 tab, then
select exercises etc for that day...Click Day 2 select exercises for that
day. i have set up 2 days but on Day 2 it shows the exercise information
from Day 1. Is this a possiblility to make this set up easier?

well, it's becoming unclear just what you want the workouts table for. from
the table fields you originally posted, including a date field, it seemed
clear that you wanted to a) enter records assigning specific exercises to be
done on specific dates (2/16/09, 2/17/09, 2/18/09, etc), OR b) enter records
to track what specific exercises were performed on specific dates. either
purpose would be served by the same table structure.

but now you're talking about Day 1, Day 2, etc. that sounds more like you
want to set up a workout program for each client, with a set of exercise
assignments for a set of rotating days, such as

Day 1
exercise a, ...
exercise b, ...
exercise c, ...

Day 2
exercise d, ...
exercise e, ...
exercise f, ...

Day 3
exercise b, ...
exercise d, ...
exercise f, ...
exercise g, ...

then the client would perhaps take a day off, and begin the rotation again
with the exercises assigned to Day 1.

in the above scenario, you wouldn't have a date field in the workouts table
at all. you'd have a "Day" field, to assign specific exercises to specific
*days* in the program rotation. so please clarify, just what is the intended
purpose of the database, and specifically of the workout table?


I'm just learning here...what are your suggestions?
Thanks!

"tina" wrote:

well, you could split the Workout table into a Workout table with a
child
WorkoutExercises table - but i can't see much sense in that if the
Workout
table's only common field will be the workout date field. you'd still
have
the same number of fields in the child table, just replacing the date
field
with a foreign key field linking back to the Workout table. it just adds
an
unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an unbound
textbox control to frmClients, i'll call it txtWorkoutDate. enter the
date
you want to see/add workout records for. add the txtWorkoutDate to the
subform control's LinkMasterFields property, and add the workout date
field
name (from the workouts table) to the LinkChildFields property. by
changing
the date in the textbox, you'll change the records returned by the
subform.
also, you can add a Default value to the date control in the subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically
have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my frmClient.
When
I
Click this button I want to open a new form, how do i tell it to do
that?

the command button opens frmClient. what "new form" do you want to open?
instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout later if needed
by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick
workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any existing
workout record anytime you want, as well as adding a workout record
anytime
you want. if instead you want a form simply to add/edit client records,
and
a separate form to add/edit workout records for specific clients/dates,
you
can do that pretty much as you proposed - with an unbound listbox with
RowSource based on the clients table, and an unbound listbox of dates
with
RowSource based on a SELECT DISTINCT query of the date field of the
workout
table, and filtering the workouts form's RecordSource with the values
chosen
in those two controls. though having a listbox of dates might be
somewhat
trickier from the standpoint of entering new records in the form with a
date
that was not yet in the date listbox - you'd want to requery the listbox
at
some point, and some issues might arise; i'd have to work through it
with a
test setup to really discover the ins and outs of making it work.

hth


"LTOSH" wrote in message
...
Tina, I could use a little more help.

I have made the changes as you suggested and looks great so far but I
am
not
sure where to put my workout date. In my subform I have done as you
suggested below. However, if I put my workout date in this subform
and
make
it a continous form it repeats the workout date too. I want to create
a
Workout for a specific client and a specific date. then add multiple
exercises for that date. Hope this makes sense.

I have a command button on a "switchboard" that opens my frmClient.
When
I
Click this button I want to open a new form, how do i tell it to do
that?

I then want to be able to EDIT that particular workout later if needed
by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick
workout
date (list box).

Thanks so much for all help!
LTOSH

"tina" wrote:

you're welcome


"LTOSH" wrote in message
...
thanks allen and tina for guidance on my tables...i will get to
work
on
them
probably tomorrow. i may be bck for further help.
thanks again!!

"tina" wrote:

a note on your tables design: recommend you remove the Exercise
Name
field
from the Workout table. that value is already stored in the
Exercises
table,
which is linked to the Workout table by the ExerciseID foreign
key
field, so
storing it again violates normalization rules. and if the
Description
field
in the Workout table is the same as the Description field in the
Exercises
table, then remove it from the Workout table too, for the same
reason.
and,
btw, recommend you remove any spaces from your fieldnames -
Exercise
name
should be ExerciseName; for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

once you've made the correction(s) above, suggest the following
for
your
data entry forms: create a form bound to the Client table,
SingleForm
view,
i'll call it frmClients. create another form bound to the
Workout
table,
Datasheet view, i'll call it sfrmWorkouts. in sfrmWorkouts, add
a
combobox
control, and set the ControlSource to ExerciseID; set the
RowSource
to a
SQL
statement, as

SELECT ExerciseID, ExerciseName FROM Exercises;

set the following combobox properties, as

ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
LimitToList: Yes

when you look at the combobox droplist in Form view (Datasheet
view,
in
this
case), you'll see only the exercise name, but when you pick an
exercise
from
the list, the ExerciseID will be saved in the field in the
Workout
table, as
it should be.

add sfrmWorkouts to the frmClients, as a subform. set the
subform's
LinkChildFields property to ClientID (which refers to the
foreign
key
field
in the Workout table), and set the LinkMasterFields property to
ClientID
(which refers to the primary key field in the Client table).

so now you can open the mainform (frmClients), and add a new
client
record
or go to an existing client record, and then - in the subform -
choose
an
exercise from the combobox droplist and enter the date, sets,
reps,
and
weight; enter as many exercise records as you want for each
client.

hth


"LTOSH" wrote in message
...
(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name, address
etc.
Exercises Table...ExerciseID(pk), exercise name, description
etc
Workout Table...WorkoutID(pk), ClientID, ExerciseID, workout
date,
Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client
(combo
box),
type
in the workout date, then create a workout. I want to save
this
workout
specific to the client name. Where do i begin?

Thanks!

i apologize for being unclear.

your second scenio is what i am thinking. i just haven't been able to
explain it properly. Yes i want to set up a workout program with a set of
exercises specific to day 1, day 2 etc as you have described but specific for
a client...


"tina" wrote:

comments inline.

"LTOSH" wrote in message
...
when i say add "new form", i mean when i click on the button it opens a
blank
frmClient...ready for new information....right now when i click my command
button and it opens the frmClient it has the information that was
previously
entered.

you can add code to frmClients Load event procedure, as

DoCmd.RunCommand acCmdRecordsGoToNew


i have been playing around some waiting for a response on last post and
wondered about adding on the frmClient a tab control (labeled Day 1, Day 2
etc). then add the sfrmWorkouts to each Day. Ex. Click Day 1 tab, then
select exercises etc for that day...Click Day 2 select exercises for that
day. i have set up 2 days but on Day 2 it shows the exercise information
from Day 1. Is this a possiblility to make this set up easier?

well, it's becoming unclear just what you want the workouts table for. from
the table fields you originally posted, including a date field, it seemed
clear that you wanted to a) enter records assigning specific exercises to be
done on specific dates (2/16/09, 2/17/09, 2/18/09, etc), OR b) enter records
to track what specific exercises were performed on specific dates. either
purpose would be served by the same table structure.

but now you're talking about Day 1, Day 2, etc. that sounds more like you
want to set up a workout program for each client, with a set of exercise
assignments for a set of rotating days, such as

Day 1
exercise a, ...
exercise b, ...
exercise c, ...

Day 2
exercise d, ...
exercise e, ...
exercise f, ...

Day 3
exercise b, ...
exercise d, ...
exercise f, ...
exercise g, ...

then the client would perhaps take a day off, and begin the rotation again
with the exercises assigned to Day 1.

in the above scenario, you wouldn't have a date field in the workouts table
at all. you'd have a "Day" field, to assign specific exercises to specific
*days* in the program rotation. so please clarify, just what is the intended
purpose of the database, and specifically of the workout table?


I'm just learning here...what are your suggestions?
Thanks!

"tina" wrote:

well, you could split the Workout table into a Workout table with a
child
WorkoutExercises table - but i can't see much sense in that if the
Workout
table's only common field will be the workout date field. you'd still
have
the same number of fields in the child table, just replacing the date
field
with a foreign key field linking back to the Workout table. it just adds
an
unnecessary layer to your tables/relationships.

instead, suggest you keep the tables structure as is, and add an unbound
textbox control to frmClients, i'll call it txtWorkoutDate. enter the
date
you want to see/add workout records for. add the txtWorkoutDate to the
subform control's LinkMasterFields property, and add the workout date
field
name (from the workouts table) to the LinkChildFields property. by
changing
the date in the textbox, you'll change the records returned by the
subform.
also, you can add a Default value to the date control in the subform, as

=[Forms]![frmClients]![txtWorkoutDate]

so when you *add* a new record to the subform, it will automatically
have
the date entered in the txtWorkDate control in the mainform.

the above will work for the form/subform setup i outlined in my previous
post. i'm not sure what you mean by

I have a command button on a "switchboard" that opens my frmClient.
When
I
Click this button I want to open a new form, how do i tell it to do
that?

the command button opens frmClient. what "new form" do you want to open?
instead of frmClient? in addition to frmClient? please explain.

I then want to be able to EDIT that particular workout later if needed
by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick
workout
date (list box).

well, in the form/subform setup i proposed, you CAN edit any existing
workout record anytime you want, as well as adding a workout record
anytime
you want. if instead you want a form simply to add/edit client records,
and
a separate form to add/edit workout records for specific clients/dates,
you
can do that pretty much as you proposed - with an unbound listbox with
RowSource based on the clients table, and an unbound listbox of dates
with
RowSource based on a SELECT DISTINCT query of the date field of the
workout
table, and filtering the workouts form's RecordSource with the values
chosen
in those two controls. though having a listbox of dates might be
somewhat
trickier from the standpoint of entering new records in the form with a
date
that was not yet in the date listbox - you'd want to requery the listbox
at
some point, and some issues might arise; i'd have to work through it
with a
test setup to really discover the ins and outs of making it work.

hth


"LTOSH" wrote in message
...
Tina, I could use a little more help.

I have made the changes as you suggested and looks great so far but I
am
not
sure where to put my workout date. In my subform I have done as you
suggested below. However, if I put my workout date in this subform
and
make
it a continous form it repeats the workout date too. I want to create
a
Workout for a specific client and a specific date. then add multiple
exercises for that date. Hope this makes sense.

I have a command button on a "switchboard" that opens my frmClient.
When
I
Click this button I want to open a new form, how do i tell it to do
that?

I then want to be able to EDIT that particular workout later if needed
by
creating a command button called "edit workout". when i click this
command
button i want it to ask me for which client(list box), then whick
workout
date (list box).

Thanks so much for all help!
LTOSH

"tina" wrote:

you're welcome


"LTOSH" wrote in message
...
thanks allen and tina for guidance on my tables...i will get to
work
on
them
probably tomorrow. i may be bck for further help.
thanks again!!

"tina" wrote:

a note on your tables design: recommend you remove the Exercise
Name
field
from the Workout table. that value is already stored in the
Exercises
table,
which is linked to the Workout table by the ExerciseID foreign
key
field, so
storing it again violates normalization rules. and if the
Description
field
in the Workout table is the same as the Description field in the
Exercises
table, then remove it from the Workout table too, for the same
reason.
and,
btw, recommend you remove any spaces from your fieldnames -
Exercise
name
should be ExerciseName; for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

once you've made the correction(s) above, suggest the following
for
your
data entry forms: create a form bound to the Client table,
SingleForm
view,
i'll call it frmClients. create another form bound to the
Workout
table,
Datasheet view, i'll call it sfrmWorkouts. in sfrmWorkouts, add
a
combobox
control, and set the ControlSource to ExerciseID; set the
RowSource
to a
SQL
statement, as

SELECT ExerciseID, ExerciseName FROM Exercises;

set the following combobox properties, as

ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
LimitToList: Yes

when you look at the combobox droplist in Form view (Datasheet
view,
in
this
case), you'll see only the exercise name, but when you pick an
exercise
from
the list, the ExerciseID will be saved in the field in the
Workout
table, as
it should be.

add sfrmWorkouts to the frmClients, as a subform. set the
subform's
LinkChildFields property to ClientID (which refers to the
foreign
key
field
in the Workout table), and set the LinkMasterFields property to
ClientID
(which refers to the primary key field in the Client table).

so now you can open the mainform (frmClients), and add a new
client
record
or go to an existing client record, and then - in the subform -
choose
an
exercise from the combobox droplist and enter the date, sets,
reps,
and
weight; enter as many exercise records as you want for each
client.

hth


"LTOSH" wrote in message
...
(Using Access 2000)
I have a 3 tables.
Client Table...ClientID(pk), first name, last name, address
etc.
Exercises Table...ExerciseID(pk), exercise name, description
etc
Workout Table...WorkoutID(pk), ClientID, ExerciseID, workout
date,
Exercise
Name, sets, reps, weight, description.

I am wanting to create a form where i can choose a client
(combo
box),
type
in the workout date, then create a workout. I want to save
this
workout
specific to the client name. Where do i begin?

Thanks!