Kamsach: the problem you are having falls under the category of Rules of Data Normalization for Relational Databases. This problem is not uncommon. There are 5 rules of data normalization: 1NF-Eliminate Repeating Groups; 2-Eliminate Redundant Data; 3NF-Eliminate Columns Not dependent on Key,etc. this information can be found in any and most books referencing Relational Databases, MS Access, MS SQL Server, ASP.NET databases with VB
.NET, just to name a few.
I suggest the following tables: the CaptainTbl,TeamTbl,NewPlayerTbl, should have its own table (according to 1NF).
1 Alan Faye
2 Jo Brigger
Adding a captain and/or changing the captain on an existing team is easy. Just make that change to one table as opposed to searching through a table for every occurrance of the captains name.
1 Cambers Ball
2 Cambers Hockey
3 Cambers Cricket
TCPKID NewTeamIDPK** CapIDPK**
1 1 1
2 2 1
3 3 2
**Note: The Primary Keys (PK) in the NewTeamTbl and CaptainTbl become Foreign Keys (FK) in the joined Table TeamCaptainTbl. This table eliminates redundancy.
PTIDPK PlayerIDPK*** NewTeamIDPK***
1 1 1
2 1 2
3 2 1
table allows for a player to be a member of more than one team. It also allows for the Captain to be a player in addition to being on more than one team. Using the Player team table will get a true count of the number of players per 1 or all teams from your query.
Other information about the player(s) may be added here, for example the players email address. But to add the position the player holds, create a Position Table, then add the Position Table PK ID to the PlayerTeamTbl to give u the position the plays on that particular team. Also this table allows for a player to play more than one position on the same team....
This should help.