OPERATE DATABASE APPLICATION

Operate Data base Application
Unit of
competence
Operate Database Application
Learning
Outcomes
Create database objects
Customize basic settings
Retrieve information
Create forms
Create reports
Prepared By Taye B 1

Objectives:
At the ofthe modules, trainees will be able to
 Understand fundamental concepts including database, table, record, field,
field name, and primary key
 Understand the uses ofsimple data types including text, number, and
date
 Create a table in Access using Design view
 Add records toa table using Datasheet view
 Find data using the Find feature
 Delete a record from a table
 Sort a table on one field
Prepared By Taye B
.
2

• What is Database?
• Database is any collection of data organized for
storage in a computer memory and designed for easy
access by authorized users. This information can be
referred to time and time again. The data may be in
the form of text; numbers, or encoded graphics.
• Database is made up of fields and records. Each field
is part of record.
• Databases are designed to organize and store large
amount of data.
• What is Microsoft Access?
• Microsoft Access is a powerful database
management system (DBMS) that functions in the
windows environment and allows you to create and
process data in a database.

• Definitions
• These words are used often in Access so you will want
to become familiar with them before using the
program and this tutorial.
• Relational Database: in relational databases such as
Access, data is stored in tables made up of one or
more fields (Access calls a column a field). The data
stored in each column must be of a single data type
such as Character, Number or Date. A collection of
values from each column of a table is called a record
or a row in the table.
• Different tables can have the same column in
common. This feature is used to explicitly specify a
relationship between two tables. Values appearing in
column A in one table are shared with another table

• Table: tables are the main units of data storage in a database.
A table is a collection of data about a specific topic; it is made up
of one of more fields.
• Field: a field is a column in a table and defines a data type for
a set of values in a table. For example, a mailing list table might
include fields for first name, last name, address, city, state, zip
code, and telephone number.
• Record: a record in a row in a table and is a set of values
defined by fields. In a mailing list table, each record would
contain the data for one person as specified by the intersecting
fields.
• Data type: data types are the properties of each field. A field
only has one data type, such as Character, Number or Date.
• Primary Key: a primary key is a value that can be used to
identify a unique record in a table.
•
• Design View: it provides the tools for creating fields in a table.
• Datasheet View: it allows you to update, edit, and delete
information from a table.

LO1: - Create database objects
Starting Microsoft Access
 Click on start button.
 Click on all program and then a sub menu appear.
 Click on Microsoft office and then click on Microsoft
office Access.
 Then after a while Microsoft Access window will
appear.
Getting Started
You can start database with different option from the
task pane on the new opened windows. Unlike Word
documents, Excel worksheets and Power Point
presentations, you must save an Access database

New Blank Database
 ChooseFilemenuandclickon New,andthen a new
filetaskpanewillappear totheright side.
 Then clickon blankdatabasefrom newfiletaskpane,
andthen a filenewdata basedialogboxwillappear.
 Choosesavein location toshowwheretosaveyour filein
dropdown box,then writea filenameunder filename
text box.
 Then,clickon createbutton.

Object Description
Tables Tables are where the actual data is defined, entered and store a database’s
data. Tables consist of records (rows) and fields (columns).
Queries Queries ask a question of data stored in a table. A query consists of
specifications indicating which fields, records, and summaries you want to
see from a database.
Forms Forms are custom screens that provide an easy way to enter and
view data in a table or query.
Reports Reports present data from a table or query in a printed format.
Pages A data access page is a special type of Web page designed for viewing and
working with data from the Internet or an intranet. This data is stored in a
Microsoft Access database or a Microsoft SQL Server database.
Macros A macro is a set of one or more actions that each performs a particular
operation, such as opening a form or printing a report.
Modules A module is a collection of programming Language Visual Basic for
Applications declarations and procedures that are stored together as a unit.
Database Components/objects
An Access database consists of several different components. Each componen
listed is called an object.

LO2: - Customize basic settings
Building Database Tables
This section describes how to construct data tables, enter and edit data, sort
records, and find records that meet certain conditions.
Creating Tables in Design View
1. By clicking the Table tab on the left hand side, you will find Access provides
three ways to create a table for which there are icons in the Database window.
Create Table in Design view will allow you to create the fields of the table. It
allows you to define the fields in the table before adding any data to the
datasheet. This is the most common way of creating a table and is explained
in detail below.
Create table by using wizard will step you through the creation of a table.
Create table by entering data will give you a blank datasheet with unlabelled
columns that looks much like an Excel worksheet.

2. Double clicking on Create table in Design view displays the Table Design
screen where you define fields for your table. The screen is divided into two
parts: a top pane for entering the field name, data type, and an option
description of the field and a bottom pane for specifying field properties.
3. Every table consists of fields. For each field, specify the name of the field,
the type of data, and any description needed to determine what data the
field contains. Pressing the Tab key moves the cursor from one column to
the next in the Table Design screen. You can select the data type from the
drop-down list in the Data Type column as shown in figure above
Prepared By 2BN 10

• Elements of table with design view
1. Field Name – The name by which the field
is identified.
2. Data Type – When you add a field MS-
Access automatically assigns in the text. If
you want to change this data type, you can
select from data type list.
3. Description – Is an optional descriptive
text that you can add for created fields.
Prepared By 2BN 11

Data Types
1. Text – Used to store text information (Alphanumeric character) up to 255
bytes.
2. Memo – Used to store large block of text up to 64,000 characters (About
20 pages) in length.
3. Number – Used to store numeric data that can be used in mathematical
calculation.
4. Date/Time – Used to store date and time.
5. Currency – Used to store monetary values.
6. Auto number – To store numeric values (sequential value) that MS-Access
automatically increment the value by 1 starting from 1.
7. Yes/No – Used to recording logical Yes/NO, True/False, and On/Off values.
8. OLE object – Used to store Graphics, Drawing, Photographs, Signature and
other objects that were created in other application.
9. Lookup Wizard – Used to create a field that allow you to choose a value from
a list of values using combo box (List box).
Prepared By Daba Safera 12

• Setting Field Properties
- Field properties are a set of characteristics that
provide additional control over how a field works.
You set a field property to specify how you want its
data stored, handled and displayed. Properties you
can set for a field however are dependent on its data
type. - The available field properties in Ms-Access
are;
1. Field Size – Sets the maximum allowable number of
characters stored in a field. The default field size for
Text data type is 50.
2. Format – Affect how a field data is displayed. You
can use predefined format or set your own.

Examples Date Format Predefined Format Sample Format
- Short date 19/6/1998 - D/M/YYYY - Medium date 19-
Jun-98 - DD/MMM/YY - Long date Sunday, June 19,
1998 - DDDD/MMMMM/YYYY - General date
19/6/1998 5:10:20 AM - D/M/YYYY h:n:s “AM” h –
For hour n – For minute Number Format Sample Format
Examples - #,###.00 1,234.00 - Birr #,###.00 Birr
1,234.00 - Birr #,### Birr 1,234
3. Input Mask – Display formatting characters in a field.
You can use predefined masks or customize your own.
E.g. “(205)”-00-00-00 = (205)-41-12-26 “(115)”-000-
000 =(115)-111-222 “MTVET”000 = MTVET001
Mask Characters Their usage C - Accept any character
or space. A - Accept letter or digits E.g. (000)-AAA-
AAAAA (390)-666-MTVET < - Cause character to the
right to be converted to lower case E.g. <????? = abebe
7

> - Cause character to the right to be converted to
upper case. E.g. >????? = ABEBE >?<???? =
Abebe
4. Caption – Used to describe a field name on table
datasheet view.
E.g. Fname = First Name Lname = Last Name
5. Default Value – a value that is automatically
entered in a field when records are created.
6. Validation Rule – An expression used by MS-
Access to determine whether or not an item entered
in a field should be stored as value.

E.g. F or M =To enter either F or M. >20 = To enter a value
grater than 20. < >0 = To enter none zero value.
7. Validation Text – A text that appears when invalid data is
entered in a field. When you enter a data that break the
validation rule, a validation text will be displayed as
message. Example Validation Rule Validation Text M or F
- Enter only M or F 0 or >100 - Enter a value either 0 or
greater than 100. <1/1/92 - Enter a date before 1992
>1/1/91 and <1/1/92 - Enter a date must be in 1991 NB –
In the validation text box, type the message that you want
to display when the validation rule is broken.
8. Required – Setting the forces a user to make an entry in a
field. If this field property is set to Yes MS-Access doesn’t
allow a null value to be stored in this field. 9. Indexed –
When this field property is set to Yes (No Duplicates) MS-
Access creates and maintain an index of values entered in
to the field.

Sorting and Finding Records
Two of the most common database tasks are sorting records
and searching for records that meet a particular condition.
Fortunately, both of these operations are easy to perform
while viewing the table datasheet.
Sorting Records
To sort records by a particular field, select the field you want to
sort. From the Records menu select Quick Sort then choose
either Ascending or Descending order. Access quickly sorts
the records by the selected field.
Finding Records
If you want to locate a record that contains a certain name or
date use the Find command on the Edit menu. Type the
search string in the Find What box and click Find First.
Access will highlight the first record that contains the search
string.

Setting The Primary Key
To set a primary key;
- Switch to table design view
- Select the field that you want to define as a primary key
- Click on Edit menu – Select Primary key command
OR
- From standard toolbar click on Primary key button ( ).
Removing The Primary Key - If the primary key field is used in
relationship, you must delete the relationship before removing the
primary key.
To remove the primary key;
- Switch to table design view
- Select the field that you want to delete it’s primary key
- Click on primary key button

Creating Relationship between Tables
- After you have set up different tables for each subject in your database, you need
away of telling MS-Access how to bring that information back together again. The
first step in this process is defining and creating relationship between tables.
To create relationship;
Switch to the database window
Click on Tools Menu – select Relationship command OR From standard toolbar click
on relationship button ( ) The show Table dialog box will be displayed.
Add the table(s) To add a table;
Select the table that you want to add then click on Add button or double click
on the table name. If MS-Access doesn’t automatically display the show table
dialog box click on show table button ( ) from standard toolbar.
After adding the necessary tables close show table dialog box.
Drag the field that you want to relate from one table to the related field in
the other table. In most cases, you drag the primary key field (which is displayed in
bold text) from one table to a similar field (often with the same field name)
called the foreign key in the other table.

Check the Enforce Referential Integrity check box.
- Click on the Create button.
Repeat the above steps for each pair of tables you want to
relate.
Finally close the relationship dialog box.
Viewing Existing Relationship
To view existing relationship; Be on the database window
Click on relationship button or select Relationship command
from Tools menu.
Deleting Relationship
To delete existing relationship; Be on the database window
Click on the relationship button
Click on the relationship line for the relationship that you
want to delete (the line will turn to bold when it’s
selected), and then press the delete key.

LO3: - Retrieve Information
• Creating Queries
A query is simply a question you ask a database.
Queries select records from one or more tables in
a database that match the criteria you set, they
can be viewed, analyzed, and sorted on a common
datasheet.

Starting Queries
Types of queries
• There are various types of queries for different uses:
Select queries- The most commonly used type of query.
-It is used to select data from several tables with or without criteria.
-Used to perform calculations.
-It is the default query type.
Update queries- important in adding information in the fields of a
Table.
- Is used to make changes to a group of records in one or more tables
based on certain criteria such as;
- Increase the salary of employees by 5%
- Decrease the price of product by 10%
To create Update query; Start a new query using design view Add the necessary
tables and fields on the query gridline area. Create an expression on the Update
To row for the field that you want to update and specify the criteria.
Finally save and run your query.
Note: Whenever you run the update query the value on that field will be changed.

Append Query – Used to add (copy) a group of records from
one or more tables. You can append a group of records
those are deleted by delete query.
To create Append query;
Stat a new query
Add the source table (a table from which want to append)
E.g. Staff2
Add the fields from source table
Select Append Query from query type list
= The Append dialog box will be displayed
Select the destination table on the table name (the table in
which you want to the record)
Specify the criteria
Finally save and run your query

• Delete Query – Used to deletes a group of records from
one or more tables based on certain criteria.
Parameter Query – Is used to display the record for a
given value. To create parameter query type the prompt
message in square bracket on the criteria space of the
selected field. When you run the parameter query
created prompt message will be displayed. Then enter
the value on the prompt message box.
Crosstab Query – Is a type query operation that
summarized data from the field in one or more tables. In
order to create a crosstab query you must specify at
least 3 fields.
The row heading field
The column heading field
The summary value field

Make-Table Query – Used to create a new table
from an existing one or more tables based on
certain criteria.
To create Make-Table query;
Start anew query using design view
Add the necessary table and fields
Select Make-Table query from query type list
The Make Table dialog box will be displayed
Give the new table name - Ok
Finally save and run your query

• LO4:- Create a Form
 To create a form using the assistance of the wizard,
follow these steps:
 Click the Create form by using wizard option
 Double click on the form by using wizard
 From the Tables/Queries drop-down menu, select the
table or query whose datasheet the form will modify.
Then, select the fields that will be included on the form
by highlighting each one the Available Fields windows
and clicking the single right arrow button > to move the
field to the Selected Fields window. To move all of the
fields to select fields, click the double right arrow >>. If
you make a mistake and would like to remove a field or
all of the fields from the selected fields window, click
the left arrow < or left arrow << buttons.

After the proper fields have been selected, click
the Next > button to move on to the next screen.
Select the layout of form that you want and click
on Next > button
Select the style of the form that you want and
click on the Next > button
At the end type the title of the form _______
form and click on the Finish button.

• LO5: - Create a Report
• A report summarizes data in a format suitable for
publishing i.e. to view data on the screen or to print it
on a printer or to publish it on the web. Reports are
used to present data in a meaningful and attractive
manner and here you can combine data, charts,
images, and even audio video.
 To create reports
Double click on the report by using wizard
Select tables or queries from Tables/Queries box from
which you want to add fields and select the fields
from Available Fields: list box and click the add
button >> and click on the Next > button.

 Click on the Add Grouping Level button ( >) to add the field
as a grouping level and click on Next > button.
 You can sort the records ascending or descending order as
you want and click on the Next > button.
 Select the Layout and Orientation of your report and click
on the Next > Button.
 Select the style of your report as you want and click on the
Next > button.
 Type the title of your report _________Report and select
the preview the report or modify the report’s design and
click on the Finish button.
Close the Report window when you have finished
viewing the report.
Close the Access window.

Gaaffii…???

OPERATE DATABASE APPLICATION

Recommended

More Related Content

What's hot (20)

Similar to OPERATE DATABASE APPLICATION (20)

Recently uploaded (20)

OPERATE DATABASE APPLICATION