Fantasy Football DB Help

[dagoat10]

Greetings,
I have been working on an database idea that will assist people with having multiple teams on multiple websites, but i have hit a snag. I am trying to relate 2 tables together but so far it's been no good, I have the following

tblPlayers
ID
Player
Position
Number
Team

tblFantasyTeam
Player
Positon
Team
Number
Stats

I can not get the 2 tables to link together the way i want, which is if you click on the player in the Fantasy Team Table, you can see the data in the Players Table, but i have not been able to do so, can someone help me please.

 

[dhookom]

Shouldn't the Player field in tblFantasyTeam be the ID field from tblPlayers?

Duane
Hook'D on Access
MS Access MVP

 

[dagoat10]

I tried that and it gives me a one to many relationship, but not a one to one relationship like i want. And i can enforce referential integrity because it claims that the in the tblFantasy Team that there could be a record in there that is not in tblPlayers, but that is not true.

 

[dhookom]

Are you suggesting a player can belong on only one fantasy team? If that is correct then you can create a unique index on the Player field.

If the relationship is truly one to one then you might not even need two tables.

Duane
Hook'D on Access
MS Access MVP

 

[dagoat10]

no they can belong to more than one fantasy team. For example: say i have chris johnson on 2 diffrent fantasy teams, then when i click the + on his name in the tbl player pool, then a record for each team that he is on should show up.

 

[dhookom]

You have confused me by

I tried that and it gives me a one to many relationship, but not a one to one relationship like i want

and then

no they can belong to more than one fantasy team

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

http://mattabase.com/archives/50

Since they can belong to more than one team you will have to create a junction table because what you have is not a one to many but a many to many.
Many different players on a single team, and Many different teams with the same player.

See the above fantasy football database. It has some problems, but it will show you the relationship.

 

[dhookom]

Isn't tblFantasyTeam the junction table? It would sit between the tblPlayers and the table of unique fantasy teams.

I assume the Team field in tblFantasyTeam is a foreign key to a table like:
[tt]
tblFantasyTeams
FTTeam pk
FTOwner
FT...
[/tt]
Maybe dagoat10 needs to tell us more about his tables and fields. The existing field names don't do a good job of describing the relationships and purpose of the fields.


Duane
Hook'D on Access
MS Access MVP

 

[dagoat10]

ok sorry for confusing some of you guys

I do have a table of unique Fantasy Teams which looks like

tblFantasyTeams
TeamName
Owner

Now to clarify

tblTeam1
PlayerName
NFLTeam
Stats(Which is many Statistical fields)

This should connect to this table

tblPlayerPool
PlayerName
Position
JerseyNumber
NFLTeamName

Now the idea is that any player in the Player Pool can belong to many teams, but the a single fantasy team a player has one record in the Player Pool Table.

now i do like the idea of a join table that sits in between the 2 tables that i mentioned, The Unique Fantasy Team table could be the key to this, in essence instead of trying to go for a direct many to many relationship in my database.

Which i think my problem was best described by MajP.

Many different players on a single team, and Many different teams with the same player.