Forum Discussion
Solved
Creating a relationship between one main table and two sub-tables
Hi Everyone, I am creating a client database using access. I have a main table "Contacts" which includes my clients and their information. I also have two other tables: criteria A and criteria B...
What you are describing is actually NOT a really helpful design feature, as a matter of fact. I'll get back to this point after explaining what it is. This is what is referred to as "Subdatasheet". It's a feature of Access which APPEARS to be really handy because it allows the records of a related table on the many side to be shown embedded in a so-called subdatasheet in the one-side table. You can define it here.
That allows you to drag EVERY SINGLE RECORD from the many-side table into the one-side table when you open it in datasheet view. Seems handy, right? It's a drain on performance. And it is extremely unlikely that you will ever show the table directly to a user anyway, so it's sort of a superfluous fillip anyway.
For that reason, most experienced Access developers will take steps to eliminate them as quickly as possible when they encounter them.
For interface functions, rely on the forms that are the interface display tools in Access. If you need to see the two sets of records, rather I should say if you want to show the two sets of records to a user, you'll want to employ a main form/sub form design. In this case, youll need two subforms, one for each of the Criteria tables.
In my more than twenty-five year career I think I've seen one relational database application that employed this feature in a production environment.
What you are describing is actually NOT a really helpful design feature, as a matter of fact. I'll get back to this point after explaining what it is. This is what is referred to as "Subdatasheet". It's a feature of Access which APPEARS to be really handy because it allows the records of a related table on the many side to be shown embedded in a so-called subdatasheet in the one-side table. You can define it here.
That allows you to drag EVERY SINGLE RECORD from the many-side table into the one-side table when you open it in datasheet view. Seems handy, right? It's a drain on performance. And it is extremely unlikely that you will ever show the table directly to a user anyway, so it's sort of a superfluous fillip anyway.
For that reason, most experienced Access developers will take steps to eliminate them as quickly as possible when they encounter them.
For interface functions, rely on the forms that are the interface display tools in Access. If you need to see the two sets of records, rather I should say if you want to show the two sets of records to a user, you'll want to employ a main form/sub form design. In this case, youll need two subforms, one for each of the Criteria tables.
In my more than twenty-five year career I think I've seen one relational database application that employed this feature in a production environment.