Trouble with relating two simple tables

[djclinton15]

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!

[moke123]

Unhide the hidden fields in book table, join the fields and then edit the join type.

[Welshgasman]

Sounds like you have books without an author on your author table?

[moke123]

Btw, I don't recall ever seeing tables colored like that. Why?

[xps35]

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............

[orange]

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.

[mike60smart]

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

[djclinton15]

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?

[moke123]

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.

[Micron]

I miss the moke123 doggie.