Running Sums with Dsum! (Oh the Joy!)

[jrub]

I'm trying to get a running sum working with two calculated fields.

The Dsum is nested within an IIF statement, to detect if it's the first starting year and provide the initial balance value.

In subsequent years, I would like to have a sum of the previous Interest and previous Contribution calculated fields added RunningBalance (taking into account the base year IIF statement).

My RunningBalance code is the following:

RunningBalance: IIf([StudyYears]=[ReserveParameters]![StartingBalanceYear],[ReserveParameters]![StartingBalance],Val(Nz((DSum("[Interest]+[Contribution]","Query1","StudyYears <=" & [StudyYears])),0)))

My results is the following:

Can anyone point out what Im doing wrong with my dsum statement?
It should read 404585,488098,519307,405530

What I am getting at, is there a way to define the base value of a dsum function?


When I try the following, to make the Interest and Contribution field be zero if there is no previous records, I get the #error value.

RunningBalance: Val((DSum("Nz([Interest],0)+Nz([Contribution],0)+[StartingBalance]","Query1","StudyYears <=" & [StudyYears])))

 

[spikepl]

It's doing exactly what you asked of it - showing the sum of Contribution and Interest in 2014 and onwards. You probably wanted something else.

 

[jrub]

Thanks for the reply.

I am trying to have the first year (2013) Balance be defined elsewhere and then have the running sum incorporate the "defined elsewhere" as the first value of the running sum.

I tried using a modified dsum statement with Nz around the Interest and Contribution fields to incorporate it, but I get #error.

 

[jrub]

Can anyone out there help ?

I mangled the following code together:

RunningBalance: IIf([StudyYears]=[ReserveParameters]![StartingBalanceYear],[ReserveParameters]![StartingBalance],Val(Nz((DSum("[Interest]+[Contribution]+[Inflation_Adjusted_Expenditures]","Query1","StudyYears <=" & [StudyYears])),0)))

And it places the correct first value into the running balance, but then gives #Error for the remainder of the values.

Below is a sample db of the current progress of efforts, can anyone help get it going?

 

[Solo712]

Can anyone out there help ?

I mangled the following code together:

RunningBalance: IIf([StudyYears]=[ReserveParameters]![StartingBalanceYear],[ReserveParameters]![StartingBalance],Val(Nz((DSum("[Interest]+[Contribution]+[Inflation_Adjusted_Expenditures]","Query1","StudyYears <=" & [StudyYears])),0)))

And it places the correct first value into the running balance, but then gives #Error for the remainder of the values.

Below is a sample db of the current progress of efforts, can anyone help get it going?

Just at the first passing glance: you have a circular reference between Interest and RunningBalance. You need to fix that first.

Best,
Jiri

 

[jrub]

Thats kind of my intention
.
The running balance calculates from the previous record. The interest is calculated from the result of the running balance for the current record.

The very first record's running balance and interest record's are seeded with a value set by the user, where the following records are a result of calculations.

Is there super crazy to perform?

 

[gemma-the-husky]

I think many of us are not used to the idea of performing calcluations based on "other rows". Databases do not work like that, and attempts to "compel" them to do that are

I think if you have a steady regular stream of cash flows, then there ought to be a standard formula to compute the NPV or equivalent of each of the rows, without needing the other rows. That is what I would be looking for.

 

[jrub]

I see your point. The interest is calculated using a mid-year interest formula, which I don't think access has. But Im looking into other ways to compute it so it doesn't require other "within the same record" values.

 

[jrub]

Although, is there a way to set the first record a specific value, and then have the Dsum perform a running sum from record 2 and onward (taking into account record 1's set value) ?

 

[jrub]

Ok, what if I include an iif statement within the dsum to place the correct value in the first record, and begin the running sum in record number two?

It would go like this I imagine:

RunningBalance: Val(Nz((DSum("[Contribution]+[BaseYear]","Query1","((IIF([StudyYears]=[StartBalanceYear]","[StartingBalance]","[StudyYears]+1)) <=" & [StudyYears]))))

I am getting an error, can anyone fill me in on the proper way to write the iif staement within a dsum?

 

[Solo712]

Ok, what if I include an iif statement within the dsum to place the correct value in the first record, and begin the running sum in record number two?

It would go like this I imagine:

RunningBalance: Val(Nz((DSum("[Contribution]+[BaseYear]","Query1","((IIF([StudyYears]=[StartBalanceYear]","[StartingBalance]","[StudyYears]+1)) <=" & [StudyYears]))))

I am getting an error, can anyone fill me in on the proper way to write the iif staement within a dsum?

Is this what you are looking for ?

Jiri