J
Joe Cilinceon
I'm trying to come up with a single table to add to my database to track
credits, balance due and refunds. Credits and balance due amounts would be
associated with a transaction # in most cases but not always. Refunds would
be associated with a move out (vacate) date. All would be associated with a
LedgerID (account number basically). I also realize that this will have many
records for 1 account as I will need to generate a history report on each
account when done. Most of this data would be stored by SQL in the table at
the time a payment is posted or a unit vacated.
Things I would need to track is who (ledgerid), what (credit, balance due,
refund and transaction if applicable), when it was settled and a how. I
would also need a means to enter the start amount since data would start
with the paper records kept prior. Things I needing to know about setting
this up is, would it be wise to assign each event with a unique tracking
number or would the other like LedgerID, Transaction # be enough. In the
case of a refund that we paid, I will need the date paid and how.
Credits would be a tenant over pays a part of his/her monthly rent, mails a
late fee not owed etc. An unusual reason would be if we gave a credit for
customer service, services rendered or a transfer of credits from 1 account
to another.
Balance Due would be where we decide not penalize a tenant and put an under
payment as credit. We would instead, give credit for the months rent and
allow them to pay the balance on their next payment without a penalty.
Refunds would normally be given when a tenant vacates with unused rent and
credits earned. These we can either pay by check or in some cases we apply
it to another of the tenant's accounts as a credit.
Transaction is a auto number generated every time a tenant pays us for
anything. LedgerID is the CustNo & UnitNo & Date of Move in.
Tables would be Tenant (general customer info). Leases (LedgerID (main)
based on CustNo, UnitNo, Move in date, Move out date etc. Ledger would hold
a copy of every transaction with the transaction number, ledgerid and ties
to other tables for Payments, Charges.
Sorry about the length but I don't know how to explain it shorter.
credits, balance due and refunds. Credits and balance due amounts would be
associated with a transaction # in most cases but not always. Refunds would
be associated with a move out (vacate) date. All would be associated with a
LedgerID (account number basically). I also realize that this will have many
records for 1 account as I will need to generate a history report on each
account when done. Most of this data would be stored by SQL in the table at
the time a payment is posted or a unit vacated.
Things I would need to track is who (ledgerid), what (credit, balance due,
refund and transaction if applicable), when it was settled and a how. I
would also need a means to enter the start amount since data would start
with the paper records kept prior. Things I needing to know about setting
this up is, would it be wise to assign each event with a unique tracking
number or would the other like LedgerID, Transaction # be enough. In the
case of a refund that we paid, I will need the date paid and how.
Credits would be a tenant over pays a part of his/her monthly rent, mails a
late fee not owed etc. An unusual reason would be if we gave a credit for
customer service, services rendered or a transfer of credits from 1 account
to another.
Balance Due would be where we decide not penalize a tenant and put an under
payment as credit. We would instead, give credit for the months rent and
allow them to pay the balance on their next payment without a penalty.
Refunds would normally be given when a tenant vacates with unused rent and
credits earned. These we can either pay by check or in some cases we apply
it to another of the tenant's accounts as a credit.
Transaction is a auto number generated every time a tenant pays us for
anything. LedgerID is the CustNo & UnitNo & Date of Move in.
Tables would be Tenant (general customer info). Leases (LedgerID (main)
based on CustNo, UnitNo, Move in date, Move out date etc. Ledger would hold
a copy of every transaction with the transaction number, ledgerid and ties
to other tables for Payments, Charges.
Sorry about the length but I don't know how to explain it shorter.