Objective group 1. Manage databases – MOS Study Guide for Microsoft Access Expert Exam MO-500

Objective group 1

Manage databases

The skills tested in this section of the Microsoft Office Specialist Expert exam for Microsoft Access 365 and Microsoft Access 2019 relate to managing database structure, relationships, printing, and exporting. Specifically, the following objectives are associated with this set of skills:

1.1 Modify database structure

1.2 Manage table relationships and keys

1.3 Print and export data

Every Access database exists within a larger ecosystem of objects, including tables, queries, forms, and reports. Mastering database management requires knowing not only how to import data into Access, but also how to work with and create sophisticated relationships between objects.

This chapter guides you in studying methods for importing data, deleting database objects, working with objects in the Navigation Pane, understanding table relationships, setting primary and foreign keys, enforcing referential integrity, configuring print options, and exporting database objects.

Objective 1.1: Modify database structure

Import objects or data from other sources

Rather than you having to create tables, queries, forms, reports, macros, or modules from scratch, the object or data you need might already reside in another Access database. In that case, you can import one or more objects into your current Access database. If you import a table, you also have the option of linking to the data source, which means if the source changes, so does your version of the data.

See Also For more information on importing data into tables, see “Objective 2.1: Create tables.” To learn more about linked tables, see “Create linked tables from external sources” in that same objective.

Importing objects from an Access database requires selecting the source data and how you want the objects imported.

You use the Import Objects dialog box to select what you want to import using the six tabs, one for each object type: Tables, Queries, Forms, Reports, Macros, and Modules. On each tab, Access displays a list of the available objects of that type in the source data.

Options for importing database objects from an Access database.

Clicking the Options button displays the following importing options:

  • Import The Relationships option determines whether table relationships are preserved in the import operation; selecting the Menus And Toolbars option imports any custom menus and toolbars from databases created in versions of Access prior to Access 2007; selecting Import/Export Specs includes any import or export specifications defined in the source database; selecting Nav Pane Groups imports any custom Navigation Pane groups set up in the source database; and selecting All Images And Themes includes these elements with the import.

  • Import Tables Importing both the definition and data for a table means that you end up with the entire table object: its structure and all of its records. Importing just the definition means you end up with a table that has the same structure as the source table but does not contain any records.

  • Import Queries Importing a query as a query imports just the query definition. The database must have a table of the same name as the one the query uses, with compatible fields. Importing a query as a table creates a new table with a datasheet that includes the query’s results. Any records or fields the query definition excludes are excluded.

Exam Strategy In the Access Expert exam, you need to know not only how to import data into Access, but also how to export data from Access to other formats. Therefore, be sure to also study the material in “Export objects to alternative formats” in “Objective 1.3: Print and export data.”

If you often perform a particular import operation, it can be time-consuming to repeat the import steps over and over. You can reduce time and effort by saving the steps. Doing so enables you to perform the import operation in the future with just a few mouse clicks.

You can optionally save the steps for an import operation.

To import objects from an Access database

  1. On the External Data tab, in the Import & Link group, click New Data Source.

  2. Click From Database, then click Access. The Get External DataAccess Database dialog box opens.

  3. In the File name text box, type the path and file name for the Access database file. Alternatively, click Browse to display the File Open dialog box, select the Access database file, and then click Open.

  4. Select the Import Tables, Queries, Forms, Reports, Macros, and Modules Into The Current Database check box.

  5. Click OK. The Import Objects dialog box opens.

  6. Use the tabs to click each object you want to import. If you select an object by mistake, click it again to deselect it.

    Tip Within each tab, you can quickly select every object in the tab by clicking Select All. To start over in a tab, click Deselect All.

  7. Click Options to display the Import options at the bottom of the dialog box, then set the options for the import operation:

    • In the Import group, select the check box for each type of object you want to include in the import.

    • If you’re importing one or more tables, in the Import Tables group, click Definition And Data or Definition Only.

    • If you’re importing one or more queries, in the Import Queries area, click As Queries or As Tables.

  8. Click OK. The Save Import Steps screen appears.

  9. If you want to save the import steps, see the “To save import steps” procedure covered next. Otherwise, click Close. Access imports the objects into the database.

To save import steps

  1. Perform an import, as shown in the “To import objects from an Access database” procedure, earlier in this task.

  2. On the Save Import Steps screen of the operation, select Save import steps. Additional text boxes appear in the dialog box.

  3. In the Save As text box, type a name for the saved import steps.

  4. In the Description text box, type a description for the steps.

  5. Select Create Outlook Task if you want Access to create an Outlook task that reminds you when it’s time to run the import. The task includes a Run Import button that enables you to launch the import operation from Outlook.

    Exam Strategy In the Access Expert exam, you might be asked to create a recurring Outlook task for running an import operation, so be sure you know how to modify the Outlook task so that it recurs on a regular schedule.

  6. Click Save Import. Access saves the operation’s steps. If you chose to create an Outlook task, the task opens in Outlook.

