To expedite the database creation process, Access provides several built-in database templates. A template is a ready-to-use database that contains all the necessary components — tables, forms, reports, queries, macros, and relationships — to track a specific type of data. You can use a template database as is or customize it as needed.

In addition to the templates that install with Access, you can also use templates available on Office Online. To download an Office Online template, click a template category under the Office.com Templates area in Backstage view, click the desired template from the list that appears, and click Download on the right side of the screen. Access downloads the template for you and opens it when the download is complete.

In addition to creating databases with templates, you can create forms within a database by using a template. Each Access form template contains a pre-created set of fields, so you do not have to create fields manually. Templates work well when you need a standard form with common fields. To create a form from a template, click the Create tab in the Ribbon, click Application Parts, and choose from the list of templates that appears.

Suppose you have an Excel worksheet that contains data you want to include in an Access database. You can import the worksheet right into Access, creating a new table. This table becomes part of the Access database; it does not retain any ties to Excel. (Note that in addition to importing the spreadsheet in its entirety, you can also import portions of it.)

For Excel data to import correctly into Access, it must be set up to mimic a datasheet in Access. Specifically, field names should appear in row 1; each record should be on its own row; and no formulas or functions should be included.

You use the Import Spreadsheet Wizard to import Excel data into Access. When you do, the wizard asks you various configuration-related questions, such as whether fields in the table you are creating should be indexed and whether duplicates are okay. It also gives you the option to name the table.

Suppose you want to conduct an e-mail survey and track the results using an Access database. In that case, you can create a data-entry form that can be e-mailed to others via Microsoft Outlook. When a recipient of your survey completes the data-entry form and returns it to you via e-mail, you can assimilate the data he or she has shared into your Access database the instant the message lands in your inbox. You create and distribute the data-entry form using the Collect Data Through E-mail Messages Wizard. Note that the recipient of your e-mail need not use Outlook in order to complete the data-entry form the e-mail contains; any e-mail program will do.

You have two options with regard to what types of forms to include in your e-mail message: HTML or InfoPath. If you are not certain whether all the recipients of your message have the necessary software to view and interact with InfoPath forms, opt for HTML.

Using Outlook as a data-collection tool also works well for gathering and tracking status reports and organizing an event.

Suppose you are creating a form to record customers' contact information. If your business is limited to a certain state, you might make that state the default value in the State field. In this way, you can speed up data entry.

You can set default values for fields that use the following data types: Text, Memo, Number, Date/Time, Currency, Yes/No, and Hyperlink. If you do not set a default value, the field remains blank until a value is entered.

Note that in order to set a default value, you must open the table containing the field for which you want to set the value in Design view. To do so, right-click the table in the Navigation pane and choose Design View from the menu that appears. Alternatively, if the table is already open in another view, right-click the table's tab and choose Design View.

A primary key field is always required for each record. (As mentioned in the task "Import Data from Excel," a primary key is a field or set of fields in a table that contains a unique identifier for every record. For example, if your table contains customer information, the primary key would likely be a field that contains a unique ID number for each customer.)

You are not limited to making the primary key field required, however. You can make other fields required as well. (Note that doing so does not change the primary key setting.) When a field is required, Access does not enable users to move past it during data entry until they have entered a value in the field.

In order to set a field as required, you must open the table containing the field in Design view. To do so, right-click the table in the Navigation pane and choose Design View from the menu that appears. Alternatively, if the table is already open in another view, right-click the table's tab and choose Design View.

If a field in your table has an ambiguous or grammatically incorrect name, you can apply a caption to the field. For example, you might add the caption "Last Name" (note the space) to make a LastName field easier to identify. Captions appear in datasheet headings and on labels in forms and reports, so making them easy to understand can go a long way in speeding up the reading of your Access data.

To apply a caption to a field, you must open the table containing the field in Design view. To do so, right-click the table in the Navigation pane and choose Design View from the menu that appears. Alternatively, if the table is already open in another view, right-click the table's tab and choose Design View. You can also use the view tools on the Ribbon's Home tab to switch views, or the view icons in the lower right corner of the Access program window.

If you are entering the same value in the same field in a table or form over and over again, you can use a shortcut technique to copy the value of the previous record. Rather than retype it each time, you can summon the value with a keyboard shortcut. This can really reduce the amount of time you spend entering records into tables or forms.

To make the best use of this technique, type your record data in a table using Datasheet view. This allows you to easily see the previous record's values and determine if you need to re-enter the same information again.

This technique makes use of a little-known shortcut key. You can create all kinds of other custom shortcut keys to help you navigate through your database. For example, open a form in Design view and select the Caption property of a field name to which you want to navigate. Type an ampersand before the letter that you want to act as a shortcut key, such as P&hone. Save your changes and switch to Form view. Access adds an underscore to the letter you designate as your navigation shortcut. To navigate to the field in Form view, press Alt+H.

You can apply an input mask to a field to help minimize data-entry errors. Although it sounds rather like a beauty product you might smear onto your face, input masks help users enter the proper number and type of characters. They do so by providing a template for entering data in the field. For example, you might implement an input mask to include parentheses for the area code portion of a telephone number. Input masks act like quality control checks, helping users know what sort of data they are expected to enter into a table or form.

Access provides several predefined input masks from which you can choose. If none of these predefined input masks quite suits your needs, you can customize an input mask.

To apply an input mask to a field, you must open the table containing the field in Design view. To do so, right-click the table in the Navigation pane and choose Design View from the menu that appears. Alternatively, if the table is already open in another view, right-click the table's tab and choose Design View.

Input masks help users enter the proper number and type of characters, but they cannot restrict the field to certain entries based on logic. That is where data-validation rules come in. You can construct a validation rule that forces field entries to pass a logical test of their validity. For example, you could make sure that negative numbers are not entered into a numeric field. You can also create validation text, a custom message that appears when the rule is violated.

You create data-validation rules using Expression Builder. It can guide you in determining the correct syntax for an expression. There are many types of expressions available in Expression Builder, including functions, constants, and operators. For example, you can create an expression to primarily test data including checking for one in a series of data, such as "New York" Or "Los Angeles" Or "Tokyo." You can also use expressions to perform mathematical operations, such as >5 which forces the user to enter values greater than 5. The possibilities are endless.

Suppose you are maintaining a database of job candidates, and you want to attach each candidate's resume to his or her record. You can easily do so using Access. A single record can have multiple attached files of various types.

You can add an attachment to a record only if the table containing the record includes an Attachment field. To insert a field into a table, open the table in Design view, click the field above which the new field should appear, and click Insert Rows. Type a name for the new field in the Field Name column; then click in the Data Type column, click the drop-down arrow that appears, and choose Attachment.

To add an attachment to a record, the table containing the record must be open in Datasheet view. If it is not in Datasheet view, right-click the table's tab in the Access screen and choose Datasheet View in the menu that appears. Attachment fields are marked by a paperclip. The number in parentheses is the number of attachments the field currently contains.

Attaching a file to a database record works well if the file is static. If, however, it is dynamic — that is, it will be changed over time — you should insert the file as an OLE object rather than as an attachment. That way, the version of the file in the Access database will match the original, even if the original is changed in some way.

You can insert an OLE object into a record only if the table containing the record includes an OLE Object field. To insert a field into a table, open the table in Design view, click the field above which the new field should appear, and click Insert Rows on the Ribbon's Design tab. Type a name for the field in the Field Name column; then click in the Data Type column, click the drop-down arrow that appears, and choose OLE Object.

To insert an OLE object into a record, the table containing the record must be open in Datasheet view. If it is not in Datasheet view, right-click the table's tab in the Access screen and choose Datasheet View in the menu that appears.

You can use a filter to show only records that match criteria that you specify. For example, you can filter for blank or nonblank records, records containing a specific value in a particular field, multiple values, a particular text string, by form, and so on.

If you filter by form, you can save your filter parameters. That way, you can rerun the Filter By Form filter at a later time. When you save a filter, a new query is created in the database. A query is something you use to display data from a table in some modified way. Queries created from filters work just like any other query.

