Sub forms to link data

[SwampyNZ]

Hello

I am writing an access database to link appointing officials to tournaments.

I have a table called Tournaments (contains an auto-number for Tournament_ID and a number field for Contact_ID), and a table called contacts (contains an autonumber for Contact_ID and a number field for Tournament_ID).

I want to be able to open a Tournament form with a contacts subform, select an official and assign them to the tournament. I also want to be able to do it the other way (open a contact form with a tournemant subform).

I can get the subform to view

Umpires may get appointed to multiple tournaments ... tournaments will have multiple officials.

I will then build reports that show by tournament or by official.

Any assistance greatly appreciated.

 

[plog]

Your not quite ready for forms.

You need 3 tables like so:

Tournaments
tourn_ID, auto number, primary key
...other fields for tournament info

Contacts
con_ID, auto number, primary key
...other fields for contact info

TournamentContacts
tc_ID, auto number, primary key
ID_Contact, number, foreign key to Contacts
ID_Tournament, number, foreign key to Tournaments

TournamentContacts is where you assign tournaments to contacts and vice versa, not in the other tables. Then, TournamentContacts can become the basis of subforms that appear on the forms based on Tournaments and Contacts.

 

[SwampyNZ]

Thank you, let me play with that.

 

[SwampyNZ]

Your not quite ready for forms.

You need 3 tables like so:

Tournaments
tourn_ID, auto number, primary key
...other fields for tournament info

Contacts
con_ID, auto number, primary key
...other fields for contact info

TournamentContacts
tc_ID, auto number, primary key
ID_Contact, number, foreign key to Contacts
ID_Tournament, number, foreign key to Tournaments

TournamentContacts is where you assign tournaments to contacts and vice versa, not in the other tables. Then, TournamentContacts can become the basis of subforms that appear on the forms based on Tournaments and Contacts.

OK, so I have created that table.

So how do I create the Form: Tournament - Subform Contact (as I need to see some of the the contact detail) and then how do I select a contact to appoint to that tournament?

 

[plog]

Build a form based on Tournaments and make it work with just a single record at a time (Default View -> Single Form). Make it have inputs for every field so you can add/edit everything in Tournaments.

Build a form based on TournamentContacts and make it work with multiple records (Default View -> Continous Form). Make it have a single drop down input that uses ID_Contact but displays other data from that same record. This link should help for that.

MS Access 2013: Bind Combo Box to Primary Key but display a Description field

This MSAccess tutorial explains how to bind a combo box to a primary key (ie: autonumber field) but display a description field in the combo box results in Access 2013 (with screenshots and step-by-step instructions).

www.techonthenet.com

When both forms are done you place the TournamentContacts form on Tournaments and bind them. This link should hlep for that:

Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).

support.microsoft.com

That will allow you to add Contacts to tournaments. You can also use the same method above to build 2 forms to add Tournaments to Contacts.

 

[mike60smart]

OK, so I have created that table.

So how do I create the Form: Tournament - Subform Contact (as I need to see some of the the contact detail) and then how do I select a contact to appoint to that tournament?

Hi
When you create your Forms as suggested by Plog, upload the database so we can check it for you.

 

[arnelgp]

i made another table (Tournament Players) and made query on it (query1).
made a form for query1 and use it as subform of Appointment Main.

 

[SwampyNZ]

i made another table (Tournament Players) and made query on it (query1).
made a form for query1 and use it as subform of Appointment Main.

Thanks, playing with it now.

 

[mike60smart]

Thanks, playing with it now.

Hi Swampy

I think your form should be as in the attached

 

[SwampyNZ]

Thanks, playing with it now.

i made another table (Tournament Players) and made query on it (query1).
made a form for query1 and use it as subform of Appointment Main.

Thannks very much, I was able to make that work,

 

[arnelgp]

i just noticed that the form is for Tournament Officials, therefore rename your New table as "Tournament Officials" (not players).
if you want some details, you can add a Team table:

Team table:
Team_ID (Auto) (PK)
Country Code (Long)
Team Name
Team Manager
..etc (other info about the team)

Player table:
Player_ID (PK, Primary Key)
Team_ID
Player Name
Ranking (World ranking? etc.)
..(Other info)

For each Tournament, there should be Stats for each Team Players:

Tournament Player table:
TourID (Auto) (PK)
Tournamen_ID (Long, FK to Tournament)(FK - Foreign Key)
Player_ID (Long, FK to player table)
Time Played (date, short time)
Goals (Integer)

I can't imagine what other info you need, but I am sure you all got it all on your list.
goodluck with your project.

 

[SwampyNZ]

Cheers

 

[mike60smart]

Cheers

Hi Swampy
Does my example help ?