I am trying to get a running sum going on a quantity field. But When it reaches the Sum of limit of 500 I need it to start over again. How would I go about this. Example of data below.

LID PID GID Qty RunSum
2547 0769 AI 50 50
2547 1132 AI 10 60
2547 1273 AI 10 70
2547 1274 AI 25 95
2547 0769 DDS 25 120
2547 1132 DDS 25 145
2547 1273 DDS 25 170
2547 1274 DDS 25 195
2547 0769 DEM 300 495
2547 1132 DEM 10 10
2547 1273 DEM 10 20
2547 1274 DEM 175 195
2547 0769 GRN 15 210
2547 1132 GRN 10 220
2547 1273 GRN 10 230
2547 1274 GRN 10 240
2547 0769 LIB 15 255
2547 1132 LIB 10 265
2547 1273 LIB 10 275
2547 1274 LIB 10 285
2547 0769 NP 175 460
2547 1132 NP 10 470
2547 1273 NP 10 480
2547 1274 NP 75 75
2547 0769 PF 10 85
2547 1132 PF 10 95
2547 1273 PF 10 105
2547 1274 PF 10 115
2547 0769 REP 350 465
2547 1132 REP 10 475
2547 1273 REP 10 485
2547 1274 REP 175 175

How does the RunSum field get populated? It seems to me that whatever logic you apply to adding Qty to RunSum needs only to know that RunSum would be tipped over the limit, and thus set RunSum to zero before adding. I doubt you'd be able to do this without some coding though.

So in your last line: If Qty + RunSum > 500 Then RunSum = Qty Else RunSum = Qty + RunSum

I did the sample in excel. Just adding the QTY together to get the Running Sum. One more thing I just realized. For every new LID i need the running sum to start over again as well.

So, you're saying that you want a RunningSum for each unique LID, but only until the RunningSum (for a specific LID) reaches 500, then (that LID's RunningSum) resets to 0?

What happens if the amount to be added would cause the RunningSum to exceed 500? Do you NOT add it and then reset?

What happens if the amount ... exactly 500? Reset or wait for the next one?

To do a RunningSum, you need to have the records in some order. What are you using to order the records?

More info, please...

Yes I need a running sum for each Unique LID until it reaches 500 then needs to reset and continue on that unique LID until it changes to another LID.
If the next amount causes it to go over 500 then reset and continue on that LID. If it reaches 500 evenly then reset and continue.
The sort is on the LID ascending, PID ascending and GID ascending.