Checking Account Design

[Guest]

Hi:

I’ve used a program very similar to Access, called Alpha Five, for many
years. I created a checkbook application in A5 that works great and now I
want to convert it over to Access 2000, but I need some serious help. I have
lots of questions, let me start with the basics.

The Parent is tblAccounts, the child is tblTransactions, and there is a
lookup table called tblPayees. The parent and child are linked by the text
ID_No field. In addition to account information, the parent table also has 3
fields, Debits, Credits, and Balance which displays the current checkbook
balance. The total Debits and Credits are obtained from the child table and
the Balance field is simply a calculated field [Credits – Debits].

The child table has a text fields called Trans_No, which is the primary key,
every child record has a unique Trans_No.

The child table has a field called Trans_Type, which is restricted in A5 to
only two choices, “Debit†or “Creditâ€. If the user selects “Debitâ€, the
Credit currency field is disabled. And obviously if the user selects
“Creditâ€, the [Debit] currency field is disabled.

TblTransactions also has a field called [Line_Bal] which is the current
balance for each transaction, just like Quicken.

Here are some of my questions:

[1] How can I disable the child Debit or Credit currency fields based upon
the user’s choice in the Trans_Type field?

[2] Can I create a form-based query that calculates the total Debits and
Credits from the child table and places them in the parent table each time a
record is saved?

[3] How can I sort the child table by Date and then by; Trans_Type? In other
words, for each date, I want the “Credit†transactions displayed before the
“Debit†transactions.

And here’s the big question which I’ll leave for another time. Is there a
way to write code that will update all of the relevant child records if the
user enters a transaction on an earlier date or edits a saved transaction? In
other words, if today’s date is 03/01/2005 and the user enters a new record
that occurred on let’s say 02/25/2005, Access will have to update the
[Line_Bal] field for every record between February 25th and March 1st.

Is there a sample Access checking database that has already solved the above
problems and/or does it more efficiently that can someone can point to? Any
other thoughts?

Thanks for all of your help,
Robert T

 

[Jamie Collins]

The Parent is tblAccounts, the child is tblTransactions, and there is a
lookup table called tblPayees. The parent and child are linked by the text
ID_No field...

FWIW rather than 'parent table' and 'child table', the standard terms
are 'referenced table' and 'referencing table' respectively; this may
help when considering FOREIGN KEYs.

... In addition to account information, the parent table also has 3
fields, Debits, Credits, and Balance which displays the current checkbook
balance. The total Debits and Credits are obtained from the child table and
the Balance field is simply a calculated field [Credits - Debits].

The child table has a text fields called Trans_No, which is the primary key,
every child record has a unique Trans_No.

The child table has a field called Trans_Type, which is restricted in A5 to
only two choices, "Debit" or "Credit". If the user selects "Debit", the
Credit currency field is disabled. And obviously if the user selects
"Credit", the [Debit] currency field is disabled.

TblTransactions also has a field called [Line_Bal] which is the current
balance for each transaction, just like Quicken.

Consider this slightly amended design:

CREATE TABLE Accounts (
ID_No CHAR(9) NOT NULL PRIMARY KEY,
CHECK (LEN(ID_No) = 9)
)
;
CREATE TABLE Transactions (
ID_No CHAR(9) NOT NULL,
transaction_date DATETIME DEFAULT DATE() NOT NULL,
Trans_Type VARCHAR(6) NOT NULL,
CHECK (Trans_Type IN ('Debit', 'Credit')),
Debit CURRENCY, CHECK (Debit > 0),
Credit CURRENCY, CHECK (Credit > 0),
CHECK(IIF(
Trans_Type = 'Debit',
CREDIT IS NULL AND Debit IS NOT NULL,
TRUE)),
CHECK(IIF(
Trans_Type = 'Credit',
Debit IS NULL AND Credit IS NOT NULL,
TRUE)),
FOREIGN KEY (ID_No) REFERENCES Accounts (ID_No)
ON UPDATE CASCADE ON DELETE CASCADE
)
;
CREATE VIEW Balances AS
SELECT ID_No, SUM(Credit) as total_credits,
SUM(Debit) as total_debits,
SUM(Credit) - SUM(Debit) as balance
FROM Transactions
GROUP BY ID_No
;

Jamie.

--

 

[Guest]

Hi Jamie:

Thanks for the great input and redesign suggestion. However, after many
years of using the Parent-Child paradigm, it will probably take several more
years to reprogram my mind to use different terms.

Your suggestion and redesign format were right on time, it looks as if that
was exactly what I need to get started. Alpha Five is very similar to Access,
but it uses the Xbasic programming language and the design paradigm is
slightly different. Therefore, it will take awhile to get familiar with the
Access way of doing things. Incidentally, Access seems to rely more on
queries, I like that.

Jamie, now if you can only figure out how to keep the [Line_Bal]field up to
date when the user changes the date of a transaction or enters a new record
that occurred several days ago. In A5, I use some heavy duty Xbasic
programming to accomplish such, but I have no idea how to do this in Access.
Hopefully I can use another query as opposed to programming what I need in VB
which is similar to Xbasic, but so different in that it will take some time
to get adjusted.

Thanks for your help,
Robert T.

 

[Jamie Collins]

now if you can only figure out how to keep the [Line_Bal]field up to
date when the user changes the date of a transaction or enters a new record
that occurred several days ago

Simple answer: I wouldn't, that's what I provided the Balances VIEW
for. What advantage do you gain by storing the balance rather than
calculating it ad hoc?

Jamie.

--

 

[Guest]

Hi Jamie:

You're right, the line balance isn't absolutely necessary because you have
the overall balance in the checking account, which is after all, the ultimate
goal. However, it is a nice little touch if and when you want to know the
checkng account balance on a particular date. Thtat's what Quicken does and
I've gotten used to seeing that over the past 10 years or so.

Robert T