Creating relationships

[omar]

Hello,

thank you for taking the time to read this, I appreciate
it very much.

Im creating a database the allows an individual/student to
input different activities. I created 3 tables.

table 1-- this table contains fields the are generic to
all other (table) activities like userid, date, hours
exercising, activity type, etc. It also includes an
autonumber field call trackingID set to primary key.

table 2-- this table contains specific items for an
activity like basketball.

table 3-- this table contains specific items for an
activity like football.

I then created a form that contains all the fields in
table 1. Within the form, I added (using the tab control
and subform tool) 2 subforms. These subforms were created
using table 2 and 3.

Hopefully I did a decent job of explaining.. Now for the
questions..

First, I would like to add a macro/expression of some sort
that would detect the selection of activity type within
the table 1 and depending on what the user selected it
(access) would point to the specific subform page.

Second, I would like to create a relationship between
table 1 and 2,3. I do not know what type of relationship i
need in order to make this work. right now I have it setup
with a one-to-one relation between 1 and tables 2-3. using
primary key of trackingID from table 1 to primary key of
trackingID from tables 2and3. but this does not work..

thank you

[Omar]

John,
First I would like to sincerely thank you for taking the
time to read and answer my questions. You are a true
gentleman.

Now to answer your questions..

QUESTION 1:
"I take it you have another table with the user name and
other bio information, linked to this table."

My reply:
Your guess is correct. I have another table that contains
bios information like student ID, name, etc. This table
currently has a one-to-many relationsip with table 1. I
might add, table 1 this is my so-called "Main header".
This table 1, again contains general info about the
activity, like date, student name, grade, etc.

QUESTION 2:
"What's the "activity type"? the code for Baseball,
Football, etc.?"

My reply:
You are again correct. The activity type is currently a
pull down menu containing a list of all particular
activity types. Like Baseball, Football, Basketball, etc.
Although I think it would be more appropiate to use an
option group instead of a pull down menu, would you
concur?

QUESTION 3:
"Will one user have multiple records in Table1, one for
each activity? or just one (which would limit them to only
one activity)?"

My reply:
Yes, I need the database to store multiple entries by one
user. For example, user001 may have 2 or more activities
for a particular day, and these entries could be of the
same "type"; meaning that user001 could play basketball
more than once a day, hence he would have two entries for
basketball on that day alone, this is beyond doing another
activity. hence, yes I need to allow users to enter
multiple records regardless of activity type.

QUESTION 4:
"with regards to table 2;for the activity in general? or
for a particular userid's involvement in the activity?
What "items", for example?"

My reply:
No, table 2&3 contain information specifically regarding
the activity type. For example, for activity
type "Football; it contains a checklist items like, how
many yards did you throw the ball, how many catches did
you make, Did you have any problems running and catching,
did you experience any discomfort?, etc. Tables 2&3
contain items that are specific for the type of activity
the user picks. Hence, table 2&3 contain specific
information regarding an activity type. table 2 contains
info regarding basketball, table 3 contains info regarding
football, etc. For a particular user involvement in that
particular activity.

ADDITIONAL REMARKS:

table 1 is design to capture basic information regarding
the activity, tables 2&3 contain specific information
regarding the activity chosen from table 1.
---------------------------------------------------------
---------------------------------------------------------

>>Im creating a database the allows an individual/student

to

>>input different activities. I created 3 tables.
>>
>>table 1-- this table contains fields the are generic to
>>all other (table) activities like userid, date, hours
>>exercising, activity type, etc. It also includes an
>>autonumber field call trackingID set to primary key.

>
>I take it you have another table with the user name and

other bio

>information, linked to this table. What's the "activity

type"? the

>code for Baseball, Football, etc.? Will one user have

multiple records

>in Table1, one for each activity? or just one (which

would limit them

>to only one activity)?
>

>>table 2-- this table contains specific items for an
>>activity like basketball.

>
>For the activity in general? or for a particular userid's

involvement

>in the activity? What "items", for example?
>

>>table 3-- this table contains specific items for an
>>activity like football.

>
>ditto...
>

>>I then created a form that contains all the fields in
>>table 1. Within the form, I added (using the tab control
>>and subform tool) 2 subforms. These subforms were

created

>>using table 2 and 3.

>
>This sounds like it might be "upside down". I don't guess

I understand

>the role of Tables 2 and 3 correctly. Could you explain

what

>information they contain?
>

>>Hopefully I did a decent job of explaining.. Now for the
>>questions..

>

>>First, I would like to add a macro/expression of some

sort

>>that would detect the selection of activity type within
>>the table 1 and depending on what the user selected it
>>(access) would point to the specific subform page.

>
>The AfterUpdate event of a combo box choosing the

activity could do

>this. Invoke the Code Builder and put in code like:
>
>Private Sub cboActivity_AfterUpdate()
>Select Case Me!cboActivity
> Case "Football" ' user selected Football
> Me!pgPageControl.TabIndex = 0 ' the first page of the

tab control

> Case "Basketball"
> Me!pgPageControl.TabIndex = 1
><etc>
>End Select
>

>>Second, I would like to create a relationship between
>>table 1 and 2,3. I do not know what type of relationship

i

>>need in order to make this work. right now I have it

setup

>>with a one-to-one relation between 1 and tables 2-3.

using

>>primary key of trackingID from table 1 to primary key of
>>trackingID from tables 2and3. but this does not work..

>
>Without knowing what's in tables 2 and 3 and what logical

relationship

>there is between these tables' entities and that of

table1, I can't

>help!
>
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
>[url]http://go.compuserve.com/msdevapps?loc=us&access=public[/url]
>.
>