Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011

    Unanswered: Relationships and Linkages

    This is difficut to explain so I loaded some screenshots that I hope will help illustrate.

    I've created a database (from scratch by scratching my way through) that has a table dependant on another table. The jpeg "WOINFO" shows a table with 'WOSN' as a primary key. The jpeg "DEFINFO" has a field for 'WOSN' which autopopulates (just as I'd like it to) from data typed into "WOINFO". The primary key for "DEFINFO" is the auto ID.

    HOWEVER, there is also a column in "DEFINFO" for 'Evaluator' which, no matter how hard I try, will not autopoluate from "WOINFO".

    There is a jpeg of the "Relationships" screenshot as well.

    Please ask whatever questions you need. I have tried everything I can think of. Thank you VERY MUCH appreciate the help!
    Attached Thumbnails Attached Thumbnails WOINFO.JPG   DEFINFO.JPG   Relationships.JPG  

  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    Your table structure needs some work. A Primary Key is used to uniquely identify a record within a table. By adding an Auto ID to each table you have defined a Primary Key. But tables should be joined on the Primary Key.

    The Primary Key should be joined to the Foreign Key of the other table. For instance your Defect Categories table links from the DefectCategory field in Defect Categories to Defect Information. You have two choices:

    1. Change your Primary Key to DefectCategory in Defect Categories and remove ID (assumes DefectCategory in the DC table is unique)
    2. Join using the ID field to the DefectCategory field in the Defect Information field

    Another suggestion I would make is to rename the ID fields (if you plan to keep them) a suggestion would be CategoryID, DescriptionID, StampTypeID, etc.

    But I think the reason you are having a problem is the link between the Evaluator fields in the WOI table and the DI table. The DI table should link to the Evaluator table.

  3. #3
    Join Date
    Jul 2011

    Thank you

    I will certainly attempt to follow your guidance, thank you. One thing I should mention is that the main form (where folks enter the data) is made from the WOI table and the DI table is made into a subform within that primary form... so when someone enters a WOSN number in the primary form, the number needs to be unique (no duplicates). But within that WOSN there may be multiple errors (hence the subform). So for the DI, the WOSN cannot be unique. Does that make sense? Should I make it a primary key with duplicates OK? Will that help with the evaluator name problem?

    Thanks again.

  4. #4
    Join Date
    Nov 2007
    Adelaide, South Australia
    It is impossible to make a primary key that is not unique (DuplicatesOK).

    The subform should be using a related table that has a foreign key in it that links back to the primary key (WOSN) of the primary table (WORK ORDER).

    You really need to get your tables designed correctly before going further. What is the point of the ID field in WORK ORDER?

    There is also no point of having the Evaluator in the DEFECT table since there is one already defined for the work order... either use the evaluator in the WORK ORDER or this field has to be related to the EVALUATOR table, instead of the WORK ORDER.

    Too hard to tell because your table design is a bit of a mess.
    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

  5. #5
    Join Date
    Dec 2001
    I think you need to re-do a large portion of your data base schema. Let's start with this:

    1. Remove all the relationships you currently have.
    2. I suggest you name your work orders table "WorkOrders"
    3. Name the primary key of the work orders table "WorkOrderID"
    4. Name the primary key of the Evaluators table "EvaluatorID"
    5. Add a field named "EvaluatorID" to your WorkOrders table. This is the foreign key to the Evaluators table.
    6. Remove the field named "Evaluator" from the WorkOrders table.
    7. In the relationships window, create a one to one relationship between EvaluatorID in the WorkOrders table and the primary key (EvaluatorID) of the Evaluators table.

    Proceed in a similar way with the rest of the tables and keys.
    Peter De Baets
    Peter's Software - Microsoft Access Tools for Developers

Tags for this Thread

Posting Permissions

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