Database Essentials

1
LESSON SKILL MATRIX
Skills Exam Objective Objective Number
Working in the Access Interface Display objects in the Navigation Pane. 1.3.4
Change views. 1.3.5
Selecting Tools and Commands
Defining Data Needs and Types Change field data types. 2.4.5

SOFTWARE ORIENTATION
The Access Opening Screen
Before you begin working in Microsoft Access 2016, you need to be familiar with the primary user
interface. In the next section, you will be asked to open a new blank desktop database in Access.
When you do so, a screen appears that is similar to the one shown in Figure 1-1.

Quick access toolbar Tab Microsoft Office Access Help Ribbon

Figure 1-1
Opening screen for new
blank Access database

Message Bar

Navigation Pane Work area Status Bar

When you create a blank database in Access, the opening screen provides you with a workspace in
which to build a database. Being familiar with the screen elements helps you understand import-
ant tools and information. The elements and features of your screen may vary if default settings
have been changed or if other preferences have been set.

1
2 Lesson 1

GETTING STARTED
A database is a tool for collecting and organizing information. For example, as a database, a
phone book organizes a large amount of data—names, addresses, and phone numbers—so you
can access it by name in alphabetic order. Even a grocery list is a simple type of database. A com-
puterized database management system (DBMS), such as Access, enables you to easily
collect large volumes of data organized into categories of related information. This type of data-
base allows you to store, organize, and manage your data, no matter how complex it is, and then
retrieve and present it in various formats and reports. Using a DBMS to organize data gives you
the power to manipulate, view, and report the data in ways that other applications like spread-
sheets and word processing documents cannot. As with any program, however, the first tasks are
the most basic. This section shows you how to start Access and open an existing database.

Starting Access
Access 2016 runs on the Windows 7 and later operating systems, and the steps to start Access
differ depending on the version of Windows you have installed on your system.

When you start Access, a start screen with several options related to creating a database appears.
The Access startup screen allows you to create a new, custom web app database, a blank desktop
database, a database from a template, open a recent database (if you have already created one), or
search online for templates.

Take Note A web app database is a type of database that was introduced in Access 2013. It allows you to share
the database with others as a Microsoft SharePoint app (an application on a network-based service
that allows for collaboration) in a web browser or through Office 365 (a subscription-based version
of Office provided on the web).

In Office 2016, you can sign into an Office application using your Microsoft or Organizational
account. You can easily create a Microsoft account when you sign up for a Microsoft web service
such as OneDrive, Xbox Live, Outlook.com, or other Microsoft service. An Organizational ac-
count is an account that your workplace or school would use to connect you to a Microsoft service.
Once you sign in, your sign-in information will appear in the upper-right corner of each of the
Office 2016 applications. This account information includes your name, email address, and user
icon. You can use the Switch account command beside your user icon to sign in as a different user.

Microsoft enables you to sign into Office to make it easier for you to access and share your doc-
uments from virtually anywhere. You can open and save your documents using OneDrive, a free
cloud-based file sharing service that can be accessed from any Internet-connected computer. Sign-
ing into Office also allows access to your personalized application settings and themes, and will
even remind you where you left off in your document.

In this exercise, you learn to start Access from both Windows 10 and Windows 8 systems.

STEP BY STEP Start Access in Windows 10

GET READY. Before you begin these steps, make sure that your computer is on. Sign on, if
necessary.
1. Press the Windows key on the keyboard to display the Start menu.
2. Click All apps, point to Access 2016 (see Figure 1-2), and then click Access 2016 to start
Access and display its Start screen (see Figure 1-3).
 Database Essentials 3

Figure 1-2
Open Access 2016

Create a new Create a new custom web Search online for User account sign-in
desktop database app database templates information

Figure 1-3
The Access startup screen

Click to access
Open dialog box

Microsoft
templates

Clip art courtesy of Microsoft

Take Note If you preferred the Windows start screen from Windows 8, you can configure it to appear on
Windows 10. Click Start, Settings, Personalization, and Start. Turn the Use Start full screen op-
tion to On.

PAUSE. LEAVE Access open to use in the next exercise.

4 Lesson 1

STEP BY STEP Start Access in Windows 8

GET READY. Before you begin these steps, make sure that your computer is on. Sign on, if
necessary.
3. When starting Windows 8, if the Windows 8 Start screen doesn’t appear, press the
Windows key on the keyboard to display it.
4. If necessary, scroll to the Access 2016 tile.
5. Click the Access 2016 tile. The Access startup screen opens.

PAUSE. LEAVE Access open to use in the next exercise.

Take Note The Microsoft Office 2016 System, including Access 2016, is not compatible with Windows XP
and earlier Microsoft Windows operating systems.

Opening an Existing Database

When you open an existing database, you access not only your previously entered and saved data,
but also the elements you created to organize that data. In this exercise, you open a database that
is in the beginning stages of development.

The Open Other Files command on the Access startup screen displays the Open screen. From
there, you can choose to open a recent file, a file on your OneDrive location, This PC, or you can
Add a Place or Browse for a file. The This PC command allows you to find files stored locally in a
folder on your computer or on some type of removable media, or on a network drive. The Add a
Place command allows you to set up network locations like OneDrive or an Office 365 SharePoint
location. The Browse button opens the Open dialog box.

The left-hand pane of the Open dialog box lists the available locations for files, such as a folder,
drive, or network/Internet location. Click the location, and the folders will be displayed in the
folder list. From this list, you can double-click the folder you want to open. When you find the
file you want, double-click the filename to open it or click it once to select it and then click the
Open button.

STEP BY STEP Open an Existing Database

GET READY. The Access startup screen should be on the screen from the previous
exercise.
1. Click the Open Other Files command on the left side of the Access startup screen. The
Open screen appears, as shown in Figure 1-4.
2. Click Browse, navigate to the data files for this lesson, and then select
StudentInformation.accdb, as shown in Figure 1-5.
3. In the Open dialog box, click the Open button. The existing database opens.
Take Note Your figures might look slightly different depending upon which folder you have stored your files.

Take Note As part of the Access 2016 security model, when you open a database outside of a trusted location
or the database contains active content (content that can be misused by an unscrupulous person
to cause harm to your computer), a Message Bar appears warning you that certain content has
been disabled. If you know you can trust the database, click Enable Content. You can trust the
databases downloaded from the book companion website, so click Enable Content on the Message
Bar for all databases in this book.

PAUSE. LEAVE the database open to use in the next exercise.

 Database Essentials 5

Access
recently Back button to
opened return to Access
files start screen

Create a
new
database

Connect to Browse
Office 365 OneDrive
SharePoint or
One Drive
Browse PC
Access the or removable
Open dialog media
box
Figure 1-4
The Open screen

Figure 1-5
The Open dialog box

Open
button

If you open a file in a multi-user environment such as a SharePoint drive, other users can read and
write to the database as well; therefore, you may want to open the database with other options.
Click the down arrow next to the Open button to view these options:

• Open: Opens with default access.

• Open Read-Only: Opens with only viewing ability and not editing ability. Others can still
read and write.
• Open Exclusive: Opens so that the database is only available to you. Others will receive a
message that the file is already in use.
• Open Exclusive Read-Only: Opens with only viewing ability and not editing ability. Others
can only view and not edit the database.
6 Lesson 1

Take Note Each time you start Access 2016, you open a new instance, or copy, of Access. You can only open
one database at a time in a single instance of Access. In other words, you cannot start Access,
open one database, and then open another database—not without first closing the first database.
However, you can open multiple databases at the same time by double-clicking another database’s
icon. For example, to open two Access databases, start Access, open the first Access database, and
then open a second Access database by double-clicking its database icon in File Explorer (referred
to as Windows Explorer in previous versions of Windows).

WORKING IN THE ACCESS INTERFACE

The Access 2016 user interface was designed to help you find the commands you need quickly so
that you can successfully perform your tasks. In this lesson, you will begin using the Navigation
Pane, exploring the Ribbon and Backstage view.

SOFTWARE ORIENTATION
Navigation Pane
By default, the Navigation Pane, shown in Figure 1-6, appears on the left side of the Access screen
each time you create or open a database.

Opens Navigation
Pane menu

Shutter Bar
Open/Close button

Search bar

Double-click database
object to open it

Expand or collapse
group

Figure 1-6
Navigation Pane
The Navigation Pane enables you to open, copy, and delete tables and other database objects. It
also lists all the objects in your database, including: tables (the most basic database object that
stores data in categories), queries (allow you to search and retrieve the data you have stored),
forms (control data entry and data views and provide visual cues that make data easier to work
with), and reports (present your information in ways that are most useful to you). You learn
more about managing database objects (such as forms, queries, and reports) in later lessons of this
book.
 Database Essentials 7

Using the Navigation Pane

Before you can create a database, you need to understand its most basic elements. This section in-
troduces you to some of the elements in a database that help you organize data and navigate using
the Navigation Pane, object tabs, and different views.

STEP BY STEP Use the Navigation Pane

GET READY. USE the Student Information database that is still open from the previous
exercise.
1. In the Navigation Pane, double-click Students to display the table in the Access work
area.
2. Click the down arrow next to All Access Objects at the top of the Navigation Pane to
display the menu, as shown in Figure 1-7.

Figure 1-7
The Navigation Pane menu

Categories Groups that relate to the selected category

Take Note The Navigation Pane divides your database objects into categories, and those categories contain
groups. The default category is Object Type, which groups database objects by their type—tables,
forms, reports, and so on. You can change the category to Tables and Related Views, which groups
the objects in a database by the tables to which they are related. You can also change the category
to one of several other views as shown in Figure 1-7.

3. Click Tables and Related Views. The default group in this category is All Tables, which
appears in the menu at the top of the Navigation Pane. Notice the Students table and
all other objects related to it are displayed under the Students object tab.
4. Click the down arrow next to All Tables at the top of the Navigation Pane to display the
menu again and then click Object Type to return to the original view.
5. Right-click in the white area of the Navigation Pane to display a shortcut menu. Click
View By and then click Details.
6. The database objects are displayed with details. Click the right side of the Navigation
Pane and drag to make it wider so all the information can be read, as shown in Figure
1-8.
8 Lesson 1

Figure 1-8
Widening the Navigation Pane

Details
displayed

Click and drag

to enlarge
Navigation
Pane

7. If the search bar does not appear at the top of the Navigation Pane, right-click the All
Access Objects header of the Navigation Pane. On the shortcut menu, click Search Bar.
A search bar is now displayed at the top of the Navigation Pane.
8. Right-click in the white area of the Navigation Pane, click View By and then click List in
the shortcut menu to display the database objects in a list again.
9. Click the Shutter Bar Open/Close button to collapse the Navigation Pane. Notice it is
not entirely hidden, as shown in Figure 1-9.

Figure 1-9
The Navigation Pane collapsed

Navigation
Pane collapsed
 Database Essentials 9

10. Click the Shutter Bar Open/Close button to expand the Navigation Pane again.

PAUSE. LEAVE the database open to use in the next exercise.

Using Object Tabs

When you create a database in Access, all the objects in that database—including forms, tables,
reports, queries—are displayed in a single window separated by tabs. Tabs help keep open objects
visible and accessible. To move among the open objects, click a tab. To close a tab, click its Close
button. You can also right-click a tab to display the shortcut menu, which you can use to save,
close, close all, or change views. In this exercise, you practice opening and displaying object tabs.

STEP BY STEP Use Object Tabs

GET READY. USE the Student Information database that is still open from the previous
exercise.
1. In the Navigation Pane, double-click Student Details. A new object tab opens to display
the form.
2. In the Navigation Pane, double-click All Students. A new object tab opens to display
the All Students report tab along with the other two tabs, as shown in Figure 1-10.
3. Click the Close button on the report tab to close it.

