Chapter 13 Database Management Systems

Chapter Outline Learning Objectives

• Data Management After completing this chapter, you will be able

o Data and Information to
o Data Organization
o Qualities of Valuable Information • understand the basic concepts of data, da-
o Important Characteristics of a Database tabase, and database management systems.
o Database System Architecture • know the components of Microsoft Access.
o Database Models • create a table structure.
o Relational Database Model
• use the Datasheet window
o Object-Oriented Database Model
• set relationships between tables
• Database Management Systems
• create a query
o Database Management Systems
o Microsoft Access • generate reports
o Database Design
o The Access and Database Windows
o An Access Table
o An Access Query
o An Access Form
o An Access Report
o Compacting a Database
• Creating and Maintaining a Database
o Guidelines for Designing Database
o Creating a Table
o Specifying the Primary Key
o Adding Records to a Table
o Coping Records from Another Access Database
o Updating a Database
• Querying a Database
o Query Window
o Creating a Query
o Defining Table Relationship
o Sorting Data in a Query
o Filtering Data
o Defining Selection Criteria for Queries
o Performing Calculations
• Creating Forms and Reports
o Creating a From
o Creating a Report

13.1 Data Management

Data and Information

An effective information system provides users with accurate, timely, and relevant information. By information
we mean data that have been processed into a form that is meaningful and useful to human beings. Data, by con-
trast, are a collection of raw items such as words, numbers, images, and sounds that have not organized and ar-
ranged into a form that people can understand and use. For example, your grade report is information, which con-
tains several data items, including your first name, your last name, your ID number, course name, course number,
credit hours, and course grade.
Most organizations realize that information is a valuable resource. Without information, an organization could
not perform many business functions. Because information cannot be generated without data, an organization must
1
organize, manage, maintain, and protect its data resources just as it would do for any other resource. A database is
a collection of related data organized in a manner that allows access, retrieval, use, and maintenance of that data.
Database software, called database management system (DBMS), allows you to create a computerized data-
base; add, change, delete, and sort data; retrieve data from the database; and create forms and reports using the
data. A DBMS consists of a group of programs that manipulate the database and provide an interface between the
database and the user or the database and other application programs. A database, a DBMS, and the application
programs that utilize the data in the database make up a database environment. Understanding basic concepts
of database management systems can enhance your ability to use the power of a computerized database system to
support organizational IS goals.
Data are a vital organizational resource that needs to be managed like other important business assets. Today's
e-business enterprises cannot survive or succeed without quality data about their internal operations and external
environment. That is why organizations and their managers need to practice data resource management, a
managerial activity that applies information systems technologies like database management, data warehousing,
and other data management tools to the task of managing an organization's data resources to meet the information
needs of their business stakeholders. This chapter will show you the technical and managerial implications of using
data resource management technology.

Data Organization

A computer system organizes data in a hierarchy that starts with bits and bytes and progresses to fields, records,
files, and database. Each higher level consists of one or more elements from the lower level preceding it. See Figure
13-1.

Figure 13-1 A computer system organizes data in a hierarchy

A bit represents the smallest unit of data a computer can handle. A group of bits, called a byte, represents a sin-
gle character, which can be a letter, a number, or a symbol. A grouping of characters into a word, a group of words,
or a complete number, such as a student's name, address, or grade, is called a field. A field describes an aspect of
an object, activity, or event. A group of related fields is a record. For example, a student record is a collection of
fields about one student, including the student's name, ID number, birth date, address, GPA, and so forth. A collec-
tion of related records is a file. For example, a student file is a collection of all university student records. At the

2
highest level of data hierarchy is a database, a collection of integrated and related files. For example, a university
database might include student file, course file, transcript file, faculty file, and other files.
A record describes an entity. An entity is a person, place, thing, activity, or event for which data is collected,
stored, and maintained. Examples of entities include employees, inventory, and customers. Organizations organize
and store data as entities. Each characteristic describing a particular entity is called an attribute. Attributes are
usually selected to capture the relevant characteristics of entities like name, age, or address. The specific value of an
attribute, called a data item, can be found in the fields of the record describing an entity.
Every record in a data file should contain at least one field that uniquely identifies that record so that the record
can be retrieved, updated, or sorted. This identifier field is called a key field, or primary key. An example of a key
field is student ID number, which uniquely identifies each student.

Qualities of Valuable Information

Information should have the following characteristics to be considered as valuable information:

• Accurate. Accurate information is correct information. Inaccurate information often is worse than no in-
formation, because inaccurate information can lead to wrong decisions.
• Verifiable. Information can be confirmed. In other words, the accuracy of the information can be verified.
• Timely. Information has an age suited to its uses. Although most information loses its value with time,
some information, such as information on trends, gains value as time passes and more information ob-
tained.
• Organized. Information should be arranged to suit the needs and requirements of the user.
• Meaningful. Information should be relevant to the person who receives it. Because certain information is
meaningful only to specific individuals or groups, unnecessary information should be eliminated.
• Cost-effective. The cost of producing the resulting information should be less than the value of the infor-
mation.
If an organization cannot determine the value of information, it might choose to generate the information only
as people require it, instead of on a regular basis. Another alternative is to make the information available online,
thus allowing users to access and print it as they need it.

Important Characteristics of a Database

The term "database" is perhaps one of the most overused and misunderstood terms in today's business environ-
ment. Many people who say they have a database, in fact, have only file. A database is actually composed of two im-
portant elements: (1) the data itself and (2) the logical structure of the data.

Figure 13-2 As you define each field in a data dictionary, you include certain properties that
defines its logical structure

3
 Using a database, you organize and access data according to its logical structure, not its physical position. The
database concept distinguishes between logical and physical views of data. The logical view presents data as they
would be perceived by users, whereas the physical view shows how data are actually organized and structured on
physical storage media. A data dictionary contains the logical structure of data in a database. When you create a
database, you first create the data dictionary. The data dictionary contains important information or logical proper-
ties about the data. Figure 13-2 shows how you can build the data dictionary for the Student file using Microsoft
Access. The data dictionary identifies all field names, type, size, format, default values, and so on.
Rather than being in separate and independent files, data in a database is considered integrated. In a database
environment, you create ties or relationships among the data files. You first specify the primary key for each file. A
primary key is a field in a data file that uniquely describes each record. Then you create the logical relationship
between two data files using a foreign key. A foreign key is a primary key of one file that also appears in another
file. In general, data in a database should be both integrated and shared. By integrated, we mean that database
can be thought of as unification of several otherwise distinct data files, with any redundancy among those files
wholly or partly eliminated. By shared, we mean that individual pieces of data in the database can be shared
among several different users, in the sense that each of those users can have access to the same piece of data, and
different users can use it for different purpose.
By defining the logical structure of data in a database, you are developing data integrity constraint -- the
rules that help ensure the quality of the information. Data integrity is the degree to which the data in any one file
is accurate and up-to-date. Data integrity follows from the control or elimination of data redundancy. Data re-
dundancy is referred to the duplication of data in separate files. For example, keeping a student's address in only
one file decreases the probability that the student will have two different addresses stored in different data files.
Data integrity is critical because computers and individuals use information generated from data to make decisions
and take actions.

Database System Architecture

As previously described, a database is a shared collection of data. With the database approach, many application
programs in an organization could use the data in the single, shared database. To apply the database approach to
data management, additional software -- a DBMS -- is required. As previously mentioned, a DBMS consists of a
group programs that can be used as an interface between a database and the user or the database and application
programs. We know that the overall purpose of database system is to support the development and execution of
database applications. From a high-level point of view, a database system can be regarded as having a client/server
architecture, a simple two-part structure that consists of a server and a set of clients (Figure 13-3). The server is the
DBMS itself. It provides all of the basic database operation and management functions. The clients are the various
applications that run on top of the DBMS.

Database Models

Every database and database management system is based on a particular database model. A database model
consists of rules and standards that define how data is organized in a database. Five data models are hierarchical,
network, relational, object oriented and object
relational.
In the past, databases often were organized
according to the hierarchical or network data
model. In a hierarchical database, data is
organized in a series like a family tree or or-
ganization chart. As with a family tree, the hi-
erarchical database has branches made up of
parent and child records. Each parent record
can have multiple child records. Each child re-
cord, however, can have only one parent. A
network database is similar to a hierarchical
database except that each child record can have
more than one parent.
Because hierarchical and network databases
offer only limited data access and lack flexibil-
ity, database developers prefer two other data-
base model: relational and object oriented. A Figure 13-3 The database approach to data management
newer database model, the object-relational
4
database model, combines features of the relational
and object-oriented database models.

Relational Database Model

Today, the relational database model is the most
widely used model for modeling data in database. A
relational database represents all data in the data-
base as simple two-dimensional tables called rela-
tions that are the logical equivalent of files. The tables Figure 13-4 Data terminology used by file processing developer, rela-
in relational databases organize in rows and columns, tional database developer, and relational database user
simplifying data access and manipulation.
A relational database uses terminology different from a file processing environment to represent data. In the re-
lational model, each table, called relation, represents a data file; each row of a table, called tuple, represents a
data entity with columns of the table representing attributes or fields. The allowable values for these attributes
are called the domain, indicating what values can be placed in each of the columns of the relational table. A user of
a relational database, however, refers to a file as a table, a record as a row, and a field as a column. Figure 13-4 lists
the differences. In this chapter we use the user’s terms, table, row, and column, when discussing relational data-
bases.

Figure 13-5 In the relational model, all data elements are placed in tables. The Student table is links to the Schedule table
through the Student IC column. The Schedule table is linked to the Course table through the Course Code column.

5
 In addition to storing data, relational database also stores any associations among the data, which are called re-
lationships. With a relational database, you can establish a relationship between tables at any time, provided the
tables have a common attribute (column). Figure 13-5 illustrates these relational database concepts. In a relational
database, only data redundancy exists in the common attributes (columns) that establish relationships.
In a relational database, three basic operations are used to develop useful sets of data: selection, projection, and
join. The selection operation retrieves certain records (rows) from a table based on the criteria you specify. In
other words, selection operation creates a subset of rows that meet certain criteria. The projection operation
extracts fields (columns) from a table, permitting the user to create new tables that contain only the information
required. The join operation combines the data from the two tables based on a common column, providing the
user with more information than is available in individual tables. Together these three operations are called rela-
tional algebra.
Relational databases use a query language called Structured Query Language (SQL) to manipulate and re-
trieve data. SQL includes keywords and rules used to implement relational algebra operations. Most relational da-
tabase systems support SQL.
The strengths of relational database systems are great flexibility in regard to ad hoc queries, power to combine
information from different sources, simplicity of design and maintenance, and the ability to add new data and re-
cords without disturbing existing programs and applications. However, these systems are somewhat slower because
they typically require many accesses to the data stored on disk to carry out the selection, projection, and joint com-
mands.

Object-Oriented Database Model

The object-oriented model is a new model that may define how the majority of tomorrow's databases will look and
work. An object-oriented database stores and maintains objects. An object is an item that can contain both data
and the procedures that manipulate the data. For example, a Student object might contain not only data about a
student such as First Name, Last Name, Student ID, Address, and so on, but also procedures on some tasks such as
printing the student record, or calculating the student's tuition rates.
Relative to relational databases, object-oriented database systems can store more types of data and access these
data faster. With an object-oriented database, system can store and access unstructured data such as photographs,
video clips, sounds, and documents more efficiently than in a relational database. Examples of applications appro-
priate for an object-oriented database include multimedia databases that store images, audio clips and video clips,
computer-aided design (CAD) databases that store data about engineering, architectural, and scientific designs,
and hypermedia databases that contain graphics, video, sound, and text links to other documents.
Object-oriented and object-relational databases often use a query language called object query language
(OQL) to manipulate and retrieve data. OQL is similar to SQL. OQL and SQL use many of the same rules, gram-
mar, and keywords.

13.2 Database Management Systems

Database Management Systems

A database management system (DBMS) is a group of programs used as an interface between a database and
the user or a database and application programs, permitting an organization to centralize data and manage them
efficiently.
Database management systems are available for many sizes and types of computers, and support many types of
database models. For example, Microsoft Access is a popular relational DBMS for personal computers. Popular
mainframe relational DBMSs include DB2 by IBM, Oracle, Sybase, and Informix. All DBMSs share some common
functions, such as providing a user view, physically storing and retrieving data in a database, allowing for database
modification, manipulating data, generating reports, and providing data security.
A DBMS contains four important software components: data dictionary, data definition language, data manipu-
lation language, and data administration facilities. A data dictionary is a file that stores information about each
file in the database and each field within those files. For each file, a data dictionary stores information including the
file name, description, and the file's relationship to other files. For each field, a data dictionary stores information
including the field name, field size, description, type of data, default value, validation rules, and the field's relation-
ship to other fields. A DBMS uses the data dictionary to perform validation checks and maintain the integrity of the
data.
The data definition language is the formal language used by programmer to specify the content and struc-
ture of the database. A data manipulation language is used in conjunction with some conventional third- or
fourth-generation programming language to manipulate the data in the database. The data manipulation language
6
helps you add, change, and delete data in a database and mine it for valuable information. In most DBMS, you will
find a variety of data manipulation tools, including views, data entry form, report generators, query-by-example
tools, and structured query language (SQL). A view is a tool that allows you to see the content of a database file,
make whatever changes you want, perform simple sorting, and query to find the location of specific data. A data
entry form, or simply called form, is a window on the screen that provides areas for entering or changing data in
a database. Report generators help you quickly define formats of reports and what information you want to see
in a report. Once you define a report, you can view it on the screen or print it. Query-by-example (QBE) tools
have a graphical user interface that assists you with retrieving data. When you perform a QBE, you (1) identify the
files in which the data is located, (2) drag any necessary fields from the identified fields to the QBE grid, and (3)
specify selection criteria. SQL is a standardized fourth generation query language found in most database environ-
ments. SQL performs the same function as QBE, except that you perform the query by creating a statement instead
of pointing, clicking, and dragging. In other words, SQL is command-based rather than graphics-based.
A DBMS provides data administration facilities to help you manage the overall database environment for
backup and recovery, security management, query optimization, data reorganization, concurrency control, and
change management. The data administration facilities are most often used by data administrators or database ad-
ministrators -- people responsible for assuring that the database environment meets the entire information needs of
the organization.

Microsoft Access

Microsoft Access is a database management system based on the relational database model. Access database con-
tains tables and other components, each of which is called an object. A database object is a component of the
database that gives it functionality. Each database object belongs to a category of objects, known as a class. Access
has seven classes of database objects. These seven classes are tables, queries, forms, reports, pages, macros, and
modules. In Access, the database objects you create are stored in a single database file with a .mdb extension, which
stands for "Microsoft database."

Database Design

In general, you should complete five steps when designing a database using Access.

1. Determining the Purpose. Define the overall purpose of the database, including a list of user specifica-
tions for input and output.
2. Planning the Database Objects. The objects contained in your database must be carefully planned. You
must determine the appropriate number of tables, the ways in which the records will be reorganized using
queries and kinds of forms and reports your database will contain.
3. Creating and Relating Tables. Tables are the primary database objects in Access. Design the tables on
paper first. Each table should contain data about one subject. You must specify the kind of data each field
will contain before you design a table. If the database contains more than one table, you will need to estab-
lish relationships among the tables.
4. Creating Queries. It is rare to see all the field data in a database for all records at the same time. For this
reason, queries are used to reorganize or manipulate data to fulfill a specific request.
5. Creating Forms and Reports. Users can access forms and reports to work with records both onscreen
and in printed format, so forms and reports are essential to a database. Forms and report obtain their data
from tables and queries.

The Access and Database Windows

The Access window is the program window that appears when you start the Access program (Figure 13-6). The
Database window appears when you open a database; this window is the main control center for working with an
open Access database. Through the control panel on the right, you can open an existing database or create a new
database. Except for the Access window title, all screen components now on your screen are associated with the
Database window (Figure 13-7). Most of these screen components -- including the title bars, window sizing buttons,
menu bar, toolbar, and status bar -- are the same as the components in other Windows programs.

7
 Figure 13-6 Microsoft Access window

Figure 13-7 Access and Database Windows

The Database window provides a variety of options for viewing and manipulating database objects. Each item in
the Objects bar controls one of the major object groups in an Access database. The Groups bar allows you to or-
ganize different types of database objects into groups, with shortcuts to those objects, so that you can work with
them more easily.
The Database window also provides a toolbar with buttons for quickly creating, opening, and managing objects,
as well as shortcut options for some of these tasks. Figure 13-8 lists some toolbar buttons that specific for Access.

8
 Figure 13-8 Toolbar buttons

Tables contain all the data in a database.

Tables are the fundamental objects for your
work in Access. To view, add, change, or de-
lete data in a table, you first must open the
table. You can open any Access object by us-
ing the Open button in the Database window.
To open an Access object:

1. Click the type of object you want to

open in the Objects bar of the Data-
base window.
2. If necessary, scroll the object list box
until the object name appears, and
then click the object name.
3. Click the Open button in the Data-
base window (see Figure 13-9).

An Access Table

When you open an Access table, the table is Figure 13-9 Open Access object
displayed in Datasheet view on top of the Da-
9
tabase and Access windows. See Figure 13-10.
Datasheet view shows a table's contents as a datasheet in rows and columns, similar to a table or spread-
sheet. Each row is a separate record in the table, and each column contains the field values for one field in the table.
Each column is headed by a field name inside a field selector, and each row has a record selector to its left. Clicking
a field selector or a record selector selects that entire column or row (respectively), which you can then ma-
nipulate. A field selector is also called a column selector, and a record selector is also called a row selector.

Figure 13-10 Table displayed in datasheet view

An Access Query

A query is a question you ask about the data stored in a

database. In response to a query, Access displays the spe-
cific records and fields that answer your question. When
you create a query, you tell Access which fields you need
and what criteria Access should use to select the records.
The Access displays only the information you want, so you
don't have to navigate through the entire database for the
information.
You can design your own queries or use an Access
Query Wizard, which guides you through the steps to create
a query. The Simple Query Wizard allows you to select re-
cords and fields quickly. To start the Simple Query Wizard,
you click Queries in the Objects bar of the Database win-
dow to display the Queries list, and then double-click the
Create query by using wizard option. The Simple
Query Wizard dialog boxes appear. Figure 13-11 shows the
first Simple Query Wizard dialog box.
You could click the Table/Queries list arrow to choose
another table or a query on which to base the query you're
creating. The Available Fields list box lists the fields in the
selected table. You need to select fields from this list to in-
clude them in the query. To select fields one at a time, click
a field and the click the button with > symbol. The selected
field moves from the Available Fields list box on the left to
the Selected Fields list box on the right. To select all the Figure 13-11 First Simple Query Wizard dialog box
fields, click the button with >> symbol. If you change your
10
mind or make a mistake, you can remove a field by clicking it in the Selected Fields list box and then clicking the
button with < symbol. To remove all selected fields, click the button with << symbol.
Each Wizard dialog box contain buttons on the bottom that allow you to move to the previous dialog box (Back
button), move to the next dialog box (Next button), or cancel the creation process (Cancel button) and return to
the Database window. You can also finish creating the object (Finish button) and accept the Wizard's defaults for
the remaining options.

An Access Form
A form is an object you use to maintain, view, and print
records in a database. Although you can perform these
same functions with tables and queries, forms can present
data in customized and useful ways.
You can design your forms or use a Form Wizard to cre-
ate forms for you automatically. A Form Wizard is an
Access tool that asks you a series of questions, and then
creates a form based on your answers. The quickest way to
create a form is to use an AutoForm Wizard, which places
all the fields from a selected table (or query) on a form
automatically, without asking you any questions, and then
displays the form on the screen. The form displays one re-
cord at a time. To create a form using an AutoForm Wizard,
you click Forms in the Objects bar of the Database win-
dow to display the Format list, and then click the New but-
ton in the Database window to open the New Form dialog
box (Figure 13-12). You then click AutoForm: Columnar
to select this AutoForm Wizard.

Figure 13-12 New Form dialog box

An Access Report

Data stored in a table is not worth mach by itself; for the data to be useful in practical terms, it must be printed in
report form. A report is a formatted screen display or printout of the contents of one or more tables in a database.
Although you can print data from tables, queries, and forms, reports provide you with the greatest flexibility for
formatting printed output. The quickest way to create a report is to use the AutoReport tool. To create a form using
AutoReport Wizard, you click Reports in the Objects bar of the Database window to display the Format list, and
then click the New button in the Database window to open the New Report dialog box (Figure 13-13). You then se-
lect the Wizard you want to use. The quickest one is the AutoReport: Columnar Wizard.

