This suggests that there is a many-to-many relationship between Testing and
Systems, i.e. each test might cover one or more systems and each system might
be the subject of one or more tests. Right?
To model the many-to-many relationship you'd need another table which has two
foreign keys, one which references the primary key of Testing, the other
which references the primary key of Systems. Bear in mind that if a table
has a multi-column primary key then the foreign key in any other table which
references it must also be multi-columned. Or you can give the referenced
table a 'surrogate' single column primary key such as an autonumber, which
can then be referenced by a single column long integer number column in a
referencing table.
In the Testing form you'd include a continuous subform based on the new table,
linking the subform to the parent form on the key columns by means of the
subform control's LinkMasterFields and LinkChildFields properties. You don't
need to include a control in the subform bound to the column(s) which
references the key of the Testing table as this will have its value
automatically assigned behind the scenes via the linking mechanism. For the
control bound to the column which references the Systems table's key use a
combo box form which the user can select the system form a list drawn from
the Systems table. You'd also need other controls in the subform if there
are other columns in the new table representing attributes of this particular
testing of this particular system. For each system being tested you simply
insert a new record in the subform.
What you should never do in situations like this is include multiple columns
in the Testing table, System Tested I, System Tested 2 etc. That's very bad
design. Each system being tested should be represented a a row in the new
table in the way I described.
Ken Sheridan
Stafford, England
On a related question. I can type this as new if you want me to. I
appreciate all the detail. The more pieces I get of this puzzle everytime
helps me see the "picture."
I have table Testing, CustomerSystemInventory,Systems and Contacts. When I
select customers to test with on the Testing form (related to Testing table),
it dawned on me that a customer can test more than one system at a time,
which I have in a combo that pulls from systems. On the testing table and
testing form will I need something like System Tested 1, System Tested 2? Or
is there an easier way to select all of them like in a multiselect combo? If
so how would it populate on the table?
Firstly rename the Customers table as 'Customers_Old'. Then create three new
tables Customers, Regions and CustomerRegions, the first with columns (fields)
[quoted text clipped - 92 lines]
some practical detail about how to do that with the existing customers? And
how to select the regions on the customer table form for new customers?
