Simple Checking Registry

[JamesJoey]

I'd like o create a simple, bare-bones checking registry in access.

I've created the fields for the data.
The table has 5 pertinent fields:

TransactionID (AutoNumber)
Payee (Text)
Payment (Currency)
Deposit (Currency)
Balance (Currency)

I'm not sure how to handle the expression for the Balance. field. I simply want to deduct the amount in Payment and add the amount in Deposit in each record.

Any help will be appreciated,
James

 

[plog]

You don't have a balance field. You shouldn't store calculable fields in a database, instead you calculated them.

Further, you put your credits and debits in the same field. That way, you simply add one field up using a SUM and get your balance.

SELECT SUM([Amount]) AS Balance FROM YourTableNameHere;

 

[JamesJoey]

So, when entering data into the credits/debits field, I should use a minus sign in front of the number when entering a debit (-$2.45)?

The balance field should be an unbound field added to the form with the calculation you gave?

 

[plog]

Yes, use +\- to differentiate. The code I gave for the balance was for a query. For a form you would use a DSum(https://www.techonthenet.com/access/functions/domain/dsum.php)

 

[JamesJoey]

I put this into the Record Source of the form:

SELECT SUM([Amount]) AS Balance FROM tblChecking

I get #Name? on all fields

 

[plog]

The record sourve of the form should be tblCheckng. Then your balance control's source will be a DSum

 

[JamesJoey]

I found I can use 'Total' in a splitform to get my balance.

I believe this what I was looking for.

 

[JamesJoey]

About the balance field.

Should it be an unbound control or a field in the table?

 

[jdraw]

Should it be an unbound control or a field in the table?

I think plog already answered that in his earlier post #2

You don't have a balance field. You shouldn't store calculable fields in a database, instead you calculated them.

 

[JamesJoey]

His reply gives me the impression then that balance should be an unbound control.

Ok.

 

[jdraw]

Right. He was quite emphatic that you should not have a Balance field in the table

 

[JamesJoey]

I put this in the Control Source of the unbound control called Balance:

DSum("[Amount]", "tblChecking")

I get #Name? on the Balance unbound control.

 

[jdraw]

It seems that you don't have a field called "Amount" in your table???

 

[JamesJoey]

Yes, there is a field called Amount.

I needed to put an equal sign at the beginning of the expression.

Anyway, it doesn't give me a running balance like expected.
I'm accomplishing the same thing by using Total Sum on the amount field in the split form.