Microsoft Office Tutorials and References
In Depth Information
Working with Multiple Related Tables
Although a multivalue lookup field seems to be cumbersome, it can still
give you the results that you want if you simply have your query display the
multiple values separated by commas. If you want to deconstruct your data
some — and ensure that each value in a multivalue field has its own line —
add the Value property to the field name. Here’s how: Instead of entering
just multivalue field name in the query grid, enter multivalue field name.
Value .
In addition to the special instructions about multivalue fields, be aware
of the caveat about lookup fields: The value you see may not be the value
that’s actually stored. (Fortunately, the preceding section offers tips on
using lookup fields in your criteria.)
Working with Multiple Related Tables
One powerful feature of queries allows you to view related fields from
different tables together in a query datasheet. Using your database, you can
create a query to list customer name and contact information with order
dates and numbers, even though two different tables store the data. The
relationship between the two tables is the ContactID field, which is the
primary key of the Address Book table. The same field, ContactID, is in
the Orders table; it identifies the customers who placed each order. (For
more information about relating tables, see Book I, Chapter 3, and Book II,
Chapter 6.)
Book III
Chapter 1
To have Access display data from different tables, you must define a
relationship between the tables. A relationship between tables is created in one
of these ways:
A lookup field exists, creating a relationship between two tables. (For
more on lookup fields, see Book II, Chapter 5.)
A relationship was defined in the Relationships window, as described in
Book II, Chapter 6. (Creating a lookup field automatically creates a
corresponding relationship in the Relationships window.)
Access automatically creates a relationship when it finds related fields
in two tables — that is, if the two fields have the same name and data
type, and one of the matching fields is the primary key of its table.
You create a relationship in Query Design view.
When a relationship exists between two tables displayed in Design view, the
tables are joined by a line, as shown in Figure 1-11.
If you use data from two tables that aren’t directly related, you have to make
sure that any other tables related to the fields you want to display in the
query datasheet appear in Query Design view.
Search JabSto ::




Custom Search