Compacting a Database

When you work in an Access database and create and manipulate objects, such as queries, forms, and reports, the
size of your database increases. Likewise, when you delete records or database objects -- such as queries, forms, and
reports -- the space occupied by the deleted records or objects
on disk does not become available for other record or objects. To
make the space available, you must compact the database.
Compacting a database rearranges the data and objects in a
database to make its size smaller.
When you compact a database, Access repairs the database at
the same time. In many cases, Access detects that a database is
damaged when you try to open it and gives you the option to
compact and repair it at that time. If you think your database
might be damaged because it is behaving unpredictably, you can
use the "Compact and Repair Database" option to fix it. With
your database file open, choose the Database Utilities option
from the Tools menu, and then choose the Compact and Re-
pair Database option.
Access also allows you to set an option for your database file
Figure 13-13 New Report dialog box so that every time you close the database, it will be compacted
automatically. To compact a database automatically, with the
11
database file open, you click Options on the Tools menu, click the General tab in the Options dialog box, and
then click the Compact on Close check box to select it.

13.3 Creating and Maintaining a Database

This section uses an example to demonstrate how to create, modify and update an Access table. Valle Coffee Com-
pany recently created a database named Restaurant to track the company's restaurant customers, their orders, and
related data such as the products they order. You will help the company to complete and maintain this database.
The Restaurant database currently contains only one table -- the Customer table -- that stores data about Valle
Coffee's restaurant customers. The company wants to track information about each order placed by each restaurant
customer. This information includes the order's billing date and invoice amount. You will create a second table in
the Restaurant database, named Order, in which to store the order data.

Guidelines for Designing Database

A database management system can be a useful tool, but only if you

first carefully design the database so that it meets the needs of its
users. In database design, you determine the fields, tables, and re-
lationships needed to satisfy the data and processing requirements.
When you design a database, you should follow these guidelines:

• Identify all the fields needed to produce the re-

quired information. In the Valle Coffee case, the com- Figure 13-14 Valle Coffee's data requirements
pany needs information about customers and orders. Fig-
ure 13-14 lists the fields that satisfy those information requirements.
• Group related fields into tables. The fields relating to customers are grouped into the Customer table,
the other fields are grouped into the Order table, as shown in Figure 13-15.
• Determine each table's primary key. Recall that a primary key uniquely identifies each record in a ta-
ble. Without a primary key, selecting the exact record you want can be a problem. For some tables, one of
the fields such as a Social Security number naturally serves the function of a primary key. For other tables,
two or more fields might be needed to function as the primary key. In these cases, the primary key is re-
ferred to as a composite key. For a third category of tables, no single field or combination of fields can
uniquely identify a record in a table. In these cases, you need to add a field whose sole purpose is to serve as
the primary key. In the Valle Coffee's example, you will add a field to serve as the primary key. You will use
CustomerNum to be the primary key for the Customer table, and OrderNum as the primary key for the
Order table.
• Include a common field in related tables. You use the common field to connect one table logically
with another table. For example, Valle Coffee's Customer and Order tables will include the CustomerNum
field as a common field. When you include the primary key from one table as a field in a second table to
form a relationship, the field is called a foreign key in the second table; therefore, the CustomerNum field
will be a foreign key in the Order table.
• Avoid data redundancy. Data redundancy occurs when you store the same data in more than one place.
With the exception of common fields to connect tables, you should avoid redundancy because it wastes
storage space and can cause inconsisten-
cies.
• Determine the properties of each
field. You need to identify the properties
of each field so that the DBMS knows
how to store, display, and process the
field. These properties include the field's
name, maximum number of characters
or digits, data type, description, valid
values, and other field characteristics.

It is best to choose a field or object name that Figure 13-15 Fields grouped into Customer and Order tables
describes the purpose or contents of the field or
object. A name can be up to 64 characters long in Access. A table or query name must be unique within a database.
A field name must be unique within a table. Each field must be assigned a data type, which determines what field
12
values users can enter for the field. There are 10 data types available in Access. Besides giving the field name and
data type when you defining the fields of a record, you also want to determine the optimal field length as well as
possibly control how the data is to be entered. Other features such as alignment, fill characters, color are also con-
trolled via the Field Properties box for each field that is created in Design View. In our example, Valle Coffee docu-
mented the design for the Order table by listing each field's name, data type, size (if applicable), and description, as
shown in Figure 13-16.

Figure 13-16 Properties of fields for the Order table

Creating a Table

Creating a table consists of naming the fields and defining

the properties for the fields, specifying a primary key (and
a foreign key, if applicable) for the table, and then saving
the table structure. You will use Figure 13-16 as a guide
for creating the Order table. First, you need to open the
Restaurant database.
To open the Restaurant database:

1. Start Access. The Access window opens with Con-

trol panel on the right.
2. Select the Restaurant file. If the Restaurant file is
not displayed, click More folder, locate the Res-
taurant file in your computer and then click
Open.
3. If the Convert/Open Database dialog box ap- Figure 13-17 Select Tables in the Object bar
pears, select Convert Database. Then give a
new name to the file and save it into a proper folder on your computer.
4. Click Open on the Security Warning dialog box to open the Restaurant database.
5. Select Tables in the Object bar of the Data-
base window (see Figure 13-17).
6. Click the New button in the Database win-
dow. The New Table dialog box opens (see
Figure 13-18).
7. Click Design View in the list, and then click
the Ok button. The Table window opens in
Design view (see Figure 13-19)

In Access, you can create a table from entered

data (Datasheet View), define your own table (Design
View), use a Wizard to automate the table creation
process (Table Wizard), or use a Wizard to import or
link data from another database or other data source
(Import Table or Link Table). You use Design view to
define or modify a table structure or the properties of
the fields in a table. If you create a table without us-
ing a Wizard, you enter the fields and their proper-
ties for your table directly in the Design view win- Figure 13-18 New Table dialog box
dow.

13
 To define the OrderNum field using Design
view:

1. Type OrderNum in the first row's

Field Name text box, and them press
the Tab key to advance to the Data
Type text box. The default data type,
Text, appears highlighted in the Data
Type text box, which now also contains
a list arrow, and field properties for a
text field appear in the lower half of the
window.
2. Press the Tab key to accept Text as the
fields data type and move to the De-
scription text box, and then type
primary key in the Description text
box.
3. Change the default value of 50 to 3 in
the Field Size text box in the Field
Properties section, because order
numbers at Valle Coffee contain only Figure 13-19 Table window in design view
three digits.
4. Click the Required text
box. A list arrow appears
on the right side of the Re-
quired text box. See Figure
13-20.
5. Click Yes in the list ar-
row.

Now the definition of the first