To run saved import steps

  1. On the External Data tab, in the Import & Link group, click Saved Imports. The Manage Data Tasks dialog box opens with the Saved Imports tab displayed.

  2. On the Saved Imports tab, click the import that you want to run.

    Tip If you didn’t create an Outlook task for the import when you first ran the operation, you can do so from the Manage Data Tasks dialog box by clicking the import and then clicking Create Outlook Task.

  3. Click Run. Access runs the import operation and then displays a message saying that the objects were imported successfully.

  4. Click OK to return to the Manage Data Tasks dialog box.

  5. Click Close.

Delete database objects

Database objects normally store important or useful information, so the person responsible for the database takes steps to keep that data safe. However, some objects become expendable over time because the data is now out of date, inaccurate, or redundant. In such cases, you can delete any object from your database—even tables that contain records.

IMPORTANT Be careful not to delete anything that you need to keep because it is not possible to undo an object deletion.

Access databases depend on the relationship between tables to preserve the integrity of data and to eliminate redundant data. You can delete most types of database objects, including queries, forms, and reports, without affecting underlying relationships. However, Access prevents you from deleting a table that is related to another table without first deleting the relationship. Access deletes the relationship for you if you agree.

Access warns you that a table cannot be deleted until its relationships are removed.

See Also For more information about table relationships, see “Objective 1.2: Manage table relationships and keys.”

To delete a database object

  1. Close the object that you want to delete.

  2. In the Navigation Pane, right-click the object, then click Delete.

  3. In the Microsoft Access message box asking you to confirm that you want to delete the object and remove it from all groups, click Yes.

  4. If you are deleting a table and Access prompts you to confirm that you want Access to delete the relationship, click Yes to remove the relationship and delete the table.

Hide and display objects in the Navigation Pane

This section focuses on the Access Navigation Pane and how you can modify and organize it to display different views of the objects in a database. The ability to modify the Navigation Pane means that it can serve the needs of a range of users—from a database’s designer to its casual users.

For example, you can have multiple object types (tables, queries, forms, and so on) grouped under a heading such as Customers & Orders instead of headings such as Tables, Queries, and Forms. Headings such as Customers & Orders help clarify functional areas of the database and help users find forms and queries related to the area they are working with.

When you change how database objects are displayed in the Navigation Pane, you work with a menu that has several options. This menu arranges commands in two areas, marked by the shaded labels Navigate To Category and Filter By Group. The Navigate To Category area includes categories such as Object Type, Tables And Related Views, Created Date, and Modified Date. For each category, the Filter By Group area provides options that you can apply to display a subset of objects. For example, if you select Modified Date in the Navigate To Category area, you can then filter the list by selecting Today, Three Weeks Ago, Yesterday, Older, or All Dates. For the Object Type category, you can filter the Navigation Pane to view only objects of a specific type or view all objects.

The Tables And Related Views category displays each table in the database together with other database objects that depend on it. Using this view is helpful when you make changes to a table’s design. For example, by choosing the Tables And Related Views category and then choosing a single table in the Filter By Group area, you can see which objects depend on the table, and you can review the design of those objects to be sure that the changes you want to make to the table won’t affect the other objects in ways you don’t intend.

You can also sort the list of objects in a category, showing them in ascending or descending order or by name, type, and date criteria. You can also change the level of detail that is shown for objects in the Navigation Pane. You can display a list of names with a small icon, show a larger icon next to the name of the object, or show details such as the created and modified date for the object.

You can set up Navigation Pane categories and groups of your own in the Navigation Options dialog box.

Organize the Navigation Pane into categories and groups.

The categories defined for the database appear in the list at the left, and each group defined for a category appears in the list at the right. You can hide a group from being displayed in the Navigation Pane, or select display options to show hidden and system objects in the Navigation Pane or to show or hide the search bar. By using the Open Objects With options, you can control whether an object opens when it is clicked (similar to a hyperlink) or double-clicked.

When you work with the default Custom category, the Groups For list includes a group named Custom Group 1 and a group named Unassigned Objects, which is a built-in group that contains all the objects in a database.

Tip You can reposition a custom category or group by using the arrows that appear beside an item’s name when you select it. You cannot place a custom category above the two built-in categories or place a custom group below the built-in group Unassigned Objects.

When you add a database object to a custom group, you add only a shortcut to that object, not the object itself. This means that you can delete a shortcut from a custom group without deleting the database object.

To create and modify Navigation Pane categories and groups

  1. Right-click the Navigation Pane title bar, then click Navigation Options.

  2. In the Navigation Options dialog box, do any of the following, then click OK:

    • To add a category, click Add Item and then enter a name for the category.

    • To rename the selected category, click Rename Item, edit the name, and then press Enter.

    • To delete the selected category, click Delete Item, and then in the message box asking you to confirm the deletion, click OK.

    • To add a group to the selected category, click Add Group, then enter a name for the group.

    • To rename the selected group, click Rename Group, edit the name, and then press Enter.

    • To delete the selected group, click Delete Group, and then in the message box asking you to confirm the deletion, click OK.

      Tip You can rename and delete only custom groups.

    • In the Display Options area, select or clear the check boxes for showing hidden objects, system objects, and the search bar.

    • In the Open Objects With area, click Single-click or Double-click.

To add objects to a group

  1. In the Navigation Pane, display the category that contains the group.

  2. In the Unassigned Objects group, right-click the object you want to add to the group, click Add To Group, and then select the group.

To hide an object in a group

  • In the Navigation Pane, right-click the object, then click Hide In This Group.

The practice file for these tasks is located in the MOSAccessExpert2019\Objective1 practice file folder. The folder also contains a result file that you can use to check your work. Save the results of the tasks in the same folder.

  • From the Access Start screen or the New page of the Backstage view, do the following:

    • Create a blank desktop database. (Depending on your installation of Access, the template might be named Blank database or Blank desktop database.) Name the database MOSDatabase and save it in the practice file folder.

    • Import the Customers and Orders table definitions (not the data) from the AccessExpert_1-1 database.

  • Open the AccessExpert_1-1 database and do the following:

    • If the Info bar appears, click the Enable Content button.

    • Delete the Table1 table.

    • Create a Navigation Pane category named Northwind, and groups named Customers, Products, and Employees.

    • Add the Customers, Order Details, Orders, and Shippers tables to the Customers group. Add the Categories, Products, and Suppliers tables to the Products group, and add the Employees table to the Employees group.

    • Open the AccessExpert_1-1_results database. Compare the two databases to check your work. Then close the open databases.

Objective 1.2: Manage table relationships and keys

Most database applications (and all well-designed database applications) store their information in multiple tables. Although most of these tables have nothing to do with one another (for example, tables of customer information and employee payroll data), it’s likely that at least some of the tables do contain related information (such as tables of customer information and customer orders).

Working with multiple, related tables presents you with two challenges: you need to design your database so that the related data is accessible, and you need to set up links between the tables so that the related information can be retrieved and worked with quickly and easily. This topic tackles both challenges and shows you how to exploit the full multiple-table powers of Access.

Set primary keys

It is advantageous to be able to uniquely identify each record. For example, if you have a Contacts table that has multiple entries for people named John Smith, how do you easily differentiate between them? In Access, you do this by designating a primary key, which is the field by which each record will be uniquely identified and by which relationships between tables can be created. You may use any field that you like for the primary key. You can use an AutoNumber field to allow Access to assign numbering for you, or you can use a Number or Text field. The only limitations are that the field must contain a unique value for each record and it cannot be left blank. A table usually has only one primary key. When a unique combination of two or more fields’ values forms the primary key, it is called a composite key.

You set the primary key for a table using Design view. Access designates the primary key by displaying a key icon to the left of the field name.

Access designates the primary key with a lock icon.

To open a table in Design view

  • If the table is closed, right-click the table in the Navigation Pane, then click Design View.

  • If the table is open in another view, on the Design tool tab, in the Views group, click View, then click Design View.

Exam Strategy In the Access Expert exam, it is expected that you know how to switch between views for any database object, so be sure you are familiar with these methods, which can be generalized to any Access object.

To set a primary key

  1. Open the table in Design view.

  2. Select the field or fields you want to designate as the table’s primary key. To select multiple fields, press Ctrl and select the fields.

  3. On the Design tool tab, in the Tools group, click Primary Key.

To remove the primary key designation from a field

  1. Open the table in Design view.

  2. Select the field or fields from which you want to remove the primary key designation.

  3. On the Design tool tab, in the Tools group, click Primary Key.

Understand relationships

Why do you need to be concerned with multiple tables? Isn’t it easier to work with one large table instead of two or three medium-sized ones? To answer these questions and demonstrate the problems that arise when you ignore relational database models, take a look at a simple example: a table of sales leads.

The following table outlines the structure of a simple table (named Leads) that stores data on sales leads.

Field

Description

LeadID

The primary key

FirstName

The contact’s first name

LastName

The contact’s last name

Company

The company that the contact works for

Address

The company’s address

City

The company’s city

State

The company’s state

PostalCode

The company’s postal or ZIP code

Phone

The contact’s phone number

Fax

The contact’s fax number

Source

Where the lead came from

Notes

Notes or comments related to the sales lead

This structure works fine until you need to add two or more leads from the same company (a not-uncommon occurrence). In this case, you end up with repeating information in the Company, Address, City, and State fields. (The PostalCode field also repeats, as do, in some cases, the Phone, Fax, and Source fields.)

All this repetition makes the table unnecessarily large, which is bad enough, but it also creates three major problems:

  • During data entry, the repeated information must be entered for each lead from the same company.

  • Entering the same data repeatedly increases the chances of entering that data either incorrectly (due to typos and other errors) or inconsistently (for example, entering St. in one field and Street in another).

  • If any of the repeated information changes (such as the company’s name or address), each corresponding record must be changed.

One way to eliminate the repetition and solve the data entry and maintenance inefficiencies is to change the table’s focus. As it stands, each record in the table identifies a specific contact in a company. But it’s the company information that repeats, so it makes sense to allow only one record per company. You can then include separate fields for each sales lead within the company. The new structure might look something like the one shown in the following table.

Field

Description

LeadID

The primary key

Company

The company’s name

Address

The company’s address

City

The company’s city

State

The company’s state

PostalCode

The company’s postal or ZIP code

Phone

The company’s phone number

Fax

The company’s fax number

First_1

The first name of contact #1

Last_1

The last name of contact #1

Source_1

Where the lead for contact #1 came from

Notes_1

Notes or comments related to contact #1

First_2

The first name of contact #2

Last_2

The last name of contact #2

Source_2

Where the lead for contact #2 came from

Notes_2

Notes or comments related to contact #2

First_3

The first name of contact #3

Last_3

The last name of contact #3

Source_3

Where the lead for contact #3 came from

Notes_3

Notes or comments related to contact #3

In this setup, the company information appears only once, and the contact-specific data (I’m assuming this involves only the first name, last name, source, and notes) appears in separate field groups (for example, First_1, Last_1, Source_1, and Notes_1). This setup solves the earlier problems, but at the cost of a new dilemma: the structure as it stands will hold only three sales leads per company. Of course, it’s entirely conceivable that a large firm might have more than three contacts—perhaps even dozens. This raises two unpleasant difficulties:

  • If you run out of repeating groups of contact fields, new ones must be added. Although this might not be a problem for the database designer, most data entry clerks generally don’t have access to the table design (nor should they).

  • Empty fields take up as much disk real estate as full ones, so making room for, say, a dozen contacts from one company means that all the records that have only one or two contacts have huge amounts of wasted space.

To solve the twin problems of repetition between records and repeated field groups within records, you need to turn to the relational database model. This model was developed by Dr. Edgar Codd of IBM in the early 1970s. It was based on a complex relational algebra theory, so the pure form of the rules and requirements for a true relational database setup is quite complicated and decidedly impractical for business applications. However, a simplified version of the model requires just three redesign steps:

  1. Separate the data (discussed immediately following these steps).

  2. Add foreign keys to the tables (see “Set foreign keys,” later in this task).

  3. Establish a link (that is, a relationship) between the related tables (see “Set relationships,” later in this task).

After you know which fields you need to include in your database application, the first step in setting up a relational database is to divide these fields into separate tables where the “theme” of each table is unique. In technical terms, each table must be composed of only entities (that is, records) from a single entity class.

For example, the table of sales leads you saw earlier dealt with data that had two entity classes: the contacts and the companies for which they worked. Every one of the problems encountered with that table can be traced to the fact that we were trying to combine two entity classes into a single table. The first step toward a relational solution is to create separate tables for each class of data. To that end, the following shows the table structure of the contact data (the Contacts table):

Field

Description

ContactID

The primary key

FirstName

The contact’s first name

LastName

The contact’s last name

Phone

The contact’s phone number

Fax

The contact’s fax number

Source

Where the lead came from

Notes

Notes or comments related to the sales lead

And the following table shows the structure of the company information (the Companies table). Note, in particular, that both tables include a primary key field.

Field

Description

CompanyID

The primary key

CompanyName

The company’s name

Address

The company’s address

City

The company’s city

State

The company’s state

PostalCode

The company’s postal or ZIP code

Phone

The company’s phone number (main switchboard)

Set foreign keys

At first glance, separating the tables seems self-defeating because, if you’ve done the job properly, the two tables will have nothing in common. So, the second step in this relational redesign is to define the commonality between the tables.

In the sales leads example, what is the common ground between the Contacts and Companies tables? It’s that every one of the leads in the Contacts table works for a specific firm in the Companies table. What’s needed is some way of relating the appropriate information in Companies to each record in Contacts (without, of course, the inefficiency of simply cramming all the data into a single table, as we tried earlier).

The way you do this in relational database design is to establish a field that is common to both tables. You can then use this common field to set up a link between the two tables. The field you use must satisfy three conditions:

  • It must not have the same name as an existing field in the other table.

  • It must uniquely identify each record in the other table.

  • To save space and reduce data entry errors, it must be the smallest field that satisfies the two preceding conditions.

In the sales leads example, you need to add a field to the Contacts table that establishes a link to the appropriate record in the Companies table. The CompanyName field uniquely identifies each firm, but it’s too large to be of use. The Phone field is also a unique identifier and is smaller, but the Contacts table already has a Phone field. The best solution is to use CompanyID, the Companies table’s primary key field. The following table shows the revised structure of the Contacts table that includes the CompanyID field.

Field

Description

ContactID

The primary key

CompanyID

The Companies table foreign key

FirstName

The contact’s first name

LastName

The contact’s last name

Phone

The contact’s phone number

Fax

The contact’s fax number

Source

Where the lead came from

Notes

Notes or comments related to the sales lead

When a table includes a primary key field from a related database, the field is called a foreign key. Foreign keys are the secret to successful relational database design.

To set a foreign key in a table

  1. Open the table in Design view.

  2. In the Field Name column, enter the name of the foreign key field.

  3. In the Data Type column, select the data type that matches the data type of the field in the other table (usually Number).

  4. Save the changes to the table.

Understand relational database models

Depending on the data you’re working with, you can set up one of several relational database models. In each of these models, however, you must differentiate between a child table (also called a dependent table or a controlled table) and a parent table (also called a primary table or a controlling table). The child table is the one that is dependent on the parent table to fill in the definition of its records. The Contacts table, for example, is a child table because it is dependent on the Companies table for the company information associated with each person.

