Re: Need help with a tracking table design

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Joe Cilinceon wrote:

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.

Unless I missed something here, your [Transaction #] is a field in your [events] Table, so it already needs to be unique and would suffice as a tracking number. You'll also want to include a field [events].[ledgerid] to attach the record to the customer to whom it applies, but that won't make the record any more unique than it already is.

You'll also want to include the other fields you mentioned. BTW, I didn't see it in your list, but you are including the unit number to which the payment/credit applies, aren't you?

For the "what", you might want a list of choices (links to a Table listing such choices as "Credit for unneeded late payment", "Refund of unused rent", "Transfer from another account", "Transfer to another account", "Ordinary rent payment"). In addition, I sometimes include a field called [Notes], of a Text or Memo data type, that allows me to record anything else unusual about the transaction.

You could use this [events] Table to record any transaction involving money. For transfers (e.g., if an account holder vacates one of 2 rented units, gets a credit, and applies it to the remaining unit, you might generate 2 records with the same date, one for the refund, the other applying it to the remaining unit).

[...]
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.

I think I would want to avoid recording [Balance Due] events. This should be computable from other sources. For example, you know how much a client owed you (or was owed) on Jan. 1, 2005, and you could record this amount and the date either in your [Ledger] Table for the client, or in a Table linked to it. Applying any subsequent transactions (in the [events] Table) to this amount will give you the current balance due. All you would need to record for each transaction would be an [events] record, without worrying about determining the balance due or updating that amount somewhere. If you did record [Balance Due] in [events], then later discovered a mistake in some earlier transaction, all the [Balance Due] values since then would become wrong. :-(

[...]

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.

I suggest that you split these. You could put customer information into a [Customers] Table, including contact information, approved payment methods, customer account name/number, &c.

You could put lease information for a particular storage unit into a [Leases] Table, including

- [CustNo] (which would be a link to an account number in [Customers]),
- [UnitNo] (which could be either just the number posted on a sign on the storage unit, or a link to a [Units] Table containing stuff like the unit number, maintenance information, &c.),
- [Move in] (the move-in date)
- [Move out] (unused while the customer is renting this unit, but useful later for auditing purposes)
- [Lease #] (a reference to the contract that the customer signed when renting the unit, and this could serve as the primary key for this Table, as I expect you don't double up on contract numbers)

If a customer should vacate a unit and then later rent the same unit, I think I'd set up a new [Leases] record, even though it's the same unit number.

My idea here is to, for example, associate with a given customer all the information about that customer that's not likely to change often, associate with a storage unit whatever describes it that doesn't change often, &c.

For information that could change, but only rarely (such as a customer's telephone number), you can decide if you need to keep the old information, or just replace it. If you replace, then let it be a field in the main Table (in the case of a phone number, the [Customers] Table). If you think you need to keep the old records, create a separate Table with a [Date Changed] field, a [New value] field, and a [Customers_ID] field linking it to the main Table. An intermediate choice would be to replace the field value in your Access Table, but attach a piece of paper noting the change to your paper file of customer records.

  -- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
  Please feel free to quote anything I say here.

.

Relevant Pages

  • Re: Need help with a tracking table design
    ... Ledger> transaction and LedgerID from above ... Transfer Credit and unless it is more than needed it is usually gone after ... > I think I would want to avoid recording [Balance Due] events. ... > payment methods, customer account name/number, &c. ...
    (microsoft.public.access.gettingstarted)
  • Re: How to apply interest to balance in accounts
    ... Usually an interest payment would just be recoded as another (credit) ... transaction on the account. ... The problem is ascertaining the balance on which to perform the interest ...
    (microsoft.public.access.modulesdaovba)
  • Re: VALUE MANIPULATION IN MySQL with JAVA question..
    ... making tables for each account name upon input of the user. ... for each account name has four columns (date, debit, credit and ... For each row the balance column should be equal to debit- ... When you insert a new debit or credit, you exclude the current transaction ...
    (comp.lang.java.programmer)
  • Re: Southwest Cancellation amount
    ... and have always received full credit. ... The various chunks of money credited to you on Southwest carry ... never had any portion of the balance expire. ... Request a refund of the refundable balance. ...
    (rec.travel.air)
  • Re: Credit Card Refunds/Returns & Statement Date Time Frame
    ... "that any refund off the credit card is not instaneously processed at ... I HIGHLY recommend you get online access to your credit card account ... them $1,010 and then get the credit, you'll have a huge credit balance ...
    (misc.consumers)