Hi Steve

I realy do appreaciate the help. I asked about 20 times for advise on doing
a table layout for this project and only got  "go buy an accounting program"
or hire me to do it for you. I did manage to do is get an inside one of the
better applications for this business and see how their tables are done. It
gave me some idea but I'm not building a program on thier scale so much
isn't needed.

This is a one person operation just trying to find and easy way to keep
track of 455 storage units. We don't have $3000 to spend on an application
at least not till 2006 since other things like paint, door replacement,
concrete drive way repair are all on the agenda for this year. I do own
Office XP Developer, a new computer and though I've not writen a program in
15 years I figure I can at least beat the paper system used here till Nov 1.

Now with that said I am open to suggestions on how could I improve the
database design. Below is a very short version of what is does and how I
have it designed.

We rent self storage spaces (455 total) with 500 current and non current
tenants on record. A tenant can rent more than one unit, can transfer from
one unit to the next or move out and we have even had the swap units. It is
also not uncommen to vacate a unit in the AM and rent it in the PM to
someone else. When they rent we prorated first months till the end of the
current month so every one is due on the first. A trasfer we have to
transfer the un used rent to the new unit and close the ledger on the old.
We then gererate a receipt and a rental agreement based on the tenant, unit
and ledger data that must signed by both us and the tenant (legal). It also
gererates Late Letters on the 10th of the month, Invoices on the 11th,
lockcut letterson the 15th and auction letters on the last day of the month,
in addition it creates NSF letters (bad checks), balance due/ partial
payment letters.Weekly reports about the property such as occupancy,
delinquency, and finiancial data. It also tracks our quartely compititon
reports, employee records (3 of us total), bank deposits, petty cash and
other expenses.

Tenant table
   CustNo (autonumber)
   Name, Address, Phone number and such is stored here just for the tenant.
(The follow tables may or may not have records, if none give no records
created)
Tenant Contact table.
   CustNo (ties it to the Tenant file
   (Here is kept emergency contact information or in the case of a company
the contact who pay an such
Tenant Notes table
   CustNo (same as above)
   (Every time we speak to a tenant in regards to anything business related
we note it with a date and time stamp)
TenantLetters table
   Custno (again is the tie)
   Every letter generated is logged here automatically.

UNITS (455 records)
   UnitID    (The units number 1 to 455) Integer
   Type    (type unit ties to the UnitData Table) Integer
   Status (rented, vacant, damaged, combined with another unit, etc)
Integer

UnitData    (8 records cover it all.)
   Type (Integer and unique)
   Size (5x5, 5x10, 10x10, RV Parking etc)
   SQFT (square footage)

(We can vacate a unit this morning and rent it again in the afternoon to a
different tenant)
LEASES (ties unit and tenant together)
   LedgerID (text field made up of CustNO & UnitNo & StartDate (20050128
would be today)
   StartDate
   EndDate
   LockStatus (locked out or not)
   GateCode
   Invoiced
   AutoPay
   (much more here but I'm sure you get the idea)

Now for the part I asked for help with how to keep track of and post
payments

LEDGER
   Transaction (autonumber) 1 trans 1 number
   LedgerID    Who and what
   PaymentDate
   Rent
   Fees
   (here on you see it is a break down of what covers what)

PAYMENT (allows split payments as there can be only 1 Ledger record but many
payments  on the same transaction)
   Transaction (links to LEDGER
   PaymentAmount
   Method    (Integer ties it to a lookup table) Cash, Check, CRCD, Money
Order, Travels checks etc
   Tracking  (Check number and such

Now you have the basic layout of this database. There are several lookup
support tables such as Movein Type, Payment Type ect. The Tenants, Units and
such are all done in Bound forms and works great including having a copy of
their ID/Picture displayed with their record. Now as for payments those are
done on an unbound form as I use a series of queries to track the PaidThru
dates, assign fees on the 10th of the month, track lock cut fees after 45
days, Lien fees after 60 days. We also use this BalanceDue query to gererate
Invoices, and other letters sent out on set days of the month.

All this was started from scratch on Nov. 1, 2004 and is up and working
since Dec 4th, I'm working on improving some of the features now such as
auto reminders, reservations, waiting list, true auto pay (have it done in a
batch instead of 1 card at a time by hand now), batch payments (more than
one unit paid at one time in one check) . Gate control so spaces are locked
and unlocked based on the delinquency status automatically (with manual
override in place).

SNIPPED