Record level Validation help

[Bill]

Non programmer here. Using Access 2007.

I have created a small database to manage a board game we play on a regular
basis. It's a team game consisting of four players, two each side. The pairs
rotate each weekend so its not set.

I have been able to create a table and a field for Team_1 with multiple
entries which can be selected from a combo box drop down list. My problem is
for Team_2. The same drop down list is used and I need a way to prevent the
same player or players from being entered in the field either by graying out
the names already selected or by preventing the record to be saved if a
duplicate name(s) is selected. Hope I explained it properly. I am
struggling to find some validation rule to prevent this but I cant. So it
appears it may require some greater skill which I don't have.

Thanks for any pointers
Bill

 

[Paul Shapiro]

You didn't give much of your existing data structure detail, but a
reasonable design for something like this might be:

Person (personID, lastName, firstName, ...) Primary Key: personID
Game (gameID, gameDateTimeStart, durationHours) Primary Key: gameID
GameTeam (gameID, teamNumber, teamName, score) Primary Key: gameID,
teamNumber
GameTeamPlayer (gameID, personID, teamNumber) Primary Key: gameID, personID

Your rule about no one being on more than one team in a game is then
enforced automatically by the unique Primary Key. You would create enforced
relationships from Game -> GameTeam, GameTeam-> GameTeamPlayer, and Person->
GameTeamPlayer.

 

[Bill]

Thanks Paul.

Will print this out and give it a try

The structure is pretty basic. I have a table for Players, with one field
for Name (first and last, not separate fields) Maybe I should split the
latter?

Another table for scores/games where I select the teams from the Players
table, two per side. The Scores table also has date and of course the
scores. The teams or pairs do not use "team" names, just their individual
names.

Hope that gives a better idea
Thanks again
Bill