Running Balance Problem - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Running Balance Problem

William Kennedy

4 years ago

I have a working checking account database that develops a running balance report with a query using the Autoincrement ID (IDCCNZ1). A problem has been found when a mistake record is entered in the database and a new record needs to be added that was missing. It is out of sequence then. The balance no longer works correctly. These are the three fields I use.

DebitRunningSum: DSum("[CCNZ1Debit]","tblCCNZ1","IDCCNZ1<=" & [IDCCNZ1])

CreditRunningSum: DSum("[CCNZ1Credit]","tblCCNZ1","IDCCNZ1<=" & [IDCCNZ1])

Balance: [CreditRunningSum]-[DebitRunningSum]

I would like to use the (CCNZ1Date) date field in the table. That way the user can add a record with a correct date and have query sort on date when the report is generated. Ive tried to make this work but cant make it work. I know I must be doing something wrong. Any Help would be appreciated.

	Juan C Rivera @Reply 4 years ago
I am thinking the sorting may be a thing to look at the math is done in the order presented. Sort your order in the query by the date and take a look. Let us know what you find. another way to work it is to put a checkbox on the table to add only the checked items. this way the query will not add up the ubchecked... trial and error on this one.

	William Kennedy OP @Reply 4 years ago
Tried sorting in Query. Still has problem as the running balance gets corrupted. Has anyone used Date Time field in a DSUM to Replace the AutoNumber ID? Maybe Concatenate String of Date Field with Unigue Autonumber. to give ie., 20220908+ID. That Might work. I'll try that.

Juan C Rivera @Reply

4 years ago

I never use the ID other than to set up a relationship. I like the check box yes no and in the query, I include only checks that have cleared. this way the running sum only adds the cleared checks. On the reverse side, you can get a running sum of what has not clear. Richard does have a checkbook Seminar that may be of interest. I think the best bet is not to use the ID. How hard will it be to redo the database?

	William Kennedy OP @Reply 4 years ago
thanks

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View