golf database

[kiplook]

I am creating a golf database. We play match-play, which means that instead of looking at the final score, each hole results in a win tie or loss and winning the game is based on how many holes were won. So the hole-by-hole scores must be entered into the database. The approach that I’m taking is to have a table with Name, Opponent, Date, Hole 1, Hole 2, …… Hole 18, win/tie/loss.

The ideal way (for the user) is to have a form where both players are entered (names and hole-by-hole scores) and then an enter key is clicked and the database enters the two records (one for each player) into the table and figures the win/tie/loss column.

Is there a way to have 2 records on the same form?

Is there a better way to do this?

 

[KevinM]

Not well up on golf but something like this....

TABLE1: Players
PlayerID (PK)
Surname
FirstName
Handicap ?
etc

TABLE2:Matches
MatchID (PK)
MatchDate
MatchName
Venue ?

TABLE3:MatchesAndPlayers
MatchID (PK)
PlayerID (PK)

TABLE4:MatchScores
MatchID (PK)
PlayerID (PK)
HoleNum (PK)
Score

So the relationship is....

ONE Match to MANY PLAYERS to MANY Scores(Holes).

Like I said, i'm not familiar with the rules so don'y know what you'd put as a 'score' (number of strokes?)

Even so a Totals Query should be able to calculate a WIN/LOSS/DRAW.

Another forum member more familiar with golf may come up with a better way and ways to auto calculate things like 'eagles', 'birdies' etc

What you certainly DON'T want to end up with is a table with 18 fields (1 for each hole), even though a match will always consist of 18 holes.

 

[The_Doc_Man]

I'm assuming that, whether it is sanctioned or not, this is the moral equivalent of a golf league, with individual matches scheduled in some formal or informal fashion.

The variables here are:

1. Golfers
2. Matches between two golfers
3. Scores on each hole in each match

KevinM's answer is not off the mark, but might not be something you wish to consider because of its complexity. On the other hand, what I would offer isn't going to be that much easier.

Tables you will need:

1. Golfers
Arbitrary Golfer ID (could be an autonumber) - primary key
Information about the golfer such as address, phone, etc.

2. Matches
Arbitrary Match ID (could be an autonumber) - primary key
Date of match
Location of match (if not always same place, otherwise skip this)
(This table might be really small!)

3. MatchPlayers
Golfer ID
Match ID
(Both are PK for this table but foreign keys to Golfers & Matches)

4. Holes
Golfer ID
Match ID
Hole #
Score
(PK for this table is combo of Match ID, Golfer ID, Hole #)

You can have more than one sub-form on a parent form. So once you enter the two golfers as two fields on a form, you can define two sub-forms with parent-child relationships between the two players, bound to the combo of golfer # and match #. You KNOW you have 18 holes, so you can perhaps write a query that builds all 18 records at one time. The dirty way to do this is to have a table that shows hole numbers from 1 to 18, then write an improper join to build the hole records by outer joining on the wrong field. The right way to do this is to write code to add new records at the recordset level.

The comparison is easy because you can join on the match numbers and hole numbers, with a SWITCH function to assign the possible results of win, halve, or lose for each hole, perhaps as +1, 0, and -1 for numerics. Then just total it up across 18 holes.

 

[WayneRyan]

kiplook,

See this thread:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=39682&highlight=golf

Wayne

 

[kiplook]

Thanks guys. I think I’m convinced that I do not want to have a table with 18 or more fields. However, this makes my original question even more important. If the data entry form is to look like a score card (which is what I want) I will now have to have 36 records on that one form (18 for each player). How do I set up a form where many records from the same table are on the one form?

 

[KevinM]

You can't.

You have to do it one record at a time.

You can't use Access as a 'spreadsheet'

You can VIEW the data like this by means of a crosstab query.

 

[kiplook]

Could a table be made for data input only, and then upon clicking an ‘enter’ key the data is loaded into another table? Where the first table is the undesirable 36+ field type and the second is the easier to use 5 field type. The first table could be cleared each time it is loaded into the other table. As I mentioned it would be very desirable to have data entry from look like a score card with both player’s scores on it.

I’m not even sure that it would work if it were done as one 36+ field table. In such a table, each record would actually be two records (one for each player).

Help

 

[KevinM]

I think you might be better off using MS Excel.
That would allow you to view and add data as you want it.

 

[Pat Hartman]

Follow the thread that WayneRyan posted. It will take you to a sample db that is structured similarly to how you want to structure yours. It is an excellent example of why NOT to do that. There is a great deal of code required behind the forms and the programmer of the sample db only put some in so the db is far from complete. The normalized table structures may seem harder to work with because you are not familiar with the concepts involved. But, believe me, in the long run you will thank the posters for their advice on how to properly structure the tables.

 

[kiplook]

Thanks Pat:
But I don’t have preference on how to structure the db. The only preference I have is in how to structure the scorecard form. Someone here at work suggested using an append query so that I can have the form that I want and the proper tables. Not sure if that would work, I haven’t tried it yet.

Kevin:
As far as Excel is concerned – Been there. Done that. I didn’t mention when I first submitted this thread that this db is to track wins, ties, losses, net birdies, net eagles, natural birdies, natural eagles, and skins. It is to calculate handicap, actual score, net score, and equitable stroke control score (based on your handicap you can only score so high on a hole).

Before I dive in and change my tables to contain only 4 to 5 fields, does anyone have any advice or comments on using the append query that my co-worker suggested. Or perhaps another way of having a data entry form that looks like a scorecard?

Thanks
Kip

 

[EagleMon]

Golf Database

Kip,
I am an avid golfer myself as well, an IT guy for many years. I have also looked at your concept and believe you me, those other guys understand the complexity of this type of database. As nice as Access is for a lot of things, you should understand it's limitations.
The other side of it is why re-invent the wheel? There are other options out there already. One that I use on my Palm is a product called Intelligolf. There is a desktop version as well. Although this is not your complete solution, it could be used as a tool to help towards the whole solution. You would be able to build a less complex database by keeping the hole details out of your equation.
Of course, if you are hell-bent on building your own solution, than by all means go for it, but there will be a lot of work on this one with Access.
Oh yeah! and to answer your question on the append query concept...there will be many of those in your final solution along with crosstabs etc.

Good luck!


The Eagle has landed!