B-directional relationships?

[T Payne]

I am an academic, not a programmer. I have recently taken on the job of
review editor for an academic journal. I need Access in order to keep track
of:

1. Books available for review
2. Reviewers willing to review books
3. Which reviewer has been assigned which book(s) and when their review is
due.

In the "Books available for review" table, there is an "Author" field. These
Authors may have written more than one Book. Therefore I want to have an
"Authors" table that is linked to the Authors field in the "Books available
for review" table, right?

The thing is, some of the Authors are also potential "Reviewers." Therefore
my "Authors" and "Reviewers" could be the same table.

But this seems to create a circular set of relationships, and my mind at
this point, can't conceive of how to organize it (I'm new at Access, by the
way).

Call the main table "AuthorsReviewers." Any given "AuthorReviewer" may have
written several "Books." He/she may also be reviewing several "Books."
Furthermore, each "Book" has an "AuthorReviewer" that wrote it, and possibly
another "AuthorReviewer" that is reviewing it.

All the samples I've looked at have only one-way dependencies, e.g.,
suppliers have several products. But what I am looking at is two-way
dependencies -- as though suppliers could have several products and products
could have several suppliers!

So each AuthorReviewer can be associated with several Books, and each Book
can be associated with more than one AuthorReviewer.

Any suggestions on how to make this work?

Thanks for any help.

 

[Jeff Boyce]

You have people. They serve in one/more roles (author, reviewer, ?editor,
....).

A given book could have one-to-many persons serving as Author.

A given book could have one-to-many persons serving as Reviewer.

A given Person could serve as an Author on one-to-many books.

.... (you get the idea)

You'll need a way to indicate which person (?PersonID), which Role (?RoleID)
and which Book (?BookID) all fit together in a "resolver/junction" table
that lets you manage the many-to-many-to-many relationship you're faced
with.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Nurick]

To add to the fun, a book may have more than one author.

You probably need a structure like this: two main tables for Books and
Persons, and two linking tables to handle the two possible relationships
between a Book and a Person. (Two possible relationships in the domain
we're modelling: more in the real world!).

* means that a field is, or is part of, the primary key of the table.
+ means that it's a foreign key into another table.

Books
ISBN*
Title
... maybe other fields for Publisher, Price, etc.

Persons
PersonID*
...fields for name, phone number etc. (enough to
ensure that you can distinguish between the two
or three Professor John D Williamses at the U of
Bigsville)

Authorships
ISBN*+
PersonID*+
(The existence of a record in this table stores the fact
that the Person is an author of the Book. If necessary,
fields can be added to (e.g.) distinguish between editors
and contributors.)

ReviewAssignments
ISBN*+
PersonID*+
DateAssigned
DateDue
(A record in this table stores the fact that the Book
was assigned to the Person for review, with dates.
Other fields might be added - e.g. word count, honorarium...)

 

[John Vinson]

I am an academic, not a programmer. I have recently taken on the job of
review editor for an academic journal. I need Access in order to keep track
of:

1. Books available for review
2. Reviewers willing to review books
3. Which reviewer has been assigned which book(s) and when their review is
due.

In the "Books available for review" table, there is an "Author" field. These
Authors may have written more than one Book. Therefore I want to have an
"Authors" table that is linked to the Authors field in the "Books available
for review" table, right?

No. Many books have multiple authors, of course! This is a many to
many relationship, not a one (author) to many (books) relationship -
you need A NEW TABLE, Authorship, with the BookID and the AuthorID. If
an author has three books, her name would be stored once in the
Authors table, and there would be three records for her ID in the
Authorship table; if a given book has four co-authors, there would be
four records for it in Authorship.

The thing is, some of the Authors are also potential "Reviewers." Therefore
my "Authors" and "Reviewers" could be the same table.

They should.

But this seems to create a circular set of relationships, and my mind at
this point, can't conceive of how to organize it (I'm new at Access, by the
way).

Not circular at all - a book has distinct attributes, who wrote it and
who reviewed it. They're both (many to many) links to the same table
of AuthorReviewers, but that's perfectly legitimate, since authors and
reviewers are members of the same domain.

Call the main table "AuthorsReviewers." Any given "AuthorReviewer" may have
written several "Books." He/she may also be reviewing several "Books."
Furthermore, each "Book" has an "AuthorReviewer" that wrote it, and possibly
another "AuthorReviewer" that is reviewing it.

All the samples I've looked at have only one-way dependencies, e.g.,
suppliers have several products. But what I am looking at is two-way
dependencies -- as though suppliers could have several products and products
could have several suppliers!

So each AuthorReviewer can be associated with several Books, and each Book
can be associated with more than one AuthorReviewer.

Any suggestions on how to make this work?

Four tables, I'd say:

Books
BookID
Title
<other information about the book as an entity>

AuthorReviewers
PersonID
LastName
FirstName
<other information about the person, e.g. contact info>

Authorship
BookID
AuthorID
<any info about this book/this author, e.g. sequential number to
identify first author>

Reviews
BookID
PersonID
<any info about this reviewer with regard to this book, e.g. date
the book was sent, date returned, memo field for the reviewer's
comments, etc.>


John W. Vinson[MVP]

 

[John Vinson]

...fields for name, phone number etc. (enough to
ensure that you can distinguish between the two
or three Professor John D Williamses at the U of
Bigsville)