To create a Filter By Form filter, display the table you want to filter, click the Home tab, click Advanced, and click Filter By Form. Then click in the field by which you want to filter, click the drop-down arrow that appears, and choose the desired value. The results display only those records that have the value you chose in the corresponding field.

One reason people create reports and queries is to extract summary statistics about data, such as the sum or average of the values in certain fields. A new feature introduced back in Access 2007 is the ability to display such information directly on the datasheet, without having to create a query or report.

Available statistic types include Sum (this results in a total of the values in the selected column), Average (this averages the values in the selected column), Count (this totals up the number of rows in the selected column that contain a value), Maximum (this reveals the maximum value in the selected column), Minimum (this reveals the minimum value in the selected column), Standard Deviation (this measures how widely values in the selected column are dispersed from an average value), and Variance (this measures the statistical variance of all values in the selected column).

Access is a relational database. Relational databases are powerful because they can contain multiple related tables — for example, tables that share one or more fields.

In a complex database, relationships are often created among the various objects in the database. Indeed, very rarely would a table or similar object stand alone. Therefore, you should not delete an object until you understand what other objects are affected by that deletion. For example, if you delete a table on which a form is based, the form based on the table is orphaned.

One way to view an object's dependencies is to use the Relationships window. To open this window, click the Database Tools tab and click Relationships. Another way is to use Access's Object Dependencies feature, which enables you to see all the dependencies of a particular object. (Note that to see all dependencies for all objects at once, you can use the Database Documenter feature, covered in the next task.)

Whereas Access's Object Dependencies feature enables you to view all the dependencies of a particular object, the program's Database Documenter feature allows you to see all dependencies for all objects in a database. Using this feature results in a full report of the database, including details about each object in the database and how it relates to other objects the database contains. Generating such a report might help another database designer understand the structure of your database.

When you run the Database Documenter feature, the feature launches the report it generates in Print Preview mode, enabling you to print out the report with the click of a mouse. Alternatively, you can export the report to an Extensible Markup Language (XML) file or Hypertext Markup Language (HTML) document, or even merge it with Microsoft Word. To export, rather than print, the report, simply click More in the Print Preview tab's Data group and select the desired output from the menu that appears.

Reports are simply special views of your data, which are designed to be printed. Reports can be created with very basic formatting settings or can involve complex, custom layouts.

Access enables you to generate reports very easily, especially if you use the default settings. To create a report, click the table or query for which you want to generate a report in the Navigation pane, click the Create tab, and click Report.

After you have created the report, you can export it to Word. Doing so enables you to apply Word's full-featured formatting functionality to the report. To export the report to Word, you launch the Export Wizard, which steps you through the process of converting the report to a Word document.

In addition to exporting reports to Word, you can also export other types of Access objects, such as tables and queries. As with reports, you launch the export operation from within Print Preview mode.

In addition to standard reports, Access can create mailing labels. This enables you to print labels without exporting the data first into a word-processing program and to set up reusable label definitions for recurring mailings.

Labels are a special type of report. They print multiple records per page, in a layout designed to correspond to self-stick labels that feed into your printer.

After choosing the size and formatting for the label, you set up the fields that should appear on it. These come from the table or query that you selected before you started the Label Wizard.

You can format the labels you create, changing the background color, adjusting the font and font color, and more. For additional formatting options, you can export the labels to Word. To do so, click the External Data tab, click More, click Word, and follow the on-screen instructions.

If you frequently use Access to complete the same task — for example, to format the entries in a table a certain way — you can expedite the process by creating a macro. When you create a macro, you essentially record a series of actions. Then, you can run the macro you created to automatically perform the actions.

Unlike other Office programs, which enable you to "record" macros by essentially tracking the steps you take to complete a task, Access requires you to select the actions, arguments, and other elements of a macro from a list. You create a macro in Access by using Macro Builder, listing the actions you want Access to carry out when the macro is run, and in what order.

Once a macro has been created, you can easily run it with the click of a button. Using macros can save you loads of time and effort, automating tasks that might normally require dozens of keystrokes or clicks.