Need help with a tracking table design

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.
 
V

Vincent Johns

Joe said:
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 <[email protected]>
Please feel free to quote anything I say here.
 
J

Joe Cilinceon

Thanks for you response Vincent and I will go over it in detail today. I
also think perhaps a little more information might be helpful as to what
this does, how it is laid out and what I might need. Please be patient as
this is really a huge project and we have been running the whole site with
it for a year. I had no choice but to write my own or stay with the paper
ledgers (I would rather eat worms), as an off the shelf program was out of
the question. If you want to drop out now I would really understand. g

Here is a basic run down of the tables and how they interact now. I have
several other major items but they do not effect the tenant/rental side of
the program.

TENANTS
CustNo (autonumber assigned at the time we start a new rental unless the
tenant has been here before then we use existing.)
The rest of this table consists of the basic information with the following
additional tables linked to the TENANTS Table via the CustNo; TAddress,
TPhones, TContacts, TLetters, TNotes, TAP. I also have several lookup tables
for form data entry.

UNITS
UnitNo (physical number of the unit ranging from 001 to 455)
Type (ties to UNITDATA which has size and street prices amoung other pieces
of information)
Status (Rented, Vacant, Reserved, Damamged, Company Use etc)

LEASES (the main hub of all the rental records)
LeaseID ( is pretty much an account number made up of the CustNo-UnitNo-Move
In Date "yyyymmdd") text field
CustNo
UnitNo
MoveIn (Date and will always have a date)
MoveOut (Date Null if currently rented)
other things include here are does it get Invoices, late fees, locked out or
not, move in type, vacate type etc.

LEDGER (follows the money with 1 record per payment)
Transaction (autonumber)
LeaseID
PaymentDate
RentRate (current at the time of this payment as it is subject to change)
PaidFrom
PaidThru
Reversed (yes/no check box for marking NSF/Credit card reversals)
Notes (text field for quick notes)

PAYMENTS (can have more than 1 per transaction for example tenant pays with
a check for rent and fees in cash at the same time)
Transaction
PayMethod (cash, check, credit card etc)
Tracking (Check # etc)
PayAmt (how much)

CHARGES (has 2 lookup tables and can have more than 1 per transaction)
Transaction
ChgID (lookup table on form # field 1=Rent, 2=Late fee etc)
ChgAmt (amount of charge)
ChgExp (lookup table for unusual charges that has a list of excepted
responses)

qryCurrent (This query looks at the transactions and pull the max Paidthru
date for rented units)

qryBalanceDue (ties to the qryCurrent and does all the calculations require
to figure out Rent owed, next due date, fees to apply, previous balance due,
credits and refunds to apply) I have written several math functions for
handling much of the basic things (Prorate movin for example).

All data is entered and gotten to thru a series of interconnected forms that
pretty much follow a good work flow.

Now what I was wanting to know is a way to keep a record of Refunds, Credits
we might give, and Balance due amounts. At the moment the Refunds data is
part of the leases record for an account with Credits/Balance Due as part of
the Tenant record.

This is basically how it works. Rent is due on the 1st of each month for
that month with a late fee added on the 10th. In many cases we receive a
payment after the 10th without the late fee and in these cases we show in
the CHARGES as an Under Payment amount and will allow the paid thru date to
advance. The Balance Due will be that amount and will be taken off the top
of the next payment. Over Payment of course gives a credit but is not the
only type of credit. If you transfer from a larger space to a smaller unit
there will be a price difference and you can't transfer unless rent is
current. In this case you would get the unused amount of rent applied to the
new unit, we call this a Transfer Credit. We also have a case where a tenant
will have several units paid in advance (we don't give refunds on partial
months only full months paid in advance) and will vacate one. He might
choose not to have us send him a check but use the refund for his other
units as payment. Needless to say we call this a refund credit.

The main problem I have run into is this system has been on line for a
single year now. At the beginning we didn't record the starting amounts of
Balance Due and Credit amounts so some of the early transaction will show a
credit applied with source of its origin. This makes it difficult to simply
add the Ledger up and get a set amount that the tenant can use or for that
matter how much he might owe us for balance due.
 
J

Joe Cilinceon

Responce below sections

Vincent said:
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.

Sorry I wasn't very clear here. I have several basic tables with their key.
Tenants > custno, Units > unitno, Leases > ledgerid (ties tenant and unit
together), Ledger > transaction and LedgerID from above (handles payments)
with 2 sub tables, Charges > tranaction and Payments > transaction.
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?

Yes, again I wasn't very clear. Leases has the CustNo and UnitNo tied
together to make and account number I call LedgerID. It is basically the
CustNo-UnitNo-Move In Date (111-999-yyyymmdd format). This LedgerID is also
one field for every transaction.
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.

When we do a transfer that is shown automatically in the transaction as a
Transfer Credit and unless it is more than needed it is usually gone after
the transfer. Now if the transfer credit is larger than due I show the part
used as a Tranfer Credit and the overage as an Over Payment. Oh and I have
memo fields thru out most of my tables.
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).

Yes this is what I'm looking for I think. I'm needing away to show when and
where a credit came from without having to go thru the complete Ledger's
records to find it. I was thinking perhaps a table that could query for a
sum. If negitive they owe me and if possitive results I owe them.
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. :-(

Again I wasn't very clear. What I mean here by the Balance Due is from
unusual situations like short payments, unexpected chargees like a special
garbage pickup, not the normal monthly Rent, Fees type of charge. These
extra amounts is what I was talking about tracking. If I had thought this
out a little better when I started I could simply just use the Charges table
to total up everything and get credits and balance due. The problem is I
don't have complete records for the last 15 years (old paper ledgers) as I
only entered the current tenants when I started loading the tables and used
the payment that made them current. I simple stored the carry over credits
and balance due in single fields in the tenant table, pretty much how I
handle NSF check fees (you only get one then no more checks). I didn't see a
need to do more at the time I started and I'm now trying to fix some design
flaws.
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)

Oh here I'm with you in that it has been this way since the beginning. I
typed Leases for Ledger and that was a little confusing. Sorry about that.
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.

It happens a lot and it will become a new lease, hence the CustNo-UnitNo-
Movin date since that will never repeat.
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 <[email protected]>
Please feel free to quote anything I say here.

Thanks again Vincent I find it hard getting some of my questions answer
since I either give too much information or not enough. Hard to know what is
correct. g
 
V

Vincent Johns

Joe said:
Thanks for you response Vincent and I will go over it in detail today. I
also think perhaps a little more information might be helpful as to what
this does, how it is laid out and what I might need.

I have some idea of what your project does, from your previous postings,
but I wonder if emailing me a copy (sanitized to remove any actual
personal information) would be helpful. I'll still want to post answers
here for the benefit of others who have suggestions, but some of your
design details are kind of tedious to recount in a forum like this.
(And I can't promise an immediate answer, even if you do email it, but I
can look at it and return to you when I can.)
Please be patient as
this is really a huge project and we have been running the whole site with
it for a year. I had no choice but to write my own or stay with the paper
ledgers (I would rather eat worms)

I don't blame you! :)
, as an off the shelf program was out of
the question. If you want to drop out now I would really understand. g

Here is a basic run down of the tables and how they interact now. I have
several other major items but they do not effect the tenant/rental side of
the program.

TENANTS
CustNo (autonumber assigned at the time we start a new rental unless the
tenant has been here before then we use existing.)
The rest of this table consists of the basic information

As I mentioned in a previous message, I hope that this "basic"
information is really material that properly relates to a tenant, rather
than to a storage unit, etc.
with the following
additional tables linked to the TENANTS Table via the CustNo; TAddress,
TPhones, TContacts, TLetters, TNotes, TAP. I also have several lookup tables
for form data entry.



UNITS
UnitNo (physical number of the unit ranging from 001 to 455)
Type (ties to UNITDATA which has size and street prices amoung other pieces
of information)
Status (Rented, Vacant, Reserved, Damamged, Company Use etc)

Incidental note: This [Status] field could be either text, such as the
string "Vacant", or (which I'd prefer) a link to a [Status] Table that
contains all the possible values of this field.
LEASES (the main hub of all the rental records)
LeaseID ( is pretty much an account number made up of the CustNo-UnitNo-Move
In Date "yyyymmdd") text field
CustNo
UnitNo
MoveIn (Date and will always have a date)
MoveOut (Date Null if currently rented)
other things include here are does it get Invoices, late fees, locked out or
not, move in type, vacate type etc.

LEDGER (follows the money with 1 record per payment)
Transaction (autonumber)
LeaseID
PaymentDate
RentRate (current at the time of this payment as it is subject to change)

Another way you could handle [RentRate] is to keep a Table with rates,
dates they changed, and units to which they applied. I think it might
be easier to audit, and you could always calculate the proper rate from
it, given the date and the unit number.
PaidFrom
PaidThru
Reversed (yes/no check box for marking NSF/Credit card reversals)

It's possible you'd need more information on [Reversed], such as details
on what happened. I'm not sure exactly what, depends on your business
model.
Notes (text field for quick notes)

PAYMENTS (can have more than 1 per transaction for example tenant pays with
a check for rent and fees in cash at the same time)
Transaction
PayMethod (cash, check, credit card etc)
Tracking (Check # etc)
PayAmt (how much)

Plus maybe the date you received the payment, perhaps??

[...]
The main problem I have run into is this system has been on line for a
single year now. At the beginning we didn't record the starting amounts of
Balance Due and Credit amounts so some of the early transaction will show a
credit applied with source of its origin. This makes it difficult to simply
add the Ledger up and get a set amount that the tenant can use or for that
matter how much he might owe us for balance due.

In a case like yours, I usually include a special record for each
account that gives a starting (positive or negative) balance, to help
match the paper trail up to that point. But I think it's important,
having chosen such initial conditions, to have a system that
automatically gives you the balance at any given point since then.

Gottago now... I'll answer your other message later unless someone beats
me to it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
J

Joe Cilinceon

See responses below and I'm triming out my questions and leaving yours to
answer.

Vincent said:
I have some idea of what your project does, from your previous
postings, but I wonder if emailing me a copy (sanitized to remove any
actual personal information) would be helpful. I'll still want to
post answers here for the benefit of others who have suggestions, but
some of your design details are kind of tedious to recount in a forum
like this. (And I can't promise an immediate answer, even if you do
email it, but I can look at it and return to you when I can.)

Yes I will gladly make up a dummy with some fake data as I need all the help
I can get. I'll email it when I get it done and really do apprecate the help
on this. When I first started asking (design phase) all I got was buy one
ready made. I got so aggravated I just stop asking question until recently.
I've been doing this now since Nov. of last year. I'm now just trying to
finish up a few loose ends and correct some design flaws (none are crippling
luckily).
As I mentioned in a previous message, I hope that this "basic"
information is really material that properly relates to a tenant,
rather than to a storage unit, etc.

Yes, like the path to a photo copy of the picture ID used to rent the unit,
driver lic #, SS #, NSF check counter (every time one is gotten it goes up
with 2 and no more checks). NSF fee owed as I said you can only have 1. That
is about it in the TENANTS table with other tables for TADDRESS, TPHONES,
TAP (Autopay data), TNOTES, TLETTERS, TEMPL, TCONTACTS filling in the rest
we rarely need. Oh and we have to keep all addresses and phone number given
even if they are no long good. All unit data is kept in 2 tables, Units
which just has the UnitNo, Type (links to the other table for units),
Status, WTO (sorting column for a walk thru report), DateRemoved (for units
taken off line like our cart room), Combined (where we combine 2 or more
units and rent as one, Notes used for things like roof leak, needs door
replacement etc. The other table is UNITINFO ordered by [Type] where type is
an auto number (only 8 records total), Size of unit, SQFT, StreetRate,
PerDay Rate, Qty (how many we have of this type).

Incidental note: This [Status] field could be either text, such as the
string "Vacant", or (which I'd prefer) a link to a [Status] Table that
contains all the possible values of this field.

Actually the [Status] field is a number and I have a lookup table to read
what it is in my forms. I've learned early on to use number with text
descriptions kept in lookup tables.
Another way you could handle [RentRate] is to keep a Table with rates,
dates they changed, and units to which they applied. I think it might
be easier to audit, and you could always calculate the proper rate
from it, given the date and the unit number.

That is a great idea since we only keep the last one in the LEASES table.
I'm now thinking about splitting this table in to a couple of smaller ones.
Oh and we have only had a few rent changes since I took over a year ago so
no one has more than one rent change to date.

It's possible you'd need more information on [Reversed], such as
details on what happened. I'm not sure exactly what, depends on your
business model.

Actually when I look at a Tenants Ledger I see it in a form that shows the
Ledger info at the top, Charges in a sub form in the middle and the payment
at the bottom. I simply check the yes/no button in the Ledger part and
change the Check to NSF Check. From that point on it isn't figure in
anything in the way of accounting but I still have the number date it
bounced as well as a note field in the ledger to document anything I need
too. We also keep notes everytime we speak to a tenant in regards to
business.


I don't have a date in the payments nor charges as both tie directly to the
Ledger by the transaction # which holds the PaymentDate.
Plus maybe the date you received the payment, perhaps??


In a case like yours, I usually include a special record for each
account that gives a starting (positive or negative) balance, to help
match the paper trail up to that point. But I think it's important,
having chosen such initial conditions, to have a system that
automatically gives you the balance at any given point since then.

Yes, this was something I didn't think enough about when I started. Now I
have to come up with a method to fix it, even if it means going back and
pulling out all the old paper files and back tracking. Luckily I don't have
to go back to about 20 tenant's that it would apply too.
Gottago now... I'll answer your other message later unless someone
beats me to it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Thanks a lot Vincent and as I said you feel free to do this as you wish as I
do appreaciate the help given already. vbg
 
V

Vincent Johns

Joe Cilinceon wrote:

[...]
Yes, like the path to a photo copy of the picture ID used to rent the unit,
driver lic #, SS #, NSF check counter (every time one is gotten it goes up
with 2 and no more checks). NSF fee owed as I said you can only have 1. That
is about it in the TENANTS table with other tables for TADDRESS, TPHONES,
TAP (Autopay data), TNOTES, TLETTERS, TEMPL, TCONTACTS filling in the rest
we rarely need. Oh and we have to keep all addresses and phone number given
even if they are no long good.

As I may have mentioned, if you need more than 1 copy of something, such
as past addresses, it's easiest to handle if you put them into another
Table (with a date so you can find the current one) and link them to the
appropriate record in [Tenants].
All unit data is kept in 2 tables, Units
which just has the UnitNo, Type (links to the other table for units),
Status, WTO (sorting column for a walk thru report), DateRemoved (for units
taken off line like our cart room), Combined (where we combine 2 or more
units and rent as one, Notes used for things like roof leak, needs door
replacement etc.

What you can do with [Notes] is to periodically review them and, if you
see some appearing multiple times, add a field or a new value for an
existing field to account for those events.
The other table is UNITINFO ordered by [Type] where type is
an auto number (only 8 records total), Size of unit, SQFT, StreetRate,
PerDay Rate, Qty (how many we have of this type).

The rates you may be able to classify into a few categories, unless you
charge a different rate for each unit. (I rent some lockers, and they
have only 3 distinct rates, one for each of their 3 sizes.)

Your [Qty] field is probably useless. Since you already have a [Type]
field stored, you can easily compute this, if indeed you ever need it.
Actually the [Status] field is a number and I have a lookup table to read
what it is in my forms. I've learned early on to use number with text
descriptions kept in lookup tables.

That sounds good. That not only saves space but makes it easy to
correct misspellings.
That is a great idea since we only keep the last one in the LEASES table.
I'm now thinking about splitting this table in to a couple of smaller ones.
Oh and we have only had a few rent changes since I took over a year ago so
no one has more than one rent change to date.

How much work would an auditor need to go to, to determine if the rent
changes were fairly applied? Also, do you have a way to record how you
notified your tenants of rent increases?

[...]
We also keep notes every time we speak to a tenant in regards to
business.

Keeping logs or summaries of conversations may require a separate Table,
perhaps one with date/time, tenant's ID, and the matter discussed (maybe
a Memo field). Some parts of this you might be able to summarize as
standard communication (such as "Here's my payment for this month") and
be able to record in condensed form (such as a payment-received event
record).
Yes, this was something I didn't think enough about when I started. Now I
have to come up with a method to fix it, even if it means going back and
pulling out all the old paper files and back tracking. Luckily I don't have
to go back to about 20 tenant's that it would apply too.

Either that, or (as I have had to do) you can start with what you know
is the current balance, reverse all the payments that you know of since
some convenient past time, and record that result as the starting
balance. Then you can specify any date since then (and into the future)
and get an accurate balance due as of that date. If those starting
balances match the paper records, hooray. If not, well, you can
research them if you wish to or need to (such as if a lawsuit pops up),
but what you really need is good current records, and that might
suffice. (Watch out -- I'm not an attorney, and you may have legal
reasons to be required to have a fully verifiable paper/electronic trail
going all the way back. Check with a qualified professional.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
J

Joe Cilinceon

Vincent said:
As I may have mentioned, if you need more than 1 copy of something,
such as past addresses, it's easiest to handle if you put them into
another Table (with a date so you can find the current one) and link
them to the appropriate record in [Tenants].

I have another table for address which allows for as many as needed. We will
only mail to one however and have it marked as the mailing address with a
checkbox. Also have one other check box for active or not active in the
event we get a change of address. Phone numbers are that critical so in the
Tenants table I have a field for Home, Office with extension, Cell, Fax and
email address. Contacts are also in a different folder for any given tenant
and there can be a few.
What you can do with [Notes] is to periodically review them and, if
you see some appearing multiple times, add a field or a new value for
an existing field to account for those events.

I actually dump them about every 6 months except for those marked priority.
We really don't have that many as this is a small place with only 455 units
total.
The rates you may be able to classify into a few categories, unless
you charge a different rate for each unit. (I rent some lockers, and
they have only 3 distinct rates, one for each of their 3 sizes.)

Your [Qty] field is probably useless. Since you already have a [Type]
field stored, you can easily compute this, if indeed you ever need it.

Ok this might be clearer. We have 9 sizes of units (5x5, 5x10, 10x10 up too
30 x 40 as well as RV parking) each size has a set rate per month. The Qty
is the total number of each size (Type) we have. I added it to just simply
make it easier for reporting on unit sq footage.
That sounds good. That not only saves space but makes it easy to
correct misspellings.

I use the same for things line States and countries in address fields,
Charges, Payment Methods, Explanations on charges and charge descriptions.

How much work would an auditor need to go to, to determine if the rent
changes were fairly applied? Also, do you have a way to record how
you notified your tenants of rent increases?

I have every payment record for every tenant since Jan 2004 entered into the
computer and every payment since Dec 2004 has been done thru this system. It
can print out a copy of a tenant's ledger and a receipt for any payment made
in the computer. It also print reports accounting for every penny, every
charge in any time span you want from Jan. 2004 to today.

Every time we send any kind of letter the date printed, type of letter, and
for which unit to the tenant's Letter table. I can also reproduce the letter
at any time. Leases are also printed and can be reproduce from the orginial
to a new date if needed.

Keeping logs or summaries of conversations may require a separate
Table, perhaps one with date/time, tenant's ID, and the matter
discussed (maybe a Memo field). Some parts of this you might be able
to summarize as standard communication (such as "Here's my payment
for this month") and be able to record in condensed form (such as a
payment-received event record).

It exists as a memo field with a date/time stamp for each posting. This is
also kept in a TNotes table by custno.
Either that, or (as I have had to do) you can start with what you know
is the current balance, reverse all the payments that you know of
since some convenient past time, and record that result as the
starting balance. Then you can specify any date since then (and into
the future) and get an accurate balance due as of that date. If
those starting balances match the paper records, hooray. If not,
well, you can research them if you wish to or need to (such as if a
lawsuit pops up), but what you really need is good current records,
and that might suffice. (Watch out -- I'm not an attorney, and you
may have legal reasons to be required to have a fully verifiable
paper/electronic trail going all the way back. Check with a
qualified professional.)

One thing that makes it easy is, every one is due on the 1st of each month
and pays for a full month min. If they only want it for a day they pay for a
month other than the first month. We do prorate the movein from the day you
rent to the last day of the current month. I might look at those that have a
Credit applied or a Previous Balance paid and see how many I really need to
go back on. I'm willing to bet it won't be more than a dozen if that.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top