Part I: Access Building Blocks
158
Updating a unique index (primary key)
If a query uses two tables involved in a one-to-many relationship, the query must include the pri-
mary key from the one-side table. Access must have the primary key value so that they can find the
related records in the two tables.Replacing existing data in a query with a one-to-many relationship
Normally, all the fields in the many-side table (such as the tblSales table) are updateable in a
one-to-many query. All the fields (except the primary key) in the one-side table (tblCustomers)
can be updated. Normally, this is sufficient for most database application purposes. Also, the pri-
mary key field is rarely changed in the one-side table because it is the link to the records in the
joined tables.Updating fields in queries
If you want to add records to both tables of a one-to-many relationship, include the foreign key
from the many-side table and show the field in the datasheet. After doing this, records can be
added starting with either the one-side or many-side table. The one side’s primary key field is auto-
matically copied to the many side’s join field.If you want to add records to multiple tables in a form (covered in Chapters 7 and 8), remember to
include all (or most) of the fields from both tables. Otherwise, you won’t have a complete set of the
record’s data on your form.Working with the Table Pane
The upper (table) pane of the query designer contains information that is important to your query.
Understanding the table pane and how to work with field lists is critically important to building
complex queries.Cross-Reference
These lines were pre-drawn because you already set the relationships between the tables as described in
Chapter 3.
Looking at the join line
A join line connects tables in the query designer (refer to Figure 4.22). The join line connects the
primary key in one table to the foreign key in another table. The join line represents the relation-
ship between two tables in the Access database. In this example, a join line goes from tblSales
to tblCustomers, connecting ContactID in the tblCustomers table to the Buyer field in
tblSales. The join line is added by Access because relationships were set in the relationship
builder.