How to make amounts subtract

[simmiepam]

I am trying to create a financial statement for my church.



I have created a report with a balance before expenditures

I want to have a running subtraction column that takes the balance before expenditures...

And subtracts each payment made during the month to show a running negative balance.

I cannot figure out how to make the first check for the month subtract from the balance before expenditures and THEN ALSO have the balance for each row a running subtracted amount.

See attached for explanation:

Is this possible?

Pam

[Micron]

I wouldn't want to try this in Access. The main problem as I see it is that control A1 holds the preliminary amount (34k) and the first balance (C1) needs to subtract the first payment (B1). After that, the relationships change as A1 is no longer needed, and the controls involved shift to
C1 - B2
C2 - B3
C3 - B4 and so on.
At best, I think it would involve a subquery setup, which is something I try to avoid.
If it were me, I'd try involving Excel somehow - either produce the report as a spreadsheet, or link the spreadsheet to the db and the report query should be able to produce an acceptable result. That's all in accordance with me not having done this sort of report in Access, but plenty of times in Excel, so I might be biased.

[simmiepam]

I wouldn't want to try this in Access. The main problem as I see it is that control A1 holds the preliminary amount (34k) and the first balance (C1) needs to subtract the first payment (B1). After that, the relationships change as A1 is no longer needed, and the controls involved shift to
C1 - B2
C2 - B3
C3 - B4 and so on.
At best, I think it would involve a subquery setup, which is something I try to avoid.
If it were me, I'd try involving Excel somehow - either produce the report as a spreadsheet, or link the spreadsheet to the db and the report query should be able to produce an acceptable result. That's all in accordance with me not having done this sort of report in Access, but plenty of times in Excel, so I might be biased.

I appreciate your taking the time to explain it to me.
I can't involve Excel because I'm making this for the lady at church (who does our financial statement) she's not very computer savvy...and I wanted to make it so all she had to do is click a button, type in the checks the church wrote...click another button and print the report.

It would kill her if she had to go to Excel to do something! lol

Thanks anyway.
Pam

[Micron]

If you want it to be so simple, then why worry about a running balance? Just show the balance in the form footer and update it every time a new payment is saved? In fact, the calculated control should update each time the payment record is updated. Here's one example that uses a continuous form design with header and footer. It's purpose is to show an order's details along with any existing change orders at the top and what the new amounts will be when any CO category amounts are created. The Add Row to List button inserts a new detail record, whereupon the CO type is chosen from the combo. The projected order amounts are shown at the bottom (where your balance would go). The reason you don't see any calculations here is because the db is not connected to the back end tables on my laptop, thus the form is basically "disconnected". Using such a design, you could show the current balance and take into account any payments AND receipts and get the bank book balance.

[simmiepam]

It would make sense to me not to show the running balance, but the church's old statement did and so I'm trying to reconstruct it as best I can. She typed it all on a typewriter! Yikes.

[simmiepam]

Would it be possible to create some sort of hidden field on the report that does the calculation and then pass that on to the balance column?

[Micron]

EDIT.
this may help you -
https://support.office.com/en-us/art...4#bmrunningsum

[simmiepam]

Thank you very much

[simmiepam]

Yes I had found that...it adds the numbers together...doesn't subtract them. But I'm not discouraged...I'm sure I'll find an answer! Thanks

[Micron]

Make the numbers negative?

[simmiepam]

Yes. The beginning balance of the church's account at the bank.... $10,000
Then on the 2nd, they write a check for $200. I want a column that says $200 and then next to it a column with $9,800
On the 5th, they write a check for $1,000. Column for $1,000 and the next column with $8,800.

[Micron]

I realize that. It was really a suggestion, not a question.
Won't the running sum work if the amount is -200 instead of 200? If that would really mess up their minds (in which case I'd begin to wonder) you could probably have the running sum be based on the invisible negative control while they see only the positive numbers.

[simmiepam]

Ha! Well I would if I could get that very first BALANCE column to be the result of $10,000 - $200. And then once that balance column is $800...then the next amount subtract from that amount and so on.
It wouldn't matter if it has a minus sign in front...I just don't know how to get that very first balance to display.

[Micron]

If you want to provide a db with some data that includes the payment/income figures, I'll see what I can come up with.

[simmiepam]

Could I just give you a copy of the dababase I'm already using?
If so...how do I go about doing that?