By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Junction table and the Form design

P: 91
I have two tables [RuleDetail] and [Process]. The two tables are connected as many to many. I have a junction table [RuleDetail_Process] to bridge the two tables.

I have 10 different fields in table [RuleDetail] and 14 different fields in table [Process]. I am not giving the detail here as this is more of a conceptual question.

The junction table [RuleDetail_Process] has only following fields:
Ruledetail_ProcessID
ProcessID
RuleDetailID.

So that the junction table is populated automatically (via a form), I have based my form on table [RuleDetail] and in the sub-form, I have included all the fields of table[Process] and table[RuleDetail_Process].

My problem is - because of so many fields, the sub-form is looking big and very busy.

So my question is how to design a form when the two many to many tables are big? Could you suggest me a better way of managing the subform?

With so many fields in table [Process], is it a good idea to create the form only to include one or two fields from [Process] to populate the table[RuleDetail] and populate the junction table[RuleDetail_Process] and create a separate form for rest of the fields for [Process]?

Kindly advise.
Mar 29 '12 #1

✓ answered by MikeTheBike

Hi

Without knowing anything about you structure or operational requirements, I think I would base my 'Joining' form on the [RuleDetail_Process] table with a combo box pick list for ProcessID and RuleDetailID each having Control Source based on tables Process and Rule Detail respectively.

Hope that makes some sense!


MTB

Share this Question
Share on Google+
3 Replies


Expert 100+
P: 634
Hi

Without knowing anything about you structure or operational requirements, I think I would base my 'Joining' form on the [RuleDetail_Process] table with a combo box pick list for ProcessID and RuleDetailID each having Control Source based on tables Process and Rule Detail respectively.

Hope that makes some sense!


MTB
Mar 29 '12 #2

P: 91
Thanks MTB. Let me then give you some more details.
I have 5 tables (including junction table) - [Jurisdiction] 1:M[RulesMap] 1:M [RuleDetail]1:M [RuleDetail_Process] M:1 [Process].

The 1:M show the relationship between the tables.

The tables and its fiedls are as follows:
[Jurisdiction]
JurID
Jurisdiction_name

[RulesMap]
RulesMapID
JurID
RulesMapName

[RulesDetail]
RuleDetailID
RulesMapID
Initial_assessment
Rule_ref1
Rule_ref2
Description
Procedure
Active/Inactive
Functional_area
Notes

[RuleDetail_Process]
RuleDetailID
ProcessID

[Process]
ProcessID
Detail_process_name
Rule_Impact
Product_type
Rule_owner
Location1
Location2
Process_owner
Role
Activity
Processing_location1
Processing_location2

What I am trying to do is create ONE Form with connected subforms (for each table).

For eg, I would like to see the Rules and Rule details for a Jurisdiction and how a [RuleDetailID] is linked to [Detail_process_name].

Being new to Access, my difficulty is to design such form becuase of the junction table. Wondering how do I design and insert this "junction-form" in my one form view.
Mar 29 '12 #3

mshmyob
Expert 100+
P: 903
If you have so many fields for your form you could try using a tab control to organize the data entry and make it more easily read by the enduser.

Like MTB says you would use a combo box to populate your bridge table.

cheers,
Mar 29 '12 #4

Post your reply

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