按照 Ken White 的评论,每场比赛有两支球队,这是有组织的,而且即使在高中,球员也会更换球队:
请参阅此处: https://answers.microsoft.com/en-us/msoffice/forum/all/multiple-foreign-keys-in-table-linked-to-same/b0ecce86-699e-4e43-956e-8306385d0dd9 如果主键的表有别名,关系工具将接受来自同一主键的两个外键。在这种情况下,只需将团队表拖到关系表面上两次即可。
根据 June7 的评论,团队约束的 14 名成员(在我的示例中为 2 名)属于数据验证。我通常也在数据输入期间处理数据验证。在这种情况下,我想说无论如何在表级别处理我所谓的多级约束是不可能的。例如,为了清楚起见,我将约束抽象为一个函数。该函数是公共的,位于代码模块中,因此我可以在数据库中的任何地方重复使用它。
Public Function isValidTeam(TeamID As Long, day As Date) As Boolean
Dim minimum As Integer
minimum = 2 '2 players to a team
Dim teammembercount As Integer
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Select * FROM PlayersTeams Where TeamID = " & TeamID)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
If isValidPlayer(rs!PlayerID, TeamID, day) Then
teammembercount = teammembercount + 1
End If
rs.MoveNext
Loop
End If
If teammembercount >= minimum Then
isValidTeam = True
Else
isValidTeam = False
End If
End Function
Public Function isValidPlayer(PlayerID As Long, TeamID As Long, day As Date) As Boolean
'debug.print isValidPlayer(1, 1, cdate("4/2/24"))
Dim returnvalue As Boolean
returnvalue = Nz(DLookup("PlayerTeamID", "PlayersTeamsStartDateDescending", "PlayerID = " & PlayerID & " AND TeamID = " & TeamID & " AND #" & day & "# >= StartDate AND isnull(EndDate)"), False)
If returnvalue = False Then
returnvalue = Nz(DLookup("PlayerTeamID", "PlayersTeamsStartDateDescending", "PlayerID = " & PlayerID & " AND TeamID = " & TeamID & " AND #" & day & "# >= StartDate AND #" & day & "#"), False)
End If
isValidPlayer = returnvalue
End Function
以下是带有一些级联列表框的表单示例。如果团队有足够的成员,则它是有效的。
当选择一个团队时,将调用 isValidTeam 并显示结果。
Private selectedteam As Long
Public validselectedteam As Boolean
Public selectedday As Date
Public lblText As String
Public Function GetSelectedTeam() As Long
GetSelectedTeam = selectedteam
End Function
Public Function SetSelectedTeam(value As Long)
selectedteam = value
End Function
Private Sub lstTeams_AfterUpdate()
SetSelectedTeam (Me.lstTeams)
selectedday = Me.txtDate
validselectedteam = isValidTeam(selectedteam, selectedday)
If validselectedteam Then
Me.txtValid.ForeColor = RGB(0, 0, 255)
Me.txtValid = "valid"
Else
lblText = "invalid"
Me.txtValid.ForeColor = RGB(255, 0, 0)
Me.txtValid = "invalid"
End If
Me.chkValid = validselectedteam
Dim sql As String
sql = "SELECT Players.PlayerID, Players.PlayerNickName "
sql = sql & "FROM Players INNER JOIN PlayersTeams ON Players.PlayerID = PlayersTeams.PlayerID "
sql = sql & "WHERE (((PlayersTeams.TeamID)=[qryteamid]) AND ((PlayersTeams.StartDate)<[day]))"
sql = Replace(Replace(sql, "[day]", "#" & Me.txtDate & "#"), "[qryteamid]", Me.lstTeams)
Me.lstTeamPlayers.RowSource = sql
Me.lstTeamPlayers.Visible = True
Me.lblTeamPlayers.Visible = True
Me.lstAvailablePlayers.Visible = True
Me.lblAvailablePlayers.Visible = True
Me.Refresh
End Sub