Summing debits/credits in simple general ledger?

[michaelbluejay]

Hi, this is my first post here. I tried to see if this question has been addressed before but if so I couldn't find it.

Anyway, I'm trying to make the simplest flat-file general ledger but I can't figure out how to sum the accounts.

The database is simple: Each record has just the date, amount, debit account, credit account, and description. So far so good.

But I don't know how to sum each account. The problem is that for a given set of records for a given account, some of the amounts will be debits and some will be credits, and I don't know how to check for each one. I can't just sum the Amount field -- some of those entries need + values and some need - values depending on whether they're debits or credits.

The only solution I know of is to search for a debit account, record the total on a piece of paper, then search for a credit account, and subtract its total from the first total. Seems like that's a waste of owning a computer, though.

I don't mind creating a separate script for each and every account. I just don't know how to set it up to even write the first script.

Thanks very much for your help.

[BobWeaver]

The easiest solution is to create a related accounts file. This file would have one record for each account and would have two relationships. One relationship (let's call it "Credit") from the account field back to the credit account field, and one relationship (let's call it "Debit") from the account field back to the debit account field. Then, you can do summaries easily in the account file. A calculated field in the accounts file with this formula will give you an account balance:

Sum(Debit::Amount)-Sum(Credit::Amount)

You can also place a portal based on these relationships, on the layout in your accounts file and view all the transactions records pertaining to the account.