1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Advice on constructing related tables in MS Access

Discussion in 'Business Applications' started by bobthesponge2, Oct 14, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. bobthesponge2

    bobthesponge2 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    6
    I would appreciate any help anyone could give on setting up related tables in Access -- it is my first time doing so, and I am not sure if I quite have the hang of it.

    It is a database of software companies and their partner companies. The two tables (simplified for this forum) are:

    Table 1: (Software company info where each row would be info for a different company)
    Software_ID (Autonumber)
    Software_Company_Name
    Locations


    Table 2: (this contains information about partner companies)
    Partner_ID (Autonumber)
    Partner_Company_Name
    Partner_Company_City
    Software_Company_Name1
    Software_Company_Name2
    Software_Company_Name3


    My questions were:
    1) In table 1, if I have multiple fields for locations (like Austin and Boston), is it wise to have multiple columns like: Location1, Location2, etc? Or is it better to have only one column for Location and then repeat the Company Name for each location?

    2) As Table 2 shows, each partner has relationships with multiple software companies. I wasn't sure then how to relate the two table together

    3) How exactly would I relate the two tables? Would the data in the fields have to be the same for them to be related?

    Thanks so much in advance!
     
  2. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    A couple of questions come to mind:

    1. is there a difference between Location and City? Can you settle on Location?
    2. in your example, can a software company be a partner company?

    If you can settle with Location and a company can also be a partner, then this is how I'd set it up:

    Table 1: Companies
    Company_ID
    Location_ID
    CompanyName
    etc

    Table 2: Partnerships (This is just a cross reference table)
    Partner_ID
    Company_ID1
    Company_ID2

    Table 3: Location
    Location_ID
    Location

    The relationship between Companies and Partnership is one to many (one company can have 0-many partnerships) and Companies to Location is one to one as the Company can only have one location. You could, however, have Loc_ID1, Loc_ID2, etc in your Companies table, and therefore the relationship would be one to many.

    The tricky part will be the query that collects the partnerships - you could input each company in the partnership table as Co_ID1 but that's redundant if it is already referenced as a partnership under Co_ID2. But if you use a UNION query to pull all unique Co_ID1 and Co_ID2 into one list, this would overcome that.

    The whole idea with relational databases is to keep redundant information to a minimum. Any time a piece of data might be input more than once, it's worth considering adding it as a separate table (like the Location table).

    Hope this helps,

    MBN
     
  3. bobthesponge2

    bobthesponge2 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    6
    Thanks so much for your help. A couple of follow up questions...

    -- There will be more than one location for each company, as I am tracking software companies that have branches in more than one city, and I want to log that -- I think you're saying that I can either have locations in a different table or have multiple location columns in the companies table?

    -- A software company cannot be a partner company

    Would you set up the tables therefore differently?

    --How do I populate the tables with data? For example, under the "partnerships" table, you said to put the company_ID 1 and company_ID2 in there. How would I enter data for that? Is there a way to do it so that if I put the company name there, it will fill in?

    -- How exactly would I actually setup the relationship? That is, if I'm using Access 2007, I can make the tables appear in the relationships window -- what would I click on to relate the tables?

    Sorry for the super basic questions, but I very much appreciate the help!
     
  4. ~Candy~

    ~Candy~ Retired Administrator

    Joined:
    Jan 27, 2001
    Messages:
    103,706
    Hi and welcome. Any reason for registering under two user names?
     
  5. bobthesponge2

    bobthesponge2 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    6
    Hi -- I was not able to post under my first user name -- the confirmation email took me to a broken link. I think it was because the username and password were the same for the first username.
     
  6. ~Candy~

    ~Candy~ Retired Administrator

    Joined:
    Jan 27, 2001
    Messages:
    103,706
    (y) Alrighty then, I'll just delete that account....thanks :)
     
  7. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Okay, I see that I got the Company->Partner relationship backward, and if I'm understanding your reply, the analogy would be Software Company to Distributor: a distributor is not and cannot be a company.

    So net result is that you have a list of companies and a list of partners. Companies can have any number of partners, and Partners can be affiliated with any number of companies...

    Tables 1 and 2 are Company and Partner where all the info for them is stored. A third table to cross reference the relationships....instead of Co_ID1 and Co_ID2, it would be Co_ID and Partner_ID.

    A separate location table would be wise if a particular location of a company had a partnership that maybe the other locations did not. Another reason for having it as a separate table is that you don't know, from company to company, how many locations each will have. A separate table allows you to by more dynamic.... could have 0, could have 99 and you don't have to try to fit them into the table.

    So presuming you have your tables set up, the relationships can remain implied. In other words, in your cross reference table you are storing ID numbers that tie back to a particular company or partner, those are "foreign keys"... just leave the relationship implied until you begin creating queries and can create the relationship inside the query.

    The way you fill in this information (the whole reason to have a prog like Access) is that the next step is to create forms for input: a company form and a partner form to begin inputting data. Once you have a few of those input and you like the feel of those forms, you could take the next step of creating, for instance, a sub-form in your Partner form that allows you to input company partnerships.... or it might be a command button that takes you to the subform where you can input software companies.

    Queries, on the other hand, are intended to collect a subset of the data or all the data in a particular order. You would probably want to create a query to display your companies in alphabetical order (same with the partner table info). The query acts like a middleman between the table and the form for the purpose of filtering and ordering.

    This is just a very high overview...hope it helps,

    MBN
     
  8. bobthesponge2

    bobthesponge2 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    6
    That is super helpful. I think I get the gist of it -- you're saying that for the Cross reference table with the Co-ID and the Partner_ID, I can just create the table with those two columns but not fill anything in. Then when I set up a query, that table just acts to connect the other tables which have the information in them...?
     
  9. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Sort of. The query would collect the data and find the company/partnership relationship, yes; but at some stage you need to input this information.

    This would be done through forms. And as mentioned, start with the forms for the basic company and partner tables, then work out.

    Good luck.

    MBN
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If you need further assistance just let me know.
     
  11. bobthesponge2

    bobthesponge2 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    6
    Thanks MBN -- maybe I can direct my follow up question to OBP so that I don't monopolize your time!

    I wasn't quite sure how to set up the location table -- if I have multiple locations for the companies, would the locations be the column headers? Like:

    Location
    Company_Name

    and then just repeat it like:

    Boston/RandomCompany
    Austin/RandomCompany

    And with regards to the cross reference table, if the two columns are Company_ID and Partner ID, then I just need to make sure to input the information through forms so that it looks probably something like this eventually:
    RandomCompany1 - RandomPartner1
    RandomCompany1 - RandomPartner2
    RandomCompany1- -RandomPartner3
    RandomCompany2 - RandomPartner3

    and so on?

    Thanks!
     
  12. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    I'm attaching (or trying to) a jpeg of what I perceive the table structure to look like. The Location in the Company table is a lookup to the Location table and stores the LocationID (even though is says only Location).

    MBN
     

    Attached Files:

  13. bobthesponge2

    bobthesponge2 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    6
    Thanks! A picture is worth a thousand words. I really appreciate your help.
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/759234

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice