Sub forms to link data (1 Viewer)

[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.

 

[Pat Hartman]

This is a many-to-many relationship. To implement this type of relationship requires what is commonly referred to as a junction table. Frequently, the only two columns in this table would be the FK to the contacts table and the FK to the Tournaments table. In this situation, the PK of the junction table would be a compound PK. To make one, select the first column and then the second column and press the PK button. Both columns will show the PK icon. To see what this actually looks like, open the indexes dialog.

In other situations, you might want to add an autonumber as the PK for the junction table but you would STILL need a unique index on the two field that make the junction unique. Look at the indexes dialog in the first example to see the technique for creating a multi-column unique index. In still other situations, you might need intersection data. For example, if you wanted to know the date that the contact was assigned to the tournament, you would add the date field here. It can't go in the Tournament table because each row in the tournament table indicates one specific tournament and has nothing to do with contacts. It can't go in the Contacts table for the same reason. There is no tournament ID in the contacts table because contacts stand alone. It is the intersection of Contact and Tournament that gives you the unique identifier which defines the date field.

Here is a m-m sample. It is not your specific relationship but hopefully you will be able to make the transition. This sample shows you how to work with a m-m from either direction and uses two different styles of forms just to show the options. From your perspective, either the subform or the popup form might make more sense in a different situation. Or you might choose to use subforms in both cases or popups in both cases.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[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 ?