Running Total with criteria using UDF

[xav78]

Good Evening,

After a couple months of head scratching, I have determined the solution to my problem lies outside of normal SQL queries and I need to create a UDF.

Here is the problem. I need to create a running total based on the sum of two fields [PTS_ISSUED] & [PTS_REDUCED] with one restriction. The total can never be less than 0. If it is less than zero, the totals reset with [PTS_ISSUED] as the new starting point.

I've attached an excel spreadsheet. Column"C" contains the formula logic I am trying to replicate into access.

I will be honest, writing the UDF is beyond my current knowledge but once I see it I am able to understand it and implement it.

I believe the solution needs to store the running total into a variable, make the necessary comparisons and determine if its okay to add the [PTS_ISSUED and [PTS_REDUCED] to the stored total of the previous calculations then advance. I just have no clue in how to go about writing it.

If anyone can please help, I would be very appreciative. This calculation needs to be presented in Access because the results will drive several other functions within the database.

 

[JHB]

Writing the UDF is not the big problem, the problem is to determine whether the transmitted data belonging to the first row or not (ie, whether the query has just began to run or not), because if first row, then it has to reset a "global" variable.
Is there an ID field in the table that can be used to determine this?

 

[xav78]

yes there is a Key field, I've attached a copy of the database I've been working in.

 

[JHB]

Run the query in the attached database.

 

[arnelgp]

run this the report on Print Preview view.
view the code behind onFormat event of the report on Report design view.

 

[xav78]

JHB: Thank you very much for your work but I've tried the query and worked the first time os I then loaded a larger dataset to test and the totals change as I scroll through, any ideas?

 

[xav78]

arnelgp, thanks for the report option it has given me thoughts of a separate issue I'm working on. However I need it in a query, the results are ultimately being displayed in a list box along with other relevant data points associated with the running total. any other thoughts maybe?

 

[arnelgp]

here a make new query, Q1. no function required.
the only catch with this one is that you must have consistency (no break) on you key numbering.

 

[arnelgp]

there was a flaw on my last post. this hopefully is the final, using code from Module2, run query query2.