Using a query with many JOINs vs. using subforms on a form

Question

I have a "main" form whose Record Source is a "parent" table.  The parent table is linked to 8 "child" tables.  I have created a subform for each of the child tables and have embedded all 8 subforms in the main form.

I've read several subform-related articles on the web that strongly recommend avoiding the use of Access' Subform control, for a variety of reasons.  The alternative appears to be to:

1. change the main form's Record Source to a query that includes all the fields in the parent table and all the fields in the 8 child tables (excluding the linked field)
2. manually add all the aforementioned fields to the main form

But before I proceed with the alternative approach, I would like to know if anyone else (in this forum) has encountered issues with Access' Subform control.  I would also like to know if you agree that the alternative approach is better.

Thanks in advance for your comments and suggestions.

Answer 1

Since a project may have multiple financials that aren't related directly to multiple issues, subforms are the only way to go.

A single form might have worked in a situation like football (American)

A League (NFL) has multiple Conferences (NFC and AFC)

A Conference (NFC) has multiple Divisions (North, South, East, and West)

A Division (North) has multiple Teams (Chicago, Detroit, Minnesota, Green Bay)

A Team (Minnesota) has multiple players (Ponder, Peterson, Harvin,....)

If the primary and foreign keys were set up correctly, you could create a single form based on this type of relationship. I would still use multiple subforms.

Answer 2

If you join referenced tables in a query then multiple rows per row in the referenced table are returned for each matching row in the referencing table.  So with multiple referencing tables this will be exacerbated and a form based on a query in which all of the referencing tables are joined to the referenced tables is likely to be very confusing to a user.

The use of subforms to represent rows from a referencing table is a standard technique which is widely employed and, if correctly implemented, should not give rise to any problems.  To save space each subform can be placed on a page of a tab control within the parent form.  You'll find an example in the file DormsDemo.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo is a very simple illustration of how data from tables in a one-to-many relationship type can be represented in forms by (a) the use of subforms on a tab control, (b) the use of a linked form in dialogue mode or (c) the use of a linked form synchronized to the main form so that navigating to a different record in the main form causes the linked form to navigate to the matching records.

For a simple illustration of how to represent a many-to-many relationship type via a 'junction' table in a form/subform see the ParentActivities.zip file in the same SkyDrive folder.

My advice would be to not consider for a moment using a single form based on a byzantine query joining nine tables.  It would help us to be more categorical in our advice, however, if you were to describe what these none tables are actually modelling.  At present we are working in something of a vacuum.

Answer 3

In my situation, there is a one-to-many relationship between the parent table and each of the 8 child tables.  Therefore, editing the records in each child table is not a problem.

Thanks for your response and for your comment regarding the use of subforms.

Answer 4

I believe Karl is correct. You haven't given us much to go on regarding your table structures. If you want to edit records in child tables, they would all need to be chained one-to-many. This is highly unlikely.

I'm not sure where you read about avoiding the use of subforms but I have never had issues with them.

Answer 5

You may find that your multi-join is not updatable.