How to use value from previous record into current record for calculation

[NoEtymology]

Dear All,

I want to maintain a simple table with fields such as Transaction Date, Particulars, Debit, Credit, Balance. Following is an example of the table. I want the Balance field to be auto-calculated. I would want to input the initial balance from previous month (example 1000) and continue adding the expenses or new income under Debit and Credit fields respectively. If Credit is null, I want new Balance value to be old Balance value-Debit. If Credit is not null, then new Balance should be old Balance+Credit.

Transaction Date	Topic	Debit	Credit	Balance
				1000.00
1-Nov-18	Dinner	100.00		900.00
2-Nov-18	Salary		5000.00	5900.00
3-Nov-18	Shopping	250.00		5650.00

Please let me know how this can be achieved in a Query or Form. Thanks very much in advance.



Regards.

[ranman256]

access records cannot not see each other. (not like excel)
you can create some queries:
1 to sum curr. month,
1 to sum prev month.
then in Q3, see the difference.

I use a form, it has 4 text boxes
txtStartDate, txtEndDate, txtPrevStart, txtPrevEnd

user enters the start and end: txtStartDate, txtEndDate

the other 2 are calculate from them:
txtPrevStart = DateAdd("m",-1,txtStartDate)
txtPrevEnd = DateAdd("d", -1, txtStartDate)

the queries use this to pull the data.

I made some controls to fill in the dates with just a click:

[NoEtymology]

Dear @Ranman256,
Thanks for your reply. I need to get the tally for each day's each transaction. If it was on monthly basis it would have been easier.
Thanks again.

[June7]

Textbox on report has RunningSum property. This is the easiest way to calculate a running balance. Calculating running balance in query or on form is difficult. Review https://theaccessbuddy.wordpress.com...m-part-1-of-2/ and https://sfmagazine.com/post-entry/oc...unning-totals/

Pulling the aggregate balance from previous period as a StartBalance is also tricky and is often accomplished with a DSum() or DLookup() domain aggregate expression in textbox. This is a fairly common topic.