Cumulative qry skips

[TrollBro]

This is driving me nuts. I have a qry that returns a running sum, but the numbers sometimes skip a value, and it's not always the same value that it skips:

SELECT x.Points, x.[PlayerID], x.Eventdt, (SELECT Count(*) FROM tbl_Data WHERE Points < X.Points OR (Points = X.Points AND [PlayerID]<= X.[PlayerID])) AS c_CumPointsCount FROM tbl_Data AS x
ORDER BY 1, 2;

I see the problem when scrolling the qry results - the file is about 20k records - I think I may have read about this a while back somewhere, but my mind is drawing a blank.

Anybody have any suggestions?

Thanks

 

[lameid]

I'm not sure how you get a running sum out of this. Maybe you mean sum(points) instead of count(*). Otherwise, I'm guessing the where clause of your subquery (below) may have incorrect parenthesis...

WHERE Points < X.Points OR (Points = X.Points AND [PlayerID]<= X.[PlayerID])


That will give you all records where Points are less than x.points. It will also so cases where points = x.points and the PlaerID is less than or equal to x.plaerID.


Perhaps you mean...

WHERE Points <= X.Points AND [PlayerID]<= X.[PlayerID]

I don't know for sure as I am not following what you are trying to do.

 

[TrollBro]

Thanks lameid

I wasn't as clear as I should have been - I said running sum but more accurately should have said running count or cumulative count (I just want the records numbered in sequence 1 to n based on the sort criteria. I found that the code generates an accurate cumulative count if I actually set it up as a make table query (just takes forever on a 10k+ record count), but just doesn't work reliably in a select query if you start scrolling around to look at the results. I did try the

WHERE Points <= X.Points AND [PlayerID]<= X.[PlayerID]

suggestion, but it doesn't always work since there can be cases where not all x.points have a playerID <= the x.playerid, thus the need for the x.points OR clause.

Guess for now I'll live with the make table approach.

Thanks for taking a look - much appreciated.

 

[lameid]

I get it now... you want each record to increment by 1.

If you added a field with a literal value of 1 to your query and remove the subquery, you could do a running sum in a Report... Just set the running sum property of the control pointing to the field you added for 1.

This is not a query but gives you the right results.
I hope it will work for you because it is a much more efficient solution.