Multiple Child tables? Beginner-sorry! (1 Viewer)

[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.

 

[Pat Hartman]

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.

You have a query that is joining the parent table to a child table using an inner join rather than a left join. An inner join asks the database engine to return one row for every match between a record in tbl1 with a record in tbl2. A Left join (or right join) asks the database engine to return all rows in the left (or right) table and any rows in the other table for which there is a match.

As the recordsource for a form, you almost certainly don't need both the parent and the child in the same query. The query bound to the parent form selects data only from the parent table. The query bound to the child form selects only data from the one specific child table.

You didn't ask but I'm going to tell you anyway - there are a number of problems with the schema you posted which should be fixed sooner rather than later.
1. Consistency is your friend. Standardize the way you name your PKs and FKs. Personally, I always use the same name unless I can't (too detailed for this discussion). So if the PK is TestActivityID, then any FK uses the same name. This doesn't cause a problem because only rarely would you ever need both columns in a single query. Some people like to use a suffix so the FK name is ThePKName_FK
2. Never use "ID" as the name of a PK. It has no meaning and just makes it more difficult for someone to look at a table and figure out which fields are PKs and which tables they might be pointing to.
3. Never use special characters (except for the_underscore) or embedded spaces in any object names. Doing so forces you to use square brackets around all your names and will cause Access to actually rename your controls because VBA does not support these characters even though Jet and ACE (the database engines associated with Access) are more flexible.
4. The T1_Test Run and Tes... table is a junction table and is probably not necessary unless a single activity can actually occur in multiple TestRuns. TestRunID belongs in TestActivity instead.
5. TestActivity has parallel relationships with TestRun and TestResult. This is also suspect and more likely the relationship should be TestRun -->TestActivity--->TestResult which is a hierarchical relationship.
6. Do not use acronym unless they are general rather than business specific and totally obvious in context and very standard like DOB for DateOfBirth
7. When you use abbreviations, be consistent. And again, try for standard usage. For example TaskDesc
8. In case you haven't noticed yet, I use CamelCase rather than The_Underscore for separating name parts. It's a preference. I hate having to use the shift key when typing code and The_Underscore requires shifting. Caps do NOT require shifting except when you are defining the object. As you type, intellisense will fix up the case. This is an easy sanity check as you are typing because if VBA didn't proper case your object name, then you probably made a typo.
9. NEVER, EVER use function and property names as object names. You will be especially sorry if you use Name and Date as the names of columns in your tables. Why? VBA will not interpret them as you expect. For example, Me.Name is not going to return the value "Pat Hartman", it is going to return "frmMainForm" and there is nothing you can do about it. So, save yourself the problem and pay attention to the error messages you get from Access when trying to use names like this. Access only complains about the misuse of a couple of property and function names but others will also have problems. Making compound names tends to avoid the issue because most reserved words are simple, common, single words rather than compound words. So LastName, CompName, SalesDT will tend to avoid issues and not require you to memorize a thousand reserved words although over time, you'll develop a sixth sense about what might be reserved.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk