MS Access SUM problem 1

[tipster69]

Hi, I have a database that I wrote in MS Access some months ago (via lots of questions on forums and various books).

I need to add a new query to it that I am having some problems with. I will give you some background info to it.

I run a poker league and the database is used to create the league (all this runs fine) I now want to add another "mini league" thats is worked out in a different way.

In the creating of the "main league" the data that is input that is of importance is "playername", "winnings", "placed".

From this information it should be possible to create my new "mini league" without the need for any further data.

To create the "mini league" I need to look at the "placed" field and if it is between 1 and 10 give points as such (10 points for a place 1 down to 1 point for a place 10)

I have managed to do this (the long way tho i presume) by having 10 of the following in a query

place1: Sum(IIf([Infosheet 001]!Placed=1,10))

I have then repeated this in another 9 fields as follows

place2: Sum(IIf([Infosheet 001]!Placed=2,9))

This works fine in calculating the points for me for each players places.

The problem I have is that I CANNOT add these together to give me a "TOTAL POINTS".

I have tried every variation I can think of. I thought something like totalpoints=([place1] + [place2] + [place3] etc) would work but it does not.

Im tearing my hair out with this any help will be ABSOLUTLEY MOST GREATFULLY RECEIVED (I hate begging)

Many Thanks

 

[PHV]

And what about something like this ?
totalpoints: Sum(IIf([Infosheet 001].Placed<=10, 11-[Infosheet 001].Placed, 0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tipster69]

Hi and tnx for your reply.
I will give that a try.

Can I just add I would never have thought of something so complex.

If it works or if it does not I consider you a genius

tnx

 

[tipster69]

Hi.

I have tried that and it is almost there.

If everyone had a position from 1 - 10 it would definately work. The problem comes in for the people that do NOT place i.e placed=0...they all get 11 points using the above formula

Is it possible to use a boolean operator such as "AND placed <0"

If so any help would be greatly appreciated.

Once again.

Many Thanks

 

[tipster69]

Sorted it with
points: Sum(IIf([Infosheet 001].Placed>0,11-[Infosheet 001].Placed,0))

VERY MANY THANKS PHV. I would have been here till next Xmas.