variation of Running Sum in query

[lizhaskin]

Hi
I am trying to create a function to use in a query similar to a running sum. The difference being, instead of adding the field from the record before, I would like access to see whether there is a value in the current record and if there is use that value - if there isn't to then use the value from the previous record. Something like

ID Value result needed
1 100 100
2 0 100
3 150 150
4 0 150


... and so forth

I have tried adapting the if ...then ... else function for fncRunSum but have obviously done something wrong. My adaption is:

Option Compare Database

Option Explicit

Function fncCarryFwd(lngCatID As Long, lngUnits As Long) As Long
'Variables that retain their values

Static lngID As Long
Static lngAmt As Long

If lngID <> lngCatID Then

'If the current ID does not match the last ID, then (re)initialize

lngID = lngCatID
lngAmt = lngUnits

Else

If lngUnits = 0 Then

lngAmt = lngAmt

Else

'If the current ID matches the last, keep a running sum for the ID
lngAmt = lngUnits

End If

'Pass the running sum back to the query

fncCarryFwd = lngAmt

End Function

======

Please can one of you experts show me where I am going wrong
thanks
liz

 

[dcx693]

Would you only copy the value from the previous record? For example, could this happen?

ID Value result needed
1 100
2 0
3 150
4 0
5 0
6 175

 

[lizhaskin]

Hi
If there is not a value in the current record it must bring forward the value from the previous record.
thanks
liz

 

[dcx693]

If there is no value in the current record, will there really be a zero in the value field (as you indicated) or will it be blank (meaning Null)?

 

[lizhaskin]

There will be a zero "0"

 

[dcx693]

liz, I honestly did not take the time to look through the code, but thought that the problem could be more easily solved with a query. As it turns out, it's quite easy to solve with two queries.

I've attached a sample database with the solution. The table "tblValues" simply has the data you posted, along with more that I made up. In the query qry1, I added the tblValues table twice to the query, and then I matched up each record in tblValues with the "next" record in the copy of the table by referencing the ID field. Here's a table showing which records got matched up:

tblValues tblValuesCopy
   2           1
   3           2
   4           3
etc...

That way, a comparison between adjacent records was easy. I created a "Result" field that took the greater of the value in the original tblValues table or the value in the copy of the table. Finally in qry2, I needed to pull in the value from the first record in tblValues, so I did that by joining qry1 to the original tblValues table.

Oh well, I don't know if you can use this result, but I thought it an interesting solution. In general, using saved queries like this in Access is faster than using custom functions.

 

[lizhaskin]

Thank, I'll work through it and let you know how it goes.
Liz