Results 1 to 10 of 10
  1. #1
    djclinton15 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    118

    Exclamation Trouble with relating two simple tables


    Hi.

    Iím trying to relate two simple tables. My first table is called, Books_Info_tbl which contains fields like: The title of the book, The released date for each book, number of chapters, number of pages, etc. Also, this table has a Primary Key, BksID & a Foreign Key from another table, ArthrID

    The one essential detail which I excluded in the Books_Info_tbl is the author which I hope to keep in a separate table. Reason being, a lot of the books I read are by the same author. Therefore, my Authors_tbl has only 2-fields, ArthrID (set as the Primary Key) & author.

    Now, when I set up these 2-tables, in the Relationship window I was not able to join the ArthrID from the Authors_tbl to the books_Info_tbl a one-to-many relationship. One author to many books. Makes sense to me. However, it doesnít work because when I click onto Enforce Referential Integrity rule I received this error message shown below.(I mean attached) I donít understand what it is that Iím doing wrong? I also attached the database for those who care to view it.



    Any advice/suggestions would be greatly appreciated! Thanks in advance!
    Attached Thumbnails Attached Thumbnails Books Database Error.png  
    Attached Files Attached Files

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Posts
    1,563
    Unhide the hidden fields in book table, join the fields and then edit the join type.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,481
    Sounds like you have books without an author on your author table?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Posts
    1,563
    Btw, I don't recall ever seeing tables colored like that. Why?

    Click image for larger version. 

Name:	Capture.PNG 
Views:	28 
Size:	11.9 KB 
ID:	50340
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    212
    The only book in your table has 0 as value for the ArthrID (the default value). There is no author with that ID. Therefore a relationship with referential integrity cannot be created. Best create relationships before entering data (it is a design matter) and avoid default values for foreign keys.

    BTW a book could have multiple authors, so you might need to reconsider your design............
    Groeten,

    Peter

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,663
    Dave,
    I removed your existing records.Then related the tables on ArthrID. However, as has been suggested, a book could have multiple authors. Also note that the # In srs#in the Books_Info_tbl will require you to enclose it in [ and ] or else you'll get some syntax error. Better to remove the #--and use perhaps No or Num.
    I'm not sure why it was giving the error previously, but always better to set up relationships and then enter data as Peter said.

    So delete your existing records, create your relationship,then enter data.

    Good luck.

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,477
    Hi
    The attached is how your Form(s) should be laid out.

    Also when you create a VB Event you should add the line:- "Option Explicit" below the "Option Compare Database" Line
    Attached Files Attached Files

  8. #8
    djclinton15 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    118
    I wish to thank everyone here who responded to my post & make a few comments on what I did or rather fixed.

    To Mike123: Thanks, I did unhide all of the columns/fields in my tables. Sorry to say, but I don’t see how the background color really matters; all I can say is that I have Access 2021.

    Welhgasman: I’m pretty sure I had at least one author’s name in my Authors_tbl.

    Xps35: You are absolutely right, a book can surely have multiple authors.

    Orange: 1) I did change that ‘Srs#’ field to ‘NoSrs’ in the Books_info_tbl. 2) I also deleted all records from the Books_Info_tbl & the Authors_tbl. I was successfully able to joined these 2-tables in the Relationship window. However, when I used these 2-tables in a query, (I pulled everything from the Books_Info_tbl & just the Author from the Authors_tbl the query was blank & non-editable. And, yes, I had data/records in both tables.

    Mike60smar: Thank you for revising my database & forms. I could just carry on & use your properly functioning database & be done with, Although, part of my purpose in creating this Books database is to gain experience creating databases, relating tables, etc. I didn’t know that forms ‘should’ be done a certain way?

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Posts
    1,563
    but I don’t see how the background color really matters
    It doesn't matter. But since a user should never see the raw tables, I was curious why the color.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,432
    I miss the moke123 doggie.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Trouble Linking two simple tables
    By TINMAN in forum Database Design
    Replies: 3
    Last Post: 03-18-2022, 08:52 PM
  2. Relating Tables
    By qwerty in forum Access
    Replies: 4
    Last Post: 10-30-2018, 05:00 PM
  3. Relating Tables
    By Jabirali in forum Database Design
    Replies: 4
    Last Post: 12-06-2012, 03:59 AM
  4. Need help relating tables
    By LeahJB in forum Database Design
    Replies: 4
    Last Post: 02-15-2012, 08:37 AM
  5. Might someone help with relating some Tables?
    By djclntn in forum Database Design
    Replies: 20
    Last Post: 02-08-2012, 11:35 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