[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 656
  • Last Modified:

Combo Box for golf tournament database

I am running a golf tournament and have set up a database all components are working well.  However I would like to add a little enhancement.  We have 36 teams with 4 players on each team.  Starting Hole table runs a cboStartingHole with a field showing 1A, 1B, 2A, 2B, etc..  When 1 have assigned 4 players (1 team) to a hole I would like that specific hole to not show in the cbo, to prevent accidentally assigning a 5th player (or worse an entire other team)
0
ukbusiness
Asked:
ukbusiness
1 Solution
 
AndyAmessCommented:

what is the sql behind your combo box?
0
 
Rey Obrero (Capricorn1)Commented:
you can use

Select Hole From tblHoles where tblHoles.Players < 4
0
 
ukbusinessAuthor Commented:
SELECT DISTINCTROW tblStartingHole.* FROM tblStartingHole ORDER BY [tblStartingHole].[StartingHole];
0
 
Rey Obrero (Capricorn1)Commented:
Try this;

SELECT DISTINCTROW tblStartingHole.* FROM tblStartingHole
WHERE ([tblStartingHole].[PlayersField] < 4)
ORDER BY [tblStartingHole].[StartingHole];
0
 
Steve BinkCommented:
This will show you all the holes assigned to players and how many players are assigned to each hole:

SELECT HoleID, Count(PlayerID) AS NumPlayers FROM Holes LEFT JOIN Players ON Holes.HoleID = Players.HoleID GROUP BY Holes.HoleID

To grab all the holes with less then 4 players:

SELECT HoleID FROM (SELECT HoleID, Count(PlayerID) AS NumPlayers FROM Holes LEFT JOIN Players ON Holes.HoleID = Players.HoleID GROUP BY Holes.HoleID) WHERE Nz(NumPlayers,0)<4
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now