Access Form - Column showing previous value

[Joe1991]

Hi,

I'm after a form which looks like this:

Balance Previous Balance
0 0
1 0
2 1
3 2
4 3


I need a column which returns the previous value from the previous record.

I have tried using Dlookup but it fails.

Is there anyway I can do this?

Many thanks in advance!

 

[jdraw]

What exactly is the context?
If you have some sort of quantity on Hand Amount, and a series of transaction that either add yo or delete from that on Hand amount, and the transaction and on Hand amount have an associated Date/Timestamp, then you should be able to get the values (untested).

I'd like to know maore about what you want to happen and Why. Perhaps there are other options.

 

[Joe1991]

Thanks for the prompt reply..

I am trying to create a Access Form which looks like this:

Date --- Description --- Debit --- Credit --- Balance --- Net
12/03/2011 --- Cash --- -£10 --- £0 --- -£10 --- -£10
13/03/2011 --- Deposit --- £0 --- £20 --- £10 --- £20
14/03/2011 --- Deposit --- £0 --- £30 --- £40 --- £40

The real problem which is creating a cumulative balance column.

One way I thought of getting around this was having a hidden column which displayed the previous balance.

So

"Balance" Column = ("Previous Balance") - Debit + Credit

where "Previous Balance" is the obviously the last balance entered..

so

Balance --- Previous Balance
0 --- 0
1 --- 0
2 --- 1
3 --- 2
4 --- 3

Thanks!

 

[jdraw]

I see you have asked this question before. Did you have any success?
You said it worked with another software - can you replicate it?

Have you tried working through say 5 or 6 records and seeing exactly how your intended process should work?
If you have a Form and a balance, I would think you'd get a balance after every record-- I saw in your other post that this wasn't what was wanted. What should happen in your view?

 

[stopher]

You can use a sub-query like this:

SELECT TrDate, Desc, Debit, Credit, 
              (SELECT Sum([Credit]+[Debit]) from tblTransactions as t where t.TrDate<=tblTransactions.TrDate) AS Balance
FROM tblTransactions

Example attached.

Chris