Adding and substracting numbers in a Microsoft Access Table

[brialex]

I am trying to find a way to create a general ledger within an Access table so that I can report off of it. For example, I have withdrawals, deposits, and balance forward. I can't seem to find a way for the balance forward to automatically update instead of figuring the amount manually and typing it in. I would like to show a new balance forward for each time a transaction occurs. Any help would be greatly appreciated!

Trisha

 

[lespaul]

Storing a balance in a table breaks third normal form which is why you are having problems trying to figure out how to update the balance anytime a journal entry is entered.

You have two choices:

1. Create the balance on the fly in a query

2. Create a second table that just stores the account numbers' balance and update that table any time a change is made to a journal account.

Read 'The Fundamentals of Relational Database Design' for more information about normalization and third normal form.

HTH

Leslie

 

[willir]

brialex

Leslie is correct in that storing calculations does break the third rule on normalization. The idea with relational tables is remove redundancies, thus simplify maintenance.

However, I realize that with accounting, balances are useful. Moreover, it can save a heck of a lot of CPU time if you have 10,000's of transactions.

Although I have seen a balance on the G/L master record, a better solution from my perspective, is to have a monthly balance table.

As part of the month-end process, the balance table is updated for the posted month for each account. Now, when you want to now the current balance, you retrieve the balance for the previous month-end, and then run SUM calcualtion query for the current month.

This can be done, but you will need to create a centralized posting module, and do a heck of a lot of maintenance.

Have you considered purchasing an accounting applicaiton such as Quicken or MYOB or PeachTree, etc. For $50 to $200, you can save yourself a heck of a lot grief.

Richard

 

[lespaul]

I'm with you there Richard. Having worked as an accountant for 10+ years before moving to IT, I have recommended to many posters to purchase a canned accounting system. I know that I wouldn't want to design one from scratch and I'm a programmer and an accountant!

I personally like Peachtree over the others. It allows for those with the knowledge to easily fix errors. Quicken/Quickbooks are designed more for those who don't know what's going on the in background and can be difficult for knowlegable users to "correct" (especially when you get into Payroll tax problems which every balance sheet I have ever seen has the P/R tax liabilities WRONG!)

All these accounting packages have been set up so it's very easy to start a business. There are lists of default GL accounts for each kind of business and it's very easy to set up.

HTH

leslie

 

[willir]

Thanks Leslie.

As an FYI, I offered a range of applications so as not to be seen as soliciting. And I agree PeachTree has more punch but with power comes repsonsibility.

Do you like accounting better or IT? Lotus Notes (non-relational) or Access?

Cheers

 

[lespaul]

I would definite say I like IT more (most accounting work is cyclical, busy for the first 1 1/2 weeks of the month doing month end reconciliation, rest of month waiting for end of month to arrive!) I think it's because I LOVE puzzles and most programming is just how to put the pieces together so it works! Convulted logic is my speciality!

Have to say I'm a firm believer in relational databases, it took me MONTHS to have a clue in Notes! Plus, relational databases come naturally to me. I think my drafts are almost 3NF right off the bat!

If your son makes it down to the southwest to climb have him look us up!

Leslie