calculate values in a form

D

Distressed Golfer

We are doing a handicap register for a golf club and really need some help
about how to calculate the handicap. The formula we want touse is for each
round the difference between the actual score and the par value with a
maximum of 2 per hole and then take the average of three rounds. A further
complication is we want the max handicap for a women or junior to be 36 and
just 28 for a man. We are really totally lost how to do this. This is our
first ever database.
 
S

Steve

Start with these tables:

TblGolfer
GolferID
FirstName
LastName
IsFemaleOrJunior (Yes/No)
Handicap

TblHole
Hole
Par

TblScore
ScoreID
GolferID
ScoreDate

TblScoreByHole
ScoreByHoleID
ScoreID
Hole
Score
HoleHandicap

TblGolfer and TblHole are self-explanatory. Note that a golfer's handicap is
recorded in TblGolfer.

To begin describing how this works, you did not describe how handicap is
calculated if a golfer hasn't played three rounds. So, you need to figure
out how to compute a golfer's initial handicap and then enter that into the
Handicap field in TblGolfer.

You need a form/subform/subform for entering rounds of golf. The main form
is based on TblGolfer so enable you to go to any golfer. You need a subform
to the main form based on TblScore. You then need a subform in this subform
based on TblScoreByHole. When you are entering a round and recording the
scores on each hole in the subform based on TblScoreByHole, you need some
VBA code records the difference between the score and par if it is less than
2 or records a 2 if it is equal to or greater than 2. The handicap for a
round can be calculated at any time by summing the HoleHandicap for the
round so you do not need to record the handicap for the round.

After you have entered a round, you need a procedure for updating the
golfer's round. The procedure would first check to see if the golfer has
played three rounds including the current round and if he has, the procedure
would average the handicaps of the current round and the two previous rounds
and update the golfer's Handicap in TblGolfer. In this procedure you would
account for the max handicap for a woman or junior to be 36 and just 28 for
a man.

There are some questions you need to consider such as what do you do about
partial rounds, do you allow a bad round to raise a golfer's handicap and do
you count the rounds in the previous seaon when computing the handicap at
the beginning of a new season.

I am sure this is very overwhelming!!! If you want help setting this all up,
I can help you for a very reasonable fee.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]
 
J

John Marshall, MVP

What is overwhelming is your pig headedness in ignoring the rules.

These newsgroups are for FREE peer to peer support, not for a pseudo expert
to hawk his dubious services.

John... Visio MVP
 
J

John Marshall, MVP

I did a quick look at your solution and it is obvious, that you know about
as much about golf as you do about Access.

1) You would actually create a design that included gender and age as a
single boolean field? If you had actually read the specs you would have seen
that the maximum handicap was 36 no matter what age or gender. (a two stroke
cap on 18 holes is a max of 36). So the only check would be for male older
than junior.
2) You want a table for each hole? The holes are numbered 1 to 18 a single
record would have been adequate.
3) No structure for a round? You need a record with a score for each of the
eighteen holes. The actual par round for the course can be entered into this
table.
4) Why a table of ScoreByHole? Are you expecting them to go out and play
only the odd holes?
5) You also only need to calculate based on the total score for a round, not
on the individual holes.
To begin describing how this works, you did not describe how handicap is
calculated if a golfer hasn't played three rounds. So, you need to figure
out how to compute a golfer's initial handicap and then enter that into
the Handicap field in TblGolfer.
Click to expand...

The initial handicap is zero. Once he has played a round, his handicap is on
the way to being established. It's the average, so it does not make a lot of
difference if it is 1, 2 or 3 rounds.
You then need a subform in this subform based on TblScoreByHole.
Click to expand...

Can we say overkill?
When you are entering a round and recording the scores on each hole in the
subform based on TblScoreByHole, you need some VBA code records the
difference between the score and par if it is less than 2 or records a 2
if it is equal to or greater than 2. The handicap for a round can be
calculated at any time by summing the HoleHandicap for the round so you do
not need to record the handicap for the round.
Click to expand...

Let's make it difficult so we can charge more. We should also store a
calculated field like HoleHandicap in a table. Right steve, YOU are the
expert.

All that is required is to store the raw unadultered scores.
In this procedure you would account for the max handicap for a woman or
junior to be 36 and just 28 for a man.
Click to expand...

As I pointed out, the maximum handicap is already 36, so you only need to
cap for non junior men.

Ony key question you missed was whether rounds at other courses count. This
can easily be handled by adding extra records to the rounds table
representing the pars for other courses.

John... Visio MVP
 
T

Tony Toews [MVP]

Steve said:
I am sure this is very overwhelming!!!
Click to expand...

Overwhelming because you've bloated the design considerably. And
unnecessarily. Golf courses have 9, 18 or occasionally 27 holes.
Thus that should be one record.
If you want help setting this all up,
I can help you for a very reasonable fee.
Click to expand...

Please stop trolling for customers in newsgroups. This is against the
rules.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
S

Steve

Dear Distressed Golfer,

Please ignore the idiots that responded to your post!!

You need to record the score for every hole so you can limit the hole
handicap to a max of 2 as you specify. You need to be able to calculate the
handicap for each round so you can average the last three rounds as you
specify NOT average all rounds as one idiot said. Lastly, you need to know
if the golfer is a woman or junior so you can set that golfer's handicap at
a max of 36. You use a Yes/No field; if checked the golfer's max handicap is
set at 36, if left unchecked, the golfer is a man and his max handicap is
set at 28. Finally, there is a one-to-many relationship between a round of
golf and the par for each hole. You need TblHole to record the par for each
hole so you can compare each golfer's score on each hole to get his handicap
for the hole and ultimately hold it to a max of 2 as you describe.

BTW, the last idiot that responded uses 50+ characters for field names,
table names, query names, form names and report names and thinks that is
cool!!! He is probably very sad his own name is so short!!!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]
 
S

Steve

This is a great example of the "free support" you preach about!!! You have
demonstrated that the reason it is free is that it "ain't worth a tinker's
damn"!! You missed giving the OP another piece of your "Free Advise"; you
didn't tell him that he should use object names that are 50+ characters
long!! Let go of your anger at being told you were wrong, you're letting it
make an idiot of you!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]
 
J

John Marshall, MVP

You have already proved your incompetence at database many times before
Please ignore the idiots that responded to your post!!
Click to expand...

Great dialog. So someone who does not agree with you is an idiot?
You need to be able to calculate the
handicap for each round so you can average the last three rounds as you
specify NOT average all rounds as one idiot said.
Click to expand...

I guesss you never really passed grade nine math as I speculated before. I
never said that it was to be done for all rounds and your original design
did not have the concept of a round , but a handicap per hole.
if checked the golfer's max handicap is set at 36,
Click to expand...

By design the max handicap is set to 36.
You need TblHole to record the par for each hole so you can compare each
golfer's score on each hole to get his handicap for the hole and
ultimately hold it to a max of 2 as you describe.
Click to expand...

Again, more overkill.
BTW, the last idiot that responded uses 50+ characters for field names,
table names, query names, form names and report names and thinks that is
cool!!!
Click to expand...

To much of a wimp to use his name. It's Tony and I am proud that I have had
the oppurtunity of meeting him.
He is probably very sad his own name is so short!!!
Click to expand...

and your REAL name is PCDatasheet? Or are you just ashamed of being known as
steve g santus? Does the "g" stand for gullible?

Some one should copyright the word "PCDatasheet" and sue you for
infringement. PCDatasheet.com does come up for renewal on Sept 19th next
year.

John... Visio MVP
 
J

John Marshall, MVP

Let go of your anger

You REALLY do not know Tony. Anger is not in his nature, and he has come to
your partial defence on several occassions.

It appears that you are the one who is angry, mainly because you have been
exposed (again) as a fraud.

