Unique values (1 Viewer)

[RickPass]

Background:
I have a table call tblTeams. In it i have Player1ID & Player2ID. The IDs stored in tblTeams come from table tblPlayers and each ID is unique in tblPlayers.
In tblTeams records are created by selecting 2 players from tblPlayers. Each record in tblTeams is unique

tblTeams fields (other fields exist but not relavent to my issue)
ID (AutoNumber)
Player1ID (Number)
Player2ID (Number)

If I create record int tblTeams like:
ID Player1ID Player2ID
1 10 20
2 11 12
3 24 32

Scenario1:
I try to add another record: "10" as Player1ID "20" as Player2ID
I unique compound key on Player1ID & Player2ID will prevent that - that works fine

Scenario2:
I try to add another record: "20" as Player1ID "10" as Player2ID
The compound key will not catch that - thats not good!

Question:
Is there a way to create a unique identifier so that any combination of "10" & "20" will not be allowed once that combination is used?
Any type of math addition of the values would fail do to other combination of IDs equating to the same sum value.

Possible options: (Don't know how to do these- help would be appreciated!)
1) Maybe restricting the entry of a lower Player2ID then a Player1ID in the form
2) Or, before_update possibly, looping thru the recordset to see if Player1ID and Player2ID have been used (seems to be not an efficient way to check)

I welcome any ideas with open arms!!!

Thanks Rick

 

[DHookom]

Since all data entry should be done in a form, I would use the before update event with a little code with a dLookup() to identify an issue.

 

[MajP]

I would use a dcount on the forms before update. Something like this but untested

Private Sub From_BeforeUpdate(Cancel as integer)

 dim strSql as string
if isNumber(player1ID) and IsNumber(Player2ID)  then
   strSql = "(Player1ID = " &  me.player1ID & " AND Player2ID = " & Me.Player2ID &  ") OR "(Player1ID = " &  me.player2ID & " AND Player2ID = " & Me.Player1ID & ")"
    debug.print strSql
   if Dcount("*", "tblTeams",StrSql) > 0 then
     msgbox "Combination Taken", vbinformation
     cancel = true
   end if
  else
      Msgbox "Supply Player names"
      cancel = true
  end if
 
  end sub

That sql hopefully resolves to something like

(Player1ID = 10 and Player2ID = 20) OR (Player1ID = 20 and Player2ID = 10)

 

[MajP]

You could make it a little easier by making a union query first

qryBothWays
Select Player1ID, Player2ID from tblTeams
UNION
Select Player2ID, Player1ID from tblTeams

Then it should be something like
Dcount("*", "qryBothWays", "Player1ID = " & me.player1ID & " AND Player2ID = " & Me.Player2ID) > 0

 

[RickPass]

dim strSql as string if isNumber(player1ID) and IsNumber(Player2ID) then strSql = "(Player1ID = " & me.player1ID & " AND Player2ID = " & Me.Player2ID ") OR "(Player1ID = " & me.player2ID & " AND Player2ID = " & Me.Player1ID ") if Dcount("*", "tblTeams",StrSql) > 0 then msgbox "Combination Taken", vbinformation cancel = true end if else Msgbox "Supply Player names" cancel = true end if

I will give it a try. Thanks for the quick response!!

 

[RickPass]

I would use a dcount on the forms before update. Something like this but untested

Private Sub From_BeforeUpdate(Cancel as integer)

 dim strSql as string
if isNumber(player1ID) and IsNumber(Player2ID)  then
   strSql = "(Player1ID = " &  me.player1ID & " AND Player2ID = " & Me.Player2ID &  ") OR "(Player1ID = " &  me.player2ID & " AND Player2ID = " & Me.Player1ID & ")"
    debug.print strSql
   if Dcount("*", "tblTeams",StrSql) > 0 then
     msgbox "Combination Taken", vbinformation
     cancel = true
   end if
  else
      Msgbox "Supply Player names"
      cancel = true
  end if
 
  end sub

That sql hopefully resolves to something like

(Player1ID = 10 and Player2ID = 20) OR (Player1ID = 20 and Player2ID = 10)

Thanks so much for the code!!! It works fine.

 

[CJ_London]

The other way would be to have a table

Pk	teamfk	Playerfk
1	1	10
2	1	20
3	2	11

And not allow duplicates of playerfk - but does depend on whether teams can change

 

[Mark_]

My solution is a bit different.

I would use a junction table to link your players to teams. This would let you include "Season" as well.

Your junction would only have its own ID, the team ID, and the player ID. Reason I'd keep season in there is for tracking past participation. Either way, you can then check to see if you've already added that player to that team (Or any team) for that season and not have to worry about "Player 1" or "Player 2".

This goes back to normalizing your data. As soon as you see "PlayerNumber" in your table, you should probably move them to a child table to avoid issues exactly like this.

Also means if you increase group sizes you don't have to worry about changing data structures. You could even have a "Max players" field in your "Team" table and a query to verify you don't have too many players for when you decide to include a football team!

 

[Pat Hartman]

If it doesn't matter which is playerA and which is playerB, you could always assign the lowest ID value as playerA and the higher one as playerB. You can't do that with games like chess because being Black or white impacts the game to some degree. So that solution can only work if it makes sense within the rules of the game.

The other simple solution is to create TWO unique indexes. One with playerA, playerB and the other as playerB, playerA. There is no logical or design issue with two (actually 3 if you included an autonumber as the PK) unique indexes. The indexes add a bit of time when you are creating/changing a record and take up space but both are small prices to pay for not having to deal with the issue using code. Getting the database engine to enforce your rules is always better. You may have to trap the "duplicate" error message if the user is confused by it and present a less technical one that the user will be able to understand.

 

[MajP]

The other simple solution is to create TWO unique indexes. One with playerA, playerB and the other as playerB, playerA. There is no logical or design issue with two (actually 3 if you included an autonumber as the PK) unique indexes. The indexes add a bit of time when you are creating/changing a record and take up space but both are small prices to pay for not having to deal with the issue using code.

However that would only stop the user from entering 10, 20 twice or 20, 10 twice. Would not stop the user from entering 10, 20 and then 20, 10.

 

[Mark_]

@ OP,
Do you need to prevent a player from being on more than one team?

 

[MarkK]

Another approach is to enforce logic using classes rather than database constraints. In some cases, like this, where database constraints are not well suited to a problem, it is sometimes easier to just read all your data into classes that enforce the logic themselves. Iterating over a dictionary or collection of object instances is sometimes fast enough for small data sets. In other languages it is common practice to read data into a class, and bind the UI to that class. The class can then take responsibility for validating user input.

Anyway, just want to make the point that data validation can take place at other layers in an application, not just at the database.

 

[CJ_London]

or you could just include a FK to the teams table in the players table. That way a player can only be assigned to one team.

 

[Pat Hartman]

However that would only stop the user from entering 10, 20 twice or 20, 10 twice. Would not stop the user from entering 10, 20 and then 20, 10.

True. I should have stopped while I was ahead