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.
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:
SELECT SUM((CASE WHEN trans_type='CREDIT' then -1 ELSE +1 END) * amount) as balance
(Of course, they could have hidden this complexity behind a view; but of course they did not!)