help please

D

derlwyn

Registered User.

Local time

Today, 22:13

Joined

Mar 21, 2003

Messages

12

• Mar 25, 2003

• #1

I am constructing a small sports club db

I have a player table which includes playerID as the primary key

I also have a team table which has a teamID field as the primary key and stores the 5 playerID numbers of the selected 5 players.

I have used the lookup wizard in my team table to look up the player id from my player table and this works fine

I now want to prevent the coach from selecting the same player twice in the same game.

Is there a way of modifying my lookup code to do this - possibly by writing a query?

Any help would be greatly received

diolch yn fawr

 

Mile-O

Back once again...

Local time

Today, 22:13

Joined

Dec 10, 2002

Messages

11,316

• Mar 25, 2003

• #2

On the foreign key (PlayerID) in your team table, just set it so that it doesn not allow duplicates in the table design view.

 

D

derlwyn

Registered User.

Local time

Today, 22:13

Joined

Mar 21, 2003

Messages

12

• Mar 25, 2003

• #3

no luck

Just tried this - doesnt work

I have 6 fields in my team table

teamid as a key field and then 5 fields called p1,p2,p3,p4 and p5 which each store the playerID of the selected player - essentially i want to prevent the playerID in p1 from popping up in the dropdown list of p2 through to p5

 

Last edited: Mar 25, 2003

Mile-O

Back once again...

Local time

Today, 22:13

Joined

Dec 10, 2002

Messages

11,316

• Mar 25, 2003

• #4

Right, before we begin - you are going to have to normalise your database. The structure you have described is not good at all.

The structure you want is:

tblPlayers
PlayerID
PlayerForename
PlayerSurname
TeamID - linked to tblTeams (TeamID) via a one-to-many relationship
Other player related details

tblTeams
TeamID
TeamName
Other team related details;

tblSelections
SelectionID
DateOfSelection
PlayerID - linked to tblPlayers (PlayerID) via a one-to-many relationship
Other Selection related details

Since you refer to selecting one team you should be able to ignore the idea of tblTeams but with tblSelections and tblPlayers you can keep a record of all players, and a record of every player selection.

Your comboboxes will find it easier to disregard players already selected.

 

Last edited: Mar 28, 2003

The_Doc_Man

Immoderate Moderator

Staff member

Local time

Today, 16:13

Joined

Feb 28, 2001

Messages

30,038

• Mar 25, 2003

• #5

To add to what Mile-o-Phile said (and he is spot-on with the suggestion), you can do a DCount query so that you can find how many times a player has been selected (in the selections table) for a given team, date, and player's ID. Then, if the count is anything other than zero, don't allow that player to be selected again.

 

Mile-O

Back once again...

Local time

Today, 22:13

Joined

Dec 10, 2002

Messages

11,316

• Mar 25, 2003

• #6

Also, rather than use combo boxes to select the players - why not try this idea?

 

Attachments

• dbteams.zip

  24 KB · Views: 189

D

derlwyn

Registered User.

Local time

Today, 22:13

Joined

Mar 21, 2003

Messages

12

• Mar 28, 2003

• #7

normalisation

Tried your suggestion and it works,

however I have a question about normalisation

If every team picked for a game has 5 players and 5 players only then how is it that the strucutre for table team

teamid, player1id, player2id, player3id, player4id, player5id

is not a suitable structure for this DB