Microsoft Office Tutorials and References
In Depth Information
Updating Data Using a Query
Although a query datasheet is temporary and its contents are based on the criteria in the
query design grid, you can update the data in a table using a query datasheet. In this case,
Zack has changes he wants you to make to records in the Employer table. Instead of mak-
ing the changes in the table datasheet, you can make them in the EmployerAnalysis query
datasheet. The underlying Employer table will be updated with the changes you make.
To update data using the EmployerAnalysis query datasheet:
1. For the record with EmployerID 10135 (The Adele Bannister House), click the check box in
the Website field to place a check mark in it.
2. For the record with EmployerID 10152 (Alpine Touring Center), change the ContactFirstName
field value to Mary and change the ContactLastName field value to Grant .
3. Click the Close Window button on the menu bar to close the query. Note that the
EmployerAnalysis query appears in the list of queries.
4. Click the Restore Window button
on the menu bar to return the Database window to
its original size.
Now you will check the Employer table to verify that the changes you made in the query
datasheet were also made to the Employer table records.
5. Click Tables in the Objects bar of the Database window, click Employer in the list of
tables, and then click the Open button. The Employer table datasheet opens.
6. For the record with EmployerID 10135, scroll the datasheet to the right to verify that the
Website field contains a check mark. For the record with EmployerID 10152, scroll to the
right to see the new contact information (Mary Grant).
7. Click the Close button
on the Employer table window to close it.
Matt also wants to view specific information in the Northeast database. However, he needs
to see data from both the Employer table and the Position table at the same time. To view data
from two tables at the same time, you need to define a relationship between the tables.
Defining Table Relationships
One of the most powerful features of a relational database management system is its abil-
ity to define relationships between tables. You use a common field to relate one table to
another. The process of relating tables is often called performing a join . When you join
tables that have a common field, you can extract data from them as if they were one
larger table. For example, you can join the Employer and Position tables by using the
EmployerID field in both tables as the common field. Then you can use a query, a form, or
a report to extract selected data from each table, even though the data is contained in two
separate tables, as shown in Figure 3-5. In the Positions query shown in Figure 3-5, the
PositionID, PositionTitle, and Wage columns are fields from the Position table, and the
EmployerName and StateProv columns are fields from the Employer table. The joining of
records is based on the common field of EmployerID. The Employer and Position tables
have a type of relationship called a one-to-many relationship.