Subform join, master/child, reference form control or use query join, which is preferable

Question

I'm an advanced user looking for a discussion in the relative merits of the following ways of connecting subforms:

The main form has 3 fields: ID, month, year, (names simplified for this discussion) that will be required to show appropriate subform record. This is budgetary so there are 15 subforms showing financial data in different ways.

Mainform limited to one record. Must be closed to open another record.

So the three options are:

1. Join the subform using master/child to ID. Subform query still requires 2 additional criteria. This would seem to be the worst solution as the mainform only has one record.

2. Subform queries reference the form controls (mainform!ID, ect) as criteria.

3. Subform queries have 1-1 joins to mainform query on each of the criteria for a total of three joins.

Note that data transfer over the network is a prime concern.

Thanks guys, stackoverflow is the best site on the net.

Answer 1

It's not clear if you are talking about 15 subforms all showing at the same time on a main form or simply showing one subform at a time as it is dynamically selected from a list of 15 possible ones.

If you are showing all of them, then it's probably quite a lot, and it will certainly take a while to execute those 15 queries and render them when the form is open.

1. Master/detail link

You can link multiple fields on a master form/subform, so you are not limited to a single field:

2. Parent references in subforms

Not always elegant, but they work fine since you are not letting the user change the main form's fields. If you were, there are probably circumstances where you would need to re-query the subforms to make sure they update after the change.

3. Joined queries

No exactly sure what you mean there, in practical terms.

---

Some comments

Since you are (rightly) concerned about performance, here are a few pointers that may or may not help in your case:

• Consider whether you really need to display that many subforms all at once.
  Can they be split into smaller groups that could be displayed in a tab control page instead?
  If that's the case, you can easily load them on-demand when the user selects a tab. That way you only load the most important subforms on the first tab, then, if the user needs more info, she can click on another tab page that will load more data.

• Access always tries very hard to limit the amount of data fetched, so if you have master/detail links defined or if you user Parent!ID criteria in your subform's WHERE clauses, they should only pull the filtered data, not everything.
  That said, make sure you have proper indexes set on these fields, otherwise performance will be pretty bad and the database engine will need to pull all data before it can filter it, instead of relying on the indexes to do that.

• If you suspect that Access is pulling more data that needed, you can always construct your subform's queries with ad-hoc SQL that you set dynamically when the main form is loaded. This will ensure that each subform has the exact query is needs. Again, indexes are still as important as ever for good performance.

• If you need to display a lot of data, and if that data doesn't need to be calculated every time the main form is open, you could cache the data into local tables.
  Use INSERT INTO statements with your queries to inject the results into local tables and only use that local data to display in your subforms.
  On your main form, just keep track of when the data was last refreshed and show that date and time somewhere. You could also add a button so they can refresh the data manually again or just do it automatically if you detect that the data is stale.

There are always lots of strategies for improving performance in Access. Of course, make sure you actually have a problem before you try to solve it as it will require more time and resources to solve than just letting Access do the work for you.