MICROSOFT ACCESS

 Databases Basics
 Introduction to Microsoft Access
 Database Basics
A database is a collection of information organized in
orderly manner.
Examples are
 Maintaining Employee Data.
 Maintaining Students Information
 Advantages of using database
 1.Allow the user to add, delete or modify records more
easily
 2.It is easy to search and retrieve records
 3.Controls data duplication or redundancy
 4.It is aesy to generate reports that can be used in decision
making
 Structure of the Database .
Information within the database is separated into subjects.
Within the subject, information is arranged in Fields and
Records
What is a Field?
is a single item of information or data. For example, in a registration
form Name will be one field.
What is a Record?
is a collection of fields that are related to one set of data. For
example, in a registration form, all the data relating to “your name” will
make up one record.
What is a subject?
is a collection of records that are related to one set of information. For
example, Records for all employees will make up one subject
Manual Database Systems.

User

File Keeper

Files Room
 Computer Database systems.

With Computer Database systems, you can manage all

your information from a single database file.
Within the file you can : -
 divide your data into separate storage containers

called tables
 view, add, and update table data using forms
 find and retrieve just the data you want using queries
 analyze or print data in a specific layout using reports.
 How it Look like
 Example of data management using Database management
system
 Database in design stage
The database is created after analyzing the information
needs of an organization through identification of:
 Entities
Is a distinct subject or category in the organization that is
to be represented in the database e.g Customers. This will
determine the number of Tables in your database.
 Attributes
Is a specific piece of information about a subject, such as
the address for the customer. Attributes will determine
the number of fields in a table.
 Relationships
Is an association between several entities
 Database Management System{DBMS}

 Isa software system that enables users to

define, create and maintain the database.
The popular DBMS are:
• Microsoft Access

• Oracle
• Microsoft SQL Server.
• dBase IV
• Paradox etc.
 Microsoft Access
 Is a Powerful, flexible and easy to use DBMS
 In Microsoft Access, you will work with one Database at
a time.
 Your working Environment in Microsoft Access is the
DATABASE WINDOW

The Database Window is made up of different Sections

listed below:
• Tables
• Queries
• Forms
• Reports
• Macros
• Modules

These are objects that make up the database File.

 Microsoft Access Database objects
 Tables - Allows the user to organise data into rows and columns.
Rows represent records while columns are field
 Queries – Used to analyze or request for specific records from a
database.
 Forms – This is a visual display that resembles an ordinary form
used to enter or view records from a table.
 Reports – Used to generate reports or summaries from an
underlying table or query.
 Macros – used to automate some operations such as displaying a
startup form , when you start records Access
 Modules – This is a Visual Basic programming environment that
consists of a collection of declarations and procedures used to
automate other database objects.
 Pages – Data access pages are used to create web –based
databases.
 Running Microsoft Access
 Click Start button – point to Programs then
click Microsoft Access.
 Shortcut: double click Microsoft Access icon
on the desktop.
When Microsoft Access first starts up, a dialog
box is automatically displayed with options to
create a new database or open an existing
one.
 Creating a new Database.
When you create a Microsoft Access database, you
create one file that will contain tables, queries, forms,
reports, and other objects that make up the database.
To Create a blank database.
1. Go to File menu click New
2. In the New dialog box click General tab
3. Select Database icon, then click OK.
4. Specify a name and location for the database
and click Create.
After you create a blank database, you can start to
create database objects that will make up your database.
 TABLES
 A Table will store information about a particular
subject such as the particulars of all employees,
customers , orders etc.
 Data in a Table is placed in columns (fields) and
Rows (records).

In Microsoft Access you can create Tables in

three ways: -
• In Design view .
• By using Table Wizard.
• By entering data (Datasheet View)
 To create a Table in Design View
1. In the Database window, click Tables section
2. Click new button.
3. In the new Table dialog box, choose Design view then click
OK
Microsoft Access will display the Table Window in Design
view, in which you define the fields in your table

How can you Define Fields

1. Click in the Field Name column and type the name for the
Field.
2. Set data type in the Data type column
3. Type descriptions in Description column. ( This is Optional)
4. Set the field Properties in the Field Properties section if it is
required.
5. Repeat steps 1 to 4 for each field
 Primary Key
The Primary Key is the Field that Uniquely identify each
record in the Table
Your required to set the Primary Key before saving the
Table.
Setting Primary Key
1. While the Table is in Design View, Select the field you want
to define as Primary Key
2. Go to Edit Menu, Click Primary Key.
To save the Table
1. Go to File Menu Click Save.
Microsoft Access will prompts you to name the Table
2. Type the name for the Table and then Click OK
3. Go to View Menu, Click Datasheet View to open Table in
Datasheet mode.
You can now fill records to the Table.
 To add the Field to the Table
1. Go to View Menu click Design View to open
the Table in the design View
2. Click the row below where you want to add
a field and then Go to Insert Menu click
Rows. To add the field to the end of the
Table,click on the first blank row
3. Define the field by entering a field name, a
data type and field properties.
4. Save the changes and open the Table in
Datasheet View.
DESIGNING A TABLE STRUCTURE
 To design a table:
 1. Open an existing database
 2.Click the tables button, New and then Design
 3.Enter fields and their data types.Field names must be
unique and composed of up to 64 alphanumeric characters.
 4.Determine the data type of each field as either text,
memo, number,date/time,accuracy,autonumber,yes/no, OLE
or lookup
 5. Set the properties of each field such as size,format,
validation and caption
 6.Set the primary key then save the table
INDEXES AND PRIMARY KEY

An index is a key(s) used to speed up searching and sorting

records in a table.
Primary key is a field that uniquely identifies each record
stored in the table.A primary key prevents the user from
making null or double entries into a table.
To set a primary key:
1. Open a table in design view
2. Select the field or fields you want to define as primary key
3. Click Primary key on the toolbar.
Cont:
 To set an index:
1.Open a table in design view
2.Click the field in which you want to create an
index
3. In the indexed property box, click
Yes(duplicate ok) or Yes (no duplicates)
REVIEW EXERCISE
 1.Theprogram that sets up and manages electronic databases
is referred to as a
 (a) Database management system (DBMS)
 (b) Data manipulation system (DMS)
 (c) Record management system (RMS)
 2. The type of database model that stores data in tables is
referred to as
 (a) Object oriented database model
 (b) Table – related model
 (c) Relational database model
Cont:
 3.The technical name for a database table is__ while that of
a field is ___ respectively.
 (a) Relation,tuple
 (b)Tuple, attribute
 (c)Relation.attribute
 4.Two of the following are database objects except one:
 (a)Macros
 (b)Queries
 (c)relationships
Cont:
 5.A database table has all the following except one
 (a) Field (b) Record (c) Criteria
 6.The _ field type accepts both numeric and alphabetical entries.
 7.A_ is an indexed field in a table that does not accept duplicate
values.
 8.An _ field data type generates automatic numbers that
increase by 1 for each new record.
 9. An object is a dynamic entity generated from a class and it
has _ and _
 10.Two examples of database management software are_ and
___
Cont:
 11.Define the following terms
 (a)Database (b) Database management system(c) Object
 12.using illustrations,explain five database models
 13.State five reasons why it is preferable to store data on an
electronic database over the manual file cabinet
 14.Identify seven objects that are used to create a Microsoft
Access database.
 15. Differentiate between the following
 (a) A primary key and an index
 (b) Text and memo data type
 (c) Datasheet view and design view
QUERY

 A query is statement used to extract ,change, analyse or

request for specific data from one or more tables.In access
you can create two types of queries namely:
 1. Select query – Used for searching and analysing data in
one or more tables
 2. Action query – Used for modifying data in one or more
tables
 QUERIES

What does a query do?

 Retrieves data that is stored in your table.

 Allows you to ask questions about your data.
 Allows you to see the data you want
 Allows you to order and organize your data

E.g. Orders placed by a particular customer, Total sales

for one month
CREATING A SELECT QUERY

 To create a query in design view:

 1.Click the Query button, New and then Design view.
 2.Select tables(s) or query then click Add
 3.Drag the fields you want to add to the grid
 4.Specify the sort order and criteria. Select show for fields
you wish to be displayed
 5.Click the Run button on the toolbar to preview
 6.Save the query