You should consider three main relational database models:

  • One-to-many The most common relational model is one where a single record in the parent table relates to multiple records in the child table. This is called a one-to-many relationship. The sales leads example is a one-to-many relationship because one record in the Companies table can relate to many records in the Contacts table (in other words, you can have multiple sales contacts from the same firm). In these models, the “many” table is the one where you add the foreign key. Another example of a one-to-many relationship is an application that tracks accounts receivable invoices. You need one table for the invoice data (Invoices) and another for the customer data (Customers). In this case, one customer can place many orders, so Customers is the parent table, Invoices is the child table, and the common field is the Customer table’s primary key.

  • One-to-one If your data requires that one record in the parent table be related to only one record in the child table, you have a one-to-one model. The most common use of one-to-one relationships is to create separate entity classes to enhance security. In a hospital, for example, each patient’s data is a single entity class, but it makes sense to create separate tables for the patient’s basic information (such as the name and address) and medical history. This setup enables you to add extra levels of security to the confidential medical data (such as a password). The two tables then become related based on a common PatientID key field. Another example of a one-to-one model is employee data. You separate the less sensitive information such as job title and startup date into one table and restricted information such as salary and commissions into a second table. If each employee has a unique identification number, you use that number to set up a relationship between the two tables. Note that in a one-to-one model, the concepts of child and parent tables are interchangeable. Each table relies on the other to form the complete picture of each patient or employee.

  • Many-to-many In some cases, you might have data in which many records in one table can relate to many records in another table. This is called a many-to-many relationship. In this case, there is no direct way to establish a common field between the two tables. To see why, consider an accounts receivable application, which might include a table of invoice data and a table of product information. The idea here is that a given product can appear in many invoices and any given invoice can contain many products. This is a many-to-many relationship, and it implies that both tables are parents (or, to put it another way, neither table is directly dependent on the other). But relational theory says that a child table is needed to establish a common field. In this case, the solution is to set up a third table—called a linking table (or sometimes a junction table or relation table—that is the child of both the original tables. In the example, the relation table contains the detail data for each invoice, as well as foreign keys from both Invoices (InvoiceID) and Products (ProductID). The following shows a simplified example of a linking table:

    Field

    Description

    DetailID

    The primary key

    InvoiceID

    The foreign key from the Invoices table

    ProductID

    The foreign key from the Products table

    Quantity

    The quantity ordered

After you have your foreign keys inserted into your tables, the final step in designing your relational model is to establish a link between the two tables. This step is covered in the next section.

Set relationships

Now that you know the theory behind the relational model, you can turn your attention to creating and working with relationships between tables, which is what this section is all about. In Access, you use the Relationships window to create and manage relationships between tables.

You use the Relationships window to establish relations between tables.

Observe the lines that connect each table in the Relationships window. These lines are called join lines and each join line connects the two fields that contain the related information. For example, the Suppliers and Products tables are joined on the common SupplierID field. In this case, SupplierID is the primary key field for the Suppliers table, and it appears as a foreign key in the Products table. This connection lets you relate any product to its corresponding supplier data.

The symbols attached to the join lines tell you the type of relation. In the join between the Suppliers and Products tables, for example, the Suppliers side of the join line has a 1, and the Products side of the line has an infinity symbol (∞). This stands for “many,” so you interpret this join as a one-to-many relation.

Access lets you set up two main types of joins:

  • Inner join An inner join includes only those records in which the related fields in the two tables match each other exactly (which is why this type of join is often called an equijoin). This is the most common type of join.

  • Outer join An outer join includes every record from one of the tables and only those records from the other table in which the related fields match each other exactly. In the sales leads example, it’s possible that there might be companies for which no contacts have yet been established. Creating an inner join between the Company and Contacts tables shows you only those firms that have existing contacts. However, setting up an outer join shows all the records in the Companies table, including those in which there is no corresponding record in the Contacts table.

An outer join is also called a left-outer join. To see why, consider a one-to-many relation. Here, the “left” side is the “one” table, and the “right” side is the “many” table. So, this type of join includes every record from the one (left) side and only those matching records from the many (right) side. You use the term left-outer join when you need to differentiate it from a right-outer join. In a one-to-many relationship, this type of join includes every record from the many (right) side and only those matching records from the one (left) side.

If you need to establish a new relationship between two tables, you open the Relationships window, add the tables, create the relationship, and then specify the details using the Edit Relationships dialog box.

You use the Edit Relationships dialog box to configure a relationship between two tables.

In Access, a table’s dependencies are those database objects that rely on data from the table. For example, an Orders table might depend on data from a Customers table because you have set up a relationship between them. Similarly, queries or forms may depend on the table.

You can view an object’s dependencies from the Object Dependencies task pane. This is easier than trying to decipher the relationships in the Relationships window, particularly in a complex database. The Relationships window does not need to be open for you to view object dependencies.

Tip Viewing an object’s dependencies in Access requires that you enable the Name AutoCorrect feature, which tracks object name changes. On the File tab, click Options, click the Current Database tab, select the Track Name AutoCorrect Info check box, and then click OK when Access asks you to confirm. Click OK to close the Access Options dialog box, then close and reopen the database when Access prompts you.

You use the Object Dependencies pane to see which objects have a relationship with the selected object.

To open the Relationships window

  • On the Database Tools tab, in the Relationships group, click Relationships.

To display tables in the Relationships window

  1. On the Design tool tab, in the Relationships group, click Add Tables.

  2. In the Show Table dialog box, select the tables, then click Add.

    Tip Hold down the Ctrl key to select multiple tables.

To remove tables from the Relationships window

  • In the Relationships window, do either of the following:

    • To remove one table, click the table to select it. Then on the Design tool tab, in the Relationships group, click Hide Table.

    • To remove all tables, on the Design tool tab, in the Tools group, click Clear Layout.

To display relationships in the Relationships window

  • In the Relationships window, do either of the following:

    • To display all direct relationships of a specific table, click the table to select it. Then on the Design tool tab, in the Relationships group, click Direct Relationships.

    • To display all relationships in the database, on the Design tool tab, in the Relationships group, click All Relationships.

To create a table relationship

  1. Open the Relationships window. If the tables you want to create relationships between aren’t displayed in the Relationships window, add them.

  2. Arrange the table boxes so that in each box you can see the fields you want to use for the join.

  3. Drag the related field from one table and drop it on the related field in the other table. Access displays the Edit Relationships dialog box.

  4. The grid should show the names of the fields in each table that you want to relate. If not, click the arrow to use the list in one or both cells to click the correct field or fields.

  5. To set the type of join, click Join Type to display the Join Properties dialog box. Here, option 1 corresponds to an inner join, option 2 corresponds to a left-outer join, and option 3 corresponds to a right-outer join. After you’ve clicked the option you want, click OK to return to the Edit Relationships dialog box.

    You use the Join Properties dialog box to specify the join type for the relationship.

    See Also For information on enforcing referential integrity, see “Enforce referential integrity,” later in this task.

  6. Click Create. Access establishes the relationship and displays a join line between the two fields.

To modify a relationship

  1. Open the Relationships window and do either of the following:

    • Click the relationship line between two tables. Then on the Design tool tab, in the Tools group, click Edit Relationship.

    • Right-click the relationship line between two tables, then click Edit Relationship.

  2. In the Edit Relationships dialog box, modify the table or query selections, the join type, or the options, and then click OK.

To remove a relationship

  • If you no longer need a join, you can remove it by clicking the join line and pressing Delete, or by right-clicking the join line and then clicking Delete. When Access asks you to confirm the deletion, click Yes.

To view an object’s dependencies

  1. If the Relationships window is open, on the Design tab, in the Relationships group, click Close, then click Yes if Access prompts you to save the layout.

  2. In the Navigation Pane, click the object that you want to examine.

  3. On the Database Tools tab, in the Relationships group, click Object Dependencies. Access displays the Object Dependencies task pane.

  4. Select the type of dependency you want to view:

    • Objects that depend on me Select this option to see objects that get data from the object you chose in step 2.

    • Objects that I depend on Select this option to see objects from which the object you chose in step 2 gets data.

Enforce referential integrity

Database applications that work with multiple, related tables need to enforce referential integrity rules. These rules ensure that related tables remain in a consistent state relative to one another.

One cause of integrity loss in a database is when you make changes to a primary key value in the parent table. If that new key value isn’t also applied to each child table, then the relationship between the parent table and its child tables is broken. For example, if you change a CompanyID value in the Companies table, all related records in the Contacts table should have their CompanyID fields updated.

Another cause of integrity loss is when you delete a record in a parent table but you don’t also delete the related records on all child tables. In the sales leads application, for example, suppose the Companies table includes an entry for “ACME Coyote Supplies” and that the Contacts table contains three leads who work for ACME. What would happen if you deleted the ACME Coyote Supplies record from the Companies table? The three records in the Contacts table would no longer be related to any record in the Companies table. Child records without corresponding records in the parent table are called, appropriately enough, orphans. This situation leaves your tables in an inconsistent state, which can have unpredictable consequences. For example, certain types of queries may fail because Access can’t properly relate the underlying tables.

Applying primary key parent table changes to child tables and preventing orphaned records is what is meant by enforcing referential integrity.

To enforce referential integrity

  1. Either create, but don’t complete, a relationship between two tables, or edit an existing relationship.

  2. In the Edit Relationships dialog box, select the Enforce Referential Integrity check box.

  3. Select the Cascade Update Related Fields check box if, when you make changes to a primary key value in the parent table, you want Access to update the new key value for all related records in all child tables.

  4. Select the Cascade Delete Related Fields check box if, when you delete a record from the parent table, you want Access to delete all related records in all child tables.

  5. Click Create (for a new relationship) or OK (for an edited relationship).

The practice file for these tasks is located in the MOSAccess2016\Objective1 practice file folder. The folder also contains a result file that you can use to check your work.

  • Open the AccessExpert_1-2 database from the practice file folder, then do the following:

    • If the Info bar appears, click the Enable Content button.

    • Open the Categories table in Design view.

    • Set the CategoryID field as the table’s primary key.

    • Save your changes and then close the table.

    • Open the Suppliers table in Design view.

    • Set the SupplierID field as the table’s primary key.

    • Save your changes, then close the table.

    • Open the Products table in Design view.

    • Set the ProductsID field as the table’s primary key.

    • Add two foreign keys to the Products table: CategoryID and SupplierID (use the Number data type for both).

    • Save your changes, then close the table.

  • Open the Relationships window and do the following:

    • Add the Categories and Products tables in the window.

    • Create a relationship between the Category table (CategoryID field) and the Products table (CategoryID field).

    • Edit the relationship between the Category table and the Products table to enforce referential integrity.

    • Use the Add Tables command to add the Suppliers table to the Relationships window.

    • Create a relationship between the Suppliers table (SupplierID field) and the Products table (SupplierID field).

    • Edit the relationship between the Suppliers table and the Products table to enforce referential integrity.

  • Open the Access_1-2_results database. Compare the two databases to check your work, then close the open databases.

Objective 1.3: Print and export data

You can use the data you store in Access in several ways. Within Access, you can create reports, for example, and distribute the reports in printed or electronic format. You can also export data to formats that are compatible with earlier versions of Access and with other programs, including Microsoft Excel and Word.

This topic first focuses on how to print reports and specific database records. It then describes how to export data from Access.

Configure print options for records, forms, and reports

When you print a table, query, form, or report, you can send the data directly to the default printer (without setting any printing options), use the Print dialog box to select a printer and set printing options, or work in print preview, a view that enables you to refine the layout, view the data in different ways, and export the data.

A report open in print preview.

The Print dialog box provides standard options with which you can specify a page range, set the number of copies, and adjust the page setup. It also provides an option for printing selected records. You must select the records you want to print before opening the Print dialog box. In general, you will print records that you select in a table or query that is open in Datasheet view.

In print preview, Access provides commands to adjust page size and margins, change the page layout and page orientation (switching from portrait to landscape, for example), set up the printout in columns, and view the printout by zooming in and out or by displaying one or more pages. With these views, you can assess whether the printout’s formatting is consistent, for example, or whether any important data might be missing. Many of the commands on the Print Preview tab are also available when you design and format a report in Design view or Layout view.

Tip The Print Preview tab also provides a set of options (in the Data group) for exporting data to other programs or in various formats. These options are described in “Export objects to alternative formats” later in this topic.

In the Page Size group on the Print Preview tab, the Show Margins option displays or hides the printout’s margins, and the Print Data Only option removes elements such as column headings and information in page headers and footers that Access normally prints. You can open the Page Setup dialog box from the Page Size group, but many of the options in the Page Size and Page Layout groups duplicate options that the dialog box provides.

The range of zoom levels in print preview extends from 10 percent to a maximum of 1,000 percent (not all zoom levels apply to every object), but you can choose only preset options (such as 75% or 200%). The Zoom slider, in the lower-right corner of the Access window, adjusts the zoom level with greater flexibility. The Zoom group also lets you choose how many pages to display in a multipage printout. By default, one page is displayed. You can also display 2, 4, 8, or 12 pages.

To print an object directly to the default printer

  • For a report, right-click the report, then click Print.

Or, for all objects

  1. Open the object from the Navigation Pane.

  2. On the Print page of the Backstage view, click Quick Print.

To set printing options and print an object

  1. Open the report from the Navigation Pane.

  2. On the Print page of the Backstage view, click Print.

  3. In the Print dialog box, set options for the print range, number of copies, and other printer properties. Then click OK.

To print selected records from a table or a query

  1. Open the table or query in Datasheet view.

  2. Select the records you want to print.

  3. On the Print page of the Backstage view, click Print to open the Print dialog box.

  4. In the Print range area, click Selected Records. (If you don’t click Selected Records, Access prints all the records in the datasheet.)

  5. Click OK.

To manage print and page setup options for a database object in print preview

  1. Open the database object you want to print if it is not already open.

  2. On the Print page of the Backstage view, click Print Preview.

  3. In the Page Size group, adjust paper size and margins and specify whether only data should be printed.

  4. In the Page Layout group, set the page orientation, columns, and other page setup options.

  5. In the Print group, click Print.

  6. In the Print dialog box, set options for the number of copies and other printing options, and then click OK.

Export objects to alternative formats

One of the advantages of entering and maintaining data in a database is the capability to make the data available in other formats. For example, you can export data to use it in other programs and in other contexts. Data related to sales, budgets, orders, and other financial records can be exported to Excel for analysis. A list of contacts can be exported to a list in a SharePoint site or used in a mail merge in Word. Exporting data to a text file or to an XML file puts the data in a format that is compatible with other database and spreadsheet programs, and creating a PDF or an XPS file by using an export operation lets you distribute data in formats designed for review instead of analysis and editing.

The Export dialog box provides options to maintain an object’s formatting and layout when you export it, view the exported file when the operation is complete, and export only selected records (in lieu of the complete record set that is contained in a specific table or query, for example). Specific operations, such as exporting to a text file, require you to set additional options that control where and how data is exported. You can also save export settings and then repeat an export operation in a single step.

Exporting data to an Excel workbook.

The default setting for exporting data to Excel is the Excel Workbook file format (.xlsx). The options you can choose for a file format depend on the type of object you export. When you export records from a query, for example, you can keep Excel Workbook (.xlsx) or choose Excel Binary Workbook, Microsoft Excel 5.0/95 Workbook, or Excel 97–Excel 2003 Workbook. If you export a report, the file formats are limited to Microsoft Excel 5.0/95 Workbook and Excel 97–Excel 2003 Workbook.

The availability of export options also depends on the type of object. If you export a report, the Export Data With Formatting And Layout check box is selected by default and cannot be cleared. If you export a query or a table, you can select or clear the formatting and layout check box. By selecting that check box, you can open the destination file, and if you selected a subset of the records, you can then select the option to export only those records.

If you export an object’s complete record set, Access displays another dialog box, which has an option for saving the export steps. Saving the export steps saves time if you expect to run this export operation again using the same object and the same export settings.

When you export data to a text file, the steps you follow depend on whether you select the Export Data With Formatting And Layout option. When you select this option, Access displays the Encode As dialog box, which provides a choice of encoding schemes: Windows (Default), MS-DOS, Unicode, or Unicode (UTF-8). The Windows (Default) and MS-DOS options apply to text files that will be used only in programs that support these formats. Most programs consuming text files can use files encoded with the Unicode option. Unicode (UTF-8) is a format used widely on the web.

If you don’t select the Export Data With Formatting And Layout option, Access displays the Export Text Wizard. In export operations that rely on the Export Text Wizard, you specify whether to export the data as a delimited text file or as a fixed-width text file.

The Export Text Wizard.

From this point, the Export Text Wizard displays screens that refine your initial choice. For example, for delimited text files, you specify the character that separates fields in each record (often a comma), whether to include field names in the first row of the exported file, and the text qualifier character (which is used to handle instances of the delimiting character that appear in actual values). For fixed-width exports, you use the wizard to indicate where field breaks occur by dragging lines to create columns.

When you export data to an XML file, you have the option to also export the schema for the data (an XSD file) and the presentation of the data (which is defined in an XSL file). For the data, you can export records in related tables in addition to the data in the object you selected. You can also specify an encoding scheme (UTF-8 or UTF-16). Among the options related to exporting the schema are whether to include table and field properties and whether to embed the schema in the XML file or create a separate schema document. Presentation options include the location where the XLS file is stored, where related images are stored, and whether the XSL transformation is run from a client or a server computer. In the Run From area, the Client option creates an HTML file on the local computer that programmatically merges the XSL file and the data (XML) file. This option does not embed the presentation information in the data, which lets you update either the XSL file or the XML file without having to run the export operation again. The Server (ASP) option creates an Active Server Pages (ASP) file that merges the presentation with the data and sends the HTML file that is created to the local computer.

You can export database objects to another Access database or in the following formats:

  • For a PDF or XPS file, you can export all the object’s data, selected records, or specific pages from a report. Both formats also provide options for accessibility.

  • The Email option in the Export group attaches a database object to email messages in a format that you select.

  • You can export the data in an object to use in a mail-merge operation in Word (the data becomes the recipient list associated with the mail merge) or save the data as a rich-text format (RTF) document.

If you expect to use an export operation regularly, you can save the export steps you defined. By saving the export steps, you can run the operation in a single step.

Select the Save Export Steps check box to later perform the export in a single step.

When you want to run a saved export in Access, on the External Data tab, in the Export group, click Saved Exports. Access opens the Manage Data Tasks dialog box. This dialog box provides options to run the export, create an Outlook task, modify the name or description provided earlier, and delete any saved exports (or saved imports) that you no longer need.

To export data from Access

  1. In the Navigation Pane, select the object that contains the data you want to export.

  2. On the External Data tab, in the Export group, click the format or program you want to export to.

  3. In the Export dialog box, specify the file name and location, and select the export options you want to use: to include formatting and layout, to view the exported file, and to export only selected records.

  4. Depending on the export option you select in step 2, use the options in the dialog boxes and the wizards Access provides to specify file format and related export options.

To save export steps

  1. In the Export dialog box, select Save export steps.

  2. Enter a name for the export steps (or accept the default name) and enter a description.

  3. If you want, select Create Outlook Task.

  4. Click Save Export.

To run a saved export

  1. On the External Data tab, in the Export group, click Saved Exports.

  2. In the Manage Data Tasks dialog box, select the export operation you want to run, then click Run.

The practice file for these tasks is located in the MOSAccessExpert2019\Objective1 practice file folder. The folder also contains a result file that you can use to check your work.

  • Open the AccessExpert_1-3 database from the practice file folder and do the following:

    • If the Info bar appears, click the Enable Content button.

    • Open the Customers report from the Navigation Pane, and then display the report in print preview. Change the margins to Wide.

    • Export the Customers report to Word (use the Rich Text format option).

    • Export the Customers table to Excel.

  • Open the AccessExpert_1-3_results database. Compare the two databases to check your work, then close the open databases.