Solved

Combo Box for golf tournament database

Posted on 2004-03-31
5
627 Views
Last Modified: 2012-05-04
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
Comment
Question by:ukbusiness
5 Comments
 
LVL 4

Expert Comment

by:AndyAmess
ID: 10725855

what is the sql behind your combo box?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10725856
you can use

Select Hole From tblHoles where tblHoles.Players < 4
0
 

Author Comment

by:ukbusiness
ID: 10726256
SELECT DISTINCTROW tblStartingHole.* FROM tblStartingHole ORDER BY [tblStartingHole].[StartingHole];
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10726379
Try this;

SELECT DISTINCTROW tblStartingHole.* FROM tblStartingHole
WHERE ([tblStartingHole].[PlayersField] < 4)
ORDER BY [tblStartingHole].[StartingHole];
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 250 total points
ID: 10726495
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now