Junction table and the Form design

[sg2808]

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.

[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

[sg2808]

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.

[mshmyob]

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,