Audit Log

[Darkladymelz]

I needed an Audit Log to track when tenants move property without losing the information of their previous property. I also want to be able to search for a tenant and all their previous tenancies will come up as well as their current tenancy.

I was told to try an Audit Log which i found online by 'Alan Browne' but it keeps coming up with errors. I have no understanding of Visual Basic at all so would be grateful if someone could fix this for me.

Attached is a dummy version of my entire database.

Thanking you in advance for your help.

[jzwp11]

An audit log documents changes to data made by users of the database. From what you descibe, it sound like you want to capture the history of tenants' rentals which is different.

A tenant can rent many properties/units over time, so that describes a one-to-many relationship. A property/unit can be rented by many tenants over time, so another one-to-many relationship. When you have two one-to-many relationships between the same two entities (tenants and properties in your case) you have a many-to-many relationship which is capture in a junction table.

First a table to hold the basic tenant info

tblTenants
-pkTenantID primary key, autonumber
-txtFName
-txtLName


A table to hold the properties/units available for rent

tblProperties
-pkPropID primary key, autonumber
-txtPropertyName


Now the junction table

tblTenantProperties
-pkTenantPropID primary key, autonumber
-fkPropID foreign key to tblProperties
-fkTenantID foreign key to tblTenant

Since for each combination of tenant & property you have multiple dates associated with that relationship (move in and move out dates) that describes a one-to-many relationship.

tblTenantPropertyEvents
-pkTenantPropEventID primary key,autonumber
-fkTenantPropID foreign key to tblTenantProperties
-dteEvent (date of the event)
-txtEventName (ie. move in or move out)

Some may argue that this last table takes normalization too far and that it would be easier to just have two date fields in tblTenantProperties, but what happens if you want to capture other events related to a tenant's rental such as when an eviction notice is served.

[Darkladymelz]

I am really confused.

Is it possible for you to do an example of what you mean for me to have a look at?

Thank you

[jzwp11]

I was going to take the database you posted and modify it accordingly to provide an example, but before I can do that I had some questions. In your tenant details table you have several fields related to transfer, cancellation and booked. Are these fields related to a property the tenant is renting or to something else?

Out of curiosity, why would a tenant have a supplier? Is the supplier a referring agency?

Also in the property detail table you have both a supplier and a landlord. Does the landlord work for the supplier? Is the supplier basically the owner of the property and the landlord just manages it?

[Darkladymelz]

The transfer, cancellation and booked fields refer to when the tenant was transferred, cancelled or booked in that particular property (the history table/log may mean we can delete the transfer field as only the cancellation and booked fields will be relevant) a tenant could move up to three or four times so it is vital it is recorded somewhere.

With regards to your other question, this is a bit more difficult to explain. The Landlord is the owner of the property. He then will give his property to a company (supplier) who will in turn give the Landlord a regular rental payment regardless whether there are tenants in the property or not. The company (supplier) then supplies the council with the property for tenants to move into. We need this recorded as the rental income is different dependent on what supplier the property is with. The supplier is selected under the tenant and not the property, as the property can move between suppliers and changes dependent on the tenant... I hope that makes some sense to you.

[jzwp11]

The transfer, cancellation and booked fields refer to when the tenant was transferred, cancelled or booked in that particular property (the history table/log may mean we can delete the transfer field as only the cancellation and booked fields will be relevant) a tenant could move up to three or four times so it is vital it is recorded somewhere.

From the above it sounds like transfer, cancellation and booked refer to events related to the combination of a tenant relative to a property. If my understanding is correct, that info does not belong in the tenant table.

With regards to your other question, this is a bit more difficult to explain. The Landlord is the owner of the property. He then will give his property to a company (supplier) who will in turn give the Landlord a regular rental payment regardless whether there are tenants in the property or not. The company (supplier) then supplies the council with the property for tenants to move into. We need this recorded as the rental income is different dependent on what supplier the property is with. The supplier is selected under the tenant and not the property, as the property can move between suppliers and changes dependent on the tenant

Based on the above, it does not make sense to have the supplier field in the property detail table.

Is a tenant always associated with a particular supplier or will that be variable as well?

What do borough and scheme represent?

[Darkladymelz]

]From the above it sounds like transfer, cancellation and booked refer to events related to the combination of a tenant relative to a property. If my understanding is correct, that info does not belong in the tenant table.

Please bare in mind this is the first complex database I have created so some of the information may need to be moved about.

Based on the above, it does not make sense to have the supplier field in the property detail table.

Is a tenant always associated with a particular supplier or will that be variable as well?

What do borough and scheme represent?

The supplier is necessary in both tenant and property table as the supplier could be different for each. The tenants supplier will be variable.
Borough is what town/council that has referred a tenant and scheme is like a category the tenant is put in that establishes what rent they pay.

I hope you can help me.

[jzwp11]

The supplier is necessary in both tenant and property table as the supplier could be different for each.

With regards to your other question, this is a bit more difficult to explain. The Landlord is the owner of the property. He then will give his property to a company (supplier) who will in turn give the Landlord a regular rental payment regardless whether there are tenants in the property or not. The company (supplier) then supplies the council with the property for tenants to move into. We need this recorded as the rental income is different dependent on what supplier the property is with. The supplier is selected under the tenant and not the property, as the property can move between suppliers and changes dependent on the tenant... I hope that makes some sense to you.

This is still confusing to me. This is telling me that if a property is managed by a supplier and a new tenant moves in then the property is then somehow managed by a new supplier even though the original supplier contracted with the landlord to manage the property. Is that correct?

Borough is what town/council that has referred a tenant and scheme is like a category the tenant is put in that establishes what rent they pay.

Do either the borough or scheme attributable to a tenant change over time or do they remain fixed?

[Darkladymelz]

This is still confusing to me. This is telling me that if a property is managed by a supplier and a new tenant moves in then the property is then somehow managed by a new supplier even though the original supplier contracted with the landlord to manage the property. Is that correct?

I'll try to explain again.... Let's say for example a tenant is with Supplier A and a property is with Supplier B, the tenant could be placed in that property but the suppliers will not change for either tenant or property. It is rare for this to happen, but has been done in the past when there is no other available property for the tenant. Please be aware though that the suppliers for tenant and properties may change from time to time. Usually if a tenant moves property we may change the supplier, or when a property is empty we may change the supplier.

It is a very confusing process, but the supplier is dependent on what requirements the tenant has, for instance if a tenant has mental health issues they would be with supplier A but if they are a family they would be with Supplier B. The properties work in the same way, a property that is with Supplier A would be suitable for tenants with Metal Health because it has been health and safety checked with them in mind, whereas a property that is with Supplier B would not need the same attention as it will only require furnishing suitable for a family. I hope that makes a bit more sense.

Do either the borough or scheme attributable to a tenant change over time or do they remain fixed?

The borough will usually remain the same but it may change. The scheme is likely to change.

[jzwp11]

I'm starting to get a handle on the roles of suppliers, so thank you for the clarification.

The borough will usually remain the same but it may change. The scheme is likely to change.

Since both can change that would describe a one tenant to many boroughs as well as one tenant to many schemes.

BTW what exactly is a scheme? From what you had in your original table, they look like they are organization names. Could you explain a little further?

[Darkladymelz]

Borough = councils
Scheme = groups (not organizations), they are like categories. They are groups that tenants get put in dependent on their requirements.
Suppliers = companies - like schemes are dependent on the tenants requirements.

Two people could be in the same scheme but with different suppliers, or could be with different schemes but the same supplier. It is a very confusing process. I still get confused sometimes. HAHA!

[jzwp11]

Attached is the restructured database based on our discussions to date. The only things I have not changed yet are the rent, property details and floor tables.

I opted to put all organizations, suppliers, companies, landlords and tenants in 1 table called tblEntity and built off of that. There is a type field that you can use to designate to which type each record would correspond. Please take a look at the relationship diagram to see how I translated our discussion into the tables & relationships.

Now as to the properties, can a property have many units that can be rented out? Can you provide some examples of a typical property and units within that property and what type of data you capture about each?

In terms of rent, what kind of info do you need to capture?

[Darkladymelz]

To be honest I don't really understand what you were trying to achieve here.

You have redone all the tables and I'm not quite sure where I'm suppose to enter the relevant information that I want to.

You have not actually helped me answer the problem that I am having. I just want to be able to log the information of when a tenant moves property. Nothing else. Every other table was working perfectly fine. I just need another table to log the history of my tenants. I just want to be able to search a persons name and a list of all the properties they have been in appears!

I really appreciate you trying to help but I don't need the tables redone, just a need to know how to achieve the above.

Thanks

[jzwp11]

You have redone all the tables and I'm not quite sure where I'm suppose to enter the relevant information that I want to.

You will have to create forms for entering data.

You have not actually helped me answer the problem that I am having. I just want to be able to log the information of when a tenant moves property. Nothing else. Every other table was working perfectly fine. I just need another table to log the history of my tenants. I just want to be able to search a persons name and a list of all the properties they have been in appears!

In order to do what you describe, you have to have the correct table structure. Without the correct table structure, you will not be able to do what you want.

[Darkladymelz]

This is a database that I found in another forum that was made for someone that update statistics for their machines. It is simple and does exactly what is needed.

It updates the current record, but keeps a table of the history of the old statistics. Is their anyway this can be implemented into my already existing database.

I understand that you are trying to help me achieve the best I can from my database but I have spent months getting it to where it is currently (I am only a beginner at this) and feel that redoing the tables, forms, queries etc will take me to long. I only have until tomorrow to complete this.

If there is any possible way to achieve this in the same way as the attached database it would be much appreciated.