Results 1 to 10 of 10
  1. #1
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

    Parts Database connecting tables together with Relationships


    I have many tables of different electronic parts types, all have xx_ID with autonumber and different field names. I would like to tie them all together with one table and create a relationship between tables. Each table is it own partType, with dropdown list table to add partsub type, connected one to many. I have forms for each part table. I can't quite get what I need, one new table Parts or two tables connecting them all. Please point me in the right direction, Thanks.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Same project database, work in progress. I've been working on tables, forms and queries, but can't seem to join all tables together. I could work with what I have, maybe I don't need them joined? Thanks!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Relationships are based on business rules, so a description of the business is really required to offer focused advice/suggestions.

    In an earlier post I mentioned/linked to info by Dr. Soper and Dr. Widom, so you probably have seen the concepts involved.

  5. #5
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

    Relationship to Parts table?

    I'm still working on this project and can't seem to get the relationship with my tables. I need to link tblResistors, tblCapacitors and tblDiode tables with tblParts table. I need one record in tblParts to link to one and only one of the 3 tables.

    I have been to Many videos and web pages and need some help please!

    Thanks for any help to put me it the right direction!
    Attached Files Attached Files

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It might be more efficient if:
    - you post a copy of your current database,
    - include an overview description in plain English of a business process or 2 that involves the subject matter and can relate the things (tables)
    - a sample of input and desired output for a given process
    - instructions to highlight the issue you are having

    Good luck.

  7. #7
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Is it possible to have this relationship with my tables one to one with three tables? See PDF file. The reason I need this is because each table has different Values, C_value = UF, R_value = ohms, etc. Each table has different info. I want to enter a part in parts table and link it to the corresponding table depending on P_Type. Some how I need to link P_Type to each table. If I can't get this to work I'll put all the fields in one Parts Table with many fields. Please Help!
    Attached Files Attached Files
    Last edited by MadTom; 12-20-2018 at 07:17 PM.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,
    It isn't that I don't understand HOW to add relationships to your table. It is that I don't understand your business or the rules of your business to identify and make such relationships. I provided a link to "business rules" and how they relate to database relationships in post #4. Did you read it? Is there part of it you don't understand?

    If your question is how do I physically add relationships between tables, then that is easiest done in the relationship window.
    But I don't think that is the issue. You relate tables one to another(s) by means of Keys --Primary Key and Foreign Key. The reference materials to which I provided links discuss and provide samples.

    The videos by Dr. Soper and Dr. Widom that were referenced in earlier links work through examples.

    I don't need a book about your database or problems. I'd like to see the overview of the purpose of the database-- the business to be supported by this database.

    Good luck with your project.

    No one can tell you the relationships that support your business unless you provide some info about that business.

    Here is a link to more info on Database Relationships.
    Last edited by orange; 12-20-2018 at 07:46 PM. Reason: added link to 3 types of relationships

  9. #9
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    How do I link 1 table to 3 tables using the same PK? Do I need to use 2 fields as PK in the Parts Table and one in the SubParts table? Do you have a link to a webpage that talks about linking many tables together using 2 PK to 1 FK? Been thru all the links many time over and even more videos. Thanks

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,

    Either post a copy your database with a description of what the database is intended to do.
    OR write a description of a business process or 2 that you are trying to support with this database.

    The following info comes from Barry Williams' site (DatabaseAnswers dot org)
    Here is a link to a set of "business rules" for student Activities.
    Here is a link to the data model with relationships that support those business rules.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. HELP - Relationships Are Not Connecting!
    By Doncow in forum Database Design
    Replies: 19
    Last Post: 10-09-2017, 04:01 PM
  2. connecting tables in a database
    By maligauss in forum Access
    Replies: 10
    Last Post: 05-20-2014, 05:20 AM
  3. Replies: 1
    Last Post: 12-01-2013, 08:13 PM
  4. Connecting periods from different tables - database design problem
    By sockswithsandals in forum Database Design
    Replies: 4
    Last Post: 04-15-2012, 08:39 AM
  5. Database design - connecting 2 tables
    By Eisaz in forum Database Design
    Replies: 2
    Last Post: 10-16-2009, 09:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums