Prop Mgnt: Charges, Receipts, Transactions

hiro24601 · #1 (**permalink**) 10-03-04, 18:16

I cannot get my head around the whole charges, payments, receipts, and transactions (accounting) side of my business.

I have a table called tbl.ChargesRecurring which updates tbl.Charges on the 1st of every month for current Leases.

Charges and payments are so closely related I don't know whether to include them in the same or different tables. A charge is neg and a payment is positive, (or vice versa accounting seems very abstract to me.) This implies that both should be in the same table denoted as positive or negative. This would make ledger functions very easy.

In my business tenants may incure other charges. Due to the "Act" that governs my practices I must allocate monies to some charges before others in order to actually collect them. Charges and payments may also be reversed. I can have one payment to many charges, or many payments for one charge. For deposit reasons I need to be able to print reports on how much in cash, check, money order, and credit has been received during a certain period of time.

My first priority is to be able to create receipts for payments and allocate them to appropriate charges. My second is to be able to print deposit slips. My third is to be able to print tenant ledgers.

I'm working in Access, not the best program but the one I started with and the one I'll finish with.

I know I am not the only Property Managment geek intent on building a personalized program. Somebody has to have some insite. There are multiple software programs out there designed for my business.

One or multiple tables? How is it done? Any suggestions would be welcome.

andrewst · #2 (**permalink**) 10-04-04, 05:58

Most accounting-type databases I have seen use a single table for both credit and debit transactions, with the sign of the number indicating whether credit or debit.

Actually, my current client uses a single table with positive numbers for all transaction types, and another column to indicate whether it is a credit or a debit. This is mad, in my view, since every query then has to use expressions like:

Code:

SELECT SUM((CASE WHEN trans_type='CREDIT' then -1 ELSE +1 END) * amount) as balance
FROM transaction;

(Of course, they could have hidden this complexity behind a view; but of course they did not!)

hiro24601 · #3 (**permalink**) 10-04-04, 13:56

Thanks Tony,

I yanked all my advertisements for this week so I should have time to focus on it. I'll keep you informed...

This is the first forum I've every participated in. I didn't know what to expect. If I can figure out how it all works this will be very beneficial.

Daryl Mhoon

hiro24601 · #4 (**permalink**) 10-04-04, 16:45

WOW Tony,

You know your stuff! I appologize for asking a question you have already answered multiple times.

I'm new to this or any other forum and I've spent most of the day reading and learing to navigate. ALL the comments have helped tremendously.

I'll be back when I have more intelligent questions that have not already been answered.

Now to apply the logic...

Daryl

Internet Services

Web Development

Digital Marketing

Consumer Tech