Query Calculation Help Please!

[Guest]

I have asked and received great help from this list and I hope this time is
the same. I need to calculate points based on the number of entries and the
place received in a competition. It goes something like this:
1 entry, first place =1 point
2 entries, first place =2 points, 2nd place =1 point
3 entries, first place = 3 points, 2nd place =2 points, 3rd place =1 point
4 entries, first place =4 points, 2nd place =3 points, 3rd place =2 points,
4th place =1 point
5 entries, first place =5 points, 2nd place =4 points, 3rd place=3 points,
4th place=2 points, 5th place = 1 point
6 entries, first place =6 points, 2nd place=5 points, 3rd place=4 points,
4th place=3 points, 5th place=2 points, 6th place=1 point

I have worked on this all day and am now fried!
Thanks for any help you can give me.

 

[John Vinson]

I have asked and received great help from this list and I hope this time is
the same. I need to calculate points based on the number of entries and the
place received in a competition. It goes something like this:
1 entry, first place =1 point
2 entries, first place =2 points, 2nd place =1 point
3 entries, first place = 3 points, 2nd place =2 points, 3rd place =1 point
4 entries, first place =4 points, 2nd place =3 points, 3rd place =2 points,
4th place =1 point
5 entries, first place =5 points, 2nd place =4 points, 3rd place=3 points,
4th place=2 points, 5th place = 1 point
6 entries, first place =6 points, 2nd place=5 points, 3rd place=4 points,
4th place=3 points, 5th place=2 points, 6th place=1 point

I have worked on this all day and am now fried!
Thanks for any help you can give me.

It looks like you just want

[Entries] - [Place] + 1

But since you haven't posted any indication of your table structure or
where the values for entries and place are stored or can be
calculated, it's a bit hard to give specific help!


John W. Vinson[MVP]

 

[Guest]

Thanks for your reply. You are sort of right about not including any table
structures. I have 2 tables: tblClassEntries and tblHorse. In
tblClassEntries, the field Place holds the competitors placing (a number
1-6). I want a calculated field (Points) in the query where I join
tblClassEntries and tblHorse with the constraints I listed previously. There
is a field in this query (from another query) that contains the final entries
in the class. There may be more than 6 entries in a class, but points are
only calculated to 6th place.

Does this help?
Thanks.

 

[John Vinson]

Thanks for your reply. You are sort of right about not including any table
structures. I have 2 tables: tblClassEntries and tblHorse. In
tblClassEntries, the field Place holds the competitors placing (a number
1-6). I want a calculated field (Points) in the query where I join
tblClassEntries and tblHorse with the constraints I listed previously. There
is a field in this query (from another query) that contains the final entries
in the class. There may be more than 6 entries in a class, but points are
only calculated to 6th place.

Does this help?

How about

IIf([Entries] > 6, 6, [Entries]) - [Place] + 1

You might need to use DLookUp on the other query to find the value of
[Entries], or use DCount() on some table to count the relevant
entries.

John W. Vinson[MVP]

 

[Guest]

Thanks for your reply. I tried the following and it appears to work
correctly. I had another query similar to this that works and I adjusted
some of the numbers to fit my requirements.

SELECT tblClassEntries.PlaceJ1,
IIf([FinalEntries]>=1,([FinalEntries]-[PlaceJ1])+1,0) AS Points1,
IIf([Points1]>CInt(Nz(Choose([PlaceJ1],6,5,4,3,2,1),0)),CInt(Nz(Choose([PlaceJ1],6,5,4,3,2,1),0)),[Points1]) AS Points

I think we are basically doing the same thing only your solution looks
shorter.
DN

Thanks for your reply. You are sort of right about not including any table
structures. I have 2 tables: tblClassEntries and tblHorse. In
tblClassEntries, the field Place holds the competitors placing (a number
1-6). I want a calculated field (Points) in the query where I join
tblClassEntries and tblHorse with the constraints I listed previously. There
is a field in this query (from another query) that contains the final entries
in the class. There may be more than 6 entries in a class, but points are
only calculated to 6th place.

Does this help?

How about

IIf([Entries] > 6, 6, [Entries]) - [Place] + 1

You might need to use DLookUp on the other query to find the value of
[Entries], or use DCount() on some table to count the relevant
entries.

John W. Vinson[MVP]