Form with many-to-many source

[Melissa]

I need to create a form with a subform. The master form
will have Club Team information from a query based on
tblClubTeams. The subform will allow entry of the Club
Team schedule. The source will be a query based on
tblTournaments. I want it to look and work as follows:

Master Form: ClubName, ClubCoach
Subform: Tournament (combobox lookup to
tblTournaments), Dates

The subform will be continuous forms. So the form would
appear as follows:

Club Texas Coach: John Smith
Tournament: Las Vegas Tourney Dates: Feb.12-15, 2004
Tournament: Indy Cup Dates: Feb. 27-29, 2004
Tournament: Mideast Qualifier Dates: Mar. 7-9, 2004

One club team will play in many tournaments and one
tournament will have many club teams participating
resulting in a many-to-many relationship, which I am
completely unfamiliar with. So my questions:

1. My only experience with lookup fields is in one-to-many
relationships. So, I normally have a corresponding foreign
key field on the many side. Should I simply include a
number field TournamentSite in tblClubTeam and a number
field ClubTeam in tblTournaments? Or do I need a junction
table?

2. If I need a junction table, how does Tournament on the
subform become a lookup field if I do not have a
corresponding foriegn key field (like TournamentSite) in
tblClubTeam?

3. If yes to the junction table, do I need fields other
than ClubTeamID and TournamentID, the primary keys from
each table? All of the fields I need for any form or
report are already included in tblClubTeams and
tblTournaments.

4. If I need a junction table, I prefer to use queries as
the source for all forms, since I use a lot of
concatenation. So, what source tables are required for my
master form - tblClubTeams and my new junction table? Do I
need tblTournaments and the new junction table in my query
for the subform source?

5. I will also need to create a report that will list in
the following order:

Tournament (ascending starting date order)
ClubTeam (ascending club team alpha order)

Does this impact any answers to questions 1-4?

Sorry for the length of the post...and thanks for any help
you can offer.

 

[Steffen Johnson]

You first need to resolve the many-to-many issue. You do that by
creating an intersect table ( I'm not sure if that's what you meant by
junction table ).

The intersect table should have as a minimum a foreign key from the
team table which should be the teams table primary key and a foreign
key from the tournament tables which again should be the primary key
from the tournament table.

Now set up your form to use intersect table and create the subform to
use the tournament table.