473,320 Members | 1,853 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Populating Junction Table & Many to Many Relationship

91
I have two tables (tblPolicyMap and tblBusiness) and the fields within it are:

1. tblPolicyMap
PolicyID (PK)
PolicyDetail

2. tblBusiness
BusinessID (PK)
HLProcessName
Business_Functions

One policy could affect more than 1 HLProcessName and Business_Functions.
Also, One Business_Function / HLProcessName could have multiple policy.
Therefore, this is a many to many relationship.

My questions are:

a. As Policy detail has n-n relationship with both HLProcessName and Business_Functions, should I create 2 n-n relationship (Policy - HLProcessname) and (Policy - Business-Functions)? or it is it still ok to have only two tables as above?

b. With the assumption that only two tables will suffice (as above), I have created a junction table (tblPolicy_Business) with fields - PolBizID (PK), PolicyID (FK)and BusinessID (FK). I do not know how to populate the junction table, What approach should I take to populate the Junction table assuming that I have all the data in tblPolicyMap and tblBusiness?

c.To add to the above, how should I create a form so that I can easily populate the junction table for new records?


Kindly advise.
Mar 19 '12 #1

✓ answered by NeoPa

I'm not sure there's actually anything missing from Mshmyob's post related to the question, but let's see if we can go over it again to aid comprehension.

There are, of course, less basic approaches conceivable which include code to add records to recordsets not bound to the current form, but I'll take you through the standard approach :
  1. A form is needed for each main table. Each handles maintenance of the associated table.
  2. A form is required, just as Mshmyob outlined, to maintain the bridging table. This allows adding of FK values from ComboBoxes listing the existing PK values of both main tables separately.
Thus, yes. You will need three tables and three separate forms in order to maintain them. This is pretty standard in your circumstances.

6 9132
mshmyob
904 Expert 512MB
Since you have a M:N relationship between the two tables you need your bridge table like you are thinking.

Your bridge table can consist of only two fields:

PolicyID (PK)
BusinessID (PK)

Make the two fields a composite primary key.

To populate the bridge table your form will have combo box controls for selecting the Policy and Business. Upon selection of a value from the combo box the combo box will save the PK from the appropriate table and store it into the appropriate FK in the bridge table.

Note: Each part of the PK in the bridge table is the FK to the corresponding table.

cheers,
Mar 19 '12 #2
NeoPa
32,556 Expert Mod 16PB
There is no way to reflect the relationships between the two main tables without creating a bridging table.

Mshmyob's suggestion of two FK fields joined together as a composite PK is certainly workable, but I'd recommend your original suggestion (AutoNumber PK as well as two separate FK fields.) over that generally, and especially if the volume of bridge records is vast. The composite key would still be required, to ensure no duplication of the data, but searches can be faster on indices containing less data. If you can be sure that the volumes won't be high then Mshmyob's simpler solution might win out, as it takes less space and less time to create records in the first place.

Both approaches are perfectly valid of course.

PS. Please note that questions must not be multiple in a thread. The rule is one thread - one question. In this case, principally because Mshmyob had already responded fairly fully, but also because the questions were all so closely related, I was able to treat it as a single composite question rather than three separate ones, but please bear in mind for future questions.

PPS. Nice job to clarify the question in the first post rather than simply leave it spread out over multiple posts. Saved me a job ;-)
Mar 19 '12 #3
NeoPa
32,556 Expert Mod 16PB
Another discussion, related to this thread, was started in here, but has now been moved to Discussion: About Relationships.
Mar 20 '12 #4
sg2808
91
Given my scenario (as above), does this mean that I will have to create 3 Forms? - 2 Form for the two tables and another Form for Junction table?
I am not quite clear on this from mshmyob's post above.

What is the best way here? Kindly clarify which Forms should capture which fields.
Many thanks.
Mar 20 '12 #5
NeoPa
32,556 Expert Mod 16PB
I'm not sure there's actually anything missing from Mshmyob's post related to the question, but let's see if we can go over it again to aid comprehension.

There are, of course, less basic approaches conceivable which include code to add records to recordsets not bound to the current form, but I'll take you through the standard approach :
  1. A form is needed for each main table. Each handles maintenance of the associated table.
  2. A form is required, just as Mshmyob outlined, to maintain the bridging table. This allows adding of FK values from ComboBoxes listing the existing PK values of both main tables separately.
Thus, yes. You will need three tables and three separate forms in order to maintain them. This is pretty standard in your circumstances.
Mar 20 '12 #6
sg2808
91
Thank you so much NeoPa and mshmyob for your advices.
Mar 20 '12 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: mksql | last post by:
As an example, I am building an authentication mechanisim that will use data in the 3 left tables to determine rights to objects in a destination table, diagrammed below. In this structure,...
8
by: Marcy | last post by:
I am trying to build a database to keep track of training topics completed by people in my department. Our department has a set of 37 training topics. There are 7 job classifications in the...
1
by: Riley DeWiley | last post by:
I have an application with two tables, A and B. Each has an autonumber unique ID field, plus other data. I have a junction table, AB, containing fields AID, BID, and Count (a number). AB has...
1
by: LurfysMa | last post by:
I am working on an electronic flashcard program. Most of the subjects are simple lists of questions and answers. Those seem to be working. Some of the "subjects" have "categories" of questions. ...
3
by: inthemix | last post by:
My goal is to have a sub-form located on the main form which containts a listbox. The user will be able to select anany number of offices (by officeName) from this listbox. IMO the design is very...
1
by: que576 | last post by:
I have created a junction table so that I can relate data from 2 other tables. Table 1 - Category Table (has the following fields with data) CatID (primary key) CategoryName CategoryStatus ...
2
by: Henry Stockbridge | last post by:
Hi, I need a recommendation when to add a record to a junction table that complements a many to many relationship. There will be a Contacts form, and an Interests subform with the parent/child...
3
by: dbertanjoli | last post by:
Hello, I have a questinarrie webform I use INSERT statement(s) to insert a new record in the User and Questions tables and then (HERE IS MY PROBLEM) I need to update my junction table...
1
by: pwebbie | last post by:
In an MS Access Project tied to SQL Server, I am trying to create a data entry form that allows the user to enter info about a law, and then (in a datasheet) edit/insert related web site records...
0
by: aRuaL | last post by:
Hi all, i have a problem with updating the junction table. I am doing this access project after learning for 2 weeks so pardon me if what the question im asking is really simple. Firstly i have 3...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

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.