Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    72

    Unanswered: One to many relationship - not allowing

    *Items (books)
    ID
    Title

    *SubjectHeadings
    ID
    Subject

    *item_subjectheadings
    bookid
    subjectid

    If my SubjectHeadings table includes records of headings that haven't been used yet (a standard list) could this be causing the problem that I cannot get the relationships window to accept a One to Many relationship between Items.ID and item_subjectheadings.book?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assuming
    *item_subjectheadings
    bookid
    subjectid
    holds true then you have, effectively, one to one relationship between book and subjectheadings in item_subjectheadings. ie you cannot have the same subjectheading more than once for the same book.

    if you need to have the same subjectheading appearing more than once then you need some mechanism to handle that. depending on what you are trying to achieve that could be another element that makes an item_subjectheading unique, or if you cannot find one then mebbe use a surrogate key, eg another auton generated number.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Make sure both bookid and subjectid are primary key to item_subjectheadings and try again.
    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

  4. #4
    Join Date
    Jul 2007
    Posts
    72
    Healdem. It is right that no book can have the same subject heading more than once. So although a bookid appears many times in the item_subjectheadings table it will never appear in a row with the same subjectid.
    Startrekker, I did make bookid and subjectid the primary key of the item_subjectheadings table but I get the same problem.

    I checked both tables for duplicates and there aren't any. If I think that my primary table has a related record in the item_subjectheadings table for every record, is there a way of checking if I'm right? There are about 5000 records in the primary table and 8000 in the item_subjectheadings.

  5. #5
    Join Date
    Jul 2007
    Posts
    72
    I used the Find Unmatched Query Wizard (who'd have thought!) and there was a sneaky record that indeed did not have a record in the item_subjectheadings table. Now that it is gone I was able to make the relationship One-to-many with enforced referential integrity.

    I always misread that option and thought it had to do with an "unmatched query" and not "find records that are unmatched via a query"

Posting Permissions

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