Many to many relationship and subform

[Vinayak]

Hi all,

I have a many to many relationship that I want to present in a
form/subform combination. The tables are Book, Book_Author_Link and
Author. The main form is based on the table Book. How do I link a
subform showing Author details of the related Authors for a Book? I can
create the two forms independently but am not able to link the forms.
Can someone help?

Thank you,
Vinayak

[Ed Robichaud]

You might want to reconsider your data structure. Check out the any of the
similar samples/templates in Access. For your setup, you only need two
tables, though you might want to add more for publishers, store/library,
category, etc.

tblBooks
BookID - primary key (autonumber?)
title
authorID - foreign key -linked to tblAuthors
price
ISDN
publisherID -foreign key -linked to tblPublisher
category -foreign key linked to tblCategory
etc.

tblAuthors
AuthorID - primary key
Lastname
Firstname
etc.
Use the subform wizard, and it will walk you through defining the necessary
links between your main form and related subform.
-Ed
"Vinayak" <vp******@columbus.rr.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi all,

I have a many to many relationship that I want to present in a
form/subform combination. The tables are Book, Book_Author_Link and
Author. The main form is based on the table Book. How do I link a
subform showing Author details of the related Authors for a Book? I can
create the two forms independently but am not able to link the forms.
Can someone help?

Thank you,
Vinayak

[Tom]

Hi:

I assume you are structured this way because there can be more than one
author to a book and an author can write more than one book...

One way to approach this is to create forms and subforms as follows
..Form frmAuthors with a subform frmAuthors_subBooks
..Form frmBooks with a subform frmBooks_subAuthors

A user looking for information on Authors can open the frmAuthors, see
all the information in tblAuthor and a list of all the books the author
has written in the subform.

A user looking for information on Books can open the frmBooks, see all
the information in tblBook and a list of all the authors of the book in
the subform.

Include buttons in each of the subform records to open the other main
form. For example, if looking at frmBooks and the user wants more
information on one of the authors, facilitate that by putting a button
on the frmBooks_subAuthors record that opens the frmAuthors to the
right author.

Good luck

Tom

Vinayak wrote:

Hi all,

I have a many to many relationship that I want to present in a
form/subform combination. The tables are Book, Book_Author_Link and
Author. The main form is based on the table Book. How do I link a
subform showing Author details of the related Authors for a Book? I can create the two forms independently but am not able to link the forms.
Can someone help?

Thank you,
Vinayak

[Vinayak]

Tom,
I see what you are saying. I am trying to do one of the combinations
you have described. I am trying to create frmBooks that has all
information in tblBook and list of related Authors by including a
subform. I want to know how to create a frmBooks main form including a
subfrmAuthors based on the three tables tblBooks, tblAuthors,
tblBooks_Authors_Link. The simplified schema is as follows:

tblBooks
BookID - primary key (autonumber)
title
price
ISBN

tblAuthors
AuthorID - primary key
Lastname
Firstname

tblBooksAuthorsLink
BookAuthorsLinkID - primary key
AuthorID
BookID

How does one configure the subfrmAuthors form to get the details from
tblAuthors table based on the Book record in main form i.e. frmBooks?
Thanks,
Vinayak

[Ed Robichaud]

Oops, just after sending that post, I realized that some books can have more
than one author, so your table design is needed. The main-subform set would
be generally the same, though you'll need to use two different main forms to
get both perspectives.
-Ed

"Vinayak" <vp******@columbus.rr.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi all,

I have a many to many relationship that I want to present in a
form/subform combination. The tables are Book, Book_Author_Link and
Author. The main form is based on the table Book. How do I link a
subform showing Author details of the related Authors for a Book? I can
create the two forms independently but am not able to link the forms.
Can someone help?

Thank you,
Vinayak

[Tom]

Vinayak:

Make a query based on tblBooksAuthorsLink joined to tblBooks. Include
all the fields from the join table and the bookname from tblBooks.

Make a form based on that query including the button to open the
frmBooks. All you really need on the form is the bookname and the
button. The IDs need to be part of the data source, but not displayed.
This will become your subform.

Open the frmAuthors that you have already design and use the subform
tool to add the subform you just designed. Let the wizard create the
appropriate links.

Do similar to create the author subform on the books main form.

When you get this all set up you will likely find that the user will
make hash of everything by following a thread of information. For
example, somebody will look up a favorite book, see the author, open
the author form, look at the other books written, select one of those
and open. You will need to coordinate between the two main forms to
make sure its obvious which is active and to make sure you don't get
multiple instances of each form open simultaneously. I'd suggest
setting the visible property = false on the form that is being left.
Good luck
Tom

[Vinayak]

Tom,

Got it. I was missing the part of creating a query and basing the
subform on that query instead of basing it on the table.
Thanks a ton,
Vinayak