Creating a relationship between one main table and two sub-tables

[EricTao]

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. The two tables have different column titles. In my Contacts table, the primary key is the contact ID. There is also contact ID in both criteria A and criteria B tables. I linked them as a one-to-many relationship (each contact can have several rows of criteria A and criteria B). As a result, in my Contacts table, when I click the little "+" button on the left side of each row, I can see the criteria A table bounded to the contact name. However, I cannot see the criteria B table anywhere. Vice versa, I would only see the criteria B table under each contact. 

Please let me know how to fix this and if there is a better way to plan this. Thanks so much!

[#2 · Accepted solution]

@EricTao 

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.