Description
Assignment Instructions:Follow the detailed instructions Objective 2 section of MOS Study Guide for Microsoft Access Expert Exam MO-500 Refer to the data files provided in the introduction section of the text or in Data Files area of our course.
Unformatted Attachment Preview
Skip to Content
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.
To complete the practice tasks in this chapter, you need the practice files contained in
the MOSAccessExpert2019Objective2 practice file folder. For more information, see “Download the
practice files” in this book’s Introduction.
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 Data – Excel 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:
o
To import the data from a worksheet, select Show Worksheets, then click the worksheet
name.
o
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:
o
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.
o
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.
o
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 Data – Excel 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 Data – HTML 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:
o
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.
o
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.
o
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 Data – HTML 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 Data – XML 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:
o
Structure Only Select this option to import just the field names.
o
Structure and Data Select this option to import both the field name and the data.
o
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 Data – Text 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:
o
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.
o
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:
o
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.
o
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.
o
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 Data – Text 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 Data – Outlook 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:
o
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.
o
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.
o
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 Data – Outlook 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:
o
From File, and then Excel
o
From File, and then HTML Document
o
From File, and then Text File
o
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 Data – Access 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 Data – Text 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 Data – Excel 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 Data – ODBC 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 Data – ODBC 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 Data – ODBC 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 Data – ODBC 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 Data – dBASE 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.
Objective 2.1 practice tasks
The practice files for these tasks are located in the MOSAccessExpert2019Objective2 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:
o
If the Info bar appears, click the Enable Content button.
o
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.
o
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.
o
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.
o
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.
o
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.
o
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.
o
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:
o
To hide one field, right-click the field column heading, then click HideFields.
o
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:
o
To freeze one field, right-click the field column heading, then click FreezeFields.
o
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
