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
> 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.
tedmi - 16 Jan 2008 22:03 GMT
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.

Signature
Ted
Anita - 20 Jan 2008 19:53 GMT
thats what I want - but I dno't understand innder and outer joins and what
you have described...
> 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
[quoted text clipped - 11 lines]
>
> Hope this helps.
Steve Schapel - 20 Jan 2008 21:55 GMT
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.

Signature
Steve Schapel, Microsoft Access MVP
> thats what I want - but I dno't understand innder and outer joins and what
> you have described...