Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006
    Posts
    20

    Red face Unanswered: Need help with Table Relationships

    Hello everyone,

    I've been working on this for about 2 weeks now and I'm trying establish a relationship with the following tables below. I was wondering if anyone out there could offer their assistance. The whole database is based on real estate and contracts. Here are the tables below:

    //Customer table:
    cust_ID
    address
    profession
    dob
    agent_ID

    //Agent table:
    agent_ID
    name
    title
    phone
    hireDate

    Builder table:
    //builder_ID
    name
    phone
    POC

    //Contract table:
    contract_ID
    contractDate
    amount
    agent_ID
    builder_ID

    //Home table
    home_ID
    address
    lotSize
    purchasePrice
    model_ID

    //Standard Model table:
    model_ID
    bedRooms
    garage
    baths
    cost
    builder_ID

    //Feature table:
    feature_ID
    description
    suggestedCost
    builder_ID

    //Standard_Model_Feature table:
    feature_ID
    actualCost
    model_ID

    //Standard_Feature table:
    feature_ID

    //Non_standard Feature table:
    feature_ID

    //Home_nonstandard_feature table:
    home_ID
    feature_ID
    actualCost

    //Sale table:
    sale_ID
    actualAmount
    saleDate
    home_ID
    builder_ID
    agent_ID
    contact_ID

    I've created all the tables and tried to establish their relationships but I keep getting this referential integrity issues. I am respectfully asking for assistance in establishing the relationships on the above tables. I would greatly appreciate the help. Thank you so much!!!!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    A referential integrity issue involves establishing a relationship where a value (for example an ID value) in one table must correspond to a value (ID value) in another table. Breaking the rules of referential integrity can cause "orphaned records/values". Consider these 2 tables you have...
    //Customer table:
    cust_ID
    address
    profession
    dob
    agent_ID

    //Agent table:
    agent_ID
    name
    title
    phone
    hireDate

    and these values in the tables...
    CustID,address,profession,dob,agent_ID
    1,1 West Wash Ave,Programmer,12/27/1964,22
    2,22 Park St,Sales Agent,03/21/1955,29
    3,21 W. Park St,Some Profession,02/01/1945,24
    and
    agent_ID,name,title,phone,hireDate
    22,Joe's Agency,CEO,222-3343,12/01/2007
    24,Bill's Company,Sales Manager,221-3433,01/01/2004

    In the above scenario, you could NOT establish referential integrity between the Customer and Agent table linking the agent_ID field because this record...
    2,22 Park St,Sales Agent,3/21/1955,29 in the Customer table
    breaks the rules of integrity (ie. there is no agent_ID = 29 in the Agent table.) Thus, the 29 becomes an unmatched value and no referential integrity can be established between the agent_ID fields between these 2 tables (a corresponding agent_ID number must exist in the Agent table to be used for agent_ID in the Customer table.) In order to establish referential integrity between these 2 tables and the agent_ID field, 29 would need to be removed from the agent_ID field in the Customer table for that record. You should always try to establish referential integrity in this kind of scenario to ensure you would not get "ID" values in 1 table which do not correspond to any "ID" values in another table (which is often the culprit of inaccurate reports!)

    As a note, when I establish relationships between 2 ID fields, I usually select the "Cascade Delete" (depending if I wanted related records in the other tables deleted or not) and "Cascade Update" options. Cascade Update means that if (in the above example in the Agent table), agent_ID 22 where changed to 32, all subsequent 22 agent_ID values in the Customer table would change to 32. Cascade Delete means that if I tried to delete agent_ID 22 in the Agent table, depending on how I set up the relationship, all "Customer" records with agent_ID 22 would be deleted (and I would get an Access message that deleting agent_ID 22 would cause cascade deleting of related records in the Customer table), or I would get an error stating that I was violating the rules of integrity because agent_ID 22 existed in the Customer table.

    Use this same concept as a guide for your other tables and the ID fields when creating relationships.

    As a last note, establishing referential integrity should be done BEFORE you start entering data. If you already have data in the tables, when you try to establish referential integrity (i.e. relationships), Access (or SQL Server) is going to check to make sure you don't break the rules of referential integrity with the existing data. If it finds an unmatched value between the 2 tables on the field you're trying to establish a relationship on, you'll get an error that it would violate the rules of referential integrity and you won't be able to establish the relationship until you FIRST, correct the data. To help you in finding unmatched values on existing data, Access has very nicely created a query in the wizard called: "Find unmatched values" which walks you through steps on being able to find unmatched values between fields on 2 tables.

    The Northwind database which ships with MSAccess has some relational table structures you may want to take a look at. I haven't covered the one-to-many, many-to-many, and many-to-one relationship types here but if you look at the Northwind database, I believe you can see different examples of these.

    Hope that helps.
    Last edited by pkstormy; 10-04-07 at 13:52.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2006
    Posts
    20
    Thank you so much for explaining all that, paul. If I understand it correctly, I will have to create a separate table (i.e. AgentCustomers table) to reflect the number of customers an agent has, right?

    But how would that work then for tables: Home, StandardModel, Features, etc.? Still not sure how establish relationships for them...I'll check the Northwind db to see if I could try to understand it better...I have a feeling I'm going to get stuck again...thank you again, paul!

Posting Permissions

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