Dear Tom,
I’ll try to explain – difficult without formatting capability here. I
really appreciate your efforts here. Why do you go to such lengths in
attempt to help a stranger?
Tables:
Members
Membership Types
Tees (Tee ID, Tee Desc, Slope Rating, & Course Rating)
Scores (Score ID, Contact ID, Tee ID, Date, Score {as 9 hole}, &
Differentials)
Reason: The members at my club are disgruntled with our decisions to use the
GHIN handicap system offered by the USGA because of its annual cost. We have
decided to mirror the system using MS Access and I said I could do that free
of charge. (What was I thinking?)
Objective:
USGA handicap calculation:
96% of Average 10 best of the last 20 scores (if 20 scores) else use table
below
(([Score]-[Course Rating]) as Differential * 113 / [Slope Rating])*.96
Number of Acceptable Scores Differential(s) to be Used
5 or 6 Lowest 1
7 or 8 Lowest 2
9 or 10 Lowest 3
11 or 12 Lowest 4
13 or 14 Lowest 5
15 or 16 Lowest 6
17 Lowest 7
18 Lowest 8
19 Lowest 9
20 Lowest 10
Example: For a nine-hole adjusted gross score of 45 with a nine-hole USGA
Course Rating of 36.2 and a Slope Rating of 121, the nine-hole Handicap
Differential is (45 - 36.2) x 113 / 121 = 8.2. If the average of the best ten
nine-hole Handicap Differentials is 8.9, then the Handicap Index (N) is 8.9
x.96 = 8.5 (truncate [not round] to 1 decimal). It is posted as "8.5N."
I have established this query and sub query to calculate the differential of
the latest 20 scores
SELECT Scores.Score_ID, Scores.Date, ([Score]-[Course Rating])*113/[Tee
Slope Rating] AS hdcp_diff
FROM (Scores INNER JOIN Tees ON Scores.[Tee ID] = Tees.[Tee ID]) INNER JOIN
Members ON Scores.[Contact ID] = Members.[Contact ID]
WHERE (((Scores.Date) In (Select Top 20 [Date] From Scores Where [Contact
ID]=[Members].[Contact ID] Order By [Date] Desc)));
I have loaded the differentials to the scores table and created this table
from that
tbl_Last20; Contact ID, Score_ID, Differential
Then the query I referenced here earlier to obtain best 10 of latest 20
scores (can’t get a hard 10 to calculate from)
SELECT tbl_Last20.[Contact ID], tbl_Last20.Score_ID, tbl_Last20.Differential
FROM tbl_Last20 INNER JOIN Members ON tbl_Last20.[Contact ID] =
Members.[Contact ID]
WHERE (((tbl_Last20.Differential) In (Select Top 10 [Differential] From
tbl_Last20 Where [Contact ID]=[Members].[Contact ID] Order By [Differential]
Asc)))
ORDER BY tbl_Last20.[Contact ID];
After I get the best 10 of the last 20 (there may be contacts who have less
than 20, thus referencing the acceptable scores table from above to determine
the number of differentials to be used.
Well if you’re not totally lost now – I am impressed.
Please respond with all additional info needed.
Thanks - Scrappydue
Tom Ellison said:
Dear Scrappy:
OK, I'll bite. You have shown us a table with two columns:
AcceptableScores and DifferentialsUsed. This couldn't be either of the two
tables we started with: tbl_Last20 and Members. Both of them had a column
[Contact ID].
How about showing an example of the data you have, and the results you
expect, plus an explanation of the difficulty. Maybe a bit about what the
application is all about would help.
Tom Ellison
scrappydue said:
<<<<< I thank you all for the efforts >>>>>
I am thinking this can't be done by simple queries alone. Even IF I
figure
out how to get a hard best 10 scores, I then need to figure out how to
calculate scores used when they have less the 20 scores to start. That
would
be by this table -
AcceptableScores DifferentialsUsed
20 10
19 9
18 8
17 7
16 6
15 6
14 5
13 5
12 4
11 4
10 3
9 3
8 2
7 2
6 1
5 1
4 0
3 0
2 0
1 0
0 0
and I don't think I can do that without establishing some kind of
variable -
and believe me when I say that is way over my head at this point.
scrappydue said:
Thanks Tom, without the member table I get only the top 10 values, not
the
top 10 values by contact ID. I need to find only the top 10 scores of
the
last 20 scores by contact to calculate golf handicaps.
:
Dear Scrappy:
Is this happening because there is a tie when sorting by Differential?
I
expect so.
The query will not arbitrarily pick out 10 when this happens. You
could add
another column (or more than one) to the ORDER BY clause, so that the
ordering becomes unique. It might be the case that you could add
[Contact
ID] for example. If the Contact ID is unique, you could then filter on
that
instead. It might be:
SELECT L.[Contact ID], L.Score_ID, L.Differential
FROM tbl_Last20 L
INNER JOIN Members M
ON L.[Contact ID] = M.[Contact ID]
WHERE L.[Contact ID] In (
Select Top 10 [Contact ID]
FROM tbl_Last20
ORDER BY Differential, [Contact ID])
ORDER BY L.[Contact ID];
Unless you have Contact IDs in tbl_Last20 for which there is no
Members.[Contact ID] you could just omit the Members table:
SELECT TOP 10 [Contact ID], Score_ID, Differential
FROM tbl_Last20
ORDER BY Differential, [Contact ID];
If you don't like this order:
SELECT * FROM (
SELECT TOP 10 [Contact ID], Score_ID, Differential
FROM tbl_Last20
ORDER BY Differential, [Contact ID])
ORDER BY [Contact ID]
There is a technical difference by omitting the Members table,
depending on
your relationships and whether you have rows in tbl_Last20 whose
Contact ID
is not in Members. You can re-insert the JOIN if it is needed.
However,
you didn't need any reference to that table from what I could see.
I made some guesses about what you want. Hope I got close.
Tom Ellison
How do I limit the following query to max 10 scores eliminating the
ties
on
the high side?
SELECT tbl_Last20.[Contact ID], tbl_Last20.Score_ID,
tbl_Last20.Differential
FROM tbl_Last20 INNER JOIN Members ON tbl_Last20.[Contact ID] =
Members.[Contact ID]
WHERE (((tbl_Last20.Differential) In (Select Top 10 [Differential]
From
tbl_Last20 Where [Contact ID]=[Members].[Contact ID] Order By
[Differential]
Asc)))
ORDER BY tbl_Last20.[Contact ID];