field (OrderNum) is complete. You
will define other four fields using
the same procedure according to
Figure 13-16.
For the Paid field, the data type
is yes/no type that will specify
whether or not an invoice has been
paid. You want the Paid field to
have a Default value property value Figure 13-20 Table window after define the OrderNum field
of "No." You set this property by clicking the Default
Value text box in the Field Properties section and type
no in the text box.
The InvoiceAmt field has the Currency data type.
However, you don't want the dollar signs displayed in the
datasheet -- repeated dollar signs are unnecessary and
they clutter the datasheet. You use the Format property
in the Field Properties section to control the display of a
field value. In the Valle Coffee's case, you select the
Standard option in the Format list box for the Format
property of the InvoiceAmt field.
For the BillingDate field, you want the values in the
field to be displayed in a format showing a two-digit
month (mm), a two-digit day (dd), and a four-digit year
(yyyy), as in the following example: 01/15/2001. You
type mm/dd/yyyy in the Format text box of the Field
Properties section. Figure 13-21 shows the structure
when you are finished.
Figure 13-21 Table window after entering all five field definitions

14
Specifying the Primary Key

Although Access does not require a table to have a primary key, including a primary key offers several advantages:

• A primary key uniquely identifies each record in a table.

• Access does not allow duplicate values in the primary key field.
• Access forces you to enter a value for the primary key field in every record in the table. This is known as en-
tity integrity. If you do not enter a value for a field, you have actually given the field what is known as a
null value. You cannot give a null value to the primary key field because integrity prevents Access from ac-
cepting and processing that record.
• Access stores records on disk in the same order as you enter them but displays them in order by the field of
the primary key.
• Access responds faster to your requests for specific records based on the primary key.

To specify a primary key for a table:

1. In the Table window in Design view, click the row selector for the field you've chosen to be the primary key.
2. If the primary key will consist of two or more fields, press and hold the Ctrl key, and then click the row se-
lector for each field.
3. Click the Primary key button on the Table Design toolbar. A key symbol appears in the row selector for
the field, indicating that the field is the table's primary key.

In the Valle Coffee's case, you will specify OrderNum as the primary key for the Order table. Figure 13-22 shows
the design view of the Order table after you've defined the fields for the table and specified its primary key. You
need save the table's structure on disk. Once the table is saved, you can use it to enter data in the table.

Figure 13-22 Completed design view of the Order table

15
Adding Records to a Table

You can add records to an Access table in several ways. A table datasheet provides a simple way for you to add re-
cords. Recall that a datasheet shows a table's contents in rows and columns. Each row is a separate record in the
table, and each column contains the field values for one field in the table. To view a table datasheet, you first must
change from Design view to Datasheet view.
You will switch to Datasheet view and add the three records in the Order table datasheet. You click the View
button for Datasheet view on the Table Design toolbar. The Table window opens in Datasheet view. The table's five
field names appear at the top of the datasheet. The current record symbol in the first row's record selector identifies
the currently selected record, which contains no data until you enter the first record. The insertion point is located
in the first row's OrderNum field, whose Description property appears in the status bar. When you enter the first
record's OrderNum field value into the table, you will see a pencil symbol in the row selector, which indicates that
the record is being edited. A star symbol in the second row's selector identifies the second row as the next one avail-
able for a new record. For the Paid attribute, the field value is Yes/No type. In the table, a blank check box repre-
sents No value, and a check mark represents Yes value. You switch Yes/No value by clicking the check box. Figure
13-23 shows the three records you entered in the Order table.
Instead of typing the remaining records in the Order table, you will copy then from a table that already exists in
another database, and then paste them into the Order table.

Figure 13-23 Order table datasheet with three records added

Coping Records from Another Access Database

You can copy and paste records from a table in the same database or in a different database only if the tables have
the same structure -- that is, the tables contain the same fields in the same order. Valle Coffee Company has another
database named Valle. The Valle database contains a table named Restaurant Order that has the same table struc-
ture as the Order table you just created. The records in the Restaurant Order table are the records you want to copy
into the Order table.
Other programs, such as Microsoft Word and Excel, allow you to have two or more documents open at a time.
However, you can have only one Access database open at a time. Therefore, you need to close the Restaurant data-
base, open the Restaurant Order table in the Valle database, select and copy the table records, close the Valle data-
base, reopen the Order table in the Restaurant database, and then paste the copied records.
To copy the records from the Restaurant Order table in the Valle database and then paste to the Order table in
the Restaurant database:

1. Close the Order table, and then close the Restaurant database.
2. Click the Open button on the Database toolbar to display the Open dialog box.
3. Open the database file named Valle. The Database window opens, showing the tables for the Valle data-
base.
4. Click Restaurant Order in the Tables list box, and then click the Open button in the Database window.
The datasheet for the Restaurant Order table opens. See Figure 13-24.

16
 Figure 13-24 Datasheet for the Valle database's Restaurant Order table

5. Click the row selector for the field name row. All the records in the table are now highlighted, which
means that Access has selected all of them.
6. Click the Copy button on the Table Datasheet toolbar. All of the records are copied to the Windows Clip-
board.
7. Click the Close button on the Table window title bar. A dialog box opens asking if you want to save the data
you copied on the Windows Clipboard.
8. Click the Yes button in the dialog box, and then close the Valle database.
9. Click File on the menu bar, and then click Restaurant in the list of recently opened databases. The Data-
base window opens, showing the tables for the Restaurant database.
10. Click Order in the Tables list box and then click the Open button in the Database window. The datasheet
for the Order table opens.
11. Click the row selector for row four, which is the next row available for a new record.
12. Click the Paste button on the Table Datasheet toolbar. A dialog box opens, asking if you are sure you want
to paste the records.
13. Click the Yes button. All the records are pasted from the Windows Clipboard. See Figure 13-25.

Figure 13-25 Table after copying and pasting records

When you copied records from the Valle database and pasted them into the Restaurant database, you used the
Windows Clipboard. The Windows Clipboard is a temporarily storage area for data that is cut or copied to it
from any Windows program. The data is stored on the Clipboard until you either close Windows or cut or copy
something else to the Clipboard.
When you need to copy multiple pieces of data from one program to another -- or within the same program
(such as Access) -- you can use the Office Clipboard. The Office Clipboard lets you cut or copy up to 12 different
items from any Office 2000 program so that you can paste these items into different locations later. The Office
Clipboard appears automatically as a Clipboard as soon as you cut or copy two items to it. When the Clipboard tool-
bar opens, you will see a Copy button, Paste All button, and a Clear Clipboard button, along with icons that repre-

17
sent each item that you either cut or copied to the Clipboard. To paste an item in a new location, such as pasting
records copied from one table to another, you select the location in which to paste the item, and then click the icon
that contains the data to paste.

Updating a Database

Updating, or maintaining, a database is the process of adding, changing, and deleting records in database table
to keep them current and accurate.
To delete a record, you need to select the record in Datasheet view by clicking the row selector for the record,
and then delete it using the Delete Record button on the Table Datasheet toolbar or the Delete Record option on the
shortcut menu.
To change the field values in a record, you first must make the record the current record. Then you position the
insertion point in the field value to make minor changes or select the field value to replace it entirely.
The Datasheet window is Access’s main method of displaying the records of an open (active) table. You can use
this window to make changes to a table. The results of many Access commands appear in the Datasheet window.

13.4 Querying a Database

Recall that a query is a question you ask about data stored in a database. When you create a query, you tell Access
which fields you need and what criteria Access should use to select the records. Access provides powerful query ca-
pabilities that allow you to:

• display selected fields and records from a table

• sort records
• perform calculations
• generate data for forms, reports, and other queries
• update data in the tables in a database
• find and display data from two or more tables

Most questions about data are generalized queries in which you specify the field and records you want Access to
select. These common requests for information, such as "Which customers have unpaid bills?" are called select
queries or information query. The answer to a select query is returned in the form of a datasheet.
More specialized, technical queries, such as finding duplicate records in a table, are best formulated using a
Query Wizard. A Query Wizard prompts you for information by asking a series of questions and then creates the
appropriate query based on your answers. For common, informational queries, it is easier for you to design your
own query than to use a Query Wizard.
When you finish creating a query, Access display the results in a dynaset, which contains the fields you speci-
fied in the query and provides a view of data contained in the database table(s) you specified. You can use a dynaset
just as you would a database table to display, enter, and edit data. The difference is that, when you use a dynaset,
you can work with several fields from several different tables instead of only one table. Any changes that you make
to the fields in a dynaset are automatically included in the records of the underlying tables.
When you have finished using a dynaset and close it, it no longer exists in memory. Access allows you to save in-
structions governing the query that defined the dynaset and then reuse those query statements later. You can also
save the dynaset to a table, but the data in that table is not updated when you make changes to the tables used as
input to the original query.

Query Window

You use the Query window in Design view to create a query. In design view you specify the data you want to view by
constructing a query by example. Using query by example (QBE), you give Access an example of the information
you are requesting. Access then retrieves the information that precisely matches your example.
To open the Query window in Design view:

1. Start Access and open the Restaurant database. The Restaurant database is displayed in the Database win-
dow.
2. Click Queries in the Objects bar of the Database window, and then click the New button. The New Query
dialog box opens. See Figure 13-26.
18
 Figure 13-26 New Query dialog box

3. Click Design View in the list box.

4. Click the OK button. Access opens the Show Table dialog box on top of the Query window. Notice that the
title bar of the Query window shows that you are creating a select query.
5. Click Customer in the Tables list box, click the Add button, and then click the Close button to close the
Show Table dialog box. The Customer table's field list in the Select Query window. See Figure 13-27.

Figure 13-27 Select Query window in design view

In design view, the Select Query window contains the standard title bar, menu bar, status bar, and the Query De-
sign toolbar. On the toolbar, the Query Type button shows a select query; the icon on this button changes according
to the type of query you are creating. The title bar on the Select Query window displays the query type, Select Query,
and the default query name, Query1. You will change the default query name to a more meaningful one later when
you save the query.
The Select Query window in Design view contains a field list and the design grid. The field list, which appears
in the upper-left area of the window, contains the fields for the table you are querying. The table name appears at
the top of the list box, and the fields are listed in the order in which they appear in the table.
In the design grid, you include the fields and record selection criteria for the information you want to see. Each
column in the design grid contains specifications about a field you will use in the query. You can choose a single
field for your query by dragging its name from the field list to the design grid in the lower portion of the window.
Alternatively, you can double-click a field name to place it in the next available design grid column. When you are
constructing a query, you can see the query results at any time by clicking the View button or the Run button on the
Query Design toolbar. In response, Access displays the datasheet (dynaset), which contains the set of fields and re-
cords that results from answering, or running, the query. The order of the fields in the datasheet is the same as the
order of the fields in the design grid. Although the datasheet looks just like a table datasheet and appears in Data-
sheet view, a query datasheet is temporary, and its contents are based on the criteria you establish in the design

19
grid. In contrast, a table datasheet shows the permanent data in a table. However, you can update data while view-
ing a query datasheet, just as you can when working in a table datasheet or a form.
If the query you are creating includes every field from the specified table, you can use one of the following three
methods to transfer all the fields from the field list to the design grid:

• Click and drag each field individually from the field list to the design grid. Use this method if you want the
fields in your query to appear in an order that is different from the order in the field list.
• Double-click the asterisk in the field list. Access places the table name followed by a period and an asterisk
(as in "Customer.*") in the design grid, which signifies that the order of the fields will be the same in the
query as it is in the field list. Use this method if you don't need to sort the query or specify conditions for the
records you want to select. The advantage of using this method is that you do not need to change the query
if you add or delete fields from the underlying table structure. Such changes are reflected automatically in
the query.
• Double-click the field list title bar to highlight all the fields, and then click and drag one of the highlighted
fields to the design grid. Access places each field in a separate column and arranges the fields in the order in
which they appear in the field list. Use this method rather than the previous one if you need to sort your
query or include record selection criteria.

Creating a Query

Suppose you want the CustomerNum, Cus-

tomerName, City, OwnerName, and FirstContact
fields to appear in the query results. You will add
each of these fields to the design grid.
You drag CustomerNum from the Customer
field list to the design grid's first column Field
text box, and then release the mouse button
(Figure 13-28). In the design grid's first column,
the field name CustomerNum appears in the
Field text box, the table name Customer appears
in the Table text box, and the check mark in the
Show check box indicates that the field will be
displayed in the datasheet when you run the
query. Sometimes you might not want to display
a field and its values in the query results. For
example, if you are creating a query to show all Figure 13-28 Field added to the design grid
customers located in Michigan, and you assign
the name "Customers in Michigan" to the query, you do not need to include the State field value for each record in
the query results -- every State field value would be "MI" for Michigan. Even if you choose not to include a field in
the display of the query results, you can still use the field as part of the query to select specific records or to specify a
particular sequence for the records in the datasheet.
Next, you double-click CustomerName, City, OwnerName, and FirstContact fields to add these fields to the de-
sign grid in that order. You can now run the
query by clicking the Run button on the Query
Design toolbar. The five fields you added to the
design grid appear in the datasheet, and the
records are displayed in primary key sequence
by customer number, as shown in Figure 13-29.

Defining Table Relationships

One of the most powerful features of a rela-

tional database management system is its abil-
ity to define relationships between tables. You
use a common field to relate one table to an-
other. The process of relating tables is often
called performing a join. When you join tables Figure 13-29 Datasheet displayed after running the query
that have a common filed, you can extract data from them as if they were one larger table. For example, you can join
the Customer and Order tables by using the CustomerNum Field in both tables as the common field. Then you can
20
use a query, form, or report to extract selected data from each table, even though the data is contained in two sepa-
rate tables, as shown in Figure 13-30. In the Order query shown in Figure 13-30, the OrderNum, Paid, and In-
voiceAmt columns are fields from the Order table, and the CustomerName and State columns are fields from the
Customer table. The joining of records is based on the common field of CustomerNum. The Customer and Order
tables have a type of relationship called a one-to-many relationship.

Figure 13-30 One-to-may relationship and sample query

A one-to-many relationship exists between two tables when one record in the first table (primary table)
matches zero, one, or many records in the second table (related table), and when one record in the second table
matches exactly one record in the first table. For example, as shown in Figure 13-30, customer 635 has more than
one order. Every order has a single matching customer 635.
Access refers to the two tables that form a relationship as the primary table and the related table. The primary
table is the "one" table in a one-to-many relationship, such as the Customer table in Figure 13-30. The related table
is the "many" table, such as the Order table in Figure 13-30.
Access enforces referential integrity, which is a set of rules that maintains consistency between related tables
when you updated data in a database. Specifically, the referential integrity rules are as follows:

• When you add a record to a related table, a matching record must already exist in the primary table.
• If you attempt to change the value of the primary key in the primary table, Access prevents this change if
matching records exist in a related table. However, if you choose the cascade updates option, Access
permits the change in value to the primary key and changes the appropriate foreign key values in the re-
lated table.
• When you delete a record in the primary table, Access prevents the deletion if matching records exist in a
related table. However, if you choose the cascade deletes option, Access deletes the record in the primary
table and all records in related tables that have matching foreign key values.

When two tables have a common field, you can define a relationship between them in the Relationship window.
The Relationship window illustrates the relationships among a database's tables. In this window you can view or
change existing relationships, define new relationships between tables, and rearrange the layout of the tables. You
need to open the Relationship window and define the relationship between the Customer and Order tables. You will
define a one-to-many relationship between the two tables, with Customer as the primary table and Order as the re-

21
lated table, and with CustomerNum as the common
field (the primary key in the Customer table and the
foreign key in the Order table).
To define a one-to-many relationship between
Customer and Order tables:

1. Click the Relationships button on the Da-

tabase toolbar. The Show Table dialog box
opens. See Figure 13-31.
2. Click Customer and then click the Add but-
ton. The customer table is added to the Rela-
tionships window.
3. Click Order and then click the Add button.
The Order table is added to the Relationships
window. Figure 13-31 Show Table dialog box
4. Click the Close button
in the Show Table to
close it.
5. Click CustomerNum
in the Customer table
list, and drag it to Cus-
tomerNum in the Or-
der table list. When you
release the mouse but-
ton, the Edit Relation-
ships dialog box opens.
See Figure 13-32.
6. Click the Enforce Ref-
erential Integrity
check box, then click the
Cascade Update Re-
lated Fields and Cas- Figure 13-32 Edit Relationship dialog box
cade Delete Related
Records check boxes.
7. Click the Create button to define the one-to-many relationship between the two tables and close the dialog
box. The completed relationship appears in the Relationships window. See Figure 13-33. The join line con-
nects the CustomerNum fields, which are common to the two tables. The "one" side of the relationship has
the digit 1 at its end, and the "many" side of the relationship has the infinity symbol ∞ at its end.
8. Click the Save button on the Re-
lationship toolbar to save the
layout in the Relationships win-
dow.
9. Click the Close button on the
Relationships window title bar.

Sorting Data in a Query

Sorting is the process of rearranging

records in a specified order or sequence.
When you sort data in a database, you do
not change the sequence of the records in
the underlying tables. Only the records in
the query datasheet are rearranged ac-
Figure 13-33 Defined relationship in the Relationship window
cording to your specifications.
To sort records, you must select the sort key, which is the field used to determine the order of records in the
datasheet. Access provides several methods for sorting data in a table or query datasheet and in a form. One method
is using the Sort buttons on the toolbar.
The Sort Ascending and Sort Descending buttons on the toolbar allow you to sort records immediately,
based on the selected field. First you select the column on which you want to base the sort, and then you click the

22
appropriate sort button on the toolbar to rearrange the records in either ascending or descending order. Unless you
save the datasheet or form after you've sorted the records, the rearrangement of records is temporary.
Sort key can be unique or nonunique. A sort key is unique if the value of the sort key field for each record is dif-
ferent. A sort key is nonunique if more than one record can have the same value for the sort key field. When the
sort key is nonunique, records with the same sort key value are grouped together, but they are not in a specific order
within the group. To arrange these grouped records in a specific order, you can specify a secondary sort key,
which is a second sort key field. The first sort key field is called the primary sort key.
Access lets you selects up to 10 different sort keys. When you use the toolbar sort buttons, the sort key fields
must be in adjacent columns in the datasheet. You highlight the columns, and Access sorts first by the first column
and then by each other highlighted column in order from left to right.
If you want to sort the multiple sort keys in different sort order, you need specify the sort keys in Design view. In
design view, you position the fields serving as sort keys from left (primary sort key) to right, and then select the sort
order for each sort key.

Filtering Data

A filter is a set of restrictions you place on the records in an open datasheet or form to temporarily isolate a subset
of the records. A filter lets you view different subsets of displayed records so that you can focus on only the data you
need. Unless you save a query or form with a filter applied, an applied filter is not available the next time you run
the query or open the form. The simplest technique for filtering records is Filter By Selection. Filter By Selection
lets you select all or part of a field value in a datasheet or form, and then display only those records that contain the
selected value in the field. To use Filter By Selection, you select part or all the field value that will be the basis for
the filter in the datasheet or form, and then click the Filter By Selection button on the toolbar. You can click the
Remove Filter button on the toolbar to redisplay all the records in the datasheet or form.

Defining Selection Criteria for Queries

Just as you can display selected fields from a table in a query datasheet, you can display selected records. To tell
Access which records you want to select, you must specify a condition as part of the query. A condition is a crite-
rion, or rule, that determines which records are selected. To define a condition for a field, you place the condition in
the field's Criteria text box in the design grid.
A condition usually consists of an operator, often a comparison operator, and value. A comparison operator
asks Access to compare the values of a database field to the condition value and to select all the records for which
the relationship is true. For example, you want to create a query that will display only those records in the Order
table with the InvoiceAmt exceeding 1000. You place the condition >1000 for the InvoiceAmt field in the Criteria
text box in the query design grid. This condition selects all records in the Order table having InvoiceAmt field values
greater than 1000. Figure 13-34 lists the Access comparison operators.

Figure 13-34 Access comparison operators

23
 Multiple conditions require you to use logical operators to combine two or more conditions. When you want a
record selected only if two or more conditions are met, you need to use the And logical operator. If you place
conditions in separate fields in the same Criteria row of the design grid, all the conditions in that row must be met
in order for a record to be included in the query results. However, if you place conditions in different Criteria rows,
a record will be selected if at least one of the conditions is met. If none of the conditions is met, the Access does not
select the record. When you place conditions in different Criteria rows, you are using the Or logical operator.
Figure 13-35 illustrates the difference between the And and Or logical operators.

Figure 13-35 Logical operators AND and OR for multiple selection criteria

In the Valle Coffee's case, suppose you want to create a query from the Order table to show only the unpaid or-
ders billed on 01/15/2001. For this query, you must place the two conditions in the same Criteria row, as shown in
Figure 13-36. If you want to see a list of those customers who have been placing orders for many years or who place
orders for a substantial amount of money in order to determine which restaurant customer are most valuable to
Valle Coffee company. Specifically, a record is selected if the FirstContact field value is less than 1/1/1994 (to find
those customers who have been doing business with Valle Coffee the longest) or if the InvoiceAmt field value is
greater than 2000 (to find those customers who spend more money). You will enter the condition for the FirstCon-
tact field in one Criteria row and the condition for the InvoiceAmt field in another Criteria row, as shown in Figure
13-37.

Figure 13-36 Query window with the AND logical operator

24
 Figure 13-37 Query window with the OR logical operator

Performing Calculations

In addition to using queries to retrieve, sort, and filter data in a database, you can use a query to perform calcula-
tions. To perform a calculation, you define an expression containing a combination of database fields, constants,
and operators. For numeric expressions, the data types of the database fields must be number, currency, or
date/time; the constants are numbers; and the operators can be arithmetic operators or other specialized operators.
You need add a calculated field to the query when performing a calculation. A calculated field is a field that dis-
plays the results of an expression. When you run a query that contains a calculated field, Access evaluates the ex-
pression defined by the calculated field and displays the resulting value in the datasheet.
To enter an expression for a calculated field, you can type it directly in a Field text box in the design grid. However,
a Field text box is too small to show an entire expression at one time. Alternatively, you can open Expression
Builder to enter the expression. Expression Builder is an Access tool that contains an expression box for entering
the expression, buttons for common operators, and one or more lists of expression elements, such as table and field
names.
To use Expression Builder:

1. Display the query in Design view.

2. In the design grid, position the insertion point in the Field text box of the field for which you want to create
an expression.
3. Click the Build button on the Query Design toolbar.
4. Use the expression elements and common operators to build the expression, or type the expression directly,
as shown in Figure 13-38, which builds an expression that multiply the InvoiceAmt field values by the nu-
meric constant 0.02 (which may represent a 2% late charge).
5. Click the OK button on the Expression Builder dialog box.

Figure 13-38 Expression for the calculated field

25
13.5 Creating Forms and Reports

Creating a Form

Recall that a form is an object you use to maintain, view, and

print records in a database. In Access, you can design your own
forms or use Form Wizards to create them for you automatically.
The Form Wizard allows you to choose some or all of the fields in
the selected table or query, choose fields from other tables and
queries, and display the chosen fields in any order on the form.
You can also choose a style for the form. You will create a form to
view and maintain data in the Order table.
To create a form using the Form Wizard:
Figure 13-39 First Form Wizard dialog box
1. Start Access and open the Restaurant database.
2. Click Forms in the Objects bar of the Database of window.
3. Click the New button in the Database window. The New Form
dialog box opens.
4. Click Form Wizard, click the list arrow for choosing a table or
query, click Order to select this table as the source for the
form, and then click the OK button. The first Form Wizard dia-
log box opens. See Figure 13-39.
5. Double-click OrderNum, CustomerNum, InvoiceAmt,
BillingDate, and Paid fields in the Available Fields list box, in
that order.
6. Click the Next button to display the second Form Wizard dia-
log box, in which you select a layout for the form. See Figure 13-
40.
7. Click each of the options to review the corresponding sample Figure 13-40 Choosing a layout for the form
layout. Then click the Columnar option button and click the
Next button. Access displays the third Wizard dialog box, in
which you choose a style for the form. See Figure 13-41.
8. Click each of the styles and review the corresponding sample.
Then click Expedition and click the Next button, Access
displays the final Form Wizard dialog box. See Figure 13-42.
9. Type Order Data for the form name, and then click
the Finish button. The completed form is displayed in
Form view. See Figure 13-43.

Creating a Report

Recall that a report is a formatted hardcopy of the contents of one or Figure 13-41 Choosing a style for the form
more tables in a database. In Access, you can create your own reports
or use the Report Wizard to create them for you. Like the Form Wizard, the Report Wizard asks you a series of
questions and then creates a report based on your answers. Whether you use the Report Wizard or design your own
report, you can
change the report's
design after you
create it. You will
create a report that
includes selected
customer data
from the Customer
table and all orders
from the Order
table for each cus-
tomer.
Figure 13-43 Completed form for the Order table
Figure 13-42 Final Form Wizard dialog box
26
To create a report using the Report Wizard:

1. Click Reports in the Objects bar of the Database window to

display the Reports list box.
2. Click the New button in the Database window. The New Re-
port dialog box opens.
3. Click Report Wizard, click the list arrow for choosing a table
or query, and then click Customer. See Figure 13-44.
4. Click the OK button. The first Report Wizard dialog box opens,
in which you select fields in the order you want them to appear
on the report. See Figure 13-45.
5. Select CustomerNum, CustomerName, City, State,
OwnerName, and Phone.
6. Click the Tables/Queries list arrow, and then click Table: Figure 13-44 New Report dialog box
Order. The fields from the Order table appear in the Avail-
able Fields list box.
7. Select all fields except CustomerNum. Then click the Next
button. The second Report Wizard dialog box opens, in
which you can choose to arrange the selected data grouped
by table or ungrouped. You can display tips and examples
for the choices in the Report Wizard dialog box by clicking
the Show me more information button. See Figure 13-
46.
8. Click the Next button. The next Report Wizard dialog box
opens, in which you choose additional grouping levels. See
figure 13-47. Your report contains no further grouping lev-
els, so you accept the default options.
9. Click the Next button. The next Report Wizard dialog box
Figure 13-45 Selecting fields
opens, in which you choose the sort for the detail records.
See Figure 13-48.
10. Click the 1 list arrow and then click OrderNum to make the records from the Order table for a customer to
appear in ascending order by the value in the OrderNum field. Then click the Next button. The next Report
Wizard dialog box opens, in which you choose a layout and page orientation for the report. See Figure 13-
49.
11. Click each layout option and examine each sample that appears. You will use the Outline 2 layout option.
12. Click the Outline 2 option button, and then click the Next button. The next Report Wizard dialog box
opens, in which you choose a style for the report. See Figure 13-50.
13. Click Corporate and then click the Next button. The last Report Wizard dialog box opens, in which you
choose a report name, which also serves as the printed title on the report. See Figure 13-51.
14. Type Customer and Orders and then click the Finish button. The Report Wizard creates the report
based on your answers as shown in Figure 13-52 and saves it as an object in your database.

Figure 13-46 Choosing a grouped or ungrouped report

Figure 13-47 Choosing additional grouping levels

27
 By clicking the View button for Design view on the Print Preview toolbar, you can modify existing reports and to
create custom reports. Each item on a report in Design view is called a control. You can modify a control by click-
ing it to activate it. You also can insert other control, such as an image, onto the report.

Figure 13-51 Entering the report name

Figure 13-48 Choosing the sort order for detail re-
cords

Figure 13-49 Choosing the report layout and page

orientation

Figure 13-52 Report displayed in print preview

Figure 13-50 Choosing a style for the report

28