New to Access, should be a simple question to answer

[jyantiss]

Hello,

This is my first Access project, I'm trying build a Budget and I'm working on an account database that has your basic fields... Date, Check Number, Deposit Amount, Payment Amount, To, For, etc...

My problem is that I need a field, I would assume in a query, that would allow me to take the Deposit Amounts - the Payment Amounts to give me an Account Balance. I can do this by summing the two categories in one query and then subtracting them in another, but what I really want is for the balance to change for each line of data.

If it was in excel, I would just write =A2-B2+C1 (in C2) and copy it down where column A = Deposits and B = Payments and C = Account Balance.

Is there a way to do this in Access?

 

[Norie]

What you should probably do is combine the two fields Deposit Amount and Payment Amount into the same field with +/- indicating whether it is a Deposit/Payment.

Then you could use a query to sum the new 'Payment' Field to give you the Account Balance.

That should work if you only want the final Account Balance but would not give you a Running Balance.

 

[SydneyGeek]

Norie's approach will work, but if you want to keep Credits and Debits separate, do this:
In a query, go to the first blank field and enter this expression --

ClosingBalance:[OpeningBalance]+[Deposits]-[Withdrawals]
This creates a calculated field with the name ClosingBalance
Adjust to suit your field names.
Denis