Resetting Fields with macros

[Rich]

You're still trying to store a balance and a running balance, both can be calculated, your history table field Paid This Month should be AmntPaid, since your storing the date paid. You need to add a PK to tblApartmentInfo, that then gets stored in the tblLease:
Avoid special characters and spaces in field names too, Access doesn't like them

 

[dsomers]

Ok. How does this look:

tblTenants:
TenantID
First Name
Last Name
Previous Address
City
State
ZIP
DOB
SSN
DL #
Phone Number
Active Status

tblDeposits:
TenantID
Security Deposit
Pet Deposit

tblLease:
TenantID
ApartmentID
Move In Date
Lease Expiration Date

tblHistory:
TenantID
AmntPaid
Date Rent Paid

tblRents:
TenantID
Additional Rents
Additional Rent Description

tblBuildingInfo:
Building ID
Building #

tblApartmentInfo:
ApartmentID
Apartment #
Rental Rate
BuildingID

I'm assuming by putting a primary key called ApartmentID from tblApartmentInfo into tblLease will then provide the relationship from the tenant to the correct Apartment they are located in. Also, I understand able special characters and spaces in field names. I just type it out like that here so everyone could understand what I'm talking about.

Thanks!
David Somers

 

[dsomers]

One more question I have is do I need a primary key for my tblDeposit and tblHistory tables? My first impression is no.

Thanks!
David Somers

 

[dsomers]

Here is a copy of the new version with the correct tables and with the couple of changes Rich mentioned. The question I'm having is does my relationships look complete and correct?

Thanks!
David Somers

 

[Rich]

post a 97ver please

 

[dsomers]

Here's the 97 version.

Thanks!
David Somers

 

[dsomers]

One more question I have is with this table setup, how do I implement cascading combo boxes for building number and apartment number? That way I can select from a drop down menu which building number the tenant stays in and have the correct corresponding apartment numbers for that building number selectable from the apartment number drop down menu. I had this implemented when I had a table for building numbers and separate tables for each building's apartment numbers.

Thanks!
David Somers

 

[directormac]

Which Ones?

D -- Not sure which of these many tables I'm supposed to be commenting on. Suggest you start a fresh project and import only the "current" version of your structure.

Based on what I see, though, I would say you can consolidate substantially. You don't need a separate table for Rent, Additional Rent, Deposits, etc., etc. One table named tblTenantTransactions will do, with a related table called tblTenantTransactionTypes.

As for cascading combos, search the forum archives. This has been covered many times.

--Confused Mac