If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Checking Account Design
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 |
#2
|
|||
|
|||
Robert T wrote: 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. -- |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
Robert T wrote:
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. -- |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Continuing VLOOKUP Question | JimFor | General Discussion | 13 | November 3rd, 2004 12:54 PM |
POP3 account omitted during scheduled send/receives | Sean Hester | General Discussion | 0 | July 21st, 2004 04:36 PM |
Service Unavailable from only one Hotmail account | Andrew | Outlook Express | 2 | June 8th, 2004 01:06 AM |