Allen Browne said:
To achieve the goal of eliminating any record that is within 10 minutes
prior to the same, time, you will need to learn to create subqueries.
See:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord
Having eliminted these "duplicates", you can then create a Totals query to
get the count.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
I am trying to build a database for FoxHunting that no one has ever been able to build very complicated to get it setup according to the rules we have.
If you will help I will try to explain the best I can
We have 2 classes of hounds
Numbers 000 to 199 are Derby Hounds
Numbers 200 to 899 are All-Age Hounds
Numbers 900 to 999 are Derby Hounds
So need it to be able to when I put In a hound Number it will know it is a
Derby or All-Age hound.
Next we have Four Catagorys that the hound ca be scored in
Speed and drive
Hunting
Trailing
Endurence
Speed and Drive
If a hound crossed the road in first place he get 35 points, if a hound
crosses in second place he get 30, third he get s 25, fourth 20 and any place
after fourth he get 15 points,
Each hounds score will only count if it is over 10 minutes apart from each
other for example as I put in question:
Hound Number 999 score 35 Time 11:00
Hound Number 999 score 30 Time 11:05
Hound Number 999 score 25 Time 11:40
Out of these scores hound number 999 will only be able to keep the first
score, and third score, because they are not within 10 minutes any thing
within 10 minutes of each other will not count,( but I need to be able to put
up separately all score for this each hound)
2. Hunting:
Hunting is a little more difficult:
A hound foring the first hour of any one days hunt can score 10 points per
judge in the first hour and 15 second hour, 20 for third hour 25 for fourth
hour and 30 for fifth hour. But a hound can only score 100 point in each of
the three days of running.
3. Trailing
A hound can not be score more than 30 point in any one interval of twenty
minutes, no hound shall receive a maximum of 30 points at anyone interval
unless he produces a fox or wolf,
4. Endurence
All hounds that finish a field trail and that
have scored in either Hunting or Trailing AND have also
scored in Speed and Driving during the entire hunt, with a
sum total of 40 points or more, will automatically earn an
Endurance Score of 1/4 of his or her total score across the
board, beginning with ten points, adding in multiples of
five where permissible - to be computed only on the
final day on the hounds tllat have finished the trials. Each
day's score will count, but figured only on the last day. The
Endurance 'score will be figured on the last day and the
total posted on the Daily Sheets in t.he Endurance colunm
before making sum total of all scores and before starting to
figure the Highest General Average.
The results: Each hound getting an Endurance score will
have received it from his other classes. No hound, therefore,
can have an Endurance score, he or she has not earned from
other scores.
To get an Endurance score, hounds must have scored in
either Hunting or Trailing, as well as, having scored also
in Speed and Driving, during the entire trial. No hound,
therefore, can have an Endurance score which he or she
has not earned.
EXAMPLES
Hound NO.5 - 10 in Hunting, 10 in Trailing, 15 in Speed
and Drive. This hound has scored across the board but he
has less than the sum total of forty (40) points, so is not
eligible for an Endurance score.
Hound No. 20 could have no Hunting, no Trailing, 200
points in Speed and Driving, therefore he could have no
Endurance score.
Hound No. 30 - 10 Hunting, 20 Trailing, 15 Speed and
Driving, a total of 45 points in the entire hunt. He would
get the closest 1/4 of his entire score - 10 points in
Endurance.
Hound No. 40 - 60 Hunting, no Trailing, 90 Speed and
Driving, a total of 150 points for entire hunt. His Endurance
score would be 35 points.
Hound No. 50 -No Hunting, 50 Trailing, 125 Speed and
Driving, a total of 175 points. The closest 1/4 in round
figures - 40 points in Endurance.
Hound No. 60 - 95 Hunting, 65 Trailing, 200 Speed and
Driving, a total of 360 points. This score rounds out in 1/4
to 90 points in Endurance.
All hounds get the Endurance they earn. They must earn
the score they are allowed. (To be figured on the last day)
This same scoring method shall be followed as many
days as judges order stake to continue to final decision.
When the judges are ready for final decision the highest
scored hound in Hunting is winner of the Hunting class.
The highest scored hound in Trailing is winner of the
Trailing class and likewise in Speed and Drive and
Endurance.
Hunting class shall be finished before starting on Trailing
then Speed and Driving and then Endurance in this order
as one class has no bearing on any other class.
HGA this is the final
After all four classes have been finished for each hound
the Highest General Average shall be figured.
The highest general averages of the hounds for the first
len places shall be determined as follows:
The hound scoring highest in each class shall receive a
weight or points of 100.
The hound scoring second in each class shall receive a
weight or points of 90.
The hound scoring third in each class shall receive a
weight or points of 80.
The hound scoring fourth in each class shall receive a
weight or points of 70.
The hound scoring fifth in each class shall receive a
weight or points of 60.
The hound scoring sixth in each class shall receive a
weight or points of 50.
The hound scoring seventh in each class shall receive a
weight or points of 40.
The hound scoring eighth in each class shall receive
weight or points of 30.
The hound scoring ninth in each class shall receive a
weight or points of 20.
The hound scoring tenth in each class shall receive a
weight or points of 10.
Each hound's general average shall be arrived at by
adding together the points received by the hound in each
class.
For example, consider the first hound on your daily score
sheet. If he won Hunting he starts with 100 points. If he is
second in Trailing he gets 90 points additional; third in
Speed and Driving, 80 points; second in Endurance, 90
points additional. Therefore, the No. 1 hound scores 360
points toward H.G.A.
The NO.2 hound perhaps scores second in Hunting and
gets 90 points. He does not score in Trailing, but is second
in Speed and Driving and fourth in Endurance, making him
a total of 250 points toward H.G.A.
Suppose NO.3 hound scored fourth in Hunting, fourth
in Trailing, first in Speed and Driving and third in
Endurance. He would then receive 320 points toward
HGA
After all this said I need to be able to click a button on a form and it
give me the Total Top ten Hound in HGA, that is the winner of the hunt.
Hope you can help if you can, but you don’t have to I been working on this
for nearing a year now but haven’t accomplished it yet and might never
without someones help.
Thanks
Larry
