I discovered by chance that there seems to be an unexpected relationship in my database -- this has to do with relating people (authors) to books.
I have a "person" table in which all people have records. I have a second instance of the "person" table called "person author" in which I relate people to books (in a "book" table).
What's strange is that the "person author" to "book" relationship seems fine. The ids are all correct, if I display the author of the book the right record is displayed, etc. -- no apparent problems.
However, if I display the "person" (not the "person author") related to the book I get what seems to be a completely random person record. Moreso, when I deleted one of these records, the next sequential record became related to the book. (I've just put "first name" fields on the layout -- from both "person" and "person author" to watch this happen. "Person author" is correct and remains so, "person" is not.)
My suspicion is that this was caused by one of two scripts designed to search people and books. The scripts are identical except that they search different tables -- I've attached one. "Search action" is the name of two calculated fields (in both the "person" and "book" tables), and I noticed that the "search action" contents matched the erroneous records -- so that a title and innappropriate person were both listed in the "search action" field. In the "person" table "search action is "first name" and "last name", in books it's "title" and "first name" and "last name". So I think the books script is the problem.
Any general ideas about why this might be happening, how I can remove this data, and where I should look for the relationship?