473,554 Members | 4,718 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Many to many relationship and subform

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_Lin k 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

Nov 13 '05 #1
6 6890
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******@colum bus.rr.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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_Lin k 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

Nov 13 '05 #2
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_subB ooks
..Form frmBooks with a subform frmBooks_subAut hors

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_subAut hors 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_Lin k 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


Nov 13 '05 #3
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_Author s_Link. The simplified schema is as follows:

tblBooks
BookID - primary key (autonumber)
title
price
ISBN

tblAuthors
AuthorID - primary key
Lastname
Firstname

tblBooksAuthors Link
BookAuthorsLink ID - 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

Nov 13 '05 #4
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******@colum bus.rr.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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_Lin k 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

Nov 13 '05 #5
Tom
Vinayak:

Make a query based on tblBooksAuthors Link 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

Nov 13 '05 #6
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

Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2086
by: Mark Hargreaves | last post by:
Hopefully someone can help. I have two tables, namely master and postings: Fields include the following: master: staffno - int (11) primary key forename - varchar (20) surname - varchar (30)
3
1997
by: Mikey | last post by:
Hi all. In the process of trying to figure this thing out, I've been doing the old "stand around in the store and read as much as possible before you look like a derelict" thing. This time, with the O'Reilly "Managing and Using SQL" (I think that's it). Anyway, I was looking at a chapter discussing database design (without any direct...
1
1673
by: ning | last post by:
It's easy to present "One - Many" relationship in XML, but how to present "Many - Many" relationship in XML?
1
1762
by: Powell | last post by:
I have a requirement to return some information from a large number of xml files I have tried XSLT, which quickly gets overwhelmed. Found references to XQuery and it seems like it might work I found reference to the Microsoft XQuery demo (the site seems to be down), and was able finally to find Xquery.msi elsewhere and get it going. I can...
2
7179
by: Maggieanp22 | last post by:
I'm designing a db which stores client details but each client has multiple 'occasions'. For each occasion there is a section which has items purchased (there are sometimes none, sometimes several). But the main difficulity I'm having is when I try to do stock control stuff to keep track of how many of each individual item has been sold. ...
2
2130
by: Todd D. Levy | last post by:
I have a primary table (containing basic contact information) and a number of subsidiary tables (containing various additional information) for employees. Most of the subsidiary tables have a One-to-One relationship with the primary table. There is an exception. The employees attend various events and I have a subsidiary table that...
0
1150
by: misscrf | last post by:
I have this normalized database, and I don't understand why setting up the forms is so hard for me. I have the main candidate entry form. This is to enter in candidates who apply for a job. I have a tab control with a page that shows if the candidate type is 1 of a few types ( in code). Otherwise 2 pages show. 1 is for contact...
4
3909
by: bobg.rjservices | last post by:
running access 2k; adp w/ linked tables to SQL server; I am absolutely stymied, and ticked off beyond belief - I can not believe how much time I've wasted trying to do something that should be comically easy!!!!! I have 2 tables: T_people, T_calls in a 1 to many relation (many calls to each person). I have a form with tabs in it. the...
2
2212
by: The Frog | last post by:
Hello everyone, I am trying to find way of writing an SQL query that can produce missing record combinations across a many to many type setup in Access. The three tables used are as follows: Table1: Field 1
0
1403
by: fred.flintstone | last post by:
I get warnings in the windows event viewer every few seconds.. See below. Why is this ? Event Type: Warning Event Source: DB2-0 Event Category: None Event ID: 5 Date: 22/08/2007 Time: 08:20:50
0
7620
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7541
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7821
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8057
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7900
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6172
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5452
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3591
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
861
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.