Wise advice. Many years ago I (John Wilmot Vinson) was a postdoc at
Harvard; at the same them there was a John William Vinson who was a
professor in the med school.

He got at least one of my paychecks; alas, I never got one of his!

John W. Vinson[MVP]

 

[T Payne]

Thank you to all who have responded to this request. I think I am getting a
better picture of what needs to be done.

T

 

[Guest]

Hi John,

Is the way that you implement a many-to-many relationship like this to
create a series of forms and subforms?

None of the responses here actually say what you do in Access. I have never
implemented a many-to-many myself but I use subforms all the time. I really
love this functionality in Access.

Thx.

LDN

 

[John Vinson]

Hi John,

Is the way that you implement a many-to-many relationship like this to
create a series of forms and subforms?

Well... that comes SECOND.

The relationships themselves are between Tables. Forms are just tools
that let you work conveniently with the data in Tables.

A many to many relationship always involves three tables: the two
"one" side tables, and a "resolver table" related one-to-many to each
of them.

You'll use forms to enter the data into these tables - but the forms
themselves *use* the relationships, they don't set them.

None of the responses here actually say what you do in Access.

I beg to differ. They say quite specifically what you do in Access.

I have never
implemented a many-to-many myself but I use subforms all the time. I really
love this functionality in Access.

A many to many is just two one-to-many relationships - and you use a
Subform to enter the data into it.

John W. Vinson[MVP]

 

[Guest]

Sorry John, I am still learning Access.

Please explain to me why the scenario suggested by Nurick works for me when
all I do is use a subform inside of a subform? I can load data using
different forms as long as the ISBN is on the main form.

I am not using the ISBN as a primary key because that confuses the indexing
function. My primary key is ID as an autonumber field.

I know that there is a limit to the number of subforms but I can't imagine
ever getting that deep. (I am three deep now.)

When will this way of working with many-to-many relationships breakdown? (Or
many-many-many-many for that matter?)

Thanks.

LDN

 

[John Vinson]

Sorry John, I am still learning Access.

Please explain to me why the scenario suggested by Nurick works for me when
all I do is use a subform inside of a subform? I can load data using
different forms as long as the ISBN is on the main form.

I guess I don't understand your setup. John suggested exactly what I
suggested: three tables, Books, People, and Authorship, using
Authorship on a subform.

I am not using the ISBN as a primary key because that confuses the indexing
function. My primary key is ID as an autonumber field.

A Primary Key *need not be an autonumber*. It should meet three
criteira: it should be unique; it should be stable; it should be
short. An ISBN code meets all three desiderata quite nicely. In what
way would using it as the primary key "confuse the indexing function"?
All you'ld need would be an ISBN field (of Text datatype, matching the
size of the field in Books) in the Authorship table, as a foreign key
link to Books.

I know that there is a limit to the number of subforms but I can't imagine
ever getting that deep. (I am three deep now.)

The depth allowed used to be 3, but now it's 7 - and that's WAY too

When will this way of working with many-to-many relationships breakdown? (Or
many-many-many-many for that matter?)

It's just that any relationship, even extremely complex relationships,
can be broken down into one-to-many relationships. A simple many to
many resolves into two one-to-many relationships; a many-many-many
relationship (Books, Authors, Reviewers for example) into three one to
many relationships (which might not all actually be needed
explicitly).

John W. Vinson[MVP]

 

[Guest]

Thanks John,

Actually, I created three tables and three forms first. I then made one of
them the main form. Next I placed one of the other forms on the main form as
the first subform. I then placed the third form inside the first subform as
the second subform.
I set each subform to be a continuous form.

My intent was understand if I really need to understand many-to-many
relationships or not.

LDN

 

[John Vinson]

Actually, I created three tables and three forms first. I then made one of
them the main form. Next I placed one of the other forms on the main form as
the first subform. I then placed the third form inside the first subform as
the second subform.
I set each subform to be a continuous form.

That doesn't help me a bit. You don't say which form is which, how the
tables are related, or what the form's Master/Child Link Fields might
be.

My intent was understand if I really need to understand many-to-many
relationships or not.

I don't understand what you don't understand. Forms, again, don't
directly have anything to do with many to many relationships; the fact
that you have forms and subforms - even if they can be made to "work"
- does not ensure that your table relationships are correct. They may
well be, but nothing you've posted would help me ascertain whether
they are or aren't.

John W. Vinson[MVP]

 

[John Nurick]

With these tables
Books
Persons
Authorships
ReviewAssignments

I'd expect to have at least these forms:

frmPersons (bound to Persons, with subforms bound to
Authorships and ReviewAssignments respectively). I.e.
this form can be used
to enter, look up and edit data about Persons and their
relationships to Books

frmBooks (bound to Books, with subforms for Authorships and
ReviewAssigments). Enter, look up and edit data about Books
and their relationships to Persons

Each subform would be in continuous view; the most important control is
a combobox bound to the relevant key field but displaying something more
user-friendly

Basically, when a new book comes in, you (1) use frmPersons to check
whether its author(s) are already included in Persons and if not add
them; (2) use frmBooks to enter the data about the book and - on the
subform - select the author from the combobox (one at a time if there is
more than one author).

Then to assign a review, you can either go to the ReviewAssignments
subform of Books and select a reviewer for the book, or to the
ReviewAssignments subform of Persons and select a book for the reviewer.