AnsweredAssumed Answered

Creating a Many-to-Many Relationship between two databases

Question asked by ProdMan on Sep 19, 2017
Latest reply on Nov 27, 2017 by mostafasadek

Many to Many relationship

I have two databases: one is Books ("Pubdb"), the other is Authors ("Contribs").

One Book can have multiple authors, one author can have multiple books. (Book A can have authors 1 & 2; Author 2 can have Books A & B.)

Each author and each book are assigned a unique identifying number sequence. In the Author database the associated Book sequences are broken down in a list field, with one number sequence (e.g., one book) per paragraph. I then created calculation fields which break out each sequence, hoping that would enable better matching.

What I need is for the Book database to find all records in the Author database that have a matching Book sequence number and pull in the Author data from that record in the Author database. I have established a relationship between the two tables, but it is only pulling in data for one title, and only then when I link the relationship to the particular sequence field (Title Record ID 12 in the screen shot) in the Author database . I tried adding a second relationship (Title Record ID 8) in the same manner but it has no effect. I have tried one to many relationships and field calculations using "Get AsText", all to no avail.

I have attached a screen shot of the relationships, the one test field (Contrib1 First). I have also attached a screen shot of the Author database showing the sequence numbers.

If there's a manual for how to do this I will buy it; if there's a URL I would love it. I'm not a whiz at this, so explanations need to be step-by-step.