John... Visio MVP
 
T

Tony Toews [MVP]

Steve said:
You missed giving the OP another piece of your "Free Advise"; you
didn't tell him that he should use object names that are 50+ characters
long!! Let go of your anger at being told you were wrong, you're letting it
make an idiot of you!
Click to expand...

What anger? Was I supposed to be angry? Where was I wrong? I've
completely forgotten.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
T

Tony Toews [MVP]

Steve said:
BTW, the last idiot that responded uses 50+ characters for field names,
table names, query names, form names and report names and thinks that is
cool!!! He is probably very sad his own name is so short!!!
Click to expand...

<chuckle>

Very occasionally I will use table, query, form and report names that
are 50+ characters long. Not, to my knowledge, field names. However
when you're dealing in an app with 160 tables, 1200 queries, 450
forms, 350 reports and 70,000 lines of code sometimes you have to get
a bit wordy.

The relationships diagram is the full 4' width of the relationships
window and about 3' tall. Stephen Lebans had to make me a special
utility to stitch the relationships windows together just so we could
print it. The maximum report size is 22" x 22", IIRC, and that was
way too small.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
T

Tony Toews [MVP]

Gina Whipp said:
FREE data model to set up a golf database:
http://www.databasedev.co.uk/golf_club_data_model.html

FREE discussion on calculating handicaps:
http://groups.google.com/group/comp...k=gst&q=golf+handicap&rnum=2#c94386112aa56027

FREE Golf Tournament example by John Viescas:
http://www.viescas.com/Info/links.htm
Click to expand...

<chuckle> Gotta love that word free, free, free.

Thanks, Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
T

Tony Toews [MVP]

Steve said:
This is a great example of the "free support" you preach about!!! You have
demonstrated that the reason it is free is that it "ain't worth a tinker's
damn"!! You missed giving the OP another piece of your "Free Advise"; you
didn't tell him that he should use object names that are 50+ characters
long!! Let go of your anger at being told you were wrong, you're letting it
make an idiot of you!
Click to expand...

All that and no response to your central problem with you Steve. Why
are you trolling for customers against the rules?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
T

Tony Toews [MVP]

Tony Toews said:
All that and no response to your central problem with you Steve. Why
are you trolling for customers against the rules?
Click to expand...

All that and no response to our central problem with you Steve. Why
are you trolling for customers against the rules?

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
F

Fred Boer

Dear Tony:
The relationships diagram is the full 4' width of the relationships
window and about 3' tall. Stephen Lebans had to make me a special
utility to stitch the relationships windows together just so we could
print it. The maximum report size is 22" x 22", IIRC, and that was
way too small.
Click to expand...

If you ask me, that *has* to qualify as some kind of art! One day, when I
create my "Access Lair" in the basement, I'm going to ask you for a signed
copy of that printout, and frame it for hanging! <g>

Cheers!
 
T

Tony Toews [MVP]

Fred Boer said:
If you ask me, that *has* to qualify as some kind of art!
Click to expand...

I've heard of two systems a bit larger, one that had 220 tables.
One day, when I
create my "Access Lair" in the basement, I'm going to ask you for a signed
copy of that printout, and frame it for hanging! <g>
Click to expand...

Sure, but I'll have to find a plotter to print it on. I fired that
client a while back due to a new *rsehole manager type who thought he
knew something about software so I don't have access to a plotter any
more.

BTW I'm told by some MS folks that the relationships diagram was
hanging in a hallway at Microsoft for six months or a year. Folks
would walk by and say "That was done in Access?!!?!"

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
A

Arno R

Steve said:
Dear Distressed Golfer,

Please ignore the idiots that responded to your post!!
Click to expand...

It is always nice when you make these jokes... I appreciate a good laugh in the morning... Thanks !!

You were the first responder, ha,ha,ha,ha
But you ALWAYS forget to look at yourself don't you ??

Arno R
 
Top