Objective group 2. Create and modify tables – MOS Study Guide for Microsoft Access Expert Exam MO-500

Objective group 2

Create and modify tables

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

2.1 Create tables

2.2 Manage tables

2.3 Manage table records

2.4 Create and modify fields

When you create a database, Access automatically creates a new table that you can use to enter data by hand. You can also create your own tables as needed. However, for many database applications the data already resides in some other format, such as an Excel spreadsheet, an HTML document, a text or XML file, or another database. In that case, it’s easier and faster to import the data from its current format to an Access table. You can either create a copy of the external data or you can set up a link between the original data and your Access table, which means that changes made to the external data are also propagated to the Access version of the data.

This chapter guides you in studying methods for importing data into tables, managing tables and records in tables, and creating and modifying fields.

Objective 2.1: Create tables

If the data you want to work with resides in an external data source—usually a local file, a remote file (on a network or on the Internet), or data on a server—you need to import it into Access. Depending on the type of data source you are using, Access gives you one or more of the following choices for importing the data:

  • Import the source data into a new table in the current database. Access either creates a new table to hold the data or replaces any data in an existing table. No link is maintained with the original data.

  • Append a copy of the records to an existing table. Access adds the source data to the existing table. If the table does not exist, Access creates it. No link is maintained with the original data.

  • Link to the data source by creating a linked table. Access adds the source data to the new table. A link is maintained with the original data, so if that data changes, the changes are reflected in the Access version of the data.

With most external data, you can import into a new table, append to an existing table, or link to the data source.

Access 365 and Access 2019 support a number of external data sources, including the following: Excel workbooks, HTML documents, XML files, text files, Access databases, and Outlook folders.

See Also For information about appending data to a table, see “Append records from external data,” later in this task. For information about creating linked tables, see “Create linked tables from external sources,” later in this task.

Import data into a new table

When you import data from Excel into a new table, the Import Spreadsheet Wizard prompts you for information to complete the operation. The wizard first prompts you for the worksheet or the named range you want to import. You can view the sample data that the wizard displays from the worksheet, but you cannot modify it. Access can use the column headings in the worksheet as field names in the database. You can also specify each field’s data type and whether Access should index the field. The wizard’s fourth page provides options for setting the table’s primary key. Access can create an ID field in the table to use as the primary key, or you can select a primary key field or use no primary key in the new table.

Define field names and data types when you import data from a spreadsheet.

See Also For information about running saved import and export operations, see “Objective 1.3: Print and export data.”

You can import data from a text file that uses the .txt, .csv, .tab, or .asc file name extension. When you import data from a text file, you work with the Import Text Wizard. In the wizard, you first need to specify whether a character separates the fields of data in the text file (a delimited text file) or whether the data is arranged in fixed-width columns. For delimited text files, you must specify which character is used as the delimiter; for fixed-width files, you indicate where column breaks should occur.

The later pages of the Import Text Wizard are similar to those you work with in the Import Spreadsheet Wizard. You can name fields, specify a data type, indicate whether the field should be indexed, and skip a specific field. The wizard also prompts you to set up a primary key for the table.

Three of the other formats you can import are as follows:

  • HTML documents Data is often available in documents created using HTML (Hypertext Markup Language), which is the language used to build web pages. Although this data is usually text, some HTML data comes either as a table (a rectangular array of rows and columns) or as preformatted text (text that has been structured with a predefined spacing used to organize data into columns with fixed widths). Both types are suitable for import into Access so that you can perform more extensive data analysis. To import HTML document data, the file must reside on your computer or on your network.

  • XML files Access uses the structure of the XML file to determine table names and fields. Import options include Structure Only, Structure And Data, and Append Data To Existing Table(s).

  • Outlook folders Importing a contacts or tasks folder from Outlook is an effective way to add this information to a database. Access runs the Import Exchange/Outlook Wizard when you import data from Outlook. The wizard prompts you to provide field names, specify data types, and set up indexes. You can skip fields if you don’t want to import them.

To import Excel data into a table

  1. On the External Data tab, in the Import & Link group, click New Data Source, click From File, and then click Excel.

  2. In the Get External DataExcel Spreadsheet dialog box, click Import The Source Data Into A New Table In The Current Database, click Browse to locate the source file, and then click OK.

  3. In the Import Spreadsheet Wizard, select the data you want to import, then click Next:

    • To import the data from a worksheet, select Show Worksheets, then click the worksheet name.

    • To import the data from a named range, select Show Named Ranges, then click the range name.

  4. If the first row of the Excel data contains headings and you want Access to use those headings as the field names for the new table, select the First Row Contains Column Headings check box, then click Next.

    IMPORTANT Your Excel headings might contain one or more characters that are illegal to use for Access field names, including the period (.), exclamation mark (!), and brackets ([ and ]). That’s not a problem because Access will delete any illegal characters (and display a message to that effect) when it creates the field names.

  5. On the next Import Spreadsheet Wizard page, click the field you want to work with, then edit the field name, select the data type, and specify whether you want the field indexed. Alternatively, you can select the Do Not Import (Skip) check box to tell Access to skip the field when importing the data.

  6. Repeat step 5 for each field, then click Next.

  7. On the next Import Spreadsheet Wizard page, select a primary key option, then click Next:

    • Let Access Add Primary Key Select this option to have Access create a new field named ID that acts as the primary key by using the AutoNumber data type.

    • Choose My Own Primary Key Select this option, then use the associated list to select the field you want to use as the primary key. Note that the field you choose must contain unique entries.

    • No Primary Key Select this option to skip adding a primary key to the new table.

  8. On the final Import Spreadsheet Wizard page, type a name for your new table, then click Finish to return to the Get External DataExcel Spreadsheet dialog box.

  9. If you want to save the steps in this operation, select Save Import Steps, then click Save Import; otherwise, click Close.

To import HTML document data into a table

  1. On the External Data tab, in the Import & Link group, click New Data Source, click From File, and then click HTML Document.

  2. In the Get External DataHTML Document dialog box, click Import The Source Data Into A New Table In The Current Database, click Browse to locate the source file, and then click OK.

  3. In the Import HTML Wizard, if the first row of the HTML data contains headings and you want Access to use those headings as the field names for the new table, select the First Row Contains Column Headings check box, then click Next.

  4. On the next Import HTML Wizard page, click the field you want to work with, then edit the field name, select the data type, and specify whether you want the field indexed. Alternatively, you can select the Do Not Import (Skip) check box to tell Access to skip the field when importing the data.

  5. Repeat step 4 for each field, then click Next.

  6. On the next Import HTML Wizard page, select a primary key option, then click Next:

    • Let Access Add Primary Key Select this option to have Access create a new field named ID that acts as the primary key by using the AutoNumber data type.

    • Choose My Own Primary Key Select this option and then use the associated list to select the field you want to use as the primary key. Note that the field you choose must contain unique entries.

    • No Primary Key Select this option to skip adding a primary key to the new table.

  7. On the final Import HTML Wizard page, type a name for your new table, then click Finish to return to the Get External DataHTML Document dialog box.

  8. If you want to save the steps in this operation, select Save Import Steps, then click Save Import; otherwise, click Close.

To import XML data into a table

  1. On the External Data tab, in the Import & Link group, click New Data Source, click From File, and then click XML File.

  2. In the Get External DataXML File dialog box, click Browse to locate the source file, then click OK.

  3. In the Import XML dialog box, in the Import Options group, select how you want the XML data imported, then click OK:

    • Structure Only Select this option to import just the field names.

    • Structure and Data Select this option to import both the field name and the data.

    • Append Data to Existing Table(s) Select this option to add the data to an existing table (or tables, if the XML file contains multiple tables).

  4. If you want to save the steps in this operation, select Save Import Steps, then click Save Import; otherwise, click Close.

To import text data into a table

  1. On the External Data tab, in the Import & Link group, click New Data Source, click From File, and then click Text File.

  2. In the Get External DataText File dialog box, click Import The Source Data Into A New Table In The Current Database, click Browse to locate the source file, and then click OK. The Import Text Wizard appears.

  3. Select the type of text file you’re importing:

    • Delimited Select this option if your text file uses a character (such as a comma) to separate each field, then click Next. Select the option that represents the character used in the source data to separate the fields (or select Other and then type the character in the text box). If the first row of the text data contains headings and you want Access to use those headings as the field names for the new table, select the First Row Contains Column Headings check box. Click Next.

    • Fixed Width Select this option if your text file uses a set width for each column of data, then click Next. Use the next Import Text Wizard page to adjust the column breaks as needed, then click Next.

  4. On the next Import Text Wizard page, click the field you want to work with, then edit the field name, select the data type, and specify whether you want the field indexed. Alternatively, you can select the Do Not Import (Skip) check box to tell Access to skip the field when importing the data.

  5. Repeat step 4 for each field, then click Next.

  6. On the next Import Text Wizard page, select a primary key option, then click Next:

    • Let Access Add Primary Key Select this option to have Access create a new field named ID that acts as the primary key by using the AutoNumber data type.

    • Choose My Own Primary Key Select this option and then use the associated list to select the field you want to use as the primary key. Note that the field you choose must contain unique entries.

    • No Primary Key Select this option to skip adding a primary key to the new table.

  7. On the final Import Text Wizard page, type a name for your new table, then click Finish to return to the Get External DataText File dialog box.

  8. If you want to save the steps in this operation, select Save Import Steps, then click Save Import; otherwise, click Close.

To import Outlook folder data into a table

  1. On the External Data tab, in the Import & Link group, click New Data Source, click From Other Sources, and then click Outlook Folder.

  2. In the Get External DataOutlook Folder dialog box, click Import The Source Data Into A New Table In The Current Database, then click OK.

  3. In the Import Exchange/Outlook Wizard, select the data you want to import, then click Next.

  4. On the next Import Exchange/Outlook Wizard page, click the field you want to work with, then edit the field name, select the data type, and specify whether you want the field indexed. Alternatively, you can select the Do Not Import (Skip) check box to tell Access to skip the field when importing the data.

  5. Repeat step 4 for each field, then click Next.

  6. On the next Import Exchange/Outlook Wizard page, select a primary key option, then click Next:

    • Let Access Add Primary Key Select this option to have Access create a new field named ID that acts as the primary key by using the AutoNumber data type.

    • Choose My Own Primary Key Select this option and then use the associated list to select the field you want to use as the primary key. Note that the field you choose must contain unique entries.

    • No Primary Key Select this option to skip adding a primary key to the new table.

  7. On the final Import Exchange/Outlook Wizard page, type a name for your new table, then click Finish to return to the Get External DataOutlook Folder dialog box.

  8. If you want to save the steps in this operation, select Save Import Steps, then click Save Import; otherwise, click Close.

Append records from external data

When you import data from an Excel spreadsheet, an HTML document, a text file, or an Outlook folder, you can choose an option to append records to an existing table. Access adds the records in the source data to the table you specify. The steps for importing the data are then essentially the same as when you import data into a new table.

See Also For more information about importing data, see “Import data into a new table,” earlier in this task.

To avoid errors when you append data, make sure that the external data source organization matches the structure of the table you are appending records to. For example, in an Excel worksheet that does not include column headings, the position and the type of data need to match the field order and data types in the destination table. When column headings are present, the name and data type for each column must match the corresponding fields (although the order of the columns and fields do not have to match). Also, check whether the source data contains any fields not included in the table. If the source data does contain other fields, you should add these fields to the destination table or specify to skip them for the import process. The destination table can include fields that are not defined in the source data, provided those fields have their Required property set to No and the fields do not contain any validation rules that prohibit null values.

The source data must include data that is compatible with the table’s primary key, and the data in that column must be unique. You receive an import error message if a primary key value in the source data matches one already defined in the destination table. Also, if the Indexed property of any field in the destination table is set to Yes (No Duplicates), the source data must include unique values for that field.

To append records to a table in the current database

  1. On the External Data tab, in the Import & Link group, click New Data Source, then click one of the following data sources:

    • From File, and then Excel

    • From File, and then HTML Document

    • From File, and then Text File

    • From Other Sources, and then Outlook Folder

    IMPORTANT You can append records to a table only from Excel workbooks, text files, HTML documents, and Outlook folders.

  2. In the Get External Data dialog box, do the following:

    Click Browse to open the File Open dialog box. Locate and select the source file, then click Open.

    Click Append ACopy Of The Records To The Table, and then in the adjacent list, select the table you want to append records to.

    Select a table to append data.

    Click OK.

  3. Follow the steps in the import wizard (if Access provides one) to import and append the data.

  4. In the Get External Data dialog box, do the following:

    If you want to save the steps of the operation for reuse, select the Save Import Steps check box and provide a name and optional description for the steps.

    Click Close.

Create linked tables from external sources

With linked tables, you can include in your database information that’s stored in an external data source. You can create a linked table that’s based on an Excel worksheet, a text file, or one of the other external data formats that Access supports. Linking to an Excel worksheet or a text file, for example, creates a one-way link. You can read the data in Access, but you cannot insert or update records—the data is maintained only in the external data source. However, you can link to tables in another Access database and work with those tables in many of the same ways you work with tables in your database. You can add and update records in a table linked to another Access database, but you can’t change the table’s design. To modify the design, open the table in the source database.

Access adds an entry for a linked table to the Navigation Pane, displaying an icon that identifies the type of data source. The icon includes a small arrow to indicate that the table is a linked table.

Linked tables are identified in the Navigation Pane.

When you link to an Excel worksheet or a text file to create a table, Access provides a wizard (the Link Text Wizard, for example) that functions much like the wizards you follow to import data into a new table.

Tip You can link to tables in other Access databases to work around the restriction on the size of a single Access database file (approximately 2 GB).

If you link to an Access database or to another external data source that is protected with a password, you must provide the password to link successfully. Access can save the password so that you don’t need to provide it each time you open the external table. Because Access saves this information, you might want to encrypt your database.

If a source file you have linked to is moved to a different location, you can update the link by using the Linked Table Manager dialog box, which lists each table linked to in the current database.

To link to a table in another Access database

  1. On the External Data tab, in the Import & Link group, click New Data Source, click From Database, and then click Access.

  2. In the Get External DataAccess Database dialog box, do the following:

    Click Browse to open the File Open dialog box. Locate and select the source database, then click Open.

    Click Link To The Data Source By Creating ALinked Table.

    Click OK to open the Link Tables dialog box.

  3. In the Link Tables dialog box, select the table or tables you want to link to, then click OK.

To link to a text file

  1. On the External Data tab, in the Import & Link group, click New Data Source, click From File, and then click Text File.

  2. In the Get External DataText File dialog box, do the following:

    Click Browse to open the File Open dialog box. Locate and select the source file, then click Open.

    Click Link To The Data Source By Creating ALinked Table.

    Click OK to start the Link Text Wizard.

  3. In the Link Text Wizard, do the following:

    On the wizard’s first page, specify the format of the text file (Delimited or Fixed Width), then click Next.

    Choose the delimiting character or specify column breaks (depending on the format).

    Select First Row Contains Field Names if this option applies.

    Click Next to work through the remaining pages to set field options.

    Enter a name for the linked table, then click Finish.

  4. In the Link Text Wizard message box that confirms the table was linked, click OK.

To link to an Excel worksheet or named range

  1. On the External Data tab, in the Import & Link group, click New Data Source, click From File, and then click Excel.

  2. In the Get External DataExcel Spreadsheet dialog box, do the following:

    Click Browse to open the File Open dialog box. Locate and select the source workbook, then click Open.

    Click Link To The Data Source By Creating ALinked Table.

    Click OK to start the Link Spreadsheet Wizard.

  3. In the Link Spreadsheet Wizard, do the following:

    On the wizard’s first page, select the worksheet or named range that contains the data you want to link to, then click Next.

    Specify whether the first row of the data includes column headings, then click Next.

    Enter a name for the linked table, then click Finish.

  4. In the Link Spreadsheet Wizard message box that confirms the table was linked, click OK.

To manage linked tables

  1. On the External Data tab, in the Import & Link group, click Linked Table Manager.

  2. In the Linked Table Manager dialog box, select the check box for the table or tables whose links you want to update, then click OK.

  3. If the source file is not in the original location, Access opens the Select New Location dialog box. In this dialog box, navigate to the new location for the file, select the file, and then click Open.

  4. In the Linked Table Manager message box, click OK.

Import tables from other databases

You can import tables from other database files or database management systems. In this section, you learn how to import one or more tables from three common types of databases:

  • SQL Server This robust and powerful server-based database management system is designed to handle massive amounts of data. To access a SQL Server instance, you need to know the login ID and password to connect to the SQL Server data source.

    You must log in to access an SQL Server database.
  • Azure Database This online database management system runs inside an Azure instance in Microsoft’s cloud.

  • dBASE This is a DBF file that you import from a local or network location.

See Also For information on importing tables (as well as other objects) from an Access database, see “Import objects or data from other sources” in Objective 1.1: Modify database structure.”

To import one or more tables from an SQL Server database

  1. On the External Data tab, in the Import & Link group, click New Data Source, click From Database, and then click From SQL Server.

  2. In the Get External DataODBC Database dialog box, click Import The Source Data Into A New Table In The Current Database, then click OK. Access opens the Select Data Source dialog box.

    Exam Strategy It is assumed here and on the Access Expert exam that a data source for the SQL Server database has already been created by an administrator and that you have been given the login credentials for the SQL Server.

  3. On the Machine Data Source tab, click the SQL Server data source, then click OK. The SQL Server Login dialog box appears.

  4. Type the login ID and password required to log in to the SQL Server, then click OK. Access displays the Import Objects dialog box.

    Use the Import Objects dialog box to select the table or tables you want to import.
  5. In the Tables list, click each table you want to import, and click OK. Access imports the data and returns you to the Get External DataODBC Database dialog box.

  6. If you want to save the steps in this operation, select Save Import Steps, then click Save Import; otherwise, click Close.

To import one or more tables from an Azure database

  1. On the External Data tab, in the Import & Link group, click New Data Source, click From Database, and then click From Azure Database.

  2. In the Get External DataODBC Database dialog box, click Import The Source Data Into A New Table In The Current Database, then click OK. Access opens the Select Data Source dialog box.

  3. On the Machine Data Source tab, click the Azure database data source, then click OK. Access prompts you to log in to the database.

  4. Type the login ID and password required to log in to the Azure database, then click OK. Access displays the Import Objects dialog box.

  5. In the Tables list, click each table you want to import, then click OK. Access imports the data and returns you to the Get External DataODBC Database dialog box.

  6. If you want to save the steps in this operation, select Save Import Steps, then click Save Import; otherwise, click Close.

To import a table from a dBASE file

  1. On the External Data tab, in the Import & Link group, click New Data Source, click From Database, and then click dBASE File.

  2. In the Get External DatadBASE File dialog box, click Browse to display the File Open dialog box, click the dBASE file you want to import, and then click Open.

  3. Click Import The Source Data Into A New Table In The Current Database, then click OK. Access imports the data.

  4. If you want to save the steps in this operation, select Save Import Steps, then click Save Import; otherwise, click Close.

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

  • In the AccessExpert_2-1 database, do the following:

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

    • Import the Customers Table worksheet from the AccessExpert_2-1 workbook (.xlsx) located in the practice file folder to create a new table in the database, using the worksheet’s column headings. Skip importing the Fax field. Use the CustomerID field as the primary key. Name the table Customers.

    • Import the table from the AccessExpert_2-1 HTML document (.html) located in the practice file folder to create a new table in the database, using the table’s column headings. Let Access create the primary key. Name the table Products.

    • Import the structure and data of the Suppliers table from the AccessExpert_2-1 XML document (.xml) located in the practice file folder to create a new table in the database.

    • Import the AccessExpert_2-1 text file (.csv) located in the practice file folder and append the data to the existing Orders table in the database. Import the text as a comma-delimited file using the text file’s column headings. Use the OrderID field as the primary key.

    • Import the AccessExpert_2-1 text file (.txt) located in the practice file folder to create a linked table in the database. Import the text as a fixed-width file using the text file’s column headings. Name the first field Currency, the second field Per $US, and the third field To $US. Name the table Exchange Rates.

    • Open the AccessExpert_2-1 text file (.txt) located in the practice file folder. In the second column of the Argentine Peso record, change the value 8.77 to 8.57. Save and close the text file, then update the Exchange Rates table to incorporate the changed value.

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

Objective 2.2: Manage tables

This topic covers various aspects of managing tables. Managing a table involves activities such as hiding or freezing fields to make a large datasheet easier to view, adding a Total row to display summary values for fields (a count of how many orders are recorded, for example, or the total sum or average amount of payments you’ve received), and adding descriptive text to table fields.

Hide fields in tables

When a table includes either a relatively large number of fields or several fields that contain relatively long entries, when you open the table in Datasheet view you might not be able to see all the fields on your screen. This means that you have to scroll horizontally to view the fields you can’t see. To avoid scrolling to view a field, you can hide fields you don’t need to refer to (the primary key field, for example, which you would very rarely change). You can also freeze fields so that a specific field (or fields) remains in view as you scroll.

You can freeze a field whose column is at any position in the datasheet. Access moves the column or columns you freeze to the far left of the datasheet, placing the column or columns before any others. Unfreezing the field does not return this column (or columns) to its original position in the table. You need to drag the column heading to place the column where you want it in the table.

You can select more than one field to hide or freeze, but the fields must be adjoining fields in the datasheet. Access selects the first field you select and each field to the left or right of the next field you select.

To hide fields in a table

  1. Open the table in Datasheet view.

  2. Do either of the following:

    • To hide one field, right-click the field column heading, then click HideFields.

    • To hide multiple adjacent fields, click the first field column heading, press and hold the Shift key, and click the last field column heading. Then right-click the selection and click Hide Fields.

To show hidden fields in a table

  1. Open the table in Datasheet view.

  2. Right-click a column heading, then click Unhide Fields.

  3. In the Unhide Columns dialog box, select the check boxes for the fields you want to show.

  4. Click Close.

    Unhide fields by selecting them in this dialog box.

To freeze fields in a table

  1. Open the table in Datasheet view.

  2. Do either of the following:

    • To freeze one field, right-click the field column heading, then click FreezeFields.

    • To freeze multiple adjacent fields, click the first field column heading, press and hold the Shift key, and click the last field column heading. Then right-click the selection and click Freeze Fields.

To unfreeze fields in a table

  • In Datasheet view, right-click a column heading, then click Unfreeze All Fields.

Add Total rows

As you will learn in Objective group 3, “Create and modify queries,” one use of a query is to summarize data—that is, to count how many orders were placed in a month, for example, or to calculate the aggregate value of a number or currency field. You can also summarize data in a table by adding a Total row to display summary values for one or more fields. A Total row uses built-in functions such as Sum and Count. For Sum to be applied to a field, the field’s data type must be set to Number or Currency. For fields that don’t use a numeric data type (such as a text field), you can apply the Count function. In numeric fields, you can also apply the functions Average, Maximum, Minimum, Standard Deviation, and Variance.

Total rows show calculated totals.

To add and configure a Total row for a table

  1. Open the table in Datasheet view.

  2. On the Home tab, in the Records group, click Totals. Access adds a Total row to the bottom of the table.

  3. For each column in the Total row where you want a total to appear, click in the column, click the arrow that appears on the left side of the cell, and then select the function you want to apply.

To remove the Total row from a table

  • On the Home tab, in the Records group, click Totals.

Add table descriptions

One of the properties you can define for a table is Description. Adding a description is another step you can take to document the objects and logic in your database. You can add a description in Design view by displaying the table’s property sheet or in a table’s Properties dialog box.

To add a description to a table from the Navigation Pane

  1. Right-click the table in the Navigation Pane, then click Table Properties.

  2. In the table’s Properties dialog box, enter a description in the Description box, then click OK.

To add a description to a table in Design view

  1. Right-click the Design view grid, then click Properties. Access displays the table’s property sheet.

  2. In the property sheet, click in the Description field, then enter a description for the table.

You can also make a table easier for other people to use by adding a description to each field. That description appears in two places:

  • In Design view, the description for each field appears in the Description column, which enables other table designers to understand the purpose of a field.

    In Design view, field descriptions appear in the Description column.
  • In Datasheet view, the description for a field appears in the Access status bar when the user navigates to that field. This helps table users to understand the data.

    In Datasheet view, a field’s description appears in the status bar when the field is active.

See Also You can also change the default field names to captions that are easier for users to read and understand. To learn how to do this, see “To change a field caption” in “Objective 2.4: Create and modify fields.”

To add descriptions to a table’s fields

  1. Right-click the table you want to modify, then click Design View.

  2. For each field, use the Description column to enter descriptive text for the field.

    IMPORTANT Your descriptions should be as clear and as accurate as possible, but they should also be succinct since each description is limited to 255 characters.

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

  • Open the AccessExpert_2-2 database from the practice file folder and do the following:

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

  • Open the Customers table in Datasheet view and do the following:

    • Hide the Customer ID, Contact Title, and Fax fields.

    • Freeze the Company Name field, and then scroll to the right.

    • Close the Customers table and save your changes to the layout.

  • Open the Products table in Datasheet view and do the following:

    • Add a Total row to the Products table.

    • In the Total row, set the Units In Stock field to display the total units that are available.

    • In the Total row, set the Units On Order field to display the total units that have been ordered.

    • In the Total row, set the Discontinued field to display the number of items that have been discontinued.

  • Close the Products table, then do the following:

    • Add the description Our loyal customers to the Customers table.

    • Open the Customers table in Design view and add the description Unique Five-Character Code Based On The Company Name to the Customer ID field.

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

Objective 2.3: Manage table records

Over time, a database can grow to include thousands of records. Locating a specific record by scrolling through a table’s datasheet (or by using a form) is inefficient in circumstances like this. To locate a record or a group of records that share specific criteria, you can use commands to find, replace, sort, and filter records in Datasheet view.

Find and replace data

Find And Replace is a single feature with two parts: one that enables you to locate text and another that enables you to replace found text with other text. Both features can save you tremendous amounts of time, particularly in large tables.

In the Find And Replace dialog box, you use the Find tab to locate text within records. You can search for numbers, partial words, entire words, or phrases. You use the Replace tab to also locate text within a table, except you can also replace the found text with some other text that you specify.

Use the Find And Replace dialog box to locate, and optionally replace, text within an Access table.

After you enter the value you’re looking for in the Find What box, you can refine the search as follows:

  • Use the Look In list to specify whether Access should search the current field or the entire table (the Current Document option).

  • Use any of the following three options in the Match list:

    • Whole Field Use this option (the default setting) to find records with values that match the entire text string you enter in the Find What box. For example, if you enter Blue, Access does not find records whose value is Light Blue, Dark Blue, or Navy Blue.

    • Any Part Of Field Use this option if you want to locate records that contain the text string you enter in any part of the field. If you enter Blue with this option selected, Access finds records for Blue, Light Blue, Dark Blue, and Navy Blue (and those like them).

    • Start Of Field Use this option to locate records that begin with a specific string of characters—all records whose Description field starts with Spa, for example, to find the records for Spanish olive oil, Spaghetti, Sparkling water, and Spanakopita.

  • Use the Search list to specify whether Access should search down, up, or all (both directions).

  • Select the Match Case option to implement a case-sensitive search.

  • Select the Search Fields As Formatted option to search a field that has a particular format or an input mask. With this option selected, Access searches the data as it is displayed instead of how Access stores it. This option is particularly useful in date and time fields.

Use the options on the Replace tab in the Find And Replace dialog box when you want to insert new data for the data Access finds. You can click Replace All to replace all instances of the data, or you can click Replace to replace just the currently highlighted instance.

To find table data

  1. Open the table in Datasheet view.

  2. Do either of the following to open the Find And Replace dialog box:

    • On the Home tab, in the Find group, click Find.

    • Press Ctrl+F.

  3. In the Find And Replace dialog box, on the Find tab, enter the text you want to locate in the Find What box.

  4. In the Look In list, select Current Field or Current Document (which refers to the entire table).

  5. In the Match list, select Whole Field, Any Part Of Field, or Start Of Field.

  6. In the Search list, select All, Up, or Down.

  7. Select the Match Case check box if you want to perform a case-sensitive search.

  8. Select the Search Fields As Formatted check box if you want to search the data as it is formatted in the datasheet.

  9. Click Find Next. Access highlights the next instance of the text you typed in the Find What text box.

  10. Repeat step 9 to find more instances of the text. When you have run through the entire table, Access displays a message box telling you it has finished searching the records.

  11. Click OK, then click Cancel to close the Find And Replace dialog box.

To replace table data

  1. Open the table in Datasheet view.

  2. Do either of the following to open the Find And Replace dialog box:

    • On the Home tab, in the Find group, click Find.

    • Press Ctrl+F.

  3. On the Replace tab, enter the text you want to find in the Find What box.

  4. Use the Replace With text box to enter the text you want to substitute for the found text.

  5. In the Look In list, select Current Field or Current Document (which refers to the entire table).

  6. In the Match list, select Whole Field, Any Part Of Field, or Start Of Field.

  7. In the Search list, select All, Up, or Down.

  8. Select the Match Case check box if you want to perform a case-sensitive search.

  9. Select the Search Fields As Formatted check box if you want to search the data as it is formatted in the datasheet.

  10. Click Find Next until you come across the next instance of the text you want to replace.

  11. Replace the text:

    • If you want to replace the text one instance at a time, click Replace. Access makes the replacement and then highlights the next instance of the text. If you don’t want to replace this instance, click Find Next until you locate the next replacement; otherwise, keep clicking Replace.

    • If you want to replace all instances of the text, click Replace All. If Access displays a warning that you will not be able to undo the Replace operation, click Yes to confirm that you want to continue.

  12. When you have run through the entire table or all instances of the text, Access displays a message box telling you it has finished replacing or searching the records.

  13. Click OK, then click Cancel to close the Find And Replace dialog box.

Sort records

By sorting the records in a table, you can arrange a datasheet in an order in which you can more easily find a specific record (such as the name of a contact) or a set of related records (for example, all orders shipped on September 12). You can sort a field in two ways:

  • Ascending Sorts the field in ascending order, as follows: alphabetical (A to Z) for text fields; smallest to largest (0 to 9) for numeric fields; oldest to newest for date and time fields.

  • Descending Sorts the field in descending order, as follows: reverse alphabetical (Z to A) for text fields; largest to smallest for numeric fields; newest to oldest for date and time fields.

You can also sort a table by multiple columns. For example, you can sort a table on the Launch Date field in ascending order (January through December) and the Campaign Budget field in descending order (largest to smallest) to see the sequence of larger expenditures for campaigns scheduled to launch over a period of time. When you sort by multiple fields, apply the second, or inner, sort first (in this case, budgets in descending order).

Text fields provide the sorting options Sort A To Z or Sort Z To A. Number fields have the commands Sort Smallest To Largest or Sort Largest To Smallest, and date fields use Sort Oldest To Newest and Sort Newest To Oldest.

For more advanced sorts, Access displays a window with a list of the table’s fields in the top pane and a grid in the bottom pane. (If you open this window when a sort order is applied, the grid shows the field and sort criteria specified.)

Sort by multiple fields by adding them to the grid.

To sort by a field, you add it to the Field row in the grid. By adding other fields to the Field row, you can create a multiple-field sort. The Sort row provides the Ascending and Descending options, which you use to apply a sort order.

To sort records from the Home tab

  1. Open the table in Datasheet view.

  2. Click the field you want to sort by.

  3. On the Home tab, in the Sort & Filter group, click Ascending or Descending.

To sort records by using a shortcut menu

  1. Open the table in Datasheet view.

  2. Right-click the field you want to sort by, then click the command for the sort order you want to use. (The command names depend on the field’s data type.)

To set up and apply an advanced sort

  1. Open the table in Datasheet view.

  2. On the Home tab, in the Sort & Filter group, click Advanced, then click Advanced Filter/Sort.

  3. In the Advanced Filter/Sort window, drag the field or fields you want to sort by to the Field row in the grid.

  4. In the Sort row, select the sort order you want to use.

  5. In the Sort & Filter group, click Toggle Filter to sort the records.

  6. Click Remove Sort to return the table to its default sort order.

Filter records

By applying a filter to a table in Datasheet view, you can select a specific set of records to review, such as all orders placed on or after a specific date or all amounts less than or greater than the target you specify.

Access provides several ways to filter records. You can filter by one or more of the values in a field, for example. Another way is to filter by selection, using either the entire value or a portion of it as the filter criterion. After selecting the criterion, you can apply an expression to apply the filter. For text fields, you can apply the expressions Equals Value, Does Not Equal Value, and Contains Value. For date fields, the expressions include Equals Date, Does Not Equal Date, On Or Before Date, On Or After Date, and Between, which lets you specify a date range to use as the filter. For Number fields, Less Than Or Equal To and Greater Than Or Equal To options are included along with Equals, Does Not Equal, and Between.

You can filter by selection progressively to home in on a specific set of records. For example, the first time you apply a filter, the criteria you use might reduce 200 records to 75. Filter the remaining records by using different criteria to review a smaller subset.

Access provides additional filters based on a field’s data type. The Text Filters command displays the Equals, Does Not Equal, Begins With, Does Not Begin With, Contains, Does Not Contain, Ends With, and Does Not End With filters. You choose a filter, then specify the criteria in the Custom Filter dialog box that Access displays.

The Date Filters commands include options such as Before, After, and Between, in addition to Next Week, Last Week, Year To Date, and many others. You can also choose All Dates In Period and then choose a period such as Quarter 1 or a specific month of the year.

Select a date filter to apply it to the field.

Advanced filtering options include Filter By Form and Advanced Filter/Sort. When you filter by form, Access opens a blank datasheet with the names of the table’s fields at the top of each column. You can then enter or select criteria for the field you want to use in the filter. You can choose a value for more than one field to create an AND condition—for example, a filter that displays records for products whose size equals Large and whose color equals Blue. Use the Or tab at the bottom of the window to set up additional values for the filter. If you specify filter criteria on the Or tab, Access returns records that match the criteria specified on either the Look For tab or the Or tab.

Select criteria to filter by form.

In the Advanced Filter/Sort window, use the grid below the list of table fields to build filter criteria. To define the criteria, you enter an expression such as ="France" for a text value or <=#2/20/2020# for a date field.

Filters are similar to Access queries in that they define criteria that display a subset of a table’s records. The similarity between filters and queries is apparent when you work with two other commands on the Advanced Filter/Sort menu: Load From Query and Save As Query. With these commands, you can use a query you’ve defined as a filter. Access shows the query’s fields and criteria in the design grid area of the Advanced Filter/Sort window. You can also save a filter as a query that you can then run independently or include in other queries.

Tip Use the Clear Grid command on the Advanced Filter Options menu to remove any criteria from the grid.

To filter by a field in Datasheet view

  1. Do either of the following to display the Sort & Filter menu for the field you want to filter by:

    • Select the field you want to filter by, and then on the Home tab, in the Sort & Filter group, click Filter.

    • In the field header of the field you want to filter by, click the arrow.

  2. On the Sort & Filter menu, clear the (Select All) check box.

  3. Select the check box for each field value you want to view.

  4. Click OK to apply the filter.

To filter by selection in Datasheet view

  1. Select the value or the portion of a value you want to use as the filter.

  2. On the Home tab, in the Sort & Filter group, click the Selection button, then click operatorvalue, where operator is a conditional operator such as Equals, Does Not Equal, Contains (for text), or Less Than Or Equal To (for numbers), and value is the value you selected in step 1. (Clicking Toggle Filter removes the filter from the table.)

To filter by form

  1. On the Home tab, in the Sort & Filter group, click Advanced, then click Filter By Form.

  2. In the Filter By Form window, on the Look For tab, enter the value in the field or fields you want to use as the filter.

  3. Click the Or tab to set up alternative conditions.

  4. In the Sort & Filter group, click Toggle Filter to apply the filter. (Clicking Toggle Filter again removes the filter.)

To create an advanced filter

  1. On the Home tab, in the Sort & Filter group, click Advanced, then click Advanced Filter/Sort.

  2. In the Advanced Filter/Sort window, in the Field row in the grid, select the fields you want to use in the filter.

  3. In the Criteria row, specify the expression to use in the filter.

  4. In the Sort & Filter group, click Toggle Filter to apply the filter. (Clicking Toggle Filter again removes the filter.)

To use a query as a filter

  1. On the Home tab, in the Sort & Filter group, click Advanced, then click Advanced Filter/Sort.

  2. Click Advanced again, then choose Load From Query.

  3. In the Applicable Filter dialog box, select the query you want to use as a filter, then click OK.

  4. In the Sort & Filter group, click Toggle Filter to apply the filter. (Clicking Toggle Filter again removes the filter.)

To save a filter as a query

  1. On the Home tab, in the Sort & Filter group, click Advanced, then click Advanced Filter/Sort.

  2. In the Advanced Filter/Sort window, in the Field row in the grid, select the fields you want to use in the filter.

  3. In the Criteria row, specify the expression to use in the filter.

  4. Click Advanced, then choose Save As Query.

  5. Enter a name for the query in the Save As Query dialog box, then click OK.

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

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

    • Open the Customers table in Datasheet view.

    • In the Contact Title field, replace all instances of Marketing Assistant with Marketing Specialist.

    • Sort the table records in ascending order by the Country field and then by the Company Name field.

    • Open the Orders table in Datasheet view.

    • Filter the table to show only records where the Employee field is Peacock, Margaret.

    • Open the Products table in Datasheet view.

    • Create an advanced filter that shows only records that have a category of Confections and a unit price greater than or equal to $30. Apply the filter, then save the filter as a query with the name ExpensiveConfections.

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

Objective 2.4: Create and modify fields

Table fields are defined not only by their name and data type. Field properties add to the definition of a field by specifying a format (as for Date/Time fields), whether a field is required, whether Access creates an index for that field, the field’s default value, and other information.

This topic expands on how tables are defined. It describes how you create and delete fields and how you work with field properties to fine-tune a table’s design. You learn how to set field sizes and captions, input masks, field validation rules, and default values. This section also describes how to increment the value in a field automatically and how to change a field’s data type.

You can accomplish most of the tasks described in this section with the table open in either Design view or Datasheet view.

Add and delete fields

In Design view, you can add a field by entering the field name in the first blank row or insert a row where you want the field to appear. (In the latter case, the row is inserted above the row you select.) If you delete a field that contains data, Access asks for confirmation, warning that you will permanently delete the field and its data.

When a table is open in Datasheet view, you add and delete fields in a couple of ways. The Click To Add column heading displays a list of field data types you can apply to the field. You can also add fields by working with commands in the Add & Delete group on the Table Tools Fields tool tab. A field you insert appears to the right of the field selected in the table. The Add & Delete group provides options that correspond to Access data types. Use the Short Text, Number, Currency, Date/Time, and Yes/No commands to insert a field and apply that data type. (The Number command inserts a field whose Field Size property is set to Long Integer.) The More Fields command opens a menu that provides additional data types, including Attachment, Hyperlink, and Long Text, plus a variety of formats for the Number, Date/Time, and Yes/No data types.

The last category in the More Fields list is the Quick Start group. These Quick Start items contain one or more fields that are assigned appropriate data types. You can add Quick Start items (also known as data type application parts) to a table to define a group of related fields.

In fields provided by Quick Start data types, many field properties are set so that you can start using the fields to capture data without additional work. For example, the Address item adds fields named Address, City, State Province, ZIP Postal, and Country Region. (You can rename the fields to fit your database.) The Payment Type item inserts a lookup field that includes the list Cash, Credit Card, Check, and In-Kind.

You can also create your own Quick Start data types. Data type application parts that you create are stored in the default location for Access database templates (usually AppData\Roaming\Microsoft\Templates under your user profile) and use the .accft filename extension. You can share these files so that colleagues and coworkers can also work with the data types you create.

The Add & Delete group also includes a Delete button. Keep in mind that Access prevents you from deleting a field that is part of a relationship and displays a message box informing you that you must delete the relationship first.

To insert a field in an existing table in Design view

  • Right-click the existing field above which you want to insert the new field, then click Insert Rows.

To add a field to a table in Design view

  1. Using either a blank row that you’ve inserted in the table, or the first blank row below the table’s existing fields, use the Field Name column to enter the name of the field.

  2. In the Data Type column, select the data type for the field.

  3. In the Description column, enter a brief description of the field.

  4. In the Field Properties area, set properties such as Field Size, Format, Indexed, and Required.

To add a field to a table in Datasheet view

  1. To the right of the last field, click the Click To Add arrow, then select the data type you want to apply to the field.

  2. Replace the FieldN column heading with the name of the field.

Or

  1. Select the field that you want to insert the new field after.

  2. On the Fields tool tab, in the Add & Delete group, click the data type for the field you want to insert. Click More Fields to display an extended list of data types.

  3. In the column heading row, replace the placeholder name (FieldN) with the name of the field.

To insert a Quick Start data type in Datasheet view

  1. Select the field that you want to insert the new fields after.

  2. On the Fields tool tab, in the Add & Delete group, click More Fields, and in the Quick Start section of the menu, click the field or field set you want to insert.

To define a custom data type application part

  1. In the datasheet, select the field or fields you want to include in the custom data type.

  2. On the Fields tool tab, in the Add & Delete group, click More Fields, then click Save Selection As New Data Type.

  3. In the Create New Data Type from Fields dialog box, enter a name and description for the custom data type. Select an entry in the Category list, or enter the name for a new category. Then click OK.

    Save a set of fields as a data type application part.

To rename a field in Datasheet view

  1. Right-click the field heading, then click Rename Field to activate the field name for editing.

  2. Enter the new field name, then press Enter.

To delete a field in Datasheet view

  1. Right-click the field heading, then click Delete Field.

  2. When Access prompts you to confirm that you want to delete the field, click Yes.

To rename a field in Design view

  • In the Field Name column, select the field name, then enter the new name.

To delete a field in Design view

  1. Right-click the row selector to the left of the field name, then click Delete Row.

  2. When Access prompts you to confirm that you want to delete the field, click Yes.

Add validation rules to fields

The data type you assign to a field prevents some erroneous data entry. For example, enter the text "test" in a Date/Time field, and Access displays a warning telling you that the value does not match the Date/Time data type. You can define a validation rule for a field to further control the data a user can enter. Validation rules don’t apply for all types of fields. You use them most often for fields that use the Short Text, Number, Currency, or Date/Time data types.

Using a validation rule and message.

A simple validation rule might compare the value of a field to one or more constants. For example, enter the expression <1000 as a field validation rule to ensure that the field contains no values greater than or equal to 1,000. Be sure to enclose text strings in quotation marks, and enclose dates with pound signs (#). For example, you can create a validation rule that specifies a list of valid values for a product size field by using the expression “Large“ OR “Medium“ OR “Small”. For a Date/Time field, the expression BETWEEN #1/1/2020# and #12/31/2020# sets the field validation rule so that only dates in calendar year 2020 are valid.

You can use the LIKE operator and wildcard characters to specify a valid pattern. For example, for a five-digit US ZIP Code, use the expression LIKE “#####”. You can also define an error message that Access displays when invalid data is entered in the field.

To add a field validation rule in Datasheet view

  1. Open the table in Datasheet view and select the field you want to add the rule to.

  2. On the Fields tool tab, in the Field Validation group, click the Validation arrow, then click Field Validation Rule.

  3. In the Expression Builder, enter the expression that defines the rule, then click OK.

To create a validation message

  1. On the Fields tool tab, in the Field Validation group, click the Validation arrow, then click Field Validation Message.

  2. In the Enter Validation Message dialog box, enter the message that Access displays if users enter invalid data, then click OK.

To add a field validation rule in Design view

  1. Open the table in Design view and select the field you want to add the rule to.

  2. In the Field Properties area, in the Validation Rule property box, enter the expression for the validation rule.

  3. In the Validation Text property box, enter the message you want Access to display if users enter invalid data.

Modify field properties

By default, a field’s name identifies the field in the column heading in Datasheet view or when the field is added to a form or a report. You can enter a caption to change the display name for the field. For example, a database designer might name fields by using internal capitalization, such as TaskName. To make the field’s name more readable on forms and reports, enter Task Name as the field’s caption.

The Field Size property for a field that uses the Short Text data type specifies the maximum number of characters that can be entered in the field. For example, suppose your company uses a six-character product code, with a combination of letters and numbers. You could set the Field Size property to 6 to ensure that no user enters more characters than are allowed.

For a Number field, the Field Size property specifies the extent of the numbers the field can contain (for example, Byte, Long Integer, and Single).

By using the Default Value command in Datasheet view (or the Default Value property box in Design view), you define the value that Access enters automatically for a field. For example, you might use the built-in function Now() in an order date field to fill in today’s date when a new order is entered. You can also use a text or numerical constant as a field’s default value. For example, you could set a default value for a country/region field so that the field is set automatically to the value you use most often. Likewise, if you sold certain products only with a minimum order quantity (only in units of 12, for example), you could set the Default Value property in the quantity field to reflect that amount.

When you’re building a table, the best approach is to set the data type for a field once and not change it. Changing a field’s data type can be problematic, especially after data has been added to a table. For example, if you change the data type for a Date/Time field to a Number field, the dates are converted to their serial value (12/31/2020 becomes 43830). Access can handle the conversion of dates to numbers (and back to dates), but other data types don’t work as smoothly. Access displays a warning if you change a field’s data type from Long Text to Short Text, telling you that some data will be lost.

To change a field caption

  • In Datasheet view, on the Fields tool tab, in the Properties group, click Name & Caption.

  • In Design view, enter the caption in the Caption property box.

To change a field size

  • In Datasheet view, to change the field size for a text field, click the Fields tool tab, then select the field. In the Properties group, enter the field size in the Field Size box.

  • In Design view, select the field, and then, in the Field Properties area, click in the Field Size property box. For a number field, select the field size setting from the list Access provides; for a text field, enter the value you want to use.

To set the default value for a field

  • In Datasheet view, on the Fields tool tab, in the Properties group, click Default Value to open the Expression Builder, and then enter the default value or use the Expression Builder to create an expression that calculates the default value.

  • In Design view, in the Field Properties area, click in the Default Value property box, then do either of the following:

    • Enter the default value.

    • Click the ellipsis button (…) in the property box to open the Expression Builder, then enter the value or expression there.

To change the data type of a field

  • In Design view, select the new value in the Data Type list.

  • In Datasheet view, on the Fields tool tab, in the Formatting group, expand the Data Type list, then click the data type you want.

Automate field values and formatting

Assign the AutoNumber data type to a field to have Access add a unique number in that field as you add records to a table. AutoNumber is often assigned to an ID field that is used as the table’s primary key.

A table can include only one AutoNumber field whose Field Size property is set to Long Integer. (You can use the AutoNumber data type for other fields if the Field Size property is set to Replication ID.) In Design view, check the setting for the New Values property for an AutoNumber field. The default setting is Increment, which means Access assigns numbers sequentially. The Random setting produces random numbers for new records. You might use the Random setting to create unique-order IDs.

An input mask defines a specific pattern for the data in a field. Adding an input mask to a field assists users with entering data correctly. Access provides input masks for data such as phone numbers, US Social Security numbers, ZIP Codes, passwords, and date formats. It is important to remember that an input mask does not affect how data is stored. A field’s data type and other properties define that format. An input mask affects only whether the data has been entered in a format Access will accept.

The Input Mask Wizard lists the input masks available for the data type of the currently selected field. For a Date/Time field, Access provides entries such as Long Time, Short Date, and Medium Date. For Short Text fields, the list includes input masks for phone numbers, ZIP Codes, and other sorts of data.

Input masks keep data consistent.

You can enter sample data in the Try It box to view how the mask controls data entry. For example, select the Short Date mask, then enter a month abbreviation in the Try It box. You can specify how an input mask is designed by using special characters to define the mask. In a mask, a zero (0) indicates that a user must enter a digit (from 0 through 9) in that placeholder. A nine (9) marks an optional digit. An uppercase L is used to denote a required letter. An optional letter is marked with a question mark (?). You can create an input mask of your own by using the defined special characters to set up the mask.

See Also You can find a complete list of special characters and how to use them in the article “Control data entry formats with input masks” on the Office support site at https://support.office.com.

To configure a field to automatically increment the field value

  1. Open the table in Design view and select the field you want to automatically increment.

  2. Set the field data type to AutoNumber.

  3. In the Field Properties area, expand the New Values property list, then click Increment.

To specify an input mask for a field

  1. Open the table in Design view and select the field you want to apply an input mask to.

  2. Click in the Input Mask property box, then click the ellipsis to start the Input Mask Wizard.

  3. Work through the wizard to select the mask you want to use, click Edit List to modify a built-in mask, or create one of your own.

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

  • Open the AccessExpert_2-4 database and do the following:

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

    • Open the Customers table in Datasheet view.

    • Select the fields Company Name, Contact Name, and Contact Title, then create a Quick Start data type named Contact Basics.

    • Create a new table in Design view. Add a field named ContactID. Set the field to the AutoNumber data type and mark this field as the table’s primary key. Save the table and name it Contacts.

    • Switch to Datasheet view, then add the Contact Basics data type application part to the Contacts table. Save and close the table.

    • Open the Order Details table in Design view. Create a validation rule for the Quantity field so that amounts entered must be less than 100. Enter the validation text Order quantities of 100 or more require approval.

    • Save the table, then switch to Datasheet view.

    • Update the Quantity field in the first record to 101, then click away from the field. Observe the message Access displays. Click OK in the message box, and then reset the field to its original value.

    • For the Employees table, use the Name & Caption command to update the Caption property for the Courtesy field so that it reads Title of Courtesy.

    • Save the table, then switch to Design view.

    • Change the format of the BirthDate field to Long Date.

    • Create an input mask for the HireDate field that uses the Medium Date format.

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