Help with Query Calculation

[Guest]

I am asking for assistance in constructing a query calculation to assign
points to how a horse is placed in a class at a show.

6 places are tied if there are 6 or more entries in the class. Only the
1st-6th would get points. The spread would look like this:
1st-6 points
2nd-5 points
3rd-4 points
4th-3 points
5th-2 points
6th-1 point

If there were only 5 entries in the class, the spread would look like this
1st-5 points
2nd-4 points
3rd-3 points
4th 2 points
5th-1 point

The spread would continue with 4 entries in the class or 3 in the class or 2
in the class until there may be only 1 in the class where that entry would
only get 1 point.

Any assistance in getting this started would be appreciated.
Debbie

 

[Jeff Boyce]

I may not be understanding, but it sounds like you are saying that the
number of points for the 1st place finisher is = the number of entries in
the class (with a maximum of 6).

If that works, then one way to look at the 2nd place finisher's score is the
difference between 1st and 2nd place (i.e., 1), subtracted from the number
of entrants (with a maximum ...).

It might be a bit of a brute force approach, but the values for finishers
1 - 6 could all be calculated that same way. You'd just throw out any zero
or negative points (because this would mean there weren't that many
entrants).

Or have I totally missed your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

 

[Guest]

Looks like you do understand the challenge. Or, at least it is a challenge
to me. Is there a way to do this with an IIf statement? I do want to throw
out (ignore) zero or negative points.

 

[Jeff Boyce]

Without a bit more description of your underlying data structure, and
possibly a look at the SQL of whatever query you are now using, it will be a
little tough to come up with suggestions.

You may want to take a look at Access HELP on the DCount() function. You
might be able to use this (instead of an IIF() function) to calculate how
many entrants you have in a category.


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,
Thanks for your replies. I did take a look at the DCount function and I am
not sure it is what I need. Here is the query I am using to calculate on
entries/points that award a bonus point. The scenario I described below only
awards straight points, no bonus. I have tried manipulating some of the
values but have not hit the right one yet. Any assistance would be
appreciated. Am I even on the right track here?

SELECT tblClassEntries.PlaceJ1,
IIf([FinalEntries]>1,([FinalEntries]-[PlaceJ1])*0.5,0) AS Points1,
IIf([FinalEntries]>3 And [PlaceJ1]=1,1,0) AS Bonus,
IIf([Points1]+[Bonus]>CInt(Nz(Choose([PlaceJ1],7,5,4,3,2,1),0)),CInt(Nz(Choose([PlaceJ1],7,5,4,3,2,1),0)),[Points1]+[Bonus])
AS Points, qryEntriesPerClass2.FinalEntries, tblClassEntries.[Class#],
tblHorse.[Back#], tblHorse.HorseName, tblClassEntries.RiderAge,
tblClassEntries.RiderName, tblHorse.OwnerFullName, tblShowBill.ShowName,
tblShowBill.A
FROM tblHorse INNER JOIN ((tblClassEntries INNER JOIN qryEntriesPerClass2 ON
tblClassEntries.[Class#] = qryEntriesPerClass2.[Class#]) INNER JOIN
tblShowBill ON (tblShowBill.[Class#] = tblClassEntries.[Class#]) AND
(qryEntriesPerClass2.[Class#] = tblShowBill.[Class#])) ON tblHorse.[Back#] =
tblClassEntries.[Back#]
ORDER BY tblClassEntries.PlaceJ1, qryEntriesPerClass2.FinalEntries;

 

[Jeff Boyce]

One approach that's worked for me before is to "chain" together several
queries. In your situation, I might first write a query that calculates all
the points, bonus, etc. Then I'd write a second query, using the first
query as a source. In the second query, I'd carry out the additional
calculations (like adding Points and Bonus). If there are an intricate
series of steps/calculations, I've been know to chain as many as 5 or 6
queries, each building on the earlier results. Not pretty, not elegant, but
it works.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Thanks for your replies. I did take a look at the DCount function and I
am
not sure it is what I need. Here is the query I am using to calculate on
entries/points that award a bonus point. The scenario I described below
only
awards straight points, no bonus. I have tried manipulating some of the
values but have not hit the right one yet. Any assistance would be
appreciated. Am I even on the right track here?

SELECT tblClassEntries.PlaceJ1,
IIf([FinalEntries]>1,([FinalEntries]-[PlaceJ1])*0.5,0) AS Points1,
IIf([FinalEntries]>3 And [PlaceJ1]=1,1,0) AS Bonus,
IIf([Points1]+[Bonus]>CInt(Nz(Choose([PlaceJ1],7,5,4,3,2,1),0)),CInt(Nz(Choose([PlaceJ1],7,5,4,3,2,1),0)),[Points1]+[Bonus])
AS Points, qryEntriesPerClass2.FinalEntries, tblClassEntries.[Class#],
tblHorse.[Back#], tblHorse.HorseName, tblClassEntries.RiderAge,
tblClassEntries.RiderName, tblHorse.OwnerFullName, tblShowBill.ShowName,
tblShowBill.A
FROM tblHorse INNER JOIN ((tblClassEntries INNER JOIN qryEntriesPerClass2
ON
tblClassEntries.[Class#] = qryEntriesPerClass2.[Class#]) INNER JOIN
tblShowBill ON (tblShowBill.[Class#] = tblClassEntries.[Class#]) AND
(qryEntriesPerClass2.[Class#] = tblShowBill.[Class#])) ON tblHorse.[Back#]
=
tblClassEntries.[Back#]
ORDER BY tblClassEntries.PlaceJ1, qryEntriesPerClass2.FinalEntries;

Without a bit more description of your underlying data structure, and
possibly a look at the SQL of whatever query you are now using, it will
be a
little tough to come up with suggestions.

You may want to take a look at Access HELP on the DCount() function. You
might be able to use this (instead of an IIF() function) to calculate how
many entrants you have in a category.


Regards

Jeff Boyce
Microsoft Office/Access MVP