Cash Book balance calculation help needed

[atisz]

Hi guys!

I have a cash book application developed in MS Access 2000 which works great the way it was designed, with stored previous balance and current balance, but now for the simplicity of data input and correcting mistakes, my client would like the application to calculate balance on the fly. I tried to figure out how to do this but unfortunately I couldn't get any results on displaying the correct previous and current balance for a certain date or for a longer time (for 1 week, 2 months, etc.) on a day by day basis. My cash book table contains more than 1 record/date, this is what make it impossible for me to calculate the balance. I need to display balance only on reports.


This is my table:

tblCashBook
---------------
Id
Date
Document_nr
Explanations
Receipts
Payments

To understand more clearly what I mean, I attach a DB containing the above table.
Using this table I have to group stored information by date and do the necessary calculations in order to obtain the balance. But how?

Any help appreciated.

Attila

[alansidman]

Crosspost at

http://www.access-programmers.co.uk/...d.php?t=210823

[jgelpi16]

Not to pick apart your design, but why do you have " lei" after your receipts and payments values? I would split that value out into a separate column maybe? Here is a formula that I use a lot when messing with dates. At it's current configuration it returns anything for last week. This may be a start to help you tell it which dates to use.

Format((Date()-7)-Weekday(Date()-7)+7,"mm/dd/yyyy")

[evander]

Hm... accounting stuff. Maybe I can help a little bit. I included few queries, a form, and a report in the database you uploaded.

The solution is to create two separate queries: one for creating a single amount of beginning balance, and another to display transactions starting from the date of the beginning balance.

Hope this helps, and good luck from here.

[evander]

Oh, and I forgot. In the report, there's a textbox that displays the difference of Receipts and Payments. Set the Running Sum property of this textbox to Over All.

[atisz]

Hm... accounting stuff. Maybe I can help a little bit. I included few queries, a form, and a report in the database you uploaded.

The solution is to create two separate queries: one for creating a single amount of beginning balance, and another to display transactions starting from the date of the beginning balance.

Hope this helps, and good luck from here.

Great job Evander! Thank you so much for helping me out on this. However, there was a small problem which led to a huge error in final balance. If I had set SartDate to 1st may 2011 (which is the date since I keep the track of cash book with this application) and EndDate to any later date that exist in tblCashBook, the resulting balance was correct. But if I had set StartDate to any other later date different from 1st may 2011, the balance wasn't correct anymore, because Beginning Balance took the value of StartDay final balance, and this produced some smaller or bigger differences on each day, and of course, EndDay balance.
So I started to write this post wishing to ask your help to fix the problem, but suddenly I realized fixing was very simple: I just head to change <=[Forms]![frmMain]![txtStartDate] to <[Forms]![frmMain]![txtStartDate] in qryBeginning, and voila! It's working great!
Thanks again for your help Evander, I have to put everything into the right place to get the desired design and I'm going to post here the result, maybe it will be useful for others too.

Attila

[evander]

You're right. The startday criteria should be "less than." You're welcome Attila. Glad I can help.


Jan

[alansidman]

Evander;
Thanks also. I have amended my check book to reflect your changes. I noted however that there was no sorting in either the report or the queries. Did I miss something? I added a sort on transaction date to the report.

Alan

[evander]

To Alan Sidman:

No, you didn't miss anything. Access can get a little tricky sometimes.

Although you can sort your table records by creating a query, I observed that rows are properly sorted in reports by using the Group and Sort command. In Access 2003 or earlier, the dialog box for Group and Sort shows up by pressing Alt+VS.

For the sample database in this thread, you can sort by either ID or Date. Sorting by ID puts the beginning balance (with a false ID of 0) at the very top of the table rows.

[alansidman]

Evander;
Clearly understand all that. The OP was satisfied with the results yet had not made any comment regarding the sort presentation so I thought maybe I had missed something. Thanks again. I really liked the idea of the beginning balance query and follow up with the Union. I had done similar approaches on other db, but it just never occurred to me to do it in this one.
Alan

[evander]

I like using UNION queries myself. As an accountant, I find it quite useful in creating ledgers with beginning balances, and also in creating automated journal entries. It's great to know you liked the idea too.

Can I add you in my friends list?
Thanks a lot, Alan.

Jan

[alansidman]

Jan
Send the invite. My background is also Finance and Accounting but mostly retired now.
Alan

[evander]

Oh, you're into finance. What a coincidence. I sent the invite a few minutes ago. Kindly confirm it. Thanks.