Object tabs Close button on the report tab

Figure 1-10
A tab showing a report along
with the other two tabs

4. Right-click the Student Details tab to display the shortcut menu. This menu allows you
to save the object, close the object, close all object tabs, or change the object view.
5. Click Close to close the form.

PAUSE. LEAVE the database open to use in the next exercise.

10 Lesson 1

Changing Views
Each database object can be viewed several different ways. The main views for a table are Data-
sheet View and Design View. Datasheet View can be used to perform most table design tasks, so
you will probably use it most often. A datasheet is the visual representation of the data con-
tained in a table or of the results returned by a query. A query is simply a question you can ask a
table or another query.

To change the view, on the Home tab, click the down arrow on the View button and then choose
a view from the menu. When you change views, the commands available on the Ribbon change
context to match the tasks you will be performing in that view.

STEP BY STEP Change Views

GET READY. USE the Student Information database you used in the previous exercise. The
Students table should be displayed in the Access work area.
1. On the Home tab, in the Views group, click the down arrow on the View button.
2. Click Design View. The table is displayed in Design View, as shown in Figure 1-11.
Notice that the Design tab is now displayed on the Ribbon.

View button Design tab

Figure 1-11
The table shown in Design
View

3. On the Design tab, in the Views group, click the down arrow on the View button and
then click Datasheet View.
4. On the Ribbon, in the Table Tools group, click the Fields tab to display the contextual
commands for that view.

PAUSE. LEAVE the database open to use in the next exercise.

SELECTING TOOLS AND COMMANDS

A command is a tool (such as an icon, a button, or a list) that tells Access to perform a specific
task. Each tab provides commands that are relevant to the kind of task you are performing. Many
of the tools and commands for working with Access are accessible through the Access Ribbon. In
 Database Essentials 11

addition to the Ribbon, Access also offers tools and commands on the File menu (also known as
Backstage view), a Quick Access toolbar, and a status bar (refer to Figure 1-1). In this section,
you explore the Ribbon, which displays groups of common commands arranged by tabs.

Using the Ribbon

The Ribbon is located across the top of the screen and contains tabs and groups of commands.
It is divided into several tabs, which organize tasks according to similar commands you’d like to
perform on a database. The Ribbon is contextual, which means it offers you commands related to
the object that you are working on or the task that you are performing.

Some groups have a dialog box launcher, which is a small arrow in the lower-right corner of
the group that you click to launch a dialog box or task pane that displays additional options or
information. Some commands on the Ribbon have small arrows pointing down. These arrows
indicate that a menu is available that lists more options from which you can choose.

In the next exercise, you will practice using the Ribbon.

STEP BY STEP Use the Ribbon

GET READY. USE the Student Information database that is still open from the previous
exercise.
1. Click the Home tab to make it active. As shown in Figure 1-12, the Ribbon is divided into
groups of commands. Notice the dialog box launcher in the lower-right corner of the
Clipboard group.

Active tab Dialog box launcher

Figure 1-12
The Ribbon and dialog box
launcher
2. Click the Create tab to make it the active tab. Notice that the groups of commands
change.
3. Click External Data and then click Database Tools to see the commands available on
those tabs.
4. Click the Home tab.
5. On the Students Object tab, click the ID column header in the table to select the ID
column.
6. Click the dialog box launcher in the lower-right corner of the Text Formatting group.
The Datasheet Formatting dialog box appears, as shown in Figure 1-13.

Figure 1-13
The Datasheet Formatting
dialog box
12 Lesson 1

7. Click Cancel to close the dialog box.

8. Double-click the Home tab. Notice the groups are hidden to give you more screen
space to work with your database.
9. Double-click Home again to display the groups.
10. Click File and then click Close to close the database. Access appears with no database
open.

Troubleshooting If you click the Close button (the X in the upper-right corner), Access closes and you will have
to reopen it for the next exercise.

PAUSE. LEAVE Access open to use in the next exercise.

Take Note You can customize the Ribbon to have greater control over the commands that appear on it by
turning off tabs and groups you rarely use, moving and/or duplicating groups from one tab to
another, creating custom groups, and even creating custom tabs.

Using the Backstage View

Microsoft introduced Backstage view in Office 2010. The Backstage view (Figure 1-14) dis-
plays when you click the File tab from an open database and contains a menu of options and
commands that allows you to access various screens to perform common tasks with your database
files—such as creating a new database, opening, saving, printing, closing, and changing user ac-
count settings and options. You can click several options and commands to view related screens
containing various options and commands used to create and manage your database. The Back
button returns you to the Access database workspace.

Back button Create new databases View Save As commands

Figure 1-14
Backstage view
 Database Essentials 13

The following is an overview of the options in the Backstage view:

• Info: Default view. Use this option to view the current database file path and view and edit da-
tabase properties. Compact and repair the database and encrypt the database with a password
to restrict access.
• New: Use this option to create a new database from scratch or from available templates.
• Open: Use this option to open an existing database and view a list of recently accessed data-
bases.
• Save: Use this option to return to the open database window where objects can be saved.
• Save As: Use this option to save the current database object (such as a table, query, form, or
report) as a new object or save the database in another format that is compatible with earlier
versions of Access. You can save the database to a document management server for sharing or
you can package the database and apply a digital signature.
• Print: Use this option to quick-print to a printer, open a dialog box from which to choose print
options, or preview your document before printing.
• Close: Use this option to close the open database but keep the Access application open.
• Account: Use this option to view and modify user account settings, change application back-
ground and theme, add a service, manage your Office 365 account, update office, and learn
more about Access.
• Options: Use this option to customize language, display, proofing, and other settings.
• Feedback: Opens the Windows Feedback dialog box, which allows you to provide feedback to
Microsoft and explore other users’ feedback.

STEP BY STEP Use the Backstage View

GET READY. START Access if it’s not already running.

1. Click the File tab. Backstage view opens.
2. Click Open and then navigate to the StudentData database from the data files for this
lesson.
3. Click File again. Backstage view now displays a menu of options down the left side of
the window and information about the currently opened database.
4. Click the New option to view the options and commands available.
5. Click the Save As option to view more options and commands.
6. Select Access Database (*.accdb) if it’s not already selected.
7. Click the Save As button in the right pane under the Save Database as area to save
the Student Data file as StudentData-final. Click Yes in the dialog box that appears
informing you that the objects will be closed.
8. Select the location to save the file and then click Save. The new database file opens
automatically.
9. Select File again to re-open Backstage view.
10. Click the Print option to view more options and commands.
11. Click the Account option to view the options and commands shown in Figure 1-15.
12. Click the Options option to view more options and commands.
13. Click the Cancel button to close the Access Options dialog box and return to the Access
workspace.

PAUSE. LEAVE the database open to use in the next exercise.

14 Lesson 1

Figure 1-15
The Account options

DEFINING DATA NEEDS AND TYPES

To create a database that achieves your goals and provides you with up-to-date, accurate informa-
tion, you need to spend time planning and designing it.

When planning a database, the first step is to consider the purpose of your database. You need to
design the database so that it accommodates all your data-processing and reporting needs. You
should gather and organize all the information that you want to include, starting with any existing
forms or lists, and think about the reports and mailings you might want to create using the data.

Once you have decided how the information will be used, the next step is to categorize the infor-
mation by dividing it into subjects such as Products or Orders, which become the tables in your
database. Each table should only contain information that relates to that subject. If you find your-
self adding extra information, create a new table.

In a database table, data is stored in rows and columns—similar in appearance to a spreadsheet.

Each row in a table is called a record. Each column in a table is called a field. For example, if
a table is named “Student List,” each record (row) contains information about a different student
and each field (column) contains a different type of information about a student, such as last name
or email address.

Once you have decided to create a Student List table, you need to determine what information you
want to store in the table—such as age, birthdate, or tuition. Organize each piece of information
into the smallest useful part—for example, use First Name and Last Name instead of just Name
if you want to sort, search, calculate, or report using either a first name or a last name (or both).
These pieces of information will eventually become your fields (columns), and each record (row)
will then contain complete information about each student.

For each table, you will choose a primary key. A primary key is a column that uniquely identi-
fies each row, such as Student ID Number. In the case of our Student List table, the primary key
(Student ID Number) uniquely identifies each student.
 Database Essentials 15

Reviewing Database Fields

Reviewing preexisting database fields can give you an idea of the type of information that you
can store in a database. In this exercise, you open a database that is further along in the process
of being developed and view the fields in a form to see what a more advanced database looks like.

STEP BY STEP Review Database Fields

GET READY. USE the StudentData-final database that is still open from the previous
exercise.
1. On the Student List form, click the ID for record 5 to display the Student Details dialog
box for Sharon Hoepf, as shown in Figure 1-16.

Figure 1-16
The Student Details dialog box

Clip art courtesy of Microsoft

2. Click the Guardian Information tab and then click the Emergency Information tab. Each
field on each tab is an example of the type of information that could be contained in a
database table.
3. Click Close to close the Student Details dialog box.

PAUSE. LEAVE the database open to use in the next exercise.

Defining and Modifying Data Types for Fields

When designing the database, you set a data type for each field (column) that you create to
match the information it will store. A data type controls the type of data a field will contain—
whether it is text, number, date/time, or some other type of data. When defining table fields, it is
important to define them as specifically as possible. For example, if you are using a number, you
should determine whether you need to use the Currency data type, the Calculated data type, or
the Number data type. Or, if you need to store large amounts of text, you may need to use the
Long Text data type (up to 64,000 characters) instead of the Short Text data type (255 characters).
Sometimes you may also need to modify data types for preexisting fields.

Access provides 11 data types, each with its own purpose. Table 1-1 describes the types of data
that each field can store.
16 Lesson 1

Table 1-1
Data Type Example Description
Types of data stored in fields
Short Text Last Name: D’Amato The most common data type for fields. Can store up
Street: 1234 Landau Ave. to 255 characters of text, and numbers (or a
combination of both).

Long Text Comments: Student will Stores large amounts of text—up to 1 gigabyte (GB)
make monthly payments on —but only the first 64,000 characters of text, and
the 15th of each month of numbers (or a combination of both) will be visible on
$247. the screen.

Number Age: 19 Stores numeric data that can be used in mathematical

Grade: 95.5 calculations.

Date/Time Birthday: September 5, 1972 Stores date and/or time data.

Currency Registration Fee: $50.00 Stores monetary data with precision to four decimal
places. Use this data type to store financial data and
when you don’t want Access to round values.

AutoNumber Student ID: 56 Unique values created by Access when you create a
new record. Tables often contain an AutoNumber
field used as the primary key.

Yes/No Insurance: Yes Stores Boolean (true or false) data. Access uses 1 for
all Yes values and 0 for all No values.

OLE Object Photo Stores images, documents, graphs, and other objects
from Office and Windows-based programs.

Hyperlink Web addresses Stores links to websites, sites or files on an intranet or

Local Area Network (LAN), and sites or files on your
computer.

Attachment Any supported type of file You can attach images, spreadsheet files, documents,
charts, and other types of supported files (up to 2 GB
per record) to the records in your database, much like
you attach files to email messages.

Calculated FullName: John Derenzo Stores an expression based on two or more fields
within the same table.
Example using concatenation operator (&):
First: John
Last: Derenzo
FullName stored as: [First]&” “&[Last]

Take Note Some of the available data types in Access 2016 can only be chosen in table Design View and not
from the Data Type drop-down menu on the Ribbon. You will learn more about table Design
View in Lesson 9.

When you create a new field in a table and then enter data in it, Access 2016 automatically tries
to detect the appropriate data type for the new column. For example, if you type a price, such as
$10, Access recognizes the data as a price and sets the data type for the field to Currency. If Access
doesn’t have enough information from what you enter to detect the data type, the data type is set
to Short Text. In this exercise, you practice reviewing and modifying data types.

Take Note The Short Text data type and the Long Text data type each has unique properties and size limits,
depending on whether you’re using them in a desktop database or an Access web app.
 Database Essentials 17

STEP BY STEP Review and Modify Data Types for Fields

GET READY. USE the StudentData-final database that is still open from the previous
exercise.
1. CLOSE the Student List form.
2. In the Navigation Pane, in the Students group, double-click Students: Table to open it.
3. Click the Date of Birth field header.
4. On the Ribbon, click the Fields tab. Notice in the Formatting group that the Data Type is
Date/Time.
5. In the Format box, click the down arrow to display the menu of formatting options for
that type, as shown in Figure 1-17.

Figure 1-17
Format options for the Date/
Time data type

6. Click the Last Name header. Notice that the Data Type is Short Text and that no
formatting options are available for that data type.
7. Scroll to the right and then click the Address header.
8. In the Data Type box, click the down arrow and then click Short Text to change the data
type.
Take Note Be aware that changing a data type might cut off some or all of the data in a field; in some cases,
it might remove the data entirely.

9. Scroll to the far right and then click the Click to Add column header. In the Data Type
drop-down list that appears, select Yes/No (see Figure 1-18). Once you click Yes/No,
notice the name of the column header, Field1, is highlighted.
18 Lesson 1

Figure 1-18
The Click to Add menu options

10. Rename the field by typing Additional Contact Info on File?. Press Enter and then click
the Additional Contact Info on File? header.
11. On the Ribbon, in the Formatting group, click the down arrow in the Format box to
display the menu of formatting options for the Yes/No data type.
12. Click outside the menu to close it.

PAUSE. LEAVE the database open to use in the next exercise.

Take Note The Number data type should be used only if the numbers will be used in mathematical calcula-
tions. For numbers such as phone numbers, use the Short Text data type.

Defining Database Tables

Tables are the most basic organizational element of a database. Not only is it important to plan
the tables so they will hold the type of data you need, it’s important to plan how the tables and
information will be connected. In this exercise, you view a visual representation of the relationship
between two tables.

In a simple database, you might have only one table. Most databases, however, will have more
than just one table. The tables you include in a database will be based on the data available. For
example, a database of students might have a table for contact information, a table for grades, and
a table for tuition and fees.

In database applications like Access, you can create a relational database. A relational database
stores information in separate tables and these tables are connected or linked by a defined relation-
ship that ties the data together.

STEP BY STEP Define Database Tables

GET READY. USE the StudentData-final database that is still open from the previous
exercise.
1. On the Database Tools tab, in the Relationships group, click Relationships to display a
visual representation of the relationship between the Students and Guardians tables,
as shown in Figure 1-19.
 Database Essentials 19

Figure 1-19
A relationship between tables

2. CLOSE the Relationships tab.

3. CLOSE the Students tab.
4. CLOSE the Student List tab.

STOP. CLOSE the database and Access.

An important principle to consider when planning a database is to try to record each piece of
information only once. Duplicate information, or redundant data, wastes space and increases
the likelihood of errors. Relationships among database tables help ensure consistency and reduce
repetitive data entry.

As you create each table, keep in mind how the data in the tables are related to each other. Enter
test data and then add fields to tables or create new tables as necessary to refine the database. The
last step is to apply data normalization rules to see if your tables are structured correctly and make
adjustments as needed. Normalization is the process of applying rules to your database design
to ensure that you have divided your information items into the appropriate tables.

Database design principles include standards and guidelines that can be used to determine if your
database is structured correctly. These are referred to as normal forms. There are five normal
forms, but typically only the first three are applied, because that is usually all that is required. The
following is a summary of the first three normal forms:

• First Normal Form (1NF): This form divides each field according to its smallest meaningful
value, removes repeating groups of data, and creates a separate table for each set of related data.
• Second Normal Form (2NF): With this form, each non-key column should be fully depen-
dent on the entire primary key. Create new tables for data that applies to more than one record
in a table and add a related field to the table.
• Third Normal Form (3NF): Use this form to remove fields that do not relate to, nor provide a
fact about, the primary key.
Before normalizing a database and defining fields for the efficient storage of data in tables, it’s
important to know where your data will come from. Data can be brought into an Access database
in a number of ways, including linking and importing. When defining tables, you have to decide
whether data should be linked to or imported from external sources. When you import data, Ac-
cess creates a copy of the data or objects in the destination database without altering the source.
Linking lets you connect to data from another source without importing it, so that you can view
and modify the latest data in both the source and destination databases without creating and
maintaining two copies of the same data thereby reducing redundant data. Any changes you make
to the data in the source are reflected in the linked table in the destination database, and vice versa.
You find out more about normalizing tables in Lesson 9.
20 Lesson 1

Knowledge Assessment

Matching
Match the term in Column 1 to its description in Column 2.

Column 1 Column 2
1. Record a. Row in a database table
2. Field b. Database object that asks a table a question
3. Query c. Database object that simplifies the process of entering, editing,
and displaying data
4. Form d. Column in a database table
5. Data type e. Kind of information a field contains

True/False
Circle T if the statement is true or F if the statement is false.

T F 1. Any list you make for a specific purpose—even a grocery list—can be

considered a simple database.
T F 2. By default, the Navigation Pane appears on the right side of the Access screen
each time you create or open a database.
T F 3. Forms, queries, and reports are examples of database objects.
T F 4. In a database table, data is stored in rows and columns—similar in appearance
to a spreadsheet.
T F 5. An important principle to consider when planning a database is to try to record
each piece of information as many times as possible for easy access.

Projects

Project 1-1: Using the Navigation Pane

As a busy editor at Lucerne Publishing, you use Access to organize and manage your task list. In
this project, you will use the Navigation Pane to organize your task list.

GET READY. LAUNCH Access if it is not already running.

1. OPEN Task List from the data files for this lesson and then SAVE it as Task List-final.
2. To display the Navigation Pane, click the Shutter Bar Open/Close button.
3. Click the Contacts group header in the Navigation Pane to display those database
objects.
4. Click the Supporting Objects group header to display those database objects.
5. In the Supporting Objects group, double-click Tasks to open that table.
6. In the Tasks group, double-click Tasks by Assigned To to open that report and view
data taken from the Tasks table.
7. In the Navigation Pane, click the Tasks Navigation header to display the menu and then
click Object Type. Notice that the Objects are now displayed by type, such as Tables,
Queries, Forms, and Reports.
8. SAVE and then CLOSE the database.

PAUSE. LEAVE Access open for the next project.

 Database Essentials 21

Project 1-2: Creating a Database and Selecting Data Types

You are a volunteer for the Tech Terrace Neighborhood Association that holds an annual March
Madness 5K Run. In the past, all data has been kept on paper, but you decide it would be more
efficient to create a database. In this project, you will create a database and select data types.

GET READY. LAUNCH Access if it is not already running.

1. Click File, click New, and then double-click Blank desktop database.
2. In the Blank desktop database screen that appears in the center of the screen, type
Runners-final in the File Name box. Click Create.
3. Click Click to Add, select Short Text, and then type Runner Number.
4. Click Click to Add, select Short Text, and then type First Name.
5. Click Click to Add, select Short Text, and then type Last Name.
6. Click Click to Add, select Number, and then type Age.
7. Click Click to Add, select Short Text, and then type First Race?.
8. Click Click to Add, select Number, and then type Previous 5K Time.
9. Click the First Race header and then change the Data Type to Yes/No.
10. SAVE and then CLOSE the object.
11. Rename Table 1 to Runners.
12. Click OK.

CLOSE the database and then EXIT Access.