Cross Referencing data in the same table

[Bill Walsh]

I need to cross reference data that resides in the same table. What I need
is to match a player with his/her partner. Each has a unique member number
but each may play with other partners throughout the golfing season. Is
there a way to do that in Access? My primary key is PlayerName.
My table structure is as follows:
Number
PlayerName
PartnerNumber
etc.

 

[Douglas J. Steele]

You've got a many-to-many relationship (player A may play with many other
players, each other player may play with player A plus many other players).
That means you need to introduce another intersection entity to resolve that
many-to-many relationship.

Typically what you'd store in that second table is the primary key of the
two players that were partnered together, plus the date on which they were
partnered. The combination of the three fields will be unique, and hence a
candidate key for the table.

To maintain the data, you'd typically use a form/subform combination.

 

[Bill Walsh]

Thank you Doug. When they sign up to play, I should not only register them
in my master table of players, but you are saying I should create a new table
with the Number/Number/Date as the primary key. Correct? Next I would need
to pair them up in a form such as
Smith and Jones
White and Washington
etc. for the tournament. Any ideas on how I can do that? I hope this makes
sense. I enjoy using Access and I believe that it can make my tournament
processes go more smoothly and easier.

 

[Douglas J. Steele]

Actually, I'm saying when they sign up to play, you create an entry in your
Player table.

Then, once you know with whom they're going to partner, you create an entry
in the second table that has the number of the two players and the date on
which they're going to play together.

I would have a form that's bound to the Player table, so that the
information about each player appears on the form (not a continous form...)

I'd also have a form (continuous) that's bound to the second table and add
that form as a subform on the first form. You'd use the number of one of the
players as the LinkMasterFields/LinkChildFields. In that way, all that would
appear in the subform are those players who are partnered with that
particular player. By putting a combo box based on the Player table on the
subform, you'd be able to select the partners.

 

[Bill Walsh]

Got it. I will try it today.

I appreciate your help. and I Thank you for your help.