Multiple Child tables? Beginner-sorry!

[Mike_P]

Hi
I am just a beginner and am noticing that when starting a project that is not just a a simple one- or two-table DB that I am confronted with lots of roadblocks that raise questions that no intro course will answer anymore and my experience is sadly insufficient. This is highly frustrating but I am hanging in there because I am aware of the time saving potential of getting it right.
I am trying to create a DB to record electrical equipment tests (T_Test_Activity) and their individual results (T Test Results) and that has required multiple tables.
First of all I wonder if it is feasible to have multiple child tables (as shown) related to the parent's primary key (auto number), in this case I have the parent table describing the test details and a one-to-many child that records the results of this test. I am also trying to tie the test table to a test run table (via a join table many-many) where the aim is to select or identify a group of tests to be run for a particular purpose and that will be named. (forgive me my multiple syntax rule issues here...)

While Access appears to allow this I cannot see the original relation in the table view of the test activity table anymore (by pressing the little cross in the table line, this will now only show the join table relation:

Even when I delete both relations (and refresh and save) it still and persistently shows the join table relation (that was deleted). I am a bit lost now as how to fix this, may I need to use SQL commands to fix the table setup?

Thanks for your patience, I probably have attempted to achieve much more than I have the skills to manage, the form side of it is an equally frightening prospect.

Cheers

Mike

 

[ebs17]

You cannot read relationships from the table view. The SubDatasheet property used can only display one dependent table at a time.
However, this feature is a performance brake in the overall use of the application and is therefore generally switched off (for me).

For viewing and editing, constructions are built from main and subforms, whereby each form usually uses exactly one table as the data source. Such constructions model relationships.

First of all I wonder if it is feasible to have multiple child tables (as shown) related to the parent's primary key (auto number)

This is of course possible.
Overall, the number is limited because relationships with referential integrity set use an index per key and the number of indexes per table is limited, which can be found in the specifications.

 

[CJ_London]

the functionality you are using is limited, most don't use it - in the table properties, check the subdatasheet name property - change to the table you require

you may also need to populate the linkchild/master properties as well

You would be better off creating a mainform/subform(s)

 

[Mike_P]

Thanks for your swift replies, will investigate. Yes, will need sub forms of course.
Am struggling with these as well at the moment btw as I presently cannot see parent records in the form that have no children anymore i.o.w. I cannot access tests that have no results yet, which is the whole point of the DB (to add them) - the form only shows me tests that have results and a blank record will blank everything.... it is an adventure! Allgood fun!

 

[jdraw]

Mike,

Many will recommend that you do not adopt a naming convention that allows embedded spaces in object names. (Spaces in names will lead to syntax errors (we've all been there!)

I would also suggest that you model your tables and some sample data. Ensure that you can access the data you need with some sample transactions before getting too deep into physical database. As a self-identified beginner, take small pieces of the bigger issue/opportunity and learn by doing small sets of logic.

Welcome to AWF by the way.

 

[The_Doc_Man]

A question you asked is whether you can have multiple child tables that depend on the same PK.

In general, yes, with warnings. As long as the child tables represent totally different things that also do not depend on each other, absolutely yes.

In the picture you showed, however, my question is related to the visible names. You have a [Test Activity], a [Test Run and (something else)], and a [Test Result], while also having a grandchild table called [Test Run]. This is suspicious since I would have thought that a test result would depend on which test run obtained that result - but it doesn't appear to express that relationship. Since I don't know what your design goals included, all I can do is suggest there is a suspicious element, but I could be wrong and it is totally proper. It just "looks" suspicious.

In the U.S. Navy, we had a personnel database with 240 tables, some of which depended on the same key (a person ID). We had a person's rank/rate history as they were promoted through the ranks. Then we had a dependents table, since persons could have spouses and children. We had a billet history table which was independent of rank/rate since persons could have multiple job (billet) assignments. We had a salary history since there IS such a thing as step-raise within rank and we needed that to be different than the rank/rate history in order to determine more easily not only what we owed someone, but WHEN we owed it. That's four child tables right there before we even stop to consider awards, uniform allowances, etc. Also, I didn't design that table. It sort of "grew in the telling" and thus might not have been optimum. But military organizations tend to have "change inertia" so we had what we had and it was normalized.

 

[theDBguy]

Thanks for your swift replies, will investigate. Yes, will need sub forms of course.
Am struggling with these as well at the moment btw as I presently cannot see parent records in the form that have no children anymore i.o.w. I cannot access tests that have no results yet, which is the whole point of the DB (to add them) - the form only shows me tests that have results and a blank record will blank everything.... it is an adventure! Allgood fun!

Hi. Welcome to AWF!

That is why you have to use a form/subform setup. For example, the main form can show ALL the equipment in the system, and the subform can show all the tests performed on each equipment. Equipments without any tests will still show up in the main form with a blank subform.