Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2007
    Posts
    30

    Unanswered: Relationship Question

    Afternoon all,

    My brain is fried and I'm trying to establish a working relationship for the following information.

    The information will include:

    Name of a Company
    Address (Company can have multiple addresses)
    Contact information at the Company (Could have multiple contacts are the same address)

    I've broken it down into three different tables.
    Name Of Company Table
    Address Of Company Table
    Contacts For Company Table)

    The Name of Company Table and Address of Company Table relationship works fine, it's when I tried to connect the Contacts to the Address table, and THEN back to the Company table that I'm running into problems.

    Any advice would be greatly appreciated!
    If I didn't make it full clear I can send you some sample data.

    Thanks so much all.

  2. #2
    Join Date
    Nov 2007
    Posts
    30
    Here's an example of the information I might be provided through excel.
    Please see attached.
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2007
    Posts
    30
    Hi again all,

    Still trying to figure out a working relationship for the above problem.

    My first relationship uses the Company name table as the parent and then the address table as the child and it utilizes the company name as the relationship.

    When I go from the Address to the Contact table though I can't use say the Address as the relationship indicator because some company's have the same address or even NO address, that's really where I'm running into my problem.

    Can I utilize a secondary item for my relationship such as Address AND Company Name? Is that a possibility?

    Thank you for all the help!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    A company is one entity
    Each company may have 0 or more addresses
    Each company may have 0 or more contacts
    but the "fun" starts as to where you want to store/assocaite those contacts.

    does your applciation need to differntiate between a contacts at different sites.. of so then the contact is a sub table hanging off address

    genrally speaking its better to use a number for a primary key.. and nealry always to use an abstract primary key (usually system generated autonumber) if there is a risk that the existing primary key may change.

    so Id suggest that unless you know better the primary key for the company should be an auto generated number.
    that number should be used in the address table to identify waht addresses 'belong' to what companies.

    in the address table you have a choice .. you can assign an address id.. another system generated number or you could invent some other form of making the address unique.. you could say have a site no (say 1....n) which you maintain so that the primary key in Address becomes company id + site no. however there is no doubt its a heck of a lot easier to use another system generated number there but have the company number as a foreign key referring to company.

    So on to the next decision
    can more than one company share the same address, or even if they do do you care? if they don't or you don't care then contact becomes entirely a child table of address.. so you need to use whatever you used as your primary key in address as a foreign key referring to the address for that company. Again Id recommend that you use an autogenerated number for both the address AND the contact tables.

    but if you only have one contact per address then you don't need a subtable.
    thats down to your busienss / homework requirement

    if you allow multiple companies to share the same address then you need another table to cross refer a company with an address..

    but to keep it simple
    Id suggest you have 3 tables company, addresses, contacts

  5. #5
    Join Date
    Nov 2007
    Posts
    30
    Thank you for the response!
    I am actually utilizing the AutoNumber field for my relationship. What I've done the Address table is use the Lookup Wizard to point to the AutoNumber field of the Company Table but reference the Company Name.
    Now that I think about this though, the way I want to have this database work is in a form style so that they end user can just search (by company name) for the company they want. Do I need to use the Lookup feature at that point or is the Lookup feature only used to ease my work while I'm in the Datasheet view?

    Regardless, I utilized also the AutoNumber field for the address table and I had a separate table for Contacts already but where I'm running into problems is when a company has a location in say New York City but we do not know the exact STREET Address.

    I think that's where I'm getting confused on how to make Access aware of this issue.


    Thank you again for the quick response!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in my view its best not to use the lookup wizard when designing tables.
    they do work but can cause big problems later

    id suggest that you design each table as a separate entity

    then establish relationships in the relationships window.

    if you need to do searches then you have several choices
    either store each line of the address as a separate line on the db, and one of those lines / fields / columns as city
    or you search the address using a wild card eg where address like *new york*"
    mind you, you easily enough can search data in access forms using the in built search button.. looks like a pair of binoculars

  7. #7
    Join Date
    Nov 2007
    Posts
    30
    Thank you again!
    Maybe this is a silly questions but:
    If I'm creating a relationship between autonumber and another field (such as Company name) the two fields must have the same datatype (so the Company name field has to be a Number's type) How then (without using lookup feature) do you input (or import in my case) information into that field that is text based when the field only supports numbers.

    Silly question I'm sure but the help is greatly appreciated it!!!

  8. #8
    Join Date
    Nov 2007
    Posts
    11
    Hi,

    Your situation touches on an aspect of a project that I've been involved with. Here's a brief/partial description of the organization structure:

    An organization can have one or many branches
    An organization branch can have one address
    An organization can have none or many affiliations
    An individual can be affiliated with one or more organizations

    The organization structure was necessary as we could have any type of organization listed from single station to multi-branch corporations and we needed to set up a hierarchy as well as organization type.

    To make it easier to use, every organization had a branch, well, sort of. For a small organization, where they didn't really have a branch, they had what was considered a Main office.

    Org: Name of org, org type, etc.
    OrgBranch: Name of Branch (Main, North, West, Other branch names), Address, telephone, etc.
    Individual: Very basic info: First, Last, Initial, Personal Professional Designation
    Affiliation: Links tblIndividual with tblOrgBranch - this is where the individual's contact information resides as well as position information

    This way, we could affiliate an individual with multiple branches and/or organizations (which is the reality for our project)

    Our model is broken up further than the example above but this may give you an added perspective

    Cheers

  9. #9
    Join Date
    Nov 2007
    Posts
    30
    That sounds great and very similar to my case... I might change it just a bit though.

    You said:

    An organization can have one or many branches
    An organization branch can have one address
    An organization can have none or many affiliations
    An individual can be affiliated with one or more organizations

    Mine would have to be:

    An organization has 0 ore more addresses
    An address as has 0 or more contacts
    A Contact can only have one address
    A Contact can only be affiliated with one organization.

    How about this scenario though, say an organization has one contact (or more) but the address is unknown.
    Because if the flow is Organization Talbe -> Address Table -> Contact Table, there would be a breakdown between the Address Table and the Contact Table.

    How then will that relationship fly?

  10. #10
    Join Date
    Nov 2007
    Posts
    11
    Because we (in our project) could have someone affiliated with a branch and could also work from a home office and be affiliated with other organizations, we had to break it down farther than the example I gave.

    The way that I set a table structure is based on the criteria. First I determine my 'anchor' table and that depends on the main attraction for the data collected.

    for your example...
    If organization is the Main emphasis, keep the address information in the same table. If there is an Hierarchical structure that you should capture, set up a Orgname table, then set up a Branch table and link to each other with OrgName being the parent and branch being the child. Put the address information in the Branch table. The main office is a branch called Main or Head Office or whatever works.

    An individual (person) table is set up and this is where a 'personal or home' address would go if this is part of the requirements. Affiliate an individual to the branch level by adding an intersect affiliation table.... link the branch table to the affiliation table and link the individual table to the affiliation table as a many to many relationship. The branch address becomes the affiated individuals 'work' address and is displayed via a query or form or whatever. If you are storing the individual's work telephone number(s), email, etc, record this in the affiliation table.

    Hope this makes sense
    Last edited by dbarends; 11-26-07 at 14:18.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dont use the lookup wizards in table design
    if you are using Access's autonumber option declare the child (foreign key) as a number: long integer
    then define your relationships
    when you come to designing the form use a list or combo box (in the first instance I'd probably use a list box) and use a wizard to do the detail. Access will then identify what table to refer to when it tries to fill the list box, and guide you on the way to gettign the right values.

    if you use the lookup wizard in table design you will have all manner of problems.. believe many people have goine down that route and come to grief.

  12. #12
    Join Date
    Nov 2007
    Posts
    11
    Here's the basic breakdown from mhy earlier post.

    Organization--->Branch--->Affiliation<-----contact

    Organization:
    Name, Type etc

    Branch:
    Branch name (Main for Head office)
    Branch address
    Branch contact information

    Contact:
    Contact name
    Personal information as appropriate\

    Affiliation:
    BranchID
    ContactID
    Contact phone1, phone2..email etc. related to affiliation

    Note that the affiliation table becomes an intersect table.

    This way, it's easy to implement the rule one org has many addresses as each one can be a branch including the main
    The contact can be affiliated directly with the branch so a location element is added. If the person is an employee, the branch address can be the contact's mailing address. If the contact is outside the org, then all pertinent contact info must be set up with the contact.

    This setup allows for many contacts affiliated with many organizations but you can keep it one contact to one org easily as well.

  13. #13
    Join Date
    Nov 2007
    Posts
    30
    Quote Originally Posted by dbarends
    Here's the basic breakdown from mhy earlier post.

    Organization--->Branch--->Affiliation<-----contact

    Organization:
    Name, Type etc

    Branch:
    Branch name (Main for Head office)
    Branch address
    Branch contact information

    Contact:
    Contact name
    Personal information as appropriate\

    Affiliation:
    BranchID
    ContactID
    Contact phone1, phone2..email etc. related to affiliation

    Note that the affiliation table becomes an intersect table.

    This way, it's easy to implement the rule one org has many addresses as each one can be a branch including the main
    The contact can be affiliated directly with the branch so a location element is added. If the person is an employee, the branch address can be the contact's mailing address. If the contact is outside the org, then all pertinent contact info must be set up with the contact.

    This setup allows for many contacts affiliated with many organizations but you can keep it one contact to one org easily as well.

    Thank Intersection table is working like a charm!
    I just realize how much work though it'll be to update all the records.

    I have an excel document with over 1400 records, and the integration of that information seems to much for one person to handle, there must be a similar way of incorporating it.

  14. #14
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by Rakh
    Thank Intersection table is working like a charm!
    I just realize how much work though it'll be to update all the records.

    I have an excel document with over 1400 records, and the integration of that information seems to much for one person to handle, there must be a similar way of incorporating it.
    Hi Rakh,

    One way to integrate your Excel data into Access is to Import the data. Go to File...Get External Data...and choose either Import or Link Tables. This is done in Access itself. Make sure the data in your columns in Excel match your datatypes in your tables in Access. When you Import it, it basically Copies your data into Access. When you Link you then keep a connection to Excel...when you change/edit data in Excel it is done in Access also. That's one option to look into if that sounds like what you want.

    have a nice one,
    BUD

Posting Permissions

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