How do I get yesterdays balance to be reflected in today`s balance. 3

[R00K]

Here is my issue:

Date B1 B2 B3 What I Want
1/1/02 100 5 105 105
1/2/02 100 3 103 108
1/3/02 100 0 100 108
1/4/02 250 -10 240 348


B1 is a running total of A1-A2

B2 is another number

B3 should be the previous day's balance plus today`s changes or -What I Want-.

I would post the code, but it is a mess and would be a distraction.

Thank you.

David

 

[kphu]

Post the code.

I'm assuming this is in excel?

 

[kphu]

Sorry it didn't occur to me that I was in the Access forum. That's what happens when you jump around.

What you can do is create an array to save the data in the record set and then use that data to update the value in Colum B3.


Sub updateB3()
dim cdb as database
dim rec as recordset
set cdb = currentdb()
set rec = cdb.opentable("Name of your table&quot
dim intA as integer
dim intI as integer
rec.movelast 'if your interested why ask me about this
intA = rec.recordcount
rec.movefirst


redim B1(1 to intA) as string
redim B2(1 to intA) as string
redim B3(1 to intA) as string

For intI = 1 to intA
B1(intI) = rec(B1).value
B2(intI) = rec(B2).value
B3(intI) = rec(B3).value
if intI = 1 then
rec.edit
rec(B3) = B1(intI) + B2(intI)
rec.update
rec.movenext
else
rec.edit
rec(B3) = B1(intI - 1) + B2(intI - 1)
rec.update
rec.movenext
end if

next intI

end sub

The code basically saves the data and updates it at the same time. I saved the data all as strings and may not work if your field was setup as something else like integer.

Let me know if you have any questions.

Ken

 

[kphu]

oops made an error on one of the lines.

where it says rec(B3) = B1(intI-1) + B2(intI-1)

replace with:

rec(B3) = B1(intI) + B2(intI) + B3(intI-1)

 

[R00K]

KPHU

Sorry for taking so long.

Yes. It is Access. That is a VB script? I'll give it a try when I am back at the office. Do I use the script in a form or something? I have only a little VB experience relating to .NET development - none in ACCESS 2003. Most of the data is presented in Data Access Pages.

Thank you.

David

 

[kphu]

Hi David,

Yes that's vb script. You'll probably want to add this code within a button. I normally put these types of code within a close button of the form that adds a new record to the table.

Let me know if that doesn't make sense.

ken

 

[R00K]

Hi Ken,

Thank you for your time on this.

If I understand correctly, the sub updates my existing records and future records manually (ie. Button click). Is there a way I can build in the process like in a query?

Thanks again.

David

Incidentally, I am converting all of my spreadsheets to this database and find some of the formulas in Excel are hard to transpose.

 

[R00K]

In Excel I used this formula in cell B3,2:

Sum(B3,1+B2,2) and drag it down the page, thus creating a cumulative total(running balance). Access is missing this feature.

 

[kphu]

Hi David,

It may be possible to do this within a query with sql as the mathemtical logic should work the same. The issue is that I'm not fimilar with SQL, perhaps someone out there might know.

My assumption was that with the table that is holding this information you would probably have a form that allows you to add new records. if so, then you can add this formula in on the close button of this form, that way the table will be updated each time a new record is added.

 

[R00K]

Good morning Ken,

I am using Data Access Pages for input and reporting. I understand the logic in the VB but translating it into SQL is killing me.

Thank you all the same.

 

[PaulBricker]

ROOK, your example confuses me a little.

[red]Date B1 B2 B3 What I Want
1/1/02 100 5 105 105
1/2/02 100 3 103 108
1/3/02 100 0 100 108
1/4/02 250 -10 240 348[/red]

You say B1 is the result of a running total of A1 - A2. What do you mean by running total. Are you just subtracting A1 - A2 and getting B1? Also, you add B2 to B1 to get B3, and then you add B2 to B3 AGAIN? for the running balance. But the last value 348 is derived by add B3 to B3. There in lies my confusion. Can you restate your needs a little and we'll look for an SQL solution.

Paul

 

[R00K]

Hi Paul,

Here is another way yo put it.

B1 is like a balance in a bank account. It goes up or down with deposits and withdrawals, respectively. If there are no changes, there is still a total recorded for each day (A1-A2). We'll say, for the sake of the analogy, that B2 is interest. The interested is added to the total which should be "What I want", but ends up like B3.

So, yesterday's balance (B1+B2=B3) should rollover to today where any changes to B1 or B2 are added, resulting in a cumulative total.

If I gave you $100 dollars yesterday and someone else gave you $5, you had $105. If today I give you another 50, you should have $155, not $150, as B# shows in the example.

This is very confusing to me, also.

 

[kphu]

Rook,

I see how Paul is confused and after i looked at it I'm a little confused too, I'm questioning the code I written earlier.

You currently have this.

Date B1 B2 B3 What I Want
1/1/02 100 5 105 105
1/2/02 100 3 103 108
1/3/02 100 0 100 108
1/4/02 250 -10 240 348

Shouldn't it be more like this?

Date B1 B2 B3 What I Want
1/1/02 100 5 105 105
1/2/02 105 3 108 108
1/3/02 108 0 108 108
1/4/02 108250?? -10 240 348

I think Paul and I are confused because if B1 is suppose to be the remaining balance then how did it jump to $250 on the last line?

 

[R00K]

LOL. I was just explaining this to my boss. He's taking an early lunch as a result.

B! records a specific type of change only. So on 1/4/02 there was a "deposit" of 150. So in B1, you are seeing 2 transactions. B2 is the interest added each day, and B3 is the result that should carry over to the next day.

 

[kphu]

Not to be a pain or not sure if this is an option but perhaps you should add in another field to state additional deposit from the remaining balance.

Example

Date B1 Deps B2 B3 What I Want
1/1/02 100 0 5 105 105
1/2/02 105 0 3 108 108
1/3/02 108 0 0 108 108
1/4/02 108 250 -10 248 348

 

[R00K]

I have a Deposit field, Withdrawal Field and a DepWith(Deposits - Withdrawals) field which is "B1" for this example, so a second Deposit field wouldn't work.

B2 is from a second table which contains several fields that are summed into "NChange" which is B2. B3 is the balance. From that balance, I need to subract (.2/365) and the final result, not sahown in the example is the starting point for the next day. However, B! must not change except for new deposits or withdrawals.

This was done on 15 sheets in a spreadsheet that was 30+ columns. It becames so cumbersome and rigid that we are making the move to Access. I understand that is is hard to wrap your head around.

 

[R00K]

Thank you both for you time on this. I am heading out for my Christmas holiday and will be back at it on the 29th. I will check back then. Maybe I will have an epiphany in the mean time.

Merry Christmas to you and yours.

 

[kphu]

ok, how about adding in a yesterday's balance field then?

example

Date B1 B2 ybal B3 What I Want
1/1/02 100 5 0 105 105
1/2/02 100 3 105 108 108
1/3/02 100 0 108 108 108
1/4/02 250 -10 108 348 348

Basically, to me it seems like a lack of fields to store the data you have, that will make it an easy formula to create.

 

[PaulBricker]

OK, here is what I have. The problem is the beginning balance. QryMinDate returns the first date and B3 balance

SELECT TOP 1 [TblName].[Date], B1 + B2 AS Expr1
FROM TblName
ORDER BY [TblName].[Date];

This should return
1/1/02 105

The next query is a union query. QryStartingBalance

SELECT TblName.Date, B2 AS Expr1
FROM TblName
Where TblName.Date <>#1/1/02#
UNION Select QryMinDate.Date,QryMinDate.Expr1
From QryMinDate;

This should return records like

Date Expr1
1/1/02 105
1/2/02 3
1/3/02 0
1/4/02 -10

The third query, QryRunningBalance, should bring it all together


SELECT qryStartingBalance.Date, Sum(qryStartingBalance_1.Expr1) AS SumOfExpr1
FROM qryStartingBalance, qryStartingBalance AS qryStartingBalance_1
WHERE (((qryStartingBalance_1.Date)<[qryStartingBalance].[Date]))
GROUP BY qryStartingBalance.Date;

And the result set should look like

Date Expr1
1/2/02 108
1/3/02 108
1/4/02 98

Try it out and post back with issues.

Paul

 

[R00K]

Paul,

I hope you had a good Christmas. This looks great. First 2 queries went off without a hitch. 3rd query prompts an error message: &quot;Query too complex&quot;. Is there a work around? I am using ACCESS 2003. I'll be working on an answer and will post if I beat you to it.

Thanks a lot.

David