Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Northern, California, USA
    Thanked 0 Times in 0 Posts

    Update Records (97 SR2)

    I'm sure it's quite simple, but here's the deal:

    Table 1: tblIndividuals contains an ID which references
    Table 2: tblBldg.
    Table 3: tblIns, also references tblBldg.

    TblIndividuals is a list of individuals. TblBldg contains the building within which the individual resides, while TblIns specifies the insurance carried by the building.

    The pertinent fields are as follows:
    TblIndividuals: BuildingID, Insurance ID
    TblBldg: InsuranceID
    TblIns: BuildingID

    The data is driven by tblIndividuals, which houses the individuals. They each have buildings, which each have Insurance.

    What i'd like to have happen is, When the insurance is selected for the building, the Insurance ID is passed to the building table AND the individual's table. At times, correspondence sent to the individual may reference either of these, but never both.

    Any Ideas? I've got the whole thing setup in forms/subforms, and i've played with relationships, it doesn't seem to be having the desired effect.

    <IMG SRC=>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 228 Times in 210 Posts

    Re: Update Records (97 SR2)

    I'm not sure I follow - if the insurance applies to the building, why do you want to store the insurance ID in the individuals table too? Surely it can be picked up anyway from the link between individuals and buildings? Or have I missed something?

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Update Records (97 SR2)

    Is the insurance carried *by* the building or *on* the building? Big difference. Also, you need to provide for more than one insurance policy on a building, so you shouldn't put the insuranceID in the building table. Instead, you need a join table that contains one record for each InsuranceID that applies to each building.

    I'm not sure what you mean by putting BuildingID in tblIns and InsuranceID in tblBldg. You wouldn't do both. One or the other or, better yet, a join table such as I suggested. tblIns would then hold policy details, including an ID for the individual who owns the policy. TblBldg would also have an ID for the individual who owns the building. The join table would allow you to handle policies that cover multiple buildings and multiple policies on a single building. If you have buildings owned my more than one individual, you need another join table between individual and building.

    Get your relationships straight before you build your forms and subforms or you'll spend far too much time trying to make something work that shouldn't.

Posting Permissions

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