Inputing date to separate tables while retaining links

[Scotty]

I have a database which is made up of several tables "contacts",
"coaching", "clubs" etc. The Contact and coach tables are linked by an
"ID" field. The Contact and clubs table are linked a by a C_ID field.
I have a have a form to input new records and I need to tnter the "ID"
value int 2 tables to maintain the link.

As a result of a previous attempt I have contacts as a main form with
coach as a sub form. Lookin good...
I now have a situation where I need to link club info from a table of
club details. The club table has fields club_code and club_name. I
want to be able to link club_code in this to the club_code field in
contacts so that I can select a club name fron a dropdown and enter the
club id into the record in contacts.

a) How can I do this ?
B) IS this the best way to enterdata and maintain the relationship?

[Phil Stanton]

I suggest you have a table of Clubs consisting of
ClubID Autonumber
ClubName
Etc

a table of Coaches
CoachID Autonumber
Coachname
Etc

a table of Contacts
ContactID Autonumner
ContactName
ClubID Long Integer
CoachID Long Integer
Etc

Then under relationships add the 3 tables and link the 2 ClubIDs from the
Contact & Club tables and link the 2 CoachIDs from the Contact and Coach
Table

If you then create a form based on the Contacts (or better a Query based on
the contacts so that the names are in alphabetical order) you can have a
list box or combo box to select the appropriate Club and Coach

HTH

Phil
"Scotty" <no****@aol.com> wrote in message
news:cb**********@sparta.btinternet.com...

I have a database which is made up of several tables "contacts",
"coaching", "clubs" etc. The Contact and coach tables are linked by an
"ID" field. The Contact and clubs table are linked a by a C_ID field.
I have a have a form to input new records and I need to tnter the "ID"
value int 2 tables to maintain the link.

As a result of a previous attempt I have contacts as a main form with
coach as a sub form. Lookin good...
I now have a situation where I need to link club info from a table of
club details. The club table has fields club_code and club_name. I
want to be able to link club_code in this to the club_code field in
contacts so that I can select a club name fron a dropdown and enter the
club id into the record in contacts.

a) How can I do this ?
B) IS this the best way to enterdata and maintain the relationship?

[Scotty]

Phil Stanton wrote:

I suggest you have a table of Clubs consisting of
ClubID Autonumber
ClubName
Etc

a table of Coaches
CoachID Autonumber
Coachname
Etc

a table of Contacts
ContactID Autonumner
ContactName
ClubID Long Integer
CoachID Long Integer
Etc

Then under relationships add the 3 tables and link the 2 ClubIDs from the
Contact & Club tables and link the 2 CoachIDs from the Contact and Coach
Table

If you then create a form based on the Contacts (or better a Query based on
the contacts so that the names are in alphabetical order) you can have a
list box or combo box to select the appropriate Club and Coach

HTH

Phil
"Scotty" <no****@aol.com> wrote in message
news:cb**********@sparta.btinternet.com...

I have a database which is made up of several tables "contacts",
"coaching", "clubs" etc. The Contact and coach tables are linked by an
"ID" field. The Contact and clubs table are linked a by a C_ID field.
I have a have a form to input new records and I need to tnter the "ID"
value int 2 tables to maintain the link.

As a result of a previous attempt I have contacts as a main form with
coach as a sub form. Lookin good...
I now have a situation where I need to link club info from a table of
club details. The club table has fields club_code and club_name. I
want to be able to link club_code in this to the club_code field in
contacts so that I can select a club name fron a dropdown and enter the
club id into the record in contacts.

a) How can I do this ?
B) IS this the best way to enterdata and maintain the relationship?

Thanks very much for your response
I have tried "almost" that and I could not get it towork.

I had

ClubID String eg har
ClubName
Etc

a table of Coaches
CoachID String eg sco
Coachname
Etc

a table of Contacts
ContactID String eg sco
ContactName
ClubID String eg har
My problem was when I needed to enter data, I had to be able to enter
sco into coachid (in tblcoach) and contactID (in tblcontact) to mainain
the relationship. I am relatively new to all this so my reasoning may
be completely off the wall.....

Also in the form linked to tblcontacts I needed a combobox with the
clubs which would insert the clubid into tblcontacts.

If you think that the slight differences in what you suggest will make a
difference I will try it.

I have gone down the route of using mother and child forms and subforms
but that doesn't seem to work either.... Maybe it is ME !!

[Phil Stanton]

You should not need subforms for this.

If you want I could send you a smple database to get you started, if so
email me direct with conmtacts as the subject, and replace discussion with
phil.

Phil
"Scotty" <no****@aol.com> wrote in message
news:cb**********@titan.btinternet.com...

Phil Stanton wrote:

I suggest you have a table of Clubs consisting of
ClubID Autonumber
ClubName
Etc

a table of Coaches
CoachID Autonumber
Coachname
Etc

a table of Contacts
ContactID Autonumner
ContactName
ClubID Long Integer
CoachID Long Integer
Etc

Then under relationships add the 3 tables and link the 2 ClubIDs from the Contact & Club tables and link the 2 CoachIDs from the Contact and Coach
Table

If you then create a form based on the Contacts (or better a Query based on the contacts so that the names are in alphabetical order) you can have a
list box or combo box to select the appropriate Club and Coach

HTH

Phil
"Scotty" <no****@aol.com> wrote in message
news:cb**********@sparta.btinternet.com...

I have a database which is made up of several tables "contacts",
"coaching", "clubs" etc. The Contact and coach tables are linked by an
"ID" field. The Contact and clubs table are linked a by a C_ID field.
I have a have a form to input new records and I need to tnter the "ID"
value int 2 tables to maintain the link.

As a result of a previous attempt I have contacts as a main form with
coach as a sub form. Lookin good...
I now have a situation where I need to link club info from a table of
club details. The club table has fields club_code and club_name. I
want to be able to link club_code in this to the club_code field in
contacts so that I can select a club name fron a dropdown and enter the
club id into the record in contacts.

a) How can I do this ?
B) IS this the best way to enterdata and maintain the relationship?

Thanks very much for your response
I have tried "almost" that and I could not get it towork.

I had

ClubID String eg har
ClubName
Etc

a table of Coaches
CoachID String eg sco
Coachname
Etc

a table of Contacts
ContactID String eg sco
ContactName
ClubID String eg har
My problem was when I needed to enter data, I had to be able to enter
sco into coachid (in tblcoach) and contactID (in tblcontact) to mainain
the relationship. I am relatively new to all this so my reasoning may
be completely off the wall.....

Also in the form linked to tblcontacts I needed a combobox with the
clubs which would insert the clubid into tblcontacts.

If you think that the slight differences in what you suggest will make a
difference I will try it.

I have gone down the route of using mother and child forms and subforms
but that doesn't seem to work either.... Maybe it is ME !!