What is a database?

Why is it Now, why do we need to

important in your future learn database?
career?

A medical technologist sees

DATABASE is a repository of their patients once or twice a
data. year on an ongoing basis.
The way they interact with
patients will be different
From the word data, because of the needs of their
database is organized into services. This will be easy if
rows, columns and tables, you have 5 to 10 patients in
and it is also indexed to your clinic but in a real world
make retrieval of relevant scenario you will be dealing
information easier. with large number of
patients with different needs
and that you need a data
Data can be a text (example repository.
is your name), image, video
or even logs (time you log in
or log out from your email Benefits of having a
account). Health Information
System

Now, if you want to organize

your data, you need a 1. Keeps vital information
database for that. A about the patient, the
database is used to organize database can help the
data so that it can be easily dentist determine how
accessed, managed and to improve his service
updated. and better meet the
needs of his patients.

2. Having a database can

help a dentist keep in
contact with patients.
 3. Help create a list of • Allows for concurrency
patients and their while keeping data
individual preferences. consistent

• Protects from disaster

What do we need to
create a database?
Designing your first
database
DBMS (Database
management system) is
Your first step in designing a
the software system that
database is to identify the
manages databases.
building blocks of your
database and you can easily
determine that by using the
Often, the terms “Database”,
entity-relationship
“DBMS”, “data server”,
diagram(ERD) .
“database server” are used
interchangeably to refer to a
DBMS.
Erd

In this class, we will use

ERDs are used to identify the
Microsoft Access, an example
Entities and Attributes which
of DBMS.
in turn will determine your
database tables.
Why DBMS? An entity is drawn as
rectangles and attributes are
drawn as ovals as can be
• Security seen on this chart.

• Can handle many users Attributes are connected

with good performance with a line to exactly one
entity. An entity is an object Dentist, you can name the
that has some properties. table tblDentist.
Erd1
All the attributes
translates into columns in
An entity can be a noun.
a table. So if the identified
attributes of Dentist are
For example, a dentist. Name, Gender, Age and
Attributes are certain Nationality, then the
properties of that entity. tblDentist will have the
columns representing all its
attributes as shown below:
For example, a dentist has a
name, gender, age and
nationality. By convention, you use the
table_name and the attribute
From the previous discussion, to name your fields or table
you learned about Entities columns as shown on the
and attributes. Why do you example above.
need to identify them? It is
because your entities and
attributes will determine the
tables that you will create in
your database. Converting
the Entities and attributes to
tables refers to the process
of Mapping.

In mapping, the entities

translates into tables. In
this case, entity “dentist”
becomes a table. By
convention, tables should be
named tbl then followed by
the name of the entity. For
example, if your entity is
 understand how they
interact with each other to
The figure above summarizes
create a fully functional
the data types available in
relational database.
Access its description and
the storage capacity of its
data type.
Watch the video below to
learn more about objects in
Access
Databases in Access are
composed of four objects:
tables, queries, forms,
Tables
and reports. Together,
these objects allow you to By this point, you should
enter, store, analyze, and already understand that a
compile data however you database is a collection of
want. data organized into many
connected lists. In Access,
all data is stored in tables,
In this lesson, you will learn which puts tables at the
about each of the four heart of any database.
objects and come to
You might already know that each record is organized into
tables are organized into fields, it belongs with the
vertical columns and other information in that
horizontal rows. record. See the number at
the left of each row? It’s the
ID number that identifies
Rows and columns in an each record. The ID number
Access table for a record refers to every
In Access, rows and columns piece of information
are referred to as records contained on that row.
and fields. A field is more
than just a column; it’s a way
Record ID numbers
of organizing information by
the type of data it is. Every Tables are good for storing
piece of information within a closely related
field is of the same type. For information. Let’s say you
example, every entry in a own a bakery and have a
field called First Name database that includes a
would be a name, and every table with your customers’
entry in field called Street names and information, like
Address would be an their phone numbers, home
address. addresses, and email
addresses. Because these
pieces of information are all
Fields and field names details on your customers,
Likewise, a record is more you’d include them all in the
than just a row; it’s a unit of same table. Each customer
information. Every cell in a would be represented by a
given row is part of that unique record, and each
row’s record. type of information about
these customers would be
stored in its own field. If you
A record decided to add any more
information—say, a
Notice how each record
customer’s birthday—you
spans several fields. Even
would simply create a new
though the information in
field within the same table.
 examples of different forms
and form components.
While you can always enter
Finally, you will learn how to
data directly into database
use forms to enter new
tables, you might find it
records and view and edit
easier to use forms. Forms
existing ones.
ensure you’re entering the
right data in the right
location and format. This can
Throughout this tutorial, we
help keep your database
will be using a sample
accurate and consistent.
database. If you would like to
follow along, you’ll need to
download our Access 2016
This lesson will address the
sample database. You will
benefits of using forms in
need to have Access 2016
a database. You will review
installed on your computer in
examples of different forms
order to open the example.
and form components.
Finally, you will learn how to
use forms to enter new
Watch the video below to
records and view and edit
learn more about working
existing ones.
with forms in Access.

While you can always enter

Why use forms?
data directly into database
tables, you might find it Many of us fill out forms so
easier to use forms. Forms often that we hardly notice
ensure you’re entering the when we’re asked to use
right data in the right them. Forms are so popular
location and format. This can because they’re useful to the
help keep your database person asking for the
accurate and consistent. information and to the
person providing it. They are
a way of requiring
This lesson will address the information in a specific
benefits of using forms in a format, which means the
database. You will review person filling out the form
knows exactly which Items table, where the
information to include and menu items that make up
where to put it. each order are recorded.

Illustration of a paper form The Order ID field links to the

Orders table, and the Menu
This is just as true of forms in
Item ID field links to the
Access. When you enter
Menu Items table
information into a form in
Access, the data goes The records in these tables
exactly where it’s supposed include ID numbers of
to go: into one or more records from other tables.
related tables. While You can’t learn much just by
entering data into simple glancing at these records
tables is fairly because the ID numbers
straightforward, data entry don’t tell you much about
becomes more complicated the data they relate to. Plus,
as you start populating because you have to look at
tables with records from two tables just to view one
elsewhere in the database. order, you might have a
For instance, the orders difficult time even finding the
table in a bakery’s database right data. It’s easy to see
might link to information on how viewing or entering
customers, products, and many records this way could
prices drawn from related become a difficult and
tables. For example, in the tedious task.
Orders Table below the
Customer ID field is linked to
the Customers table. A form containing the same
data might look like this:

The Customer ID field links to

the Customers table The same order viewed in a
form
In fact, in order to see the
entire order you would also As you can see, this record is
have to look at the Order much easier to understand
when viewed in a form.
Modifying the record also Working with forms
would be easier because you
To open an existing form:
wouldn’t have to know any
ID numbers to enter new Open your database, and
data. When you’re using a locate the Navigation
form, you don’t have to pane.
worry about entering data In the Navigation pane,
into the right tables or in the locate the form you want to
right format—the form can open.
handle these things itself.
There’s no need to go back Double-click the desired
and forth between tables form.
because forms bring all of Clicking a form in the
the information you need Navigation Bar
together in one place.
It will open and appear as a
tab in the Document Tabs
Not only do forms make the bar.
data entry process easier for Opening a form
the user, but they also keep
Entering and modifying
the database itself working
data
smoothly. With forms,
database designers can Depending on the database
control exactly how users are you’re using, the forms you
able to interact with the work with may include
database. They can even set special tools and features
restrictions on individual that let you perform common
form components to ensure tasks with one click of a
all of the needed data is button. You’ll see examples
entered and that it’s all of these tools in the
entered in a valid format. interactives later in this
This is useful because lesson. However, no matter
keeping data consistent and what type of form you’re
organized is essential for an working with, you can follow
accurate and powerful the same procedures for
database. carrying out certain basic
tasks.
 To search for a record, type
a word you know is
To add a new record:
contained in that record in
There are two ways to add a the navigation search box.
new record to a form:
Searching for a record
To save the current
In the Records group on the record:
Home tab of the Ribbon,
Select the Home tab and
click the New command.
locate the Records group.
Adding a new record from
Click the Save command.
the Ribbon
The current record will be
On the Record Navigation saved.
bar at the bottom of the
Saving a record
window, click the New
Record button. To delete the current
record:
Creating a new record from
the Record Navigation bar Select the Home tab and
locate the Records group.
To find an existing record
to view or edit: Click the Delete command.
There are two ways to find Clicking the Delete
and view an existing record Command
using a form, and they both
A dialog box will appear.
use the Navigation bar at
Click Yes.
the bottom of the screen:
Deleting a record
The record will be
To look through records one
permanently deleted.
at a time, click the
navigation arrows. The Using form features
right arrow will take you to The exact procedure you use
the next record, and the left for filling out a form will vary
arrow will take you to the depending on the content
previous one. and design of the form
Navigating through records you’re using. The forms in
your database might be format it to make it look
similar to the examples in exactly how you want.
the two interactives below.
Between them, they include
most of the features you’ll Access offers several
commonly encounter in advanced options for
forms. creating and modifying
reports. The Report Wizard
is a tool that guides you
Click the buttons in the through the process of
interactive below to learn creating complex reports.
about a simple form. Once you’ve created a report
—whether through the
Some forms may include
Report Wizard or the Report
more options, like calendar
command—you can then
buttons, drop-down lists,
format it to make it look
yes/no checkboxes,
exactly how you want.
subforms, and embedded
tables.
In this lesson, you’ll learn
how to use the Report
Watch the video below to
Wizard to create complex
learn how to include other
reports. You’ll also learn how
Form Controls:
to use formatting options
to format text, change
Access offers several report colors and fonts,
advanced options for and add a logo.
creating and modifying
reports. The Report Wizard
Throughout this tutorial, we
is a tool that guides you
will be using a sample
through the process of
database. If you would like to
creating complex reports.
follow along, you’ll need to
Once you’ve created a report
download our Access 2016
—whether through the
sample database. You will
Report Wizard or the Report
need to have Access 2016
command—you can then
installed on your computer in Clicking the Report Wizard
order to open the example. command
The Report Wizard will
appear. In the procedures
Watch the video below to
below, we’ll discuss the
learn more about the Report
different pages in the Report
Wizard in Access.
Wizard.
The Report Wizard dialog box
Step 1: Select the fields
to include in your report
Click the drop-down arrow
to select the table or query
that contains the desired
The Report Wizard
field(s).
While using the Report
Selecting a table that
command is a quick way to
contains fields to include in
create reports from the
the report
current object, it’s not as
helpful if you want to create Select a field from the list on
a report with data from the left, and click the right
multiple objects. The Report arrow to add it to the report.
Wizard makes it easy to Adding fields to a report
create reports using fields
from multiple tables and You can add fields from more
queries. It even lets you than one table or query by
choose how your data will be repeating the above steps.
organized. Once you’ve added the
desired fields, click Next.
The added fields
To create a report with
the Report Wizard: Step 2: Organize the report

Select the Create tab and The Report Wizard will

locate the Reports group. provide you with options that
Click the Report Wizard let you choose how to view
command. and organize your data.
These options group similar Moving the Sales Unit field
data within your fields and down a grouping level
organize these fields into
Once you are satisfied with
multiple levels, like in an
the organization of your
outline or bulleted list.
report, click Next.
Step 3: Sort your report data
If you are building a report
Click the top drop-down
from only one table or query,
arrow, and select the name
you can skip to Step 3 below.
of the first field you want to
sort.
Access will offer a list of Click the button on the right
several organization options. to change the sort to
Select an option from the list ascending or descending.
to preview it.
Sorting the report
Grouping options
Add any additional sorts. You
Click Next when you are can sort up to four fields. The
satisfied with the basic sort will be applied from top
organization of your data. to bottom, meaning the sort
at the top of the list will be
If you’re not satisfied with
the main sort.
the way your data is
organized, you can now When you are satisfied with
modify the grouping levels. the way your data is sorted,
Select a field from the list, click Next.
and click the right arrow to
Depending on the grouping
add it as a new level.
you have chosen for your
Adding a new grouped level data, your sorting options
may be limited.
If necessary, modify the
order of your grouped fields
by selecting a field and
Step 4: Select a layout and
clicking the up or down
title
Priority arrow to move it up
or down a level. Click the various layout
options to see how they look,
then select one to use in modify their appearance to
your report. make them look how you
want. You can add headers
Setting the report layout
and footers, apply new
Select either a portrait (tall) colors, and even add a logo.
or landscape (wide) All of these things can help
orientation for your report. you create visually appealing
Setting the report orientation reports.

Once you are satisfied with

your report layout, click Visit this tutorial page to
Next. learn more about formatting
Select the text box, and type MS Access reports:
the title you want for your [Link]
report. access2016/advanced-
report-options/1/
Select whether you want to
preview the report or modify
its design, then click Finish. What are queries?
Finishing the report Queries are a way of
Your report will be created searching for and compiling
and saved. data from one or more
tables. Running a query is
You may have to adjust your like asking a detailed
field and row size and question of your database.
location to make sure your When you build a query in
data looks the way you want Access, you are defining
it to. To do this, you’ll need specific search conditions to
to switch to Design view. find exactly the data you
When you’re done, switch want.
back to Report view to see
your changes.
How are queries used?

Formatting reports Queries are far more

powerful than the simple
One of the strengths of searches or filters you might
reports is that you can use to find data within a
table. This is because how to navigate the Query
queries can draw their Design view.
information from multiple
tables. For example, while
you could use a search in the
customers table to find the One-table queries
name of one customer at
your business or a filter on Let’s familiarize ourselves
the orders table to view only with the query-building
orders placed within the past process by building the
week, neither would let you simplest query possible: a
view both customers and one-table query.
orders at once. However, you
could easily run a query to
We will run a query on the
find the name and phone
Customers table of our
number of every customer
bakery database. Let’s say
who’s made a purchase
our bakery is having a
within the past week. A well-
special event, and we want
designed query can give
to invite our customers who
information you might not be
live nearby because they are
able to find out just by
the most likely to come. This
examining the data in your
means we need to see a list
tables.
of all customers who live
close by, and only those
When you run a query, the customers.
results are presented to you
in a table, but when you
We want to find our
design one you use a
customers who live in the
different view. This is
city of Raleigh, so we’ll
called Query Design view,
search for “Raleigh” in the
and it lets you see how your
City field. Some customers
query is put together.
who live in the suburbs live
fairly close by, and we’d like
Click the buttons in the to invite them as well. We’ll
interactive below to learn add their zip code, 27513, as
another criteria.
If you think this sounds a
little like applying a filter,
you’re right. A one-table
query is actually just an
advanced filter applied to a
table.
Dashboards (or
Switchboard)

MS Access is a powerful tool

for recording and tracking
data without being a
professional developer. To
gain full value from a
database, dashboard is
capable on creating
numerous queries, reports,
or extracting data to MS
Excel on a regular basis to
analyze data. Creating a
dashboard will organize the
objects (Tables, Queries,
Forms and Reports) you
created in MS Access.