Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2008
    Posts
    11

    Unanswered: Relationship confusion (beginner)

    I have a question, the answer is probably obvious to the experienced ones.
    But let's say I have a couple of tables:

    tblCompany - Contains company name, telephone numbers, address and so on.

    tblDepartment - A department of a company. One company can have many departments. Contains information, contact person and so on.

    tblOrders - A sheet of orders, containing from what company and what department of that company that order was placed.

    How do I set up tblOrders (or how do I build the relationships) so that when I've selected a Company, I can only select Departments from that company?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The easiest way to define a relationship in Access s to do it graphically, using tools | relationships
    add the tables you need, then drag and drop from the parent table to the child table. so to define a relationship
    first off you must have defined the columns in both tables, of the same datatype (an Access autonumber column is of type Long Integer, so you should define any child column referring to a autonumber in the parent table as long integer). In this case I'd suggest you define an autonumber column in tblCompany, call it say "CompanyID", "CoID" or just "ID", make it an autonumber column, and set it as the primary key, do something similar for all other tables unless there is a better primary key, you may consoider that an ordernumber is a good candidate for a primary key so you don't need to define and additional column to make it unique, but tblDepartment will benefit from a DepartmentID, DeptID or ID. bear in mind these ID fields have no meaning outside in the real world they are just a means for the database to identify and keep records unique. One thing to bear in mind is that if you use an autonumber as a the orderID you have a potential problem if your auditors demand that the order numbers must be contiguous

    then you need to open the relationships windows
    select the column you want to link from, and drag it to the column in the child table .

    There are some pretty good references to relational theory out there, try Rudy's (R937) site. Another mod: Pootleflump cites another web source, forget where but thats well worth a read as well.

    HTH

  3. #3
    Join Date
    Jan 2008
    Posts
    11
    Thanks alot. I'm familiar with how to make basic relationships with autonumber keys but what would be the best way to make it so that when I'm creating a new order and first select what company that ordered the item, then secondly I select what department of that very company that ordered it. So in other words, if I have a table full of departments, I would not be able to select one that is from another company.

    Does that make sense? How would I do that?

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    search this forum for filtering a combo box based on the value of another combo box

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's called a cascading combobox or linked-list (there's a few more Ican't think of too!)

    I'm fairly sure ther's something about this in the codebank on the first couple of pages; it'd be worth a look!
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2008
    Posts
    11
    I'm still confused.. Maybe it's the way I think. I feel like there is an easier way that I'm overlooking.

    How would YOU create your tables/relationships if you had the following:
    * The general purpose is to manage orders
    * You have to store information about the order (like order number, date)
    * You have to store information about from what company the order is from (like address)
    * You have to store information about from which department of that company the order comes from, each department has their own information that needs to be stored (like contact person).

    Keep in mind that you don't want to memorize which department is from which company nor do you want to be able to make the mistake of entering an order from a department that is not a part of that company.

    It seems like it should be an easy thing to do, but I'm stumped. I've thought about creating a new table for every company with a sub table for every department but that doesn't seem very practical.

    All help is greatly appreciated.

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

    Talking

    Hey BigBrown. Did you check out any of the samples in the CodeBank yet?
    Why don't you paste a copy of your Table Relationships so that it can be seen and a possible solution be given to you. In your database go to the Menu and click on Tools, and then Relationships.

    have a nice one,
    BUD

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I've thought about creating a new table for every company with a sub table for every department but that doesn't seem very practical.
    Definitely not practical.

    You could also look at the Northwind sample database or any other database that has orders to see what you need to do.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jan 2008
    Posts
    11
    Quote Originally Posted by Bud
    Hey BigBrown. Did you check out any of the samples in the CodeBank yet?
    Why don't you paste a copy of your Table Relationships so that it can be seen and a possible solution be given to you. In your database go to the Menu and click on Tools, and then Relationships.

    have a nice one,
    BUD
    I tried looking in the codebank but I couldn't find what I was looking for. It's probably because I'm not quite sure what to look for.
    I'll share a copy of my table relationship on a later time, I'm in a bit of a hurry.

    Thanks alot for the answers

  10. #10
    Join Date
    Jan 2008
    Posts
    11
    Quote Originally Posted by StarTrekker
    Definitely not practical.

    You could also look at the Northwind sample database or any other database that has orders to see what you need to do.
    Yes Northwind has a very good example database. But I don't think they apply this principle anywhere. But it's likely that I'm just constructing the darned thing wrong.

    I've attached an image which displays my table relationships (relation.JPG) and an expanded image with table contents (problem.GIF) for you who wanted to see the problem visually. I hope it helps explain the problem.
    Attached Thumbnails Attached Thumbnails relation.JPG   problem.GIF  

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I see no problem here. Apart from the fact that you're doing data entry in tables. You need to just restrict the department combo box to only the company being ordered from. You can't do that in tables.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Jan 2008
    Posts
    11
    Quote Originally Posted by StarTrekker
    I see no problem here. Apart from the fact that you're doing data entry in tables. You need to just restrict the department combo box to only the company being ordered from. You can't do that in tables.
    Okay, thanks for clearing that up. I was kinda thinking it could be done by some smart use of table relationship but I couldn't figure it out.
    How would you approach doing it in forms then (assuming forms is what you are implying)?

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, forms is what I was implying

    On a form, your combo box can have its RowSource property redefined when moving from record to record (Form's OnCurrent event) and when changing the company (Company's AfterUpdate event).
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually I've gotta disagree. I think your problem is that you have company in the orders table. What you have got going on in the orders table is a transitive dependency between non-key attributes AKA a 3rd normal form violation (Company is functionally dependent on Department). You should remove company from orders and therefore remove the relationship.

    Here is the link referred to earlier (it is actually one that Rudy posted a couple of times and I have latched on to):
    http://www.tonymarston.net/php-mysql...se-design.html

    Note that this does not preclude the user selecting company in the UI and using that to filter a list of departments for them to select from when entering a record. It just means that the company is not recorded in the order table. Enforcing RI via a GUI is the path to the dark side

Posting Permissions

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