Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2015
    Posts
    17

    Unanswered: Changing the foreign key of a record

    Hi

    I'm building a property database, which currently has three forms.

    One has the details of the property eg address etc.

    The first subform has records of inspections of the property.

    The second subform has records of tenants for the property.

    The first two forms are linked by means of a foreign key - primary key relationship.

    The second tenant subform is also linked to the main form via a foreign key - primary key relationship.

    However, there's a problem. Over time the tenants are going to change and so the foreign key in the tenant table is going to change as well. It's a bit clumsy having to alter the foreign key every time a tenant changes property. Is there a cleaner way of doing this, or alternatively some way of changing things over without having to alter the table and/or the form?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Taking a step back from forms.... get the table design right (or as best an approximation you can) BEFORE designing forms or reports IN Access (or any other environment for that matter.

    What id expect to see us a table for:-
    Properties
    Property owners
    Tennants
    An intersection table that identies what properties are rented to what tennants
    Inspections
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2015
    Posts
    17
    Quote Originally Posted by healdem View Post
    Taking a step back from forms.... get the table design right (or as best an approximation you can) BEFORE designing forms or reports IN Access (or any other environment for that matter.

    What id expect to see us a table for:-
    Properties
    Property owners
    Tennants
    An intersection table that identies what properties are rented to what tennants
    Inspections
    In answer to your suggestion:

    There are no property owners. All the properties are rented, and they are all rented by the same organisation which sublets them to tenants.
    I have a table for properties, tenants and inspections.

    How would the intersection table work? Would this be necessary to be able to alter the tenant attached to the property over time?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so on that basis
    properties
    customers
    ..then an intersection table that identifies what properties are rented by what customers
    inspections
    ..how you model inspections is up to you. do they hang off a property (presumablky with a composite PK of the properties PK and ther date of inspection or do they hang off ther intersection table. my hut feel is that they should have a FK that points to the property table directly as I guess its perfectly possible to have an inspection on a property that is not currently rented out.


    the advantage of the intersection table is that it conatins the common bits (the inmtersection) of the the property and customer and anything else pertinent to that rental (eg time period, rate, conditions etc). if you had comments or notes on the tenancy then they may be a sub table hanging off the intersection table. the intersection table allow you to have the history of that property and or that tenant. so when a new tenancy starts (either new people or an extension to an existing tenancy( then it is a new entry in the intersection table. effectively that is how a rental works, when a tenancy contract is extended its a new contract, likewise a new tenancy is a new contract. you have a history of when that property was rented out. you retain visibility of what happened to that property with those tenants. so there is no risk of say an adverse inspection report being associated with a previous or current tenant, but remains with the tenant who was actually there during the inspection
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •