Student Guide

40572A
Microsoft Access expert 2019
Module 2: Designing and setting up data structure
 Designing and setting up data structure

Contents
Contents........................................2
Module overview...........................4
Description.................................4
Scenario.....................................5
Cornerstone................................5
Lesson 1: Starting the database design 6
Overview....................................6
Warm-up.....................................6
Topic 1: Organize related data...7
The design process.................7
Dividing the information into tables 7
Turning information items into columns 8
Activity: Think-pair-share........9
Try-it: Organize related data...9
Topic 2: Overview of data types10
Activity: Pose a challenge.....12
Try-it: Overview of data types12
Topic 3: Create primary keys. . .13
Specifying primary keys........13
Setting a primary key............13
Remove the primary key.......14
Activity: Discuss and learn....15
Try-it: Create primary keys. . .15
Wrap-up....................................16
Lesson 2: Starting an Access database 17
Overview..................................17
Warm-up...................................17
Topic 1: Create an Access file. .18
Create a database by using a template 18
Create a blank database.......18

2
 Designing and setting up data structure

Compact and repair a database19

Explore ways to compact and repair a database 19
Activity: Discuss and learn....20
Try-it: Create an Access file. .21
Topic 2: Create a table.............21
Add a table............................21
Copy data from another source22
Import, append, or link to data from another source 23
Activity: Show and tell...........25
Try-it: Create a table.............26
Topic 3: Use AutoNumber and set the primary key 26
Activity: Pose a challenge.....27
Try-it: Use AutoNumber and set the primary key 28
Wrap-up....................................29
Lesson 3: Creating table fields....30
Overview..................................30
Warm-up...................................30
Topic 1: Create text fields........31
Adding fields.........................31
Activity: Show and tell...........34
Try-it: Create text fields........34
Topic 2: Create numeric fields..35
Activity: Discuss and learn....36
Try-it: Create numeric fields..37
Topic 3: Create Date/Time fields37
Understand Date/Time fields.38
Restrict data input by using validation rules 39
Activity: Tell a story..............41
Try-it: Create Date/Time fields41
Topic 4: Create Yes/No fields....42
Activity: Pose a challenge.....43
Try-it: Create Yes/No fields....43
Wrap-up....................................44
Glossary...................................45

3
 Designing and setting up data structure

Cornerstone.................................46
Overview..................................46
Objectives.................................46
Duration...................................47
Instructions...............................47
Tasks........................................47

4
 Designing and setting up data structure

Module overview
Description
Now that you’ve had a chance to review an existing database and explore
the Microsoft Access 2019 interface, it's time to begin planning and creating
your own database. This module introduces you to organizing related data
into databases. You’ll become familiar with database tables, lists, and data
types. You’ll also learn about primary keys and their purpose. Finally, you'll
learn about creating Access files and tables, using AutoNumber, setting the
primary key, and creating table fields. You'll have plenty of opportunities to
apply these concepts.
You'll begin by learning how to organize related data into tables, and then
define and describe a primary key. Next, you'll create an Access database
file with multiple tables, utilize AutoNumber, and set the primary key.
Finally, you'll create text, numeric, Date/Time, and Yes/No fields in a table,
and set various field properties.
The following table outlines the lesson objectives and their corresponding
Microsoft Office Specialist (MOS) exam objectives.

Lesson Learning objective Exam objectives

Starting the Organize related data into tables,  Not mapped
database describe data types and primary
design keys and describe their purpose.

Starting an Create an Access database file,  1.2.3

Access create tables and add table  2.1.2
database descriptions, use AutoNumber,  2.1.3
and set the primary key.  2.2.3
 2.4.6

Creating table Create text, numeric, Date/Time,  2.4.1

fields and Yes/No fields in a table, and  2.4.2
set field properties.  2.4.3
 2.4.4
 2.4.5

Lesson Learning objective Exam objectives

Cornerstone: Design and create an Access  All the
Create the CSA database, create table fields, preceding

5
 Designing and setting up data structure

Operations import tables, link tables from a exam

database and Microsoft Excel spreadsheet. objectives
tables

Table 1: MOS objectives by lesson

Scenario
Your visit with the Harvesting team at Munson’s Pickles and Preserves Farm
was very worthwhile. They demonstrated how they use their database and
described the thinking process that they used to decide which data to store
and how to label that data to maximize its usefulness in their decisions. It’s
now time to sit down with your Community Supported Agriculture (CSA)
program team to figure out what data you need to store and to think about
how you want to use the data. The answers to those two questions will
dictate your next steps—creating tables and fields to store the data.

Cornerstone
At the end of this module, you’ll work on a Cornerstone project, in which
you’ll design and create a database file. You’ll organize related data into the
basis for tables, use AutoNumber and set the primary key, create table
fields, and also compact and repair the database.

6
 Designing and setting up data structure

Lesson 1: Starting the

database design
Overview
In this lesson, you'll discuss how to sort data into tables, determine field
names, and predict the data type based on field names. You’ll then partner
with a classmate to identify unique information based on field names and
types to include in the table. At the end of this lesson, you'll be able to
organize related data into tables and lists. You’ll then describe data types
and define and explain the purpose of a primary key.

Warm-up
Use these questions to find out what you already know about this lesson’s
topics:
1. What is the storage structure for data in an Access database?
Select the correct option.
a. Forms
b. Queries
c. Reports
d. Tables
2. Which of the following are data types in Access?
Select all that apply.
a. Yes/No
b. Table
c. Large Number
d. OLE Object
3. Select here to enter text. are columns that uniquely identify each row
stored in the table.
Fill in the blank space.

7
 Designing and setting up data structure

Topic 1: Organize related data

Properly designing a database will provide you access to up-to-date,
accurate information. Because a correct design is essential to achieving your
goals in working with a database, it makes sense to invest the time required
to learn the principles of good design. In the end, you're much more likely to
create a database that meets your needs and can easily accommodate
change. In this topic, you'll learn how to design a database properly.

The design process

The design process consists of the following steps:
1. Determine the purpose of your database. Note down what you intend to
use the database for and the purpose you want to achieve.
2. Find and organize the information required. Gather all the types of
information that you might want to record in the database, such as
product name and order number.
3. Divide the information into tables. Divide your information items into
major entities or subjects, such as Products or Orders. Each subject then
becomes a table.
4. Turn information items into columns. Decide what information you want to
store in each table. Each item is a field and is displayed as a column in
the table. For example, an Employee table might include fields such as
Last Name and Hire Date.

Dividing the information into tables

To divide the information into tables, choose the major entities, or subjects.
For example, after finding and organizing information for a product sales
database, the preliminary list might look like this:
 Customers
o Name
o Address
o Email
 Suppliers
o Company name
o Contact name
o Address

8
 Designing and setting up data structure

 Products
o Product name
o Price
o Units in stock
 Orders
o Order number
o Order date
o Product
The major entities included here are the customers, the suppliers, the
products, and the orders. Therefore, it makes sense to start with these four
tables: one for facts about products, one for facts about suppliers, one for
facts about customers, and one for facts about orders. Although this doesn’t
complete the list, it's a good starting point. You can continue to refine this
list until you have a design that works well.

Turning information items into columns

To determine the columns in a table, decide what information you need to
track about the subject recorded in the table. For example, for the
Customers table, Name, Address, and Email comprise a good starting list of
columns. Each record in the table contains the same set of columns so that
you can store Name, Address, and email information for each record. For
example, the Address column contains customers’ addresses. Each record
contains data about one customer, and the address field contains that
customer's address.

Additional information
For more information about database design, refer to:
Database design basics

9
 Designing and setting up data structure

Caution
This course uses database files that follow a general storyline
for the CSA program at Munson’s Pickles and Preserves
Farm. These database files are the Learning Activity
Resource files that you’ll use to work on each module’s
activities and try-its. Each Learning Activity Resource file
contains the data and objects, such as tables, queries, forms,
and reports, that you need to complete a corresponding
activity.
Due to this course’s modular nature, the objects and data in
these files might vary across activities or try-its. Therefore,
it’s important that you start your activities or try-its only with
the designated files, as per applicable instructions. You also
should note that screenshots in the topic content are for
illustration purposes only and might appear different than
those that are in the Learning Activity Resource files.

Activity: Think-pair-share
In this activity, you'll sort related information into appropriate tables.

Resources required
 None

Activity instructions
Participate in the activity by following these instructions:
1. Pair with a classmate.
2. Review the field names that your teacher provided.
3. Review the table names and descriptions that your teacher provided.
4. Discuss your grouping and sorting options with your partner.
5. Place the fields in the appropriate table.
6. Ask clarification questions.

Try-it: Organize related data

In this standalone try-it activity, you'll think about the information to
include in the Munson’s Pickles and Preserves Farm CSA database, and you'll
create a list of tables and fields to group and organize relevant information.

Resources
You'll need the following resource for this activity:

10
 Designing and setting up data structure

 None

Instructions
1. Review the module scenario.
2. Think about at least two tables that you would use to group information
collected for use in the database.
3. List at least five fields to include for each table.
4. Share your ideas.
5. Ask clarification questions.

Topic 2: Overview of data types

Careful selection of data types can help you take advantage of more Access
features, such as data validation and functions, and improve the accuracy of
the information that you’re storing. In this topic, you'll learn that when
creating tables in Access, you must select a data type for each column of
data.
The following table lists the data types available in Access.

Data type Usage Size

Short Text Alphanumeric data, such as names Up to 255
and titles characters.
Long Text Large amounts of alphanumeric data, Up to
such as sentences and paragraphs approximately 1
gigabyte (GB), but
controls to display
a long text are
limited to the first
64,000 characters
Number Numeric data 1, 2, 4, 8, or 16
bytes
Large Numeric data 8 bytes
Number
Date/Time Dates and times 8 bytes
Currency Monetary data, stored with four 8 bytes
decimal places of precision
AutoNumbe A unique value generated by Access 4 bytes (16 bytes
r for each new record for Replication ID)
Data type Usage Size
Yes/No Boolean (true/false) data; Access 1 byte
stores the numeric value zero (0) for
false, and -1 for true.

11
 Designing and setting up data structure

OLE Object Pictures, graphs, or other ActiveX Up to

objects from another Windows–based approximately 2
application GB
Hyperlink A link address to a document or file Up to 8,192 (each
on the internet, an intranet, a local part of a Hyperlink
area network (LAN), or your local data type can
computer contain up to 2048
characters)
Attachment You can attach files such as pictures, Up to
documents, spreadsheets, or charts; approximately 2
each Attachment field can contain an GB
unlimited number of attachments per
record, up to the storage limit of the
size of a database file. Note that the
Attachment data type isn't available
in MDB file formats.
Calculated You can create an expression that Depends on the
uses data from one or more fields. data type of the
You can designate different result Result Type
data types from the expression. Note property. Short
that the Calculated data type isn't Text data type
available in MDB file formats. results can have up
to 243 characters.
Long Text,
Number, Yes/No,
and Date/Time
should match their
respective data
types.
Lookup The Lookup Wizard entry in the Dependent on the
Wizard Data Type column in Design View is data type of the
not a data type. When you choose this lookup field.
entry, a wizard starts to help you
define either a simple lookup or a
complex lookup field. A simple
lookup field uses the contents of
another table or a value list to
validate the contents of a single value
per row. A complex lookup field
allows you to store multiple values of
the same data type in each row.
Table 2: Data types available in Access

12
 Designing and setting up data structure

Additional information
For more information on data types, refer to: Data types for
Access desktop databases

Activity: Pose a challenge

In this activity, you'll think about the fields that your teacher provided and
determine what kind of data type you should use in the table field.

Resources required
 None

Activity instructions
Participate in the activity by following these instructions:
1. Review the field name that your teacher provided in Topic 1.
2. Determine the correct data type for each field.
3. Discuss your choice options.
4. Ask clarification questions.

Try-it: Overview of data types

In this standalone try-it activity, you'll determine data types of the
Munson’s Pickles and Preserves Farm CSA database.

Resources
You'll need the following resource for this try-it:
 Your list from Topic 1 of fields to include in the Munson’s Pickles and
Preserves Farm CSA database.

Instructions
You'll perform the following general tasks during this try-it:
1. Review the list of tables and fields that you created in Topic 1 for
information to collect for the Munson’s Pickles and Preserves Farm CSA
database.
2. Review the type of data types available in Access.
3. Identify which type of data type you’ll collect for each field.
4. Discuss the options for your choice.
5. Ask clarification questions.

13
 Designing and setting up data structure

Topic 3: Create primary keys

When creating a database, you should identify at least one unique number
column for each table, such as an employee ID number or a serial number.
Access uses the unique identification to quickly associate data from multiple
tables and collect that data for you. In this topic, you'll learn how to create a
column that uniquely identifies each row stored in a table.

Specifying primary keys

In database terminology, this unique identification number is called the
primary key of the table. If you already have a unique identifier for a table,
such as a product number that uniquely identifies each product in your
catalog, you can use the product number as the identifier and set it as the
table’s primary key.
You can only do this if the values in the product number column will always
be different for each record. You can't have duplicate values in a primary
key. For example, don’t use people’s names as a primary key, because
names are not unique. You could easily have two people with the same name
in the same table.
A primary key must always have a value. If a column's value can become
unassigned or unknown (a missing value) at some point, it can't be a
component in a primary key.

Setting a primary key

Unless you have a specific reason not to, you should specify a primary key
for a table. Access automatically creates an index for the primary key, which
can help improve database performance. Access also makes sure that every
record has a value in the primary key field, and that the value is always
unique. Unique values are crucial because without them, there is no way to
distinguish one row from other rows reliably.
1. Select the table whose primary key to set or change.
2. On the Home tab, in the Views group, select View, and then select
Design View.

14
 Designing and setting up data structure

3. In the table design grid, select the field or fields to use as the primary
key.
o To select one field, select the row selector for the field that you want,
as depicted in the following screenshot.

Figure 1: Setting fields as the primary key

o To select more than one field, use CTRL, and then select the row
selector for each field.
4. On the Design tab, in the Tools group, select Primary Key.
A key indicator is found to the left of the field or fields that you specify as the
primary key.

Remove the primary key

1. Select the table whose primary key you want to remove.
2. On the Home tab, in the Views group, select View, and then select
Design View.
3. Select the row selector for the current primary key. If the primary key
consists of multiple fields, use CTRL, and then select the row selector for
each field.
4. On the Design tab, in the Tools group, select Primary Key.
When you’re saving a new table without setting a primary key, Access
prompts you to create a new field for the primary key. If you select Yes,
Access creates an ID field that uses the AutoNumber data type to provide a
unique value for each record. If your table already includes an AutoNumber
field, Access uses it as the primary key. If you select No, Access does not
add a field and no primary key is set.

15
 Designing and setting up data structure

Additional information
For more information on primary keys, refer to: Specifying
primary keys, Setting a primary key, and Add or change a
table’s primary key in Access

Activity: Discuss and learn

In this activity, you'll discuss what kinds of information are unique to people.
Then you’ll examine the information that you discussed and determine if that
information is the best option as a unique identifier.

Resources required
 None

Activity instructions
Participate in the activity by following these instructions:
1. Participate in the discussion by providing examples of unique identifiers
for a specific database.
2. Ask clarification questions.

Try-it: Create primary keys

In this standalone try-it activity, you’ll determine the unique identifier
for the Munson’s Pickles and Preserves Farm CSA database tables.

Resources
 Your list from Topic 1 and Topic 2 of fields to include in the Munson’s
Pickles and Preserves Farm CSA database.

Instructions
You'll perform the following general tasks during this try-it:
1. Review the list of tables and fields that you created for your Munson’s
Pickles and Preserves Farm CSA database tables.
2. Think about the information that would be included in each field column
and identify any issues with using that field column as the primary key.

Wrap-up
Use these questions to check what you learned in this lesson:
1. What is the first step in the design process?

16
 Designing and setting up data structure

Fill in the blank space.

Select here to enter text.
2. Which of the following data field types do you use to include pictures,
graphs, or other ActiveX objects from another Windows–based
application?
Select the correct option.
a. Calculated
b. OLE object
c. Hyperlink
d. Attachment
3. If you don't set a primary key, Access will ask if you would like to set one.
Select the correct option.
a. True
b. False

17
 Designing and setting up data structure

Lesson 2: Starting an Access

database
Overview
At the end of this lesson, you'll be able to create an Access database file,
create tables in multiple ways, add table descriptions, use AutoNumber, and
set the primary key. You'll explore the available database templates, and
learn how to compact and repair a database. You’ll then follow a
demonstration of the methods for creating database tables and predict how
to create a unique ID for records.

Warm-up
Use these questions to find out what you already know about this lesson’s
topics:
1. What happens when you attempt to create a database but do not supply
a file name?
Select the correct option.
a. Access does not create the database.
b. Access creates a temporary, unnamed database.
c. Access creates a database in the default location, with the name
Database followed by a sequential number.
d. Access creates a database in the default location, with the name
MyDatabase followed by a sequential number.
2. In Access, a template is which of the following?
Select the correct option.
a. A database to manage contacts
b. Where a database is stored
c. Two tables linked together
d. A ready-to-use database

18
 Designing and setting up data structure

3. When you create a new blank database, Access opens a database that
contains which of the following?
Select the correct option.
a. One of each type of database object
b. A table
c. Sample data
d. A template

Topic 1: Create an Access file

Access comes with a variety of templates that you can use as provided
or as a starting point. A template is a ready-to-use database that contains all
the tables, queries, forms, macros, and reports needed to perform a specific
task. Some templates contain a few sample records to help demonstrate
their use. If you're not interested in using a template, you can create a
database by building tables, forms, reports, and other database objects. In
this topic, you'll learn how to organize your information by creating a new
blank database, and then prevent or fix problems that arise by using the
Compact and Repair Database command.

Create a database by using a template

1. On the File tab, select New, and then select the template that you want
to use. Several sets of templates are available in the New tab.
2. Enter a file name in the File Name box. To change the location of the file
from the default, select Browse for a location to put your database,
browse to the new location, then select OK.
3. Select Create.

Create a blank database

1. On the File tab, select New, and then select Blank Database.
2. Enter a filename in the File Name box. To change the location of the file
from the default, select Browse for a location to put your database,
browse to the new location, and then select OK.
3. Select create. Access creates the database with an empty table named
Table1 and then opens Table1 in Datasheet view. The cursor is placed in
the first empty cell in the Click to Add column.
4. Begin entering the data. Entering data in Datasheet view is designed to
be very similar to working in an Excel worksheet. Create the table
structure automatically as you enter data. Define new fields as you add a

19
 Designing and setting up data structure

new column to the datasheet in the table. Access automatically sets each
field's data type, based on the data that you enter.

Additional information
For more information about creating databases, refer to:
Create a new database

Compact and repair a database

Database files can quickly grow as you use them, sometimes affecting
performance. Files can also occasionally become corrupt or damaged. You
can use the Compact and Repair Database command to prevent or fix
these problems. The compact process does not compress your data—it
makes your database file smaller by eliminating unused space. The
Compact and Repair Database command can also help improve your
database's performance.

Explore ways to compact and repair a

database
You can use several approaches to compact and repair a database. One
common practice is to automatically compact and repair a database when it
closes. Also, you can manually run the Compact and Repair Database
command when you have a database open. You can also run it on a
database that isn't open.

Automatically compact and repair a database when it

closes
You can select the Compact on Close database option if you want to
automatically compact and repair a database when it closes. Setting this
option affects only the database that is currently open. Set this option
separately for each database that you want to automatically compact and
repair. In multiuser databases, you might not want to set this option because
it can momentarily disrupt database availability.
1. On the File tab, select Options.
2. In the Access Options dialog box, select Current Database.
3. In the Application Options section, select the Compact on Close check
box.
4. Select OK.
5. Close and reopen the database for the option to take effect.

20
 Designing and setting up data structure

Manually compact and repair a database that you have

open
 On the File tab, select Info then Compact & Repair Database.
Access creates a copy of the compacted and repaired database in the same
location.

Additional information
For more information about compacting and repairing a
database, refer to: Compact and repair a database

Activity: Discuss and learn

In this activity, you'll review the available database templates in Access,
then discuss when it's advisable to use a template and when you should use
a blank database. Lastly, you’ll follow along as your teacher demonstrates
the Compact and repair feature.

Resources required
 None

Activity instructions
Participate in the activity by following these instructions:
1. Open Access and follow along as your teacher demonstrates how to
create a database by using a template.
2. Discuss the included template options and the tables and fields in the
template that you choose.
3. Participate in the discussion about when you would choose a template
and when you would choose a blank database.
4. Follow along as your teacher demonstrates the Compact and Repair
database command.
5. Ask clarification questions.

21
 Designing and setting up data structure

Try-it: Create an Access file

In this standalone try-it activity, you'll put into practice what you
learned in the activity by creating a blank database file.

Resources
 None

Instructions
You'll perform the following general tasks during this try-it:
1. Create a new blank database for the Munson’s Pickles and Preserves Farm
CSA program and name the database CSA.
2. Set the database to automatically compact when it closes.

Topic 2: Create a table

In this topic, you'll learn how to create a new table in an existing
database, copy data from another source, and import, append, or link to data
from another source to an existing table in Access.

Add a table
You can add new tables to an existing database by using the commands on
the Create tab in the Tables group.

Figure 2: Tables group options

22
 Designing and setting up data structure

Create a table, starting in Datasheet view. In Datasheet view, you can

enter data immediately and let Access build the table structure for you. Field
names are assigned numerically (Field1 and Field2, for example), and Access
automatically sets each field's data type, based on the data that you enter.
1. On the Create tab, in the Tables group, select Table. Access creates the
table and selects the first empty cell in the Click to Add column.
2. On the Fields tab, in the Add & Delete group, select the type of field
that you want to add. If you don't find the type that you want, select
More Fields.
3. You can move the field by dragging it. When you drag a field in a
datasheet, a vertical insertion bar appears.
4. To add data, begin entering the information in the first empty cell.
5. To rename a column (field), select the column heading, and then enter
the new name. You should give a meaningful name to each field so that
you can tell what it contains when you find it in the Field List pane.

Additional information
For more information about adding tables, refer to: Creating a
table

Copy data from another source

If your data is in another program, such as Excel, you can copy and paste it
into an Access table. In general, this works best if your data is already
separated into columns, as they are in an Excel worksheet. If your data is in
a word-processing program file, it's best to use tabs or tables to separate the
columns of data in that file before you copy the data. If your data needs
editing or manipulation (for example, separating full names into first and last
names), you might consider doing this before you copy the data.
When you paste data into an empty table, Access sets the data type of each
field according to what kind of data it finds there. For example, if a pasted
field contains nothing but date values, Access applies the Date/Time data
type to that field. If the pasted field contains only the words yes and no,
Access applies the Yes/No data type to the field.

23
 Designing and setting up data structure

If Access assigns generic field names, you should rename the fields as soon
as possible to avoid confusion. Use the following procedure:
1. On the Home tab, in the Records group, select Save to save the table.
2. On the Home tab, in the View group, select Datasheet View, select
each column heading, and then enter a descriptive field name for each
column.
3. Save the table again.
You can also rename the fields by switching to Design View and editing the
field names there. To switch to Design View, on the Home tab, in the View
group, select Design View.

Additional information
For more information, refer to: Copy data from another
source into an Access table

Import, append, or link to data from another

source
You might have stored data in another application, and you want to import
that data into a new table or append it to an existing table in Access. Or you
might collaborate with people who keep their data in other applications, and
you want to work with that data in Access by linking to it. Either way, Access
makes it easy to work with data from other sources. You can import data
from an Excel worksheet from a table in another Access database, from a
SharePoint list, or from a variety of other sources. The process that you use
differs slightly depending on your source, but you can use the following
procedure to get started.
1. On the External Data tab, in the Import & Link group, select New Data
Source, and then select the type of file that you're importing, as depicted
in the following screenshot.

24
 Designing and setting up data structure

Figure 3: External Data options

For example, if you're importing data from an Excel worksheet, select Excel.
If you don't find the application type that you need, select More.
2. In the Get External Data dialog box, select Browse to find the source
data file, or type the full path of the source data file in the File name box.
3. Select the option that you want. Note that all programs let you import,
and some let you append or link.
4. In the Specify section, choose how and where you want to store the data
in the current database. You can create a new table that uses the
imported data or, with some programs, you can append the data to an
existing table or create a linked table that maintains a link to the data in
the source program.
o If a wizard starts, follow the instructions on the wizard, and on the last
page of the wizard, select Finish.
o If you import objects or link tables from an Access database, either the
Import Objects or Link Tables dialog box is revealed. Choose the
items that you want and then select, OK.
 The exact process that you use depends on whether you choose to
import, append, or link data.

25
 Designing and setting up data structure

5. Access prompts you about whether you want to save the details of the
import operation that you just completed. If you think that you’ll perform
this same import operation again in the future, select Save import
steps, and then enter the details. You can then easily repeat the
operation in the future by selecting Saved Imports on the External
Data tab in the Import & Link group; if you don't want to save the
details of the operation, select Close.
o If you chose to import a table, Access imports the data into a new table
and then displays the table under the Tables group in the Navigation
Pane.
o If you chose to append data to an existing table, Access adds the data
to that table.
o If you chose to link to data, Access creates a linked table under the
Tables group in the Navigation Pane.

Additional information
For more information, refer to: Import, append, or link to data
from another source

Activity: Show and tell

In this activity, you'll observe as your teacher demonstrates how to create a
new blank table, create a linked table, and import data from an external data
source.

Resources required
 None

Activity instructions
Participate in the activity by following these instructions:
1. Follow along as your teacher demonstrates how to create a new blank
table, create a linked table, and import data from an external data source.
2. Participate in the discussion about when you could use the various
options when creating a table.
3. Ask clarification questions.

26
 Designing and setting up data structure

Try-it: Create a table

In this standalone try-it activity, you'll link and import table data.

Resources
 Your [Link] from the previous topic or
L2_T2_try_sample_csa_starter.accdb in this lesson’s Learning
Activity Resources folder.
 The customer_sample.accdb and order_sample.xlsx in this lesson’s
Learning Activity Resources folder.

Instructions
You'll perform the following general tasks during this try-it:
1. Save customer_sample. accdb and order_sample.xlsx from this
lesson’s Learning Activity Resources folder to your class folder.
2. If it isn't open already, open your [Link] from the previous topic or
L2_T2_try_sample_csa_starter.accdb in this lesson’s Learning
Activity Resources folder.
3. Import the content from the customer_sample. accdb and name the
table customer sample.
4. Link the content from the order_sample.xlsx and name the table
order_sample.

Topic 3: Use AutoNumber and set

the primary key
Unless you have a specific reason not to specify a primary key for a
table, specify one. Access automatically creates an index for the primary
key, which can help improve database performance. Access also makes sure
that every record has a value in the primary key field, and that the value is
always unique. Unique values are crucial because without them, there is no
way to distinguish a particular row from other rows reliably. In this topic,
you'll learn how to set the primary key by using an AutoNumber field.
1. In the navigation pane, select the table to which you want to add the
primary key.
2. Open the table in Design View by selecting the Home tab and View
group, and then select Design View.
3. Locate the first available empty row in the table design grid.

27
 Designing and setting up data structure

4. In the Field Name column, enter a name, such as CustomerID

5. In the Data Type column, select the drop-down arrow and select
AutoNumber.
6. In the Field Properties section, in New Values, select Increment to
use incrementing numeric values for the primary key, or select Random
to use random numbers.

Figure 4: AutoNumber field property options

When you create a new table in Datasheet view, Access automatically

creates a primary key for you and assigns it to the AutoNumber data type.

Additional information
For more information, refer to: Add an AutoNumber field as a
primary key

Activity: Pose a challenge

In this activity, you'll determine the steps to set a unique identifier or
primary key, when no natural ID exists.

Resources required
 None

28
 Designing and setting up data structure

Activity instructions
Participate in the activity by following these instructions:
1. Think back to the discussion in Lesson 1, Topic 3 about creating unique
identifiers or primary keys, when creating a database table.
2. How could you uniquely identify records when no natural ID exists?
3. Discuss your ideas about creating the primary key.
4. Ask clarification questions.

Try-it: Use AutoNumber and set the primary

key
In this standalone try-it activity, you’ll set the primary key by using
AutoNumber and compact and repair the database.

Resources
 Your [Link] from the previous topic or
L2_T3_try_sample_csa_starter.accdb in this lesson’s Learning
Activity Resources folder.

Instructions
You'll perform the following general tasks during this try-it:
1. If it's not open already, open your [Link] from the previous topic or
L2_T3_try_sample_csa_starter.accdb in this lesson’s Learning
Activity Resources folder.
2. Open the customer sample table and add a column as the primary key,
and set it as AutoNumber with increment values.
3. Open the order_sample table and add a column as the primary key, and
set it as AutoNumber with random values.

29
 Designing and setting up data structure

Wrap-up
Use these questions to check what you learned in this lesson:
1. Entering data in Datasheet view is similar to entering data in a(n)
Select here to enter text.
.
Fill in the blank space.
2. The Templates group is located on which of the following tabs?
Select the correct option.
a. Home
b. Create
c. Database Tools
d. Datasheet
3. Before you rename a table or other database object, you should do which
of the following?
Select the correct option.
a. Save it.
b. Close it.
c. Edit it.
d. Open it.

30
 Designing and setting up data structure

Lesson 3: Creating table

fields
Overview
In this lesson, you'll learn how to create a variety of table fields, and then set
the properties of each field depending on the data that you are collecting.
You'll also participate in discussions and follow demonstrations on creating
text fields, numeric fields, Date/Time fields, and Yes/No fields.

Warm-up
1. Which of the following is not a field property?
Select the correct option.
a. Column Template
b. Field Size
c. Caption
d. Allow Zero Length
2. Which field property requires users to enter data in a specific format?
Select the correct option.
a. Validation Text
b. Default Value
c. Required
d. Input Mask
3. Short Text fields can have up to how many characters?
Select the correct option.
a. 50
b. 100
c. 155
d. 255

31
 Designing and setting up data structure

Topic 1: Create text fields

To store a new piece of data about something for which you already
have an Access table, add a field to the table. For example, suppose you
have a table that stores the last name, first name, email address, telephone
number, and mailing address of each of your customers. If you want to start
tracking each customer’s preferred means of communication, you add a field
to store that data. In this topic, you'll learn to add and configure text fields in
an Access table.

Adding fields
Every field has certain essential characteristics, such as a name that
uniquely identifies the field within a table, a data type that defines the data's
nature, the available operations, and the storage space to set aside for each
value.
Before you create fields, try to separate data into its smallest useful parts. It
is much easier to combine data later than it is to separate it. For example,
instead of a Full Name field, consider creating separate fields for Last Name
and First Name. Then, you can easily search or sort by First Name, Last
Name, or both. If you plan to report, sort, search, or calculate on a data item,
put that item in a field by itself.

To enter data in the Add New Field column

1. Create or open a table in Datasheet view by selecting the Home tab and
View group, and then selecting Datasheet view.
2. In the Click to Add column, select the data type of the field that you
would like to create.
3. Enter the name of the field that you want to create. Use a descriptive
name to make the field easier to identify.
4. Enter data in the new field.

Additional information
For more information, refer to: Adding fields

32
 Designing and setting up data structure

Create a Short Text field

You add a Short Text field to a table when you need to store small amounts
of textual data. Short Text fields can store up to 255 alphanumeric
characters. You can apply custom display formats, and you can also apply
input masks that control how users enter data.
You can add a Short Text field in Datasheet view as previously explained,
or you can add and set field properties in Design View.
1. Open the table in Design View by going to the Home tab and View
group, and then select Design View.
2. In the Field Name column, select the first blank row, and then enter a
name for the field.
3. Select the adjacent cell in the Data Type column, and then select Short
Text from the list.
4. Save your changes.

Set or change Short Text field properties

1. Use Design View to set all available properties by selecting the Home
tab and View group, and then select Design View.
2. On the General tab in the lower section of the table designer, in the
Field Properties section, locate the property that you want to change.
As depicted in the following screen clipping.

Figure 5: Short Text field properties

3. Select the field next to the property name. Depending on the property,
you can enter data or select an option from a list.
For information about how to use each field property, select the property
and then Select F1.

33
 Designing and setting up data structure

Additional information
For more information, refer to: Create or delete a Short Text
field

Create or delete a Long Text field

A Long Text field can be useful for storing large amounts of information,
such as notes, comments, and descriptions. The Long Text data type stores
up to 65,536 alphanumeric characters and supports rich text formatting,
such as different colors, fonts, and highlighting.
You can add a Long Text field in Datasheet view, as previously explained,
or you can add and set field properties in Design View.
1. Open the table in Design View by selecting the Home tab and View
group, and then select Design View.
2. In the Field Name column, select the first blank row and then enter a
name for the field.
3. Select the adjacent cell in the Data Type column, and then select Long
Text from the list.
4. Save your changes.

Set or change Long Text field properties

1. Use Design View to set all available properties by selecting the Home
tab and View group, and then select Design View.
2. On the General tab in the lower section of the table designer, in the
Field Properties section, locate the property that you want to change.
The following screenshot depicts this.

Figure 6: Long Text field properties

3. Select the field next to the property name. Depending on the property,
you can enter data or select an option from a list.
For information about how to use each field property, select the property,
and then select F1.

34
 Designing and setting up data structure

Additional information
For more information, refer to: Create or delete a Long Text
field

Activity: Show and tell

In this activity, the teacher will demonstrate how to create a field in
Datasheet view and then switch to Design View for additional field
property options.

Resources required
 None

Activity instructions
Participate in the activity by following these instructions:
1. Participate in the discussion by sharing fields that should be text, and
then identify it as Short Text or Long Text.
2. Follow along as your teacher demonstrates how to create new fields and
sets the data type and field properties.

Try-it: Create text fields

In this try-it activity, you'll create the text fields that you identified in
your list for the CSA database, and then you’ll set the field properties for
each field.

Resources
You'll need the following resource for this try-it:
 Your [Link] from the previous topic or
L3_T1_try_example_csa_starter.accdb in this lesson’s Learning
Activity Resources folder.

35
 Designing and setting up data structure

Instructions
You'll perform the following general tasks during this try-it:
1. Refer to your list that you created in Lesson 1 of the tables and fields that
you need for the Munson’s Pickles and Preserves Farm CSA database.
2. Add each table then add each text field needed to the appropriate table
by using the Datasheet view.
3. Set the properties for each field by using the Design View. You should
include a variety of field properties.

Topic 2: Create numeric fields

You add a Number or Currency field to a table when you need to store
numeric data, such as sales and revenue figures or population data. Consider
the optimal field size and additional properties such as decimal places,
uniqueness, and whether the field is required when you create numeric data.
In this topic, you'll learn to add and configure numeric fields in an Access
table.

Create a numeric field

You add a Short Text field to a table when you need to store small amounts
of textual data. Short Text fields can store up to 255 alphanumeric
characters. You can apply custom display formats, and you can also apply
input masks that control how users enter data.
You can add a numeric field in Datasheet view, as previously explained, or
you can add and set field properties in Design View.
1. Open the table in Design View by selecting the Home tab and View
group, and then select Design View.
2. In the Field Name column, select the first blank row, and then enter a
name for the field.
3. Select the adjacent cell in the Data Type column, and then select
number or long number from the list.
4. Save your changes.

36
 Designing and setting up data structure

Set or change numeric field properties

1. Use Design View to set all available properties by going to the Home
tab and View group, and then select Design View.
2. On the General tab in the lower section of the table designer, in the
Field Properties section, locate the property that you want to change,
as depicted in the following screenshot.

Figure 7: Numeric field properties

3. Select the field next to the property name. Depending on the property,
you can enter data or select an option from a list.
4. For information about how to use each field property, select the property
and then select F1.

Additional information
 For more information, refer to: Create or delete a Number
or Currency field

Activity: Discuss and learn

In this activity, the teacher will demonstrate how to create and set the field
properties for a numeric field, and then demonstrate what happens when
entering text into the numeric field.

Resources required
 None

37
 Designing and setting up data structure

Activity instructions
Participate in the activity by following these instructions:
1. Participate in the discussion by sharing fields that should be numeric, and
then identify how to set the field properties.
2. Follow along as your teacher demonstrates how to create the new fields
and set the data type and field properties.

Try-it: Create numeric fields

In this try-it activity, you'll create the numeric fields that you identified
in your list for the CSA database, and then you’ll set the field properties for
each field.

Resources
You'll need the following resource for this try-it:
 Your [Link] from the previous topic or
L3_T2_try_example_csa_starter.accdb in this lesson’s Learning
Activity Resources folder.

Instructions
You'll perform the following general tasks during this try-it:
1. Refer to your list that you created in Lesson 1 for the tables and fields
that you need for the Munson’s Pickles and Preserves Farm CSA database.
2. Add each Numeric field needed to the appropriate table by using the
Datasheet view.
3. Set the properties for each field by using the Design View. You should
include a variety of field properties.

Topic 3: Create Date/Time fields

You add a Date/Time field to a table when you need to store dates and
times. You can use date and time data for a variety of personal and business
purposes, such as birthdays, shipping and billing information, and employee
timecards. In this topic, you'll learn how to create and configure Date/Time
fields.

38
 Designing and setting up data structure

Understand Date/Time fields

When you use an Access database, you might find date and time values in
numerous formats, such as a European format (28.11.2006 or 28-11-2006), a
South Asian format (28/11/2006), or the U.S. format (11/28/2006).
Regardless of how Access formats the date and time data, and regardless of
how you enter the date or time data, Date/Time fields store the dates and
times using a system called serial dates. The following table represents a
typical serial date and time value.

Figure 8: Example of serial date

The integer portion of the value, to the left of the decimal point, represents
the date. The fractional portion, to the right of the decimal point, represents
the time.

Add a Date/Time field

You can add a Date/Time field in Datasheet view as previously explained,
or you can add and set field properties in Design View.
1. Open the table in Design View by selecting the Home tab and View
group, and then select Design View.
2. In the Field Name column, select the first blank row and then enter a
name for the field.
3. Select the adjacent cell in the Data Type column, and then select
Date/Time from the list.
4. Save your changes.

39
 Designing and setting up data structure

Set or change Date/Time field properties

1. Use Design View to set all available properties by selecting the Home
tab and View group, and then select Design View.
2. On the General tab in the lower section of the table designer, in the
Field Properties section, locate the property that you want to change,
as depicted in the following screenshot.

Figure 9: Date/Time field properties

3. Select the field next to the property name. Depending on the property,
you can enter data or select an option from a list.
4. For information about how to use each field property, select the property
and then select F1.

Additional information
 Learn more from about Date/Time fields through
Microsoft Support. For more information, refer to: Create
or delete a Date/Time field

Restrict data input by using validation rules

You can validate data in Access by using validation rules. You can use the
expression builder to help you format the rule correctly. Set validation rules
can in either Datasheet view or Design View.

40
 Designing and setting up data structure

Validation rules in Access:

 Field Validation Rule: You can use a field validation rule to specify a
criterion that all valid field values must meet. For example, a date field
might have a validation rule that disallows values in the past.
 Record Validation Rule: You can use a record validation rule to specify
a condition that all valid records must satisfy. You can compare values
across different fields using a record validation rule. For example, a record
with two date fields might require that values of one field always precede
values of the other field (for instance, the StartDate is before the
EndDate).

Create a field validation rule

1. Select the field that you want to validate.
2. On the Fields tab, in the Field Validation group, select Validation, and
then select Field Validation Rule.
3. Use the Expression Builder to create the rule. For more information
about using the Expression Builder, refer to the article: Use the
Expression Builder

Create a message to display for field input that is not

valid
1. Select the field that needs a message for input that is not valid. The field
should already have a validation rule.
2. On the Fields tab, in the Field Validation group, select Validation, and
then select Field Validation Message.
3. Enter an appropriate message. For example, if the validation rule is >10,
the message might be: Enter a value that is less than 10.
4. For some examples of field validation rules and messages, refer to:
Validation rule reference

Additional information
 Learn more from Microsoft Support. For more information,
refer to: Restrict data input by using validation rules

41
 Designing and setting up data structure

Activity: Tell a story

In this activity, the teacher will demonstrate how to create a Date/Time field
and then set a validation rule.

Resources required
 None

Activity instructions
Participate in the activity by following these instructions:
1. Participate in the discussion by sharing fields that should be Date/Time
field and identify validation rules for this field.
2. Follow along as your teacher demonstrates how to create the new fields
and set the data type and field properties.

Try-it: Create Date/Time fields

In this try-it activity, you'll create Date/Time fields for the CSA
database, and then you’ll set the validation rule for the field.

Resources
You'll need the following resource for this try-it:
 Your [Link] from the previous topic or
L3_T3_try_example_csa_starter.accdb in this lesson’s Learning
Activity Resources folder.

Instructions
You'll perform the following general tasks during this try-it:
1. Refer to your list that you created in Lesson 1 for the tables and fields
that you need for the Munson’s Pickles and Preserves Farm CSA database.
2. Add each Date/Time field needed to the appropriate table by using the
Datasheet view. If you didn’t initially list a field that will use the
Date/Time data type, review your current tables to identify a table that
requires this information, and then add the field.
3. Set the properties for the field, including a validation rule.

42
 Designing and setting up data structure

Topic 4: Create Yes/No fields

Use the Yes/No data type to store a Boolean value, such as True or
False, On or Off, Yes or No, and any field that contains only one of two
values. This data type often maps to a Check Box control on a form or
report. In this topic, you’ll learn how to add and set properties for the
Yes/No field.

Add a Yes/No field

You can add a Yes/No field in Datasheet view, as previously explained, or
you can add and set field properties in Design View.
1. Open the table in Design View by going to the Home tab and View
group, and then select Design View.
2. In the Field Name column, select the first blank row and then enter a
name for the field.
3. Select the adjacent cell in the Data Type column, and then select
Yes/No from the list.
4. Save your changes.

Set or change Yes/No field properties

1. Use Design View to set all available properties by going to the Home
tab and View group, and then select Design View.
2. On the General tab in the lower section of the table designer, in the
Field Properties section, locate the property that you want to change,
as depicted in the following screenshot.

Figure 10: Yes/No field properties

43
 Designing and setting up data structure

3. Select the field next to the property name. Depending on the property,
you can enter data or select an option from a list.
4. For information about how to use each field property, select the property
and then select F1.

Additional information
Learn more about Yes/No fields through Microsoft Support
resources. For more information, refer to: Create or delete a
Yes/No field

Activity: Pose a challenge

The teacher will ask the class how to create a Yes/No field and types of
properties for the type of field. Volunteer if you think that you can
demonstrate the solution.

Resources required
You'll need the following resource for this activity:
 None

Activity instructions
Participate in the activity by following these instructions:
1. Think about how you've added other data type fields in your database,
then predict how you’ll set the field data type as Yes/No.
2. What properties do you think you could set for the Yes/No field?
3. Share your ideas with the class and participate in the discussion.

Try-it: Create Yes/No fields

In this try-it activity, you'll create Yes/No fields for the CSA database,
and then set the properties for the field.

Resources
You'll need the following resource for this try-it:
 Your [Link] from the previous topic or
L3_T4_try_example_csa_starter.accdb in this lesson’s Learning
Activity Resources folder.

Instructions
You'll perform the following general tasks during this try-it:

44
 Designing and setting up data structure

1. Refer to your list that you created in Lesson 1 for the tables and fields
that you need for the Munson’s Pickles and Preserves Farm CSA database.
2. Add each Yes/No field needed to the appropriate table by using the
Datasheet view. If you didn’t initially list a field that should use the
Yes/No data type, review your current tables and identify a table that
requires this information, and then add the field.
3. Set the properties for the field.

Wrap-up
Use these questions to check what you learned in this lesson:
1. The Date/Time fields store the dates and times as a system called
Select here to enter text.
Fill in the blank space.
2. To ensure that the information inputted in a field is in the correct format,
Access allows you to create a Select here to enter text.
Fill in the blank space.
3. You should use the Select here to enter text. data type if you would
like to add a Check Box to collect a Boolean value such as True or
False.
Fill in the blank space.

45
 Designing and setting up data structure

Glossary
Field A column in a database table.

Primary key The column in a database that uniquely identifies each

row.
Properties Control the appearance or behavior characteristics for
objects and related parts like fields and controls.

Table The most basic database object; stores data in

categories.

Template A ready-to-use database that contains all the tables,

queries, forms, and reports needed to perform a
specific task.

Validation rule An expression that limits the values that can be

entered in the field.

Validation text The text in the error message that appears when users
violate a validation rule.

Table 3: Glossary terms and definitions

46
 Designing and setting up data structure

Cornerstone
Overview
This Cornerstone project requires you to plan and create a database. You'll
use the skills that you learned in this module to create an Access file, create
Access tables, use AutoNumber, and set the primary key. You'll also create
table fields, and compact and repair the database.

Objectives
The following table outlines the Cornerstone objectives and their
corresponding Microsoft Office Specialist (MOS) exam objectives.

Starting the database  Not mapped

design
Starting an Access  OD 1.2.3: Set Primary Keys
database  OD 2.1.2: Create linked tables from external
sources
 OD 2.1.3: Import tables from other databases
 OD 2.2.3: Add table descriptions
 OD 2.4.6: Configure fields to auto-increment

Creating table fields  OD 2.4.1: Add and remove fields

 OD 2.4.2: Add validation rules to fields
 OD 2.4.3: Change field captions
 OD 2.4.4: Change field sizes
 OD 2.4.5: Change field data types

Table 4: Cornerstone objectives

47
 Designing and setting up data structure

Duration
40 minutes

Instructions
1. Complete the tasks for each file.
2. When saving your file, add your name to the end of the filename—for
example, You_pick_day_Dwayne_Espino. Follow your teacher’s
directions for where to save your files.
3. After completing the Cornerstone, assess your completion, and then enter
the points that you think you earned within the task lists in the following
sections. You can ask your teacher for help, if necessary.

Tasks
You'll work with two supporting files in this Cornerstone:
 [Link]
 [Link]

Task: Database design (11 points)

1. Think of a subject that interests you to create a database. Some examples
would be sports statistics, animals, video games, student information, or a
company database. (1 point) (Not mapped)
2. Plan your database by listing the information that you need to collect, and
then organize that information in appropriate tables. You should list at
least three tables and a total of at least 10 fields. (5 points) (Not mapped)
3. Plan the fields needed for each table and determine the data type. Be
sure to include at least four different data types (5 points) (Not mapped)
Points scored: Select here to enter text. / 11

48
 Designing and setting up data structure

Task: Create an Access file (7 points)

1. Create your database file and set it to compact and repair the database
on close. (1 point) (Not mapped)
2. Create at least three tables. (3 points) (Not mapped)
3. Add a table description to each of the three tables. (3 points) (Exam
objective 2.2.3)
Points scored: Select here to enter text. /7

Task: Create table fields (39 points)

1. Create fields with the appropriate data type in each of your tables. You
must include at least 10 fields in total. (10 points) (Exam objective 2.4.1)
2. Establish a primary key for each of the tables. (3 points) (Exam objective
1.2.3)
3. Set at least one of the primary keys to AutoNumber. (1 point) (Exam
objective 2.4.6)
4. Configure the field captions for each of the 10 fields. (10 points) (Exam
objective 2.4.3)
5. Change the field size for each of the 10 fields. (10 points) (Exam objective
2.4.4)
6. Set the validation rules for at least five fields. (5 points) (Exam objective
2.4.2)
Points scored: Select here to enter text. / 39

Task: Import tables from another source (10 points)

1. Import the information to collect table from the [Link].
(5 points) (Exam objective 2.1.3)
2. Configure the ID AutoNumber properties to increment values. (2 points)
(Exam objective 2.4.6)
3. Set captions for each of the three fields. (3 points) (Exam objective 2.4.3)
Points scored: Select here to enter text. / 10

Task: Link a table from an Excel spreadsheet (10 points)

1. Link the picture table from the [Link] file. (5 points) (Exam
objective 2.1.2)
2. Configure the ID AutoNumber properties to random values. (2 points)
(Exam objective 2.4.6)
3. Set captions for each of the three fields. (3 points) (Exam objective 2.4.3)
Points scored: Select here to enter text. / 10

49
 Designing and setting up data structure

Total points: Select here to enter text. / 77

50