Table1 only 'seems' to hold the data from the other tables. The underlying
real values are in fact the numeric values corresponding to the values of the
primary key column in the referenced tables. One of the problems of
so-called 'lookup fields' is that they do disguise the underlying
relationships in this way. There are other objections to them, however,
which you'll find detailed at:
http://www.mvps.org/access/lookupfields.htm
Nonetheless your tables will still work.
As far as your statistical reports are concerned you can join the tables in
a query to return a 'result table' which is the equivalent of your original
single table. You can then base reports on the query rather than on the
original table. Make sure that where you have primary and foreign key
columns of the same name in the different tables you only return one of these
as a column in the query or there will be ambiguity between the two.
Note that if the you are joining a table to another table where the second
is related many-to-one to the first table, and there are rows in the first
table which don't have matches in the second, then you'll need to use a LEFT
OUTER JOIN if you want the data from all rows in the first table returned.
The default join type is an INNER JOIN, which only returns rows where there
are matches in both tables.
As regards your main form you could base this on a query joining the tables,
as with a report, but you might well find its not updatable. In any case its
not a good way to enter data. A better approach is to have a form based on
the main table and to have within it subforms based on the 'referencing'
tables (those in a many-to-one relationship with the main table), linking the
main form and subforms on the appropriate primary and foreign key fields.
As an alternative to subforms you can have separate forms and open the forms
based on the referencing tables from buttons on the main form so that they
open filtered to the related records. Subforms are generally more
convenient, however, and one thing you can do is to put each subform on
separate pages of a tab control on the main form so that you simply have to
tab to the relevant one when entering or viewing data.
Ken Sheridan
Stafford, England
