Relationships

1. I am having trouble grasping the concept here of how we determine which tables need relationships. Do they all? What is the best way of figuring this out (or thinking about it)?

Yes, all tables in this database need relationships. Each table must be related to at least one other table and all relationships between tables are (in this database) one to many relationships. To decide which is the one side of a relationship and which is the many side, you want to ask yourself the question - "is there a relationship between one record in this table and many records in another tables?" An example might be, "is it possible that one publisher record in the publisher table might be related to many book records in the book table?" Since, in this database, a single publisher is very likely to be related to many books that the publisher has published, the answer is yes and there is a one publisher to many books relationship between the publisher table and the book table. Thus each related book record must contain a copy of the primary key for the related publisher record to effect the relationship. The copy of the Primary Key (PK) from the one side of the relationship is, when it is located in a record in the many side of the relationship, known as a Foreign Key (FK).

So far I successfully made a relationship between the publisher table and the book table (1 publisher to many books). Would another one be 1 book to many authors?

Since, in this database, some books were written by more than one author and some authors have written more than one book, the overall relationship between books and authors is a many to many relationship. To construct a many to many relationship between two tables in Access, one must create an intervening (or junction) table between the two. This junction table has the sole responsibility of linking together the two tables in the many to many relationship. Each record in the junction table represents a link that binds together a record from each table in the relationship. In the Excel worksheets I asked you to use, the BookAuthor table is this junction table and includes individual records, each of which link one author to one book in a relationship. But each author may have a link to more than one book (thus it's a one author to may links-to-books relationship). And, on the other side of the relationship, each record links one book to one author by creating a link between author and book. And each book may have an individual link to more than one author, thus creating a one book to many author links relationship.

Book-----------------Link------------------Author
Title 1---------------links to---------------Author 1
Title 2---------------links to---------------Author 1
Title 1---------------links to---------------Author 2
Title 2---------------links to---------------Author 2

There are four links in the intervening junction table to account for two many to many relationships (Title 1 has a relationship to two authors and Author 1 has a relationship to two books).

I had made a relationship between authors and books and it turned out 1:1 and as I remembered that we needed to change the FK auto number to a long integer. But then I got the error about I cannot change the field data because its part of 1 or more relationships. So I deleted ALL of the tables and their relationships but it still gave me that message. What I am I doing wrong?

If you find that the PK to FK relationship tells you you have a 1:1 relationship, when using this data, the problem lies in the FK properties. You will need to ensure that the FK (which is a copy of the PK) is changed from data type Autonumber to data type Number, that both the FK and the PK are long integers, and that the FK is indexed with Duplicates OK. The FK must be able to be duplicated to effect a one to many relationship, so the indexing property must allow it to be duplicates. A PK must not be duplicated, so its indexing property must say No Duplicates.