The database you posted is not normalized either, so for it to work properly, it would have to be redesigned just like yours.

You admitted that your application is complicated with the interrelations of suppliers, tenants and properties etc. and if that is what you want to capture in your database, then it must be designed properly.

I just want to be able to log the information of when a tenant moves property.
If you want to simplify things and the above is ALL you want the database to do, then 3 tables would be necessary. The core of which would be as follows:

tblProperty
-pkPropertyID primary key, autonumber
-txtPropertyName

tblTenants
-pkTenantID primary key, autonumber
-txtFName
-txtLName

tblTenantProperty


-pkTenantPropID primary key, autonumber
-fkTenantID foreign key to tblTenant
-fkProperty foreign key to tblProperty
-dteEffective (date when tenant rented property)