Many to Many relationship

[Anita]

Can someone please explain to me how to create a data entry form based on a
many to many relationship? I know how to create the relationship but can't
figure out how to create the form to populate my tables. I know how to
create a one to many relationship and display the many side of the
relationship in a subform, but this doesn't seem to work in the same way if
you have a many to many. Even my Access Bible doesn't help.

Hope someone out there can help...

Desperate

Anita (v2003)
Many thanks in advance

 

[tedmi]

In general, it is not possible to have ONE form enter both sides of a
many-to-many realtionship. Instead, you need to enter one or the other of the
one-to-many sides. For example, a typical many-to-many is books and authors.
You could set up a form for entering a book with a subform for the book's
authors, or a form for entering an author with a subform for the author's
books.

 

[Anita]

Hi

I know - what I want is for instance a form to enter one book and many
authors, then another form that lets me display one author and input/view
many books - does that make sense? I tried to do it in the usual way as in
the case of a one to many but I understand the junction table needs to be
involved somewhere along the line and I just can't get it to work. Basically
I want to be able to see the data from two angles. Hope that makes sense!!

Thank you

 

[tedmi]

If you are showing author data in the parent form, then there should be a
sub-form for books. The data source for the subform should be an inner join
of the book table with the book-author junction table on book ID. The linking
field from parent to child must be author ID.

Conversely, if the parent contains book data, its subform shows authors. The
data source for the subform is an inner join of the author table with the
book-author junction on author ID. The linking field from parent to child
must be book ID.

Note that entries into the subforms must allow inserting links to existing
child records as well as adding new ones, to allow for the case of adding a
new book by an existing author, or adding an author to an existing book.

Hope this helps.

 

[Anita]

thats what I want - but I dno't understand innder and outer joins and what
you have described...

 

[Steve Schapel]

Anita,

A copy of my reply to your similar question in another newsgroup...

Not sure if this will help, in a non-specific way, but you might find
this article of interest.
http://accesstips.datamanagementsolutions.biz/many.htm

So, using your books and authors example, you could have a main form
based on the Authors table, and a subform based on a query that includes
both the Books and Authorships tables. Or you could have a main form
based on the Books table, with a subform based on a query that includes
Authors and Authorships tables.