Are linear calculations possible?

[jnuus]

There is an organization at my work that is involved with non-profit charity work. They are currently tracking their funds using the paper register in thier checkbook. This method is not suitable for them because they are required to keep detailed notes on each of thier financial transactions, so they asked me about the feasability of a checkbook program in Access, in which they would be able to use it as a checkbook, and be able to keep notes and other records associated with each transaction.

I don't think that Access tables are capable of performing linear calculations like its cousin Excel, however, if there is a way to cheat, I am welcoming any and all suggestions.

 

[dongle]

Are you talking essentailly of a flat file with a few fields e.g. date, payee, amount, running total, notes, cheque number, cheque ID? or is it someting much more sophisticated.

 

[jnuus]

Exactly. I'm not looking to build a complicated app, maybe a few related tables is all. Just like in Quicken, Money, or Excel, I would like to display a balance at each transaction and then also an ending balance, like in this simplified example:

1/25/03         Opening Balance                       800.00
1/29/03   #105  Education Grant     100.00            700.00
2/3/03          Deposit                      250.00   950.00
                                    
                                     ENDING BALANCE:  950.00

What I am unsure of is how to display the balances on the right as transactions are entered. This is definitely Excel territory, but they do not want to use Excel.

The Ending Balance shouldn't be a problem, probably a DSUM expression there.

Again, any help would be most appreciated.

 

[dongle]

I can't see you need any related tables for this. Give me a few more details please.

 

[dongle]

A list of fields in each table would be useful too.

 

[jnuus]

Thanks for replying. True, a flat table database would work with this app. The organization that wants to use it provides funds to people in need via an outright grant or a loan. Naturally, they have to keep detailed and accurate records of their transactions in the event they are audited. So, in addition to the ledger fields as in my example above, I will be including additional fields to allow them to record all the details. I suppose all of it can go into one table.

The big problem I am having though, is with the ledger itself - getting access to display a balance at the end of each transaction, and then an ending balance like in my example above. The fields I am using for the ledger are:

CheckNumber
Date
Payee
Debit
Credit
Balance

Other fields will hold information such as the reason for loan/grant, description of the need for funds, and recipient's contact info (address, phone, etc.). But this part is pretty straight forward. It's that bloomin' ledger that is bogging me down right now!

If you can help me turn Access into a simple checkbook, you will have my eternal gratitude, and a star to go along with it. Thanks again.

BTW, I am really surprised at how little information there is on the internet about doing this. Tek-Tips is my last hope before I tell them to stick it in an Excel spreadsheet and be happy.

 

[dongle]

Download the query examples from

http://download.microsoft.com/download/access2000/Sample/1.0/W9X2KMe/EN-US/Qrysmp00.exe

You will find a query in these examples which deals with your problem I think.