MTHATHA CAMPUS – NMD

ACCESS NOTES
MODULE CODE: CLT15M2/CLT12M2
Table of Contents
INTRODUCTION TO DATABASES ............................................................................................................. 3
What is a database? ............................................................................................................................ 3
Why use a database? .......................................................................................................................... 5
GETTING STARTED WITH MICROSOFT ACCESS ....................................................................................... 6
To create a new Database................................................................................................................... 6
Getting to know Access 2016/2019 .................................................................................................... 6
Working with your Access environment ......................................................................................... 7
The Ribbon ...................................................................................................................................... 7
The Quick Access toolbar ................................................................................................................ 8
The Navigation pane ....................................................................................................................... 8
INTRODUCTION TO OBJECTS................................................................................................................. 11
TABLES............................................................................................................................................... 11
DATATYPES ........................................................................................................................................ 13
TO CREATE TABLES ............................................................................................................................ 14
Understanding table views ........................................................................................................... 14
Creating a Table in Design View .................................................................................................... 15
Primary Key ....................................................................................................................................... 15
Sorting records in a Table ................................................................................................................. 15
Field Properties ................................................................................................................................. 17
RELATIONSHIPS, FORMS, QUERIES, AND REPORTS .............................................................................. 19
TABLE RELATIONSHIPS ...................................................................................................................... 19
FORMS............................................................................................................................................... 19
To create a form:........................................................................................................................... 20
QUERIES ............................................................................................................................................ 22
Designing a Simple Query ............................................................................................................. 23
Designing a multi-table query ....................................................................................................... 25
REPORTS ............................................................................................................................................ 28
To create a report: ........................................................................................................................ 29
Deleting fields ............................................................................................................................... 29
Printing and saving reports in Print Preview................................................................................. 30
PUTTING IT ALL TOGETHER ................................................................................................................... 31
REFERENCES .......................................................................................................................................... 34

2
INTRODUCTION TO DATABASES
Microsoft Access 2016/2019/2019 is a database creation and management program. To
understand Access, you must first understand databases. In this lesson, you will learn
about databases and how they are used.

What is a database?
A database is an organized collection of related information. It is an organized collection,
because in a database, all data is described and associated with other data. All
information in a database should be related as well; separate databases should be
created to manage unrelated information. For example, a database that contains
information about students should not also hold information about company stock
prices. Databases allow their users to enter, access, and analyze their data quickly and
easily. They're such a useful tool that you see them all the time. Ever waited while a
doctor's receptionist entered your personal information into a computer, or watched a
store employee use a computer to see whether an item was in stock? Then you’ve seen
a database in action.

The easiest way to understand a database is to think of it as a collection of lists. Think

about one of the databases we mentioned above—the database of patient information
at a doctor's office. What lists are contained in a database like that? Well, to start with,
there's a list of patients' names. Then there's a list of past appointments, a list with
medical history for each patient, a list of contact information...and so on.

This is true of all databases, from the simplest to the most complex. For instance, if you
like to bake, then you might decide to keep a database containing the types of cookies
you know how to make and the friends you give those cookies to. This is one of the
simplest databases imaginable. It contains two lists: a list of your friends, and a list of
cookies.

3
However, if you were a professional baker, you would have many more lists to keep track
of: a list of customers, a list of products sold, a list of prices, a list of orders...and so on.
The more lists you add, the more complex the database will be.

In Access, lists are a little more complex than the ones you write on paper. Access stores
its lists of data in tables, which allow you to store even more detailed information. In the
table below, the “People” list in the amateur baker’s database has been expanded to
include other relevant information about the baker’s friends.

4
If you are familiar with other programs in the Microsoft Office suite, this might remind
you of Excel, which allows you to organize data in a similar way. And in fact, you could
build a similar table in Excel.

Why use a database?

If a database is essentially a collection of lists stored in tables and you can build tables in
Excel, then why do you need a real database in the first place? While Excel is great at
storing and organizing numbers, Access is far stronger at handling non-numerical data,
like names and descriptions. Non-numerical data plays a significant role in almost any
database, and it's important to be able to sort and analyze it.

However, the thing that really sets databases apart from any other way of storing data is
connectivity. We call a database like the ones you’ll work with in Access a relational
database. A relational database is able to understand how lists and the objects within
them relate to one another. To explore this idea, let's go back to the simple database
with two lists: names of your friends, and the types of cookies you know how to make.
You decide to create a third list to keep track of the batches of cookies you make and who
they’re for. Since you're only making cookies you know the recipe for and you're only
going to give them to your friends, this new list will get all of its information from the lists
you made earlier.

5
See how the third list uses words that appeared in the first two lists? A database is
capable of understanding that the "Dad" and "Oatmeal" cookies in the "Batches" list are
the same things as the "Dad" and "Oatmeal" in the first two lists. This relationship seems
obvious, and a person would understand it right away. However, an Excel workbook
wouldn’t.

Excel would treat all of these things as distinct and unrelated pieces of information. In
Excel, you'd have to enter every single piece of information about a person or type of
cookie all over again each time you mentioned it because that database wouldn't be
relational like an Access database is. Simply put, relational databases can recognize what
a human can: If the same words appear in multiple lists, they refer to the same thing.

The fact that relational databases can handle information this way allows you to enter,
search for, and analyze data in more than one table at a time. All of these things would
be difficult to accomplish in Excel, but in Access even complicated tasks can be simplified
and made fairly user friendly.

GETTING STARTED WITH MICROSOFT ACCESS

To create a new Database
 Open Microsoft Access
 Select Blank Desktop Database
 You will be prompted to save the database. Browse and select the location to save
the database (e.g. Documents or USB Flash disk)
 Type the name of the database in the File Name box and click Ok
 Click Create. A database file will be created and Access will open in a new table in
Datasheet View and you will now be in a position to create and work with the database
objects.
Before you create any of the database objects you need to familiarize yourself with the Access
environment, including the Ribbon, Backstage view, the Navigation Pane, the Document
Tabs bar, and more. You will also learn how to navigate with a navigation form, if your
database includes one.

Getting to know Access 2016/2019

Access 2016/2019 uses the Ribbon to organize commands, just like in Access 2010 and
2007. If you've used these versions before, Access 2016/2019 will feel familiar. But if you
are new to Access or have more experience with older versions, you should first take
some time to become familiar with the Access 2016/2019 interface

6
Working with your Access environment
Microsoft Access uses features like the Ribbon and the Quick Access toolbar, where you
will find commands to perform common tasks in Access, as well as Backstage view.

The Ribbon
Access 2016/2019 uses a tabbed Ribbon system instead of traditional menus. The Ribbon
contains multiple tabs, each with several groups of commands. You will use these tabs
to perform the most common tasks in Access.

To minimize and maximize the Ribbon:

The Ribbon is designed to respond to your current task, but you can choose to minimize
the Ribbon if you find that it takes up too much screen space.

1. Click the arrow in the lower-right corner of the Ribbon to minimize it.

2. The Ribbon will be minimized. Click a tab to make the Ribbon reappear. It will
disappear again when not in use.

7
 3. To maximize the Ribbon, click a tab, then click the Pin icon in the lower-right
corner. The Ribbon will appear at all times.

The Quick Access toolbar

The Quick Access toolbar is located above the Ribbon, and it lets you access common
commands no matter which tab you are on. By default, it shows the Save, Undo, and
Repeat commands. If you'd like, you can customize it by adding additional commands.

The Navigation pane

The Navigation pane is a list containing every object in your database. For easier viewing,
the objects are organized into groups by type. You can open, rename, and delete objects
using the Navigation pane.

8
To minimize and maximize the Navigation pane:

The Navigation pane is designed to help you manage all of your objects, but if you feel
that it takes up too much of your screen space you can minimize it.

1. To minimize the Navigation pane, click the double arrow in the upper-right
corner.

2. The Navigation pane will be minimized. Click the double arrow again to
maximize it.

If you would like to make the Navigation pane smaller without fully minimizing it, you
can resize it. Simply click and drag the right border of the Navigation pane. When it is
the desired size, release your mouse.

Object sorting in the Navigation pane

9
By default, objects are sorted by type, with tables in one group, forms in another, and so
on. However, if you wish, you can sort the objects in the Navigation Pane into groups of
your choosing. There are four sort options:

 Custom allows you to create a custom group for sorting objects. After applying
the sort, simply drag the desired objects to the new group.
 Object Type groups objects by type. This is the default setting.
 Tables and Related Views groups forms, queries, and reports together with the
tables they refer to.
 Created Date or Modified Date sorts objects based on when they were created
or last edited.

To sort objects in the Navigation pane:

1. Click the drop-down arrow to the right of All Access Objects, then select the
desired sort from the drop-down menu.

2. The objects in the Navigation pane will now be sorted to reflect your choice.

10
INTRODUCTION TO OBJECTS
Databases in Access are composed of four objects: TABLES, QUERIES, FORMS, and
REPORTS. Together, these objects allow you to enter, store, analyze, and compile your
data as you wish.

In this lesson, you will learn about each of the four objects and come to understand how
they interact with each other to create a fully functional relational database.

TABLES
A table is a collection of related data organized into many connected lists within
a database. In Access, all data is stored in tables, which puts tables at the heart of any
database. It is a structure that organises data into horizontal rows and vertical columns -
forming a grid. Tables are similar to a worksheets in spreadsheet.

In Access, rows are referred to as records and columns as fields. A field is more than just
a column—it’s a way of organizing information by the type of data it is. Every piece of
information within a field is of the same type. For example, every entry in a field called
“First Name” would be a name, and every entry in field called “Street Address” would be
an address.

11
Likewise, a record is more than just a row—it's a unit of information. Every cell in a given
row is part of that row’s record.

Notice how each record spans several fields. Even though the information in each record
is organized into fields, it belongs with the other information in that record. See the
number at the left of each row? That’s the ID number that identifies each record. The ID
number for a record refers to every piece of information contained on that row.

12
Tables are good for storing closely related information. Let's say you own a bakery and
have a database that includes a table with your customers' names and information like
their phone numbers, home addresses, and email addresses. Since these pieces of
information are all details about your customers, you’d include them all in the same
table. Each customer would be represented by a unique record, and each type of
information about those customers would be stored in its own field. If you decided to
add any more information—say, a customer's birthday—you would simply create a new
field within the same table.

DATATYPES
A data type must be assigned to each field in a table. The data type of a field determines
what kind of data can be entered and stored in a field. It also determines what can be
done with the field e.g. a number data type must be used if the field will be used in
calculations.

When you design a database in Access 2013, you decide what type each field will be. Access
provides 12 field/data types for you to choose among. Choose the field/data type that best
describes the data you want to store in the field and that works for the type of analysis you
need to use the field. Here are tips on when to use which type of field.

Field Type/Data Type What It Holds / its use

Short Text Text up to 255 characters long (including spaces and

punctuation). Use a Text field, not a Number field, for codes
even if they look like numbers, such as phone numbers, zip
codes, and other postal codes.

Long Text Text up to 65,536 characters.

13
 Number Only numbers. You may use + or – before the number, as well
as a decimal point. If you plan to do math with a field, use a
Number or Currency field.

Currency Numbers with a currency sign in front of them ($, ¥, R, € and so

on).

AutoNumber Numbers unique to each record and assigned by Access as you

add records, starting at 1. Use an AutoNumber field as the
primary key field for most tables.

Date/Time Dates, times, or both.

Hyperlink Text string formatted as a hyperlink. (If you click the link, it takes
you to the page.) This field type is especially useful if related
information is available on the web.

Yes/No Yes or no (a particular condition is, or isn’t, in effect) — or other

two-word sets, such as True/False, On/Off, and Male/Female.
Use a Yes/No field if you want to display the field as a check box
on forms.

Attachment Stores one or more entire files — pictures, sound, Word

documents, even video — in one Attachment field.

Calculated Data created with a formula. Use a Calculated field when a

calculated value will be used in many queries, forms, and
reports.

TO CREATE TABLES

It is recommended to create the table structure in Design view rather than datasheet view,
as field properties cannot be altered in datasheet view.

Understanding table views

There are multiple ways to view a database object. The two views for tables are Design View
and Datasheet View.

14
DATASHEET VIEW shows the data in the database. It also allows you to enter and edit the
data or records. It does not let you change the format of the database, other than minor
changes (such as displayed column widths and sorting of records).

DESIGN VIEW allows you to create or change the table, form, or other database object, and
configure the fields. You can also set keys and restrict the values entered here. But you can't
change the database data in design view. You constantly switch back and forth between them
while designing your database. Use the View button to do this.

In design view, clicking the View button switches you to datasheet view. In datasheet view,
clicking the View button switches you to design view. You can select other views with its
dropdown arrow. It is probably the most used button on the toolbar.

Creating a Table in Design View

 Click on the create Tab in the Ribbon.
 Under Tables group, select Table design.
 Enter the fieldnames for your table and choose the appropriate datatypes in the
datatype column.
 Once you have included all the fieldnames you need to save the table. Click the save
command in the quick access toolbar.
 You will be prompted to write the name for your table and click ok
 Click yes to answer the question “ Do you want to create the primary key now”

Primary Key
A primary key is a field or set of fields with values that are unique throughout a table. It is a
unique identifier for each record in a table. Access will not allow duplicate entries in a primary
key field. When creating a new table, Access automatically creates a field “ID” with the auto
number data type and assigns this as the Primary Key.

To enter data/records in the table

You will use Datasheet view to capture or enter records or data in your table. To do so, open
the table in the navigation pane either by double clicking the table name or right clicking the
table name and choose open. The table will open up in Datasheet view allowing you to
capture the records for your table.

Sorting records in a Table

When you sort records, you are putting them into a logical order, with similar data
grouped together. As a result, sorted data is often simpler to read and understand than
unsorted data. By default, Access sorts records by their ID numbers. However, there are
many other ways records can be sorted. For example, the information in a database
belonging to a bakery could be sorted in a number of ways:

15
  Orders could be sorted by order date or by the last name of the customers who
placed the orders.
 Customers could be sorted by name or by the city or zip code where they live.
 Products could be sorted by name, category (like pies, cakes, and cupcakes), or
price.
You can sort both text and numbers in two ways: in ascending order and descending
order. Ascending means going up, so an ascending sort will arrange numbers from
smallest to largest and text from A to Z. Descending means going down, or largest to
smallest for numbers and Z to A for text. The default ID number sort that appears in
your tables is an ascending sort, which is why the lowest ID numbers appear first.

To sort records:

1. Select a field you want to sort by. In this example, we will sort by customers'
last names.

2. Click the Home tab on the Ribbon, and locate the Sort & Filter group.
3. Sort the field by selecting the Ascending or Descending command.
o Select Ascending to sort text A to Z or to sort numbers from smallest to
largest. We will select this in our example because we want the last names
to be in A-to-Z order.
o Select Descending to sort text Z to A or to sort numbers from largest to
smallest.

4. The table will now be sorted by the selected field.

16
 5. To save the new sort, click the Save command on the Quick Access toolbar.

After you save the sort, the records will stay sorted this way until you perform
another sort or remove the current one. To remove a sort, click the Remove Sort
command.

Field Properties

When working with data in tables, you may encounter situations that require the
data be restricted or adhere to some default specifications in particular columns.
You can define these requirements by using the field properties.

The field properties affect how the data is stored and presented, among other
things. The list of field properties that are available to you is dependent on the data
type chosen for that field. Some field properties are specific to Text fields, and
others are specific to Number fields. The field properties can be found in the Design
view. As you click each field, you will see the field properties for that field. Some
of the most important field properties to note are:

Field Size: It enables you to set a maximum size limit on data entered in that
column. For the Text data type, size refers to the length (number of characters and
spaces) of the Text data in that column. Text fields: The maximum number of
characters (up to 255) that can be entered in the field. The default setting is 50.

Number / Currency fields: Stores the number as a Byte, Integer, Long Integer,
Single, Double, or Replication ID. The default setting is Long Integer.

17
Format: How the data in the field will be displayed on the screen.

Input Mask: This feature can be useful in data entry situations. Where Format
controls how data is displayed, Input Mask controls how data is entered into a
particular field. Input Mask is available for the following data types: Text, Number,
Date/Time, and Currency. For example, if a user needs to enter a telephone
number, Input Mask can create the characters and structure with which you are all
familiar. As the user types, the number automatically assumes a phone number
format: (###) ###-####.

Decimal Places: The number of decimal places in Number and Currency fields.

Caption: A label for the field that will appear on forms. If you don't enter a caption,
Access will use the field name as the caption.

Default Value: A value that Access enters automatically in the field for new records.

Validation Rule: An expression that limits the values that can be entered in the
field.

Validation Text: The error message that appears when an incorrect or restricted
value is entered in a field with a validation rule.

Required: Another important property, Required simply forces a user to enter

some value, using the proper data type, in the designated field. A new record will
not be added if the Required field is not properly filled. As with Input Mask, this
property is an excellent mechanism for asserting more control over the data entry
process.

Allow Zero Length: Specify whether or not the field allows zero-length text strings
(a string containing no characters). Zero-length text strings are useful if you must
enter data in a field, but no data exists. For example, if a Social Security field
requires data, but you don't know the social security number, you would enter a
zero-length text string in the field. To enter a zero-length text string type "" in the
cell. The cell will appear empty. The default is No.

Indexed: Specify whether or not you want to index the field to speed up searches
and sorts performed on the field. The default is No.

18
RELATIONSHIPS, FORMS, QUERIES, AND REPORTS
Although tables store all of your data, the other three objects offer you ways to work with
it. These objects are forms, queries, and reports. Each of these objects interacts with the
records stored in your database's tables.

TABLE RELATIONSHIPS

Although each table stores data about a different subject, tables in an Access database
usually store data about subjects that are related to each other. For example, a database
might contain:

 A customers table that lists your company’s customers and their addresses.
 A products table that lists the products that you sell, including prices and pictures
for each item.
 An orders table that tracks customer orders.

Because you store data about different subjects in separate tables, you need some way
to tie the data together so that you can easily combine related data from those separate
tables. To connect the data stored in different tables, you create relationships. A
relationship is a logical connection between two tables that specifies fields that the tables
have in common.

FORMS
Forms are used for entering, modifying, and viewing records. You likely have had to fill
out forms on many occasions, like when visiting a doctor's office, applying for a job, or
registering for school. The reason forms are used so often is that they're an easy way to
guide people into entering data correctly. When you enter information into a form in
Access, that data goes exactly where the database designer wants it to go—in one or
more related tables.

19
Forms make entering data easier. Working with extensive tables can be confusing, and
when you have connected tables you might need to work with more than one at a time
to enter a set of data. However, with forms it's possible to enter data into multiple tables
at once, all in one place. Database designers can even set restrictions on individual form
components to ensure all of the needed data is entered in the correct format. All in all,
forms help keep data consistent and well organized, which is essential for an accurate
and powerful database.

In this lesson, you will learn how to create and modify forms. You'll also learn how to use
form options like design controls and form properties to make sure your form works
exactly the way you want.

To create a form:
Access makes it easy to create a form from any table in your database. Any form you
create from a table will let you view the data that's already in that table and add new
data. Once you've created a form, you can modify it by adding additional fields and design
controls like combo boxes.

1. In the Navigation pane, select the table you want to use to create a form. You
do not need to open the table.
2. Select the Create tab, locate the Forms group, and click the Form command.

20
 3. Your form will be created and opened in Layout view.

4. To save the form, click the Save command on the Quick Access toolbar. When
prompted, type a name for the form, then click OK.

Adding additional fields to a form

When you use the Form command on an existing table, all of the fields from the table are
included in the form. However, if you later add additional fields to the table, these fields
will not automatically show up in existing forms. In situations like this, you can add
additional fields to a form.

21
To add a field to a form:

1. Select the Design tab, then locate the Tools group on the right side of the
Ribbon.
2. Click the Add Existing Fields command.

3. The Field List pane will appear. Double-click the desired field(s).

4. The field will be added.

You can also use the above procedure to add fields to a totally blank form. Simply
create a form by clicking the Blank Form command on the Create tab, then follow
the above steps to add the desired fields.

QUERIES
Queries are a way of searching for and compiling data from one or more tables. Running
a query is like asking a detailed question of your database. When you build a query in
Access, you are defining specific search conditions to find exactly the data you want.

Queries are far more powerful than the simple searches you might carry out within a
table. While a search would be able to help you find the name of one customer at your

22
business, you could run a query to find the name and phone number of every customer
who's made a purchase within the past week. A well-designed query can give information
you might not be able to find just by looking through the data in your tables.

Designing a Simple Query

To create a simple one-table query:

1. Select the Create tab on the Ribbon, and locate the Queries group.
2. Click the Query Design command.

3. Access will switch to Query Design view. In the Show Table dialog box that
appears, select the table you want to run a query on.

4. Click Add, then click Close.

5. The selected table will appear as a small window in the Object Relationship
pane. In the table window, double-click the field names you want to include
in your query. They will be added to the design grid in the bottom part of the
screen.

23
6. Set the search criteria by clicking the cell in the Criteria: row of each field you
want to filter. Typing criteria into more than one field in the Criteria: row will
set your query to include only results that meet all criteria. If you want to set
multiple criteria but don't need the records shown in your results to meet all of
them, type the first criteria in the Criteria: row and additional criteria in the or:
row and the rows beneath it.

For this one-table query, we'll use simple search criteria.

o We want to find our customers who live in the city of Raleigh, so in our City
field we'll type "Raleigh". Typing Raleigh in quotation marks will retrieve
all records with an exact match for Raleigh in the City field.
o Some customers who live in the suburbs live fairly close by, and we'd like to
invite them as well. We'll add their zip code, 27513, as another criteria.
Because we want to find customers who either live in Raleigh or in the
27513 zip code, we'll type "27513" into the or: row of the Zip Code field.

7. After you have set your criteria, run the query by clicking the Run command on
the Design tab.

24
 8. The query results will be displayed in the query's Datasheet view, which looks
like a table. If you want, save your query by clicking the Save command in the
Quick Access toolbar. When prompted to name it, type the desired name, then
click OK.

Now you know how to create the simplest type of query with only one table. In the
next lesson, you'll learn how to create a query that uses multiple tables.

Designing a multi-table query

Queries can be difficult to understand and build if you don't have a good idea of what
you're trying to find and how to find it. A one-table query can be simple enough to make
up as you go along, but to build anything more powerful you'll need to plan the query in
advance.

Creating a multi-table query

To create a multi-table query:

1. Select the Query Design command from the Create tab on the Ribbon.

2. In the dialog box that appears, select each table you want to include in your
query and click Add. You can press and hold the Ctrl key on your keyboard to

25
 select more than one table. When we planned our query, we decided we
needed information from the Customers and Orders table, so we'll add these.

3. After you have added all of the tables you want, click Close.
4. The tables will appear in the Object Relationship pane, linked by a join line.
Double-click the thin section of the join line between two tables to edit its join
direction.

5. The Join Properties dialog box will appear. Select an option to choose the
direction of your join.
o Choose option 2: for a left-to-right join. In our query, the left table is the
Customers table, so choosing this would mean all of the customers who
met our location criteria—whether or not they had placed an order—would
be included in our results. We don't want to choose this option for our
query.
o Choose option 3: for a right-to-left join. Because our right table is our
Orders table, selecting this option will let us work with records for all of the
orders and only the customers who've placed orders. We'll choose this
option for our query because this is exactly the data we want to see.

26
 6. In the table windows, double-click the field names you want to include in your
query. They will be added to the design grid in the bottom part of the screen.

In our example, we'll include most of the fields from the Customers table: First
Name, Last Name, Street Address, City, State, Zip Code, and Phone Number.
We'll also include the ID number from the Orders table.

7. Set field criteria by entering the desired criteria in the criteria row of each field.
We want to set two criteria:
o First, to find customers who do not live in Raleigh, we'll type Not like
("Raleigh") in the City field.
o Second, to find customers who have a phone number beginning with the
area code 919, we'll type Like ("919*") in the Phone Number field.

27
 8. After you have set your criteria, run the query by clicking the Run command on
the Design tab.

9. The query results will be displayed in the query's Datasheet view, which looks
like a table. If you want, save your query by clicking the Save command in the
Quick Access toolbar. When prompted to name it, type the desired name and
click OK.

REPORTS
Reports allow you to organize and present your data in a reader-friendly, visually
appealing format. Access makes it easy to create and customize a report using data from
any query or table in your database. If you've ever received a computer printout of a
class schedule or a printed invoice of a purchase, you've seen a database report. Reports
are useful because they allow you to present components of your database in an easy-
to-read format.

In this lesson, you will learn how to create, modify, and print reports.

28
To create a report:
Reports give you the ability to present components of your database in an easy-to-read,
printable format. Access lets you create reports from both tables and queries.

1. Open the table or query you want to use in your report.

2. Select the Create tab on the Ribbon. Locate the Reports group, then click the
Report command.

3. Access will create a new report based on your object.

4. It's likely that some of your data will be located on the other side of the page
break. To fix this, resize your fields. Simply select a field, then click and drag
its edge until the field is the desired size. Repeat with additional fields until all
of your fields fit.
5. To save your report, click the Save command on the Quick Access toolbar.
When prompted, type a name for your report, then click OK.

Just like tables and queries, reports can be sorted and filtered. Simply right-click the
field you want to sort or filter, then select the desired option from the menu.

Deleting fields
You might find that your report contains some fields you don't really need to view. For
instance, our report contains the Zip Code field, which isn't necessary in a list of orders.
Fortunately, you can delete fields in reports without affecting the table or query where
you grabbed your data.

29
To delete a field in a report:

1. Click any cell in the field you want to delete, then press the Delete key on your
keyboard.

2. The field will be deleted.

When you delete a field, be sure to delete its header as well. Simply select the header
and press the Delete key.

Printing and saving reports in Print Preview

While you can print reports using commands in the Backstage view, you can also use
Print Preview. Print Preview shows you how your report will appear on the printed page.
It also allows you to modify the way your report is displayed, print it, and even save it as
a different file type.

To print a report:

1. From the Home tab, click the View command, then select Print Preview from
the drop-down list. Your report will be shown as it will appear on the printed
page.

2. If necessary, modify the page size, margin width, and page orientation using
the related commands on the Ribbon.

3. Click the Print command.

30
 4. The Print dialog box will appear. Set any desired print options, then click OK.
5. The report will be printed.

PUTTING IT ALL TOGETHER

Even if you have a good idea of how each object can be used, it can initially be difficult to
understand how they all work together. It helps to remember that they all work with the
same data. Every piece of data a query, form, or report uses is stored in one of your
database tables.

Forms allow you to both add data to tables and view data that already exists. Reports
present data from tables and also from queries which, in turn, search for and analyze
data within those same tables.

These relationships sound complicated, but in fact they work together so well and
naturally that we often don't even notice when we're using connected database objects.

31
Have you ever used an electronic card catalog to search for a book at the library? Chances
are, you entered your search in something that looks like this:

When you conducted that search, you were entering your search terms into a form that,
in turn, created and ran a query based on your request. When the query finished
searching the database's tables for records that matched your search, you were shown a
report that drew information from the query and the related tables—in this case, a list
of books matching your search terms. You could represent the connections between the
objects like this:

32
Imagine if, instead of using these tools, you'd had to search within a giant table containing
every book in the library system. The relevant records would likely be spread out across
many tables: a table for book titles and descriptions, a table containing information about
which books are checked in or out, and a table with each branch of the library, just to
name a few.

You'd have to search at least three tables just to find a book, learn its location, and see
whether it's checked in! It's easy to imagine how difficult it could become to find the right
book. If you weren't careful, you might even mess something up by accidentally deleting
or editing a record. It's easy to see how the database objects make this search much more
manageable.

In Lesson 3, we discussed the concept of a relational database, which is a database that

is able to understand how different sets of data relate to one another. Situations like the
example above are exactly why people find relational databases so useful. Without a
relational database, what should be a simple task—searching for a book and seeing if it's
checked in and where—becomes incredibly complicated and time consuming. Knowing
how to use the four Access objects can make even complicated tasks fairly user friendly.

33
REFERENCES
Du Toit, C. & Van Der Merwe, S. 2003. The practical guide to the ICDL. Cape Town:
Future Managers (Pty) Ltd.

Lubbe, M. & Benson, S. 2009. Let’s Do It: ICDL-the practical way. Cape Town. Let’s
Do It.

Parsons, Oja, Beskeen, Cram, Duffy, Friedrcichsen & Reding. 2014. Computer
Concepts and Microsoft Office 2013. Canada: GEX Publishing Services

Veermat. M.E. 2013. Discovering Computers and Microsoft 2013: A fundamental

combined approach. Canada: Nelson Education LTD.

34