Question Cash Book balance calculation help needed

A

atisz

Registered User.
Local time
Today, 10:45
Joined
Apr 5, 2005
Messages
96
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.
This is my table:

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

Using this table I have to group stored datas by date and do the necessary calculations in order to obtain the balance. But how?
Any help appreciated.

Attila
 
Isn't just a matter of summing the receipts and payments data up to the date.

You could create functions that use the date on the form and return the result in a text box control on the form.

One function would be Previous Balance and the other Current Balance etc.

Trust I haven't over simplified the issue.
 
Here is a database for a check book that I created a while ago. It has a running sum in the report and it also has a current balance in the form. Look at the underlying query and the report to see the details.

I hope this is helpful.

Alan
 

Attachments

PNGBill said:
Isn't just a matter of summing the receipts and payments data up to the date.

You could create functions that use the date on the form and return the result in a text box control on the form.

One function would be Previous Balance and the other Current Balance etc.

Trust I haven't over simplified the issue.
Click to expand...

Thanks PNGBill for your suggestion.
I know I have to do something like this, and it must be functional on forms and in reports too, the problem is I don't know HOW to create those functions, because I don't have enough skills for that :( .
 
Alansidman said:
Here is a database for a check book that I created a while ago. It has a running sum in the report and it also has a current balance in the form. Look at the underlying query and the report to see the details.

I hope this is helpful.

Alan
Click to expand...

Thanks Alan for the sample database. The solution provided in this database is quite close for what I need. Unfortunately, in my database are more than 1 records / day, so if I use your running sum, which in my case looks like this:

Code: 
SELECT tbl.Id, tbl.Document_nr, tbl.Explanations, tbl.Date, tbl.Receipts, tbl.Payments, (select Nz(Sum(Nz([Receipts])-Nz([Payments])),0) from tblCashBook
WHERE tblCashBook.Date <=tbl.Date) AS Balance
FROM tblCashBook AS tbl
WHERE (((tbl.Date) Between [Begin Date] And [EndDate]))
ORDER BY tbl.Date;

I'm going to get more than 1 record/date = the same Balance for more than once/date, and I can't use it like this.
Can the above code be modified in order to get balance just once/date?

Thank you,
Attila
 
Last edited:
Try instead of this
Code: 
WHERE tblCashBook.Date <=tbl.Date) AS Balance
Try this
Code: 
Where tblCashBook.ID <=tb1.ID) AS Balance
and change this line
Code: 
ORDER BY tbl.Date;
to
Code: 
Order By tb1.ID;
 
Alansidman said:
Try instead of this
Code: 
WHERE tblCashBook.Date <=tbl.Date) AS Balance
Try this
Code: 
Where tblCashBook.ID <=tb1.ID) AS Balance
and change this line
Code: 
ORDER BY tbl.Date;
to
Code: 
Order By tb1.ID;
Click to expand...

Thanks Alan for suggestion, but unfortunately this doesn't solve the problem.
Using Date or Id as criteria do lead to a correct balance and final balance, but the calculated balance is obtained for each record separately, while I need it separately for each date. This was half of my problem from the start.
To understand more clearly what I mean, I attach a DB containing the necessary table and the qry based on your solution.

Attila
 

Attachments

If you want to see a form on your pc screen that shows data for a date or range of dates but only a total and not a running total then try Form and Sub Form.

For a report you can use grouping and just show the period result but on a form this won't work the same.

Have a subform that shows the period result. The subform will have it's own query to get a result but have a relationship to the main form which would be the date or date range.

This will appear as one form but of course it is two or more.
 
PNGBill said:
If you want to see a form on your pc screen that shows data for a date or range of dates but only a total and not a running total then try Form and Sub Form.

For a report you can use grouping and just show the period result but on a form this won't work the same.

Have a subform that shows the period result. The subform will have it's own query to get a result but have a relationship to the main form which would be the date or date range.

This will appear as one form but of course it is two or more.
Click to expand...

I need to show the balance only on the report. But grouping it's not working. If I try to group info on report by date, I also need group header and group footer for designing purpose. Adding group header and footer gives me the following error message: "Multi-level GROUP BY clause is not allowed in a subquery".

Attila
 
Why are you using a sub query ?

try running a new query with all the data and see if grouping will work.
 
PNGBill said:
Why are you using a sub query ?

try running a new query with all the data and see if grouping will work.
Click to expand...

I'm not using any subquery, I'm using qryCashBook. If I don't use any group header or footer things are ok, but when I add group header or footer or both of them, it gave me that error. Anyway, I feel like this is something unusable in my situation, because I don't see how could I display the daily balance.
The deadline for founding a solution for this is the end of this week, and as I see it's a hopeless situation.
Does anyone have an idea or a working solution for calculating the balance?

Thanks,
Attila
 
You must log in or register to reply here.

Similar threads

S
Balance brought Forward
Replies
4
Views
713
Solo712
Solo712
T
Creating stock balance by month
Replies
5
Views
432
mike60smart
mike60smart
P
LEAVE MANAGEMENT
Replies
13
Views
857
Solo712
Solo712
G
Solved Data from form not showing on Statement of Account report
2
Replies
21
Views
1,379
GaP42
G
V
Solved Function to calculate your start and end dates for the current day
2
Replies
20
Views
983
victorlindh
V

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom