Number of tables and setup help for a Tenants association databas

[Guest]

I need to setup a database that will contain 400 apartments in a building
complex. I need to track the turnover (vacancy?) rate over the years, mont by
month but primarily contributions from residents to a Tenants fund and
attendance to meetings by tenants as well. A tenant might change apartments
in the complex, but I need to keep track of that tenant's contributions, not
the apartment's, obviously. Therefore I envision at least a permanent, almost
unchanging table for the physical apartments, their characterisitics, another
one for tenants' voluntary financial contributions, another one for meeting
attendance. The question is how will they relate to one another and which
will be the primary key for each table.

 

[John Vinson]

I need to setup a database that will contain 400 apartments in a building
complex. I need to track the turnover (vacancy?) rate over the years, mont by
month but primarily contributions from residents to a Tenants fund and
attendance to meetings by tenants as well. A tenant might change apartments
in the complex, but I need to keep track of that tenant's contributions, not
the apartment's, obviously. Therefore I envision at least a permanent, almost
unchanging table for the physical apartments, their characterisitics, another
one for tenants' voluntary financial contributions, another one for meeting
attendance. The question is how will they relate to one another and which
will be the primary key for each table.

Well, you've almost answered your own question! <g>

Each "Entity" - real-life thing, person, or event - should have its
own table. The Entities that I would see are:

Buildings
BuildingNo (or building name, whatever you now use)
Address
Description

Apartments
BuildingNo << link to Buildings
ApartmentNo << joint 2-field Primary Key
<descriptive fields, e.g. # bedrooms, # baths, etc.>

Tenants
TenantID Autonumber
LastName
FirstName
<other bio information - bear in mind names may not be unique!>

Rentals
BuildingNo
ApartmentNo
TenantID
LeaseStartDate
LeaseEndDate ' NULL if an active lease, perhaps
Rent

BuildingNo, ApartmentNo, and LeaseStartDate could be a joint 3-field
PK

Meetings
MeetingID Autonumber <Primary Key>
MeetingDateTime Date/Time
Location <may want a table of locations if there are multiple>

Attendance
MeetingID Long Integer << link to Meetings
TenantID Long Integer << link to Tenants; 2 field joint PK
Comments

Contributions
ContributionID Autonumber Primary Key
TenantID Long Integer
PaymentDate Date/Time
Amount Currency
Comments


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps