Bank Account Database Design

[Joe Williams]

I am designing a simple bank account and transaction register for a senior
living community that tracks the resident's personal spending accounts. I am
building this off of an existing access application.

I simply added a field called "AccountBalance" to thier existing resident
record and then created a transaction table that stores transaction date,
residentID, transactionType (Withdrawal, Deposit), TransactionAmount, and
TransactionBalance (Balance after transaction).

I then created a form to enter the transaction. The form's data source is a
query that joins the transaction table with the resident table. This way
when they enter a transaction I can have access to both the transaction
fields as well as the account balance field in the main resident table.

So, when the user enters the transaction information into the form and
clicks the SAVE button, the form saves the transaction record and also
updates the account balance field from the main table.

My questions are as follows:

1) From a database design perspective, am I missing anything in regards to
the transaction table and also storing the account balance on the resident
record?

2) Is it technically ok to have the transaction form's datasource be a join
with the resident record so I can update the account balance or should I be
doing some other method (updating balance via VBA or other type of query?)

3) When entering a transacation, if the user bails out of the form by
clicking the X in the upper right hand corner, it still saves the record.
How can I make it so that the record is only saved if they click the SAVE
button I have placed on the form?

Thank you in advance for your help

joe

 

[Bob Quintal]

I am designing a simple bank account and transaction register for
a senior living community that tracks the resident's personal
spending accounts. I am building this off of an existing access
application.

I simply added a field called "AccountBalance" to thier existing
resident record and then created a transaction table that stores
transaction date, residentID, transactionType (Withdrawal,
Deposit), TransactionAmount, and TransactionBalance (Balance after
transaction).

I then created a form to enter the transaction. The form's data
source is a query that joins the transaction table with the
resident table. This way when they enter a transaction I can have
access to both the transaction fields as well as the account
balance field in the main resident table.

So, when the user enters the transaction information into the form
and clicks the SAVE button, the form saves the transaction record
and also updates the account balance field from the main table.

My questions are as follows:

1) From a database design perspective, am I missing anything in
regards to the transaction table and also storing the account
balance on the resident record?

You are missing the concept that Balance is a calculation, and
therefore should not be a field in any table.

2) Is it technically ok to have the transaction form's datasource
be a join with the resident record so I can update the account
balance or should I be doing some other method (updating balance
via VBA or other type of query?)

Your balance field should be the sum of all deposits * +1 and all
withdrawals * -1. This can be generated using a totals query or the
dSum("field*transactionType","table", "criteria = " & something )
where transactionType is 1 or -1 as defined above.

3) When entering a transacation, if the user bails out of the form
by clicking the X in the upper right hand corner, it still saves
the record. How can I make it so that the record is only saved if
they click the SAVE button I have placed on the form?

The problem does not exist if you calculate the Balance.

 

[Mike Painter]

Joe Williams wrote:

1) From a database design perspective, am I missing anything in
regards to the transaction table and also storing the account balance
on the resident record?

As covered in another post it is far better to have this balance calculated.
If you choose not to then you must take into account that people will often
go back to a transaction and change it. They entered 132.00 and it should
have been 123.00.
You must determine if it is a new record or an existing one.
From an accounting point there should be a record of *all* transactions and
after being saved it should not be changable.
A mistake caught after the record is saved is corrected by another
transaction.
A decision to abandon a transaction has it marked void.

I've stored balances in fields before mainly becasue in the 70's and 80's it
would have taken a long time to generate the totals in real time.
I made the mistake of doing it once in Access.

DON'T DO IT.
It will haunt you for many years.

 

[Joe Williams]

Thanks for the response,

I understand about not saving the balance and that is easy to do, so then I
guess the only question I have is how do I go about getting the datasource
for reports that need point in time balances... For instance, when you run a
report for a particular date range it will have a beginning balance listed
as the first record - how do I go about getting that into the datasource for
the report if I have to go back to the beginning of time for the balance?

Thanks again,

joe

 

[Mike Painter]

With a query on the date range summing the amount.

 

[Benji]

I am looking for tips on building a report in access that displays a bank statement for a customer based on date range entered and their account number...i got the date range covered from an existing template used but how do include entering of an account number?? There is form that feeds a query with the date range and then the form feed a report with the Debits and Credits and balance of that date range including a balance forward form previous date. There is a table with following fields ID, Date Debit and Credit...

Please help.