To create a simple selected query in Design View
1. In database Window, click Queries object button.
2. Click New button
3. In the New query dialog box select Design View and
then click OK.
4. In show Table Dialog box, click Tables tab
5. Select the Table you want to query and then click Add
button.
6. Close the Show Table dialog box
7. Add fields to the query by dragging the field names
from the field list to the design.
8. Refine your query by entering criteria, sort order etc.
9. Save and Name the query.
10. To see the query results, Click View Menu and then
click Datasheet View.
 Setting Criteria.
We use criteria to retrieve only the records we want to see.
Common criteria
a) The value in the field is equal to the value you specify; just
type the value in field’s criteria cell.
b) The value falls within the range of values; in this case, enter
expression that defines the range you want. You can use the
following operators:-
i. = exactly equal
ii. < > Not equal
iii. <= Less than or equal
iv. >= Greater than or equal
v. Between X And Y.
c) The value begins with the value you set; in this case use
wildcards, i.e. *, ? , # etc
d) The value is the one in the list of values; in this case use In( )
function. E.g. to see customers who are located in Mbeya
and Mwanza , Type In(Mbeya,Mwanza)
 Creating a calculated field in a query
You can create fields in a query by using an expression to
calculate values.
Mathematical operators which can be used in expression are: -
+ For addition
– For Subtraction
* For Multiplication
/ For division
You can perform calculations on numeric, currency and
Date/Time data types.
You must use square brackets   around the field names used in
calculations.
You must use colon (:) to separate field name from actual
calculation. For example, Discount:[Unit Price]*0.05
1. Type field Name ( your Title) followed by the colon (:) and then
type the full mathematical expression.
2. Save the query and open it in Datasheet view to see the results.
FORMS

 A form is a visual object that enables the user

easily enter or display data from a table or
query.
 Creating a form using wizard
Forms are designed by placing controls on the
form design grid.
To create a form using form wizard

 1. open the database

 2.click the form button then New
 3.In the New form dialog box ,click form wizard.
 4.Click the table or query that contains the data, and then
click OK.
 5.The form wizard guides you through the rest of the
process
CREATING FORM IN DESIGN VIEW

 To create a form o the design grid.

 1.Click the form button then New.Select Design view
 2.Select a table or query in the list box at the bottom then click
OK.The form design grid is displayed
 3.If page and report headers are not displayed, on the View
menu,click page Header/Footer and Form Header/Footer
 4.Drag each field arranging and resizing them on the form
grid.
 5.Click the form view button to see the form layout.
 6.Save the form
 FORMS
A Form will help you to: -
 Enter, edit and View records in a custom
layout.
 Update records in the table

In Microsoft Access you can create Forms from

scratch design or quickly create it by using
Form Wizard.
 To create a Form with a wizard
1. In the Database window, click the Forms object
tab.
2. Click New.
3. In the New Form dialog box, click the Form
wizard.
4. Select the name of the table or query that includes
the data you want to base your Form on.
5. Click OK.
6. By using Next button, Follow the steps of the
wizard and provide the necessary information in
each step.
7. At the last step click Finish button, and modify
your Form to fit your need.
 REPORTS
 A Report is an effective way to present the records in printed
format

 All the Data in a report comes from the Tables in your Database

 Any other data, such as Titles or Dates are placed using the Report
Design
 Reports are created in the same way as Form, so
you can create it: -
 From scratch design
 Using Report Wizard.
 By Saving the Form as Report
 Defining relationships.
Once you have created the tables that make up the database, it is
a good idea to define relationships between the tables. Telling
Microsoft Access how data is related between tables makes it
easier to create queries, forms, and reports that include multiple
tables. 
To define relationships between tables: -
1. Close any table you have open. You can't create relationships
between open tables.
2. Go to Tools menu Click Relationships.
3. Double-click the names of the tables you want to relate, and then
close the Add Tables/Queries dialog box.
4. Drag the field that you want to relate from one table to the related
field in the other table. 
5. Click the Create button to create the relationship.
6. Repeat steps 4 to 5 for each pair of tables you want to relate.
7. Save and close the relationship window
 To edit or delete an existing relationship

1. Close any table you have open. You can't modify

relationships between open tables.
2. Go to Tools menu Click Relationships.
3. Double-click the relationship line for the
relationship you want to edit.
4. Make your changes, then Click OK.
5. Close the relationship window.
To delete a relationship, click the line that connect
the two tables, then press Delete key