Many-to-many join

M

M

I am working on a database where I need to make a many-to-many join.
It does not seem to matter how I do it I am not getting the results
that I would like. The database that I am designing is made up of
essentially three different parts: 1. Authors 2. Book specs 3.
Evaluators. Here are the relationships that I am tyring to work with:

ONE author can write MANY books
ONE book has ONE author
ONE book can be evaluated by MANY Evaluators
ONE evaluator can evaluate MANY books

Well, I was successful in getting the first two relationships to work
throughout the database. Now I am stuck on the last two. To begin
with I made a form from the Evaluators table w/fields such as:

Evaluator-ID (Prim K)
Eval Name
Eval Address, etc.

Then I wanted to have a subform (that information could be entered
into) that would tell which books that person was evaluating, date,
etc. The fields would come from the Evaluators Table as well Book
Specs table. This way I could give the person entering the ablity of
having a drop down field for the title choice. The problem is that it
keeps wanting a corresponding field in the Author table. Since I
joined the Author table & Book specs to get the first two
relationships.

Please help. I have been stuck for some time on this one.

M
 
T

Tim Ferguson

(e-mail address removed) (M) wrote in
ONE book can be evaluated by MANY Evaluators
ONE evaluator can evaluate MANY books

What you have left out is the Evaluations table: this might look something
like this:

EvaluatorID LongInt references Evaluators
BookNumber LongInt references Books
DateOfSignature DateTime
Summary Memo

Primary Key (EvaluatorID, BookNumber)


Hope that helps


Tim F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top