Need help!

[Darkladymelz]

I am currently creating a database that tracks tenants and what property they live in. Each tenant has a unique referance as does each property.

I would like to know how to track if a tenant leaves a property and transfers to another one how i would track this without losing the information from the previous property the tenant lived in. This way I could have a running list of what property the tenant has lived in and until what dates.

Any help would be much appreciated.

[alansidman]

Do you have the tenants set up in one table and the property in a separate table. If not, then this would be the first step in setting up your db. What do your tables look like? ie. what fields are in your tables, primary keys, foreign keys, etc.

[Darkladymelz]

I have a table for tenants and a table for properties. In the tenants table the fields are: Tenant Ref (primary key), tenant name, status, booking date, booking ref, scheme, company, transfer date, transfer ref, cancellantion date, cancellantion ref. In the property table is: Property ref (primary key), property address, county, postcode, property type, bedrooms, floor, start date, end date, rent, supplier ref, Landlord ref, tenant ref.

Does this help?

Also i have not come across foreign keys, are these fields that are not primary keys, or do they need to be created like a primary key does?

[Darkladymelz]

I am still having a problem with this!

Can anyone help?

[alansidman]

A foreign key is the field in which you link the primary key to in a second table. For example, in you property table you need to have a tenant key which would coorespond to the tenant in the property so that the tables could be linked and queried.

First off, you have a need for a many to many relationship of your tables. You have many tenants and many properties for rent. So you need to have a third junction table (Agreements). Look at this white paper on databases.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

Secondly, I believe that you should set up your tables in a manner similar to the following. Add/Change fields as needed. But read the white paper first before attempting.

Tenant
TenantID (PK)
TenantName
TenantPhone
OtherTenantFields

Propery
PropertyID (PK)
PAddress
PCounty
PPostCode
PType
Bedrooms
Floor
SupplierID (FK)
LandlordID (FK)

AgreementTable
AgreementID (PK)
ADate
ATenentID (FK)
APropertyID (FK)
ARent
AStartDate
AEndDate
OtherAgreementFields

Supplier
SupplierID (PK)
OtherSupplierFields

Landlord
LandlordID (PK)
OtherLandlordFields

This is how I would try to set it up. You will then join the first Three tables on the Primary/Foreign Keys. The Supplier and Landlord tables are used as look up tables.

Alan

[Darkladymelz]

Ah, that makes a lot of sense.

I will definately give that a read. And will then try and see if your version would help my problem.

[Darkladymelz]

Still need help with this!

I was givin a code that should work as an Audit Log, but it is not working. Would anyone be willing to look at my database for me and see where I have gone wrong?

Thank you

[alansidman]

Post your db. Make sure to run a compact and repair prior to uploading and make sure you dummy up all confidential information. If this is a new issue, suggest you open a new thread as it will get more attention from forum members.

Alan