Microsoft Access' XP
Ms. Access in one of the most
powerful and flexible Database Management System (DBMS). By using this
software, you can:
Ø Enter
data directly into a Database or import it from another program.
Ø Sort,
index and organize the data as per your requirement.
Ø Quickly
create reports and mailing labels, using all or part of your data.
Ø Make
customized data entry forms that simplify the way less experienced computer
users enter new information in the Database.
Ø Run
queries that extract subsets of your data based on certain conditions.
Ms.
Access is a flexible Database program because it can be used for both simple
and complex database projects. It also das a relational database feature, in
which one can define relationships between different types of information so
that you can get together.
In other words, it a system that allows
creating, designing and working with database. Ms. Access provides us with a
set of powerful tools for defining, entering, managing, manipulating and resulting
data.
What
is data base?
Database
is an organized collection of data or related information that are shared and
used for multiple purposes, for ex: a telephone directory, students records,
result sheets, population census etc.
The information is stored in such an
organized way that they may easy access as per our requirement. Hence a Database is an organized collection
of data in easily accessible form, which are shared and used multiple purpose
as per required.
There are two types of Database:
1. Conventional Database and
2. Computerized Database
There
is a difference between these two Databases. In Computerized Database, we can
store, find, manage, share and manipulate the data in easier way. But in case
of conventional Database, it is not possible. After entering any information in
conventional Database, we cannot change it. Telephone directory, Address Book
is the examples of conventional Database. Ms. Access is one of the examples of
computerized Database.
Database
organization in Access:
In Access, you first need to create
a Database file hold everything you create for that Database-not only the data,
also the customized forms, reports and indexes. In comparison to others, the
organization of data is easier in Access allows user to work with following
features in order to organize the data.
1. Table
2. Queries
3. Forms
4. Reports
5. Marco
6. Modules
Tables:
The heart of each Database is its tables.
Table is an element, which holds main information. A table looks like a
spreadsheet and used to store the main data. By using this table features, the
information is stored in tow ways.
a)
Row
wise and
b)
Column wise
Each column is a field
The
intersection of a row and column is a cell
- Access stores each
Database entry in its own row. The information stored in rows is refereed as
record. Each record is a collection of information.
- The
information stored along columns are called field. Each type of record heading
is kept in its own column.
Queries:
A query is a way of retrieving and editing
information in accordance to the conditions asked. Many users are afraid of
queries because of the technical terms associated with them such as values,
criteria and masks. But there is no need to be wary, you can create and use a
simple query.
Forms:
All the data you enter into your Database
ends up in the Table for storage. You can enter any information’s directly into
the table. But is a little bit difficult to do so. Most users find it easier to
create special onscreen form in which to enter the data. This special screen is
called form. Form facilitates to make modification, addition the data directly
into the table. It also helps user to view the table in user-defined manner.
Reports:
While forms are designed to be used
onscreen, reports are designed to be printed. Reports are specially formatted
collections of data, organized according to your specifications for summarizing
and printing.
Macros:
Macros are used to perform one or
more Database action automatically.
Modules:
It is another type of element in access.
It is a program you write using visual Basic (a programming language used to
create Office Software Package) used to automate and customize Database
function.
Planning your Database:
Planning is the most before creating any Database; you
have to make a plan like:
- What
data do I want to store, and what is the best way to organize it? This
determines what tables you will need.
- What
data entry actions do I perform in the Database? This determines the forms you
will need.
- What information do
I want to know about the status of the Database? This answer tells you what
reports and queries you will want.
Starting system of Ms. Access:
You can start Access in several
ways, depending on how you have installed it. However, you can follow any one
of the following process (method):
1. Click
on start button.
2. Choose
Program.
3. Choose
Microsoft Access and click on it.
Exiting
from Access:
When you finish working with Access, you
should exit it to free up your computer’s memory for other tasks. You can exit
Access in several ways.
1. Press
Alt + F4 key.
Or,
1. Go
to file menu.
2. Click
on Exit.
Or,
1. Direct
click on Close button of the program.
Creating
a New Database:
Before creating any Database, you have to
decide either you want to create a blank Database from scratch, and then
manually create all the tables, reports, and forms you will need or by using
given Database wizards. The answer depends up on you. You can create your
Database by using both options. If the Database wizards match your needs, you
can use it. It saves lots of time. It is the ready – made format, where most of
the usable items are given.
On
the other hand, if you want a special-purpose Database that is not similar to
any of the wizards, or if you are creating the Database primarily as a training
exercise for yourself, you should create a Database by using blank Database.
Creating a Blank Database:
Creating a blank Database is very simple
because you are just creating an outer shell at this point, without any tables,
forms, and others. If you just started Access and the Microsoft Access dialog
box is still displayed then:
1. Click
on Blank Database.
Then,
file new dialog box will appear.
2. Give
the required file Name.
3. Then
click on Create.
Then,
wait for just a minute, Access creates the new Database.
But
if the Microsoft Access dialog box is not appearing in the screen or if you
already close the Ms. Access dialog box then:
1. Go
to file menu.
2. Click
on new. Or, Direct click on new button on the standard Toolbar.
Then,
file new dialog box will appear.
3. Click
on Blank Database.
Then,
file New Database dialog box will appear.
4. Give
the required file name.
5. And
then click on Create.
After
a few time, you will see a Database window with Tables, Queries, Forms,
Reports, Pages, Macros and Modules options.
Understanding Data Types and Formats:
Each field must have a type, so
Access will know how to handle its contents. Here are the types you can choose
from:
Text : plain, ordinary typing, which
can include numbers, letters, and symbols. A Text field can contain up to 225 characters.
Number : A plain, ordinary number (not
currency or a date). Access will not allow any text in this field type.
Date / Time : Simply a date or a time.
Auto Number :
Access automatically fills in a consecutive number for each record.
Currency : A number formatted as an amount of money.
Memo
:
More plain ordinary text, except you don’t set a maximum field length, so you
can type an almost infinite amount of text up to 64,000 characters.
Yes
/ No :
The answer to a true/false question. It can contain one of two values: Yes or
No, True or False, on or off.
Ole object : Photo
Lookup wizards:
List or the items, which are directly accessible at the time of date entry.
For Example:
Fields Data Types
Stu_id Text
Name Text
Address Text
Training
Course Lookup Wizard
Agreement Yes/No
Fee
Paid Number/Currency
Joining
Date Date/Time
Description: Pls.
the Date/ Time according to the mm/dd/yy format
Photo OLE (Object Linking
Embedded)
Remarks Memo
Formatting a Table:
One
common problem with a table is that you can’t see the complete contents of the
fields. Fields often hold more date than the original columns with, so the date
in your table cut off. You can fix this problem in two ways: either making the
column wider, so it can display more date, or making the row taller, so it can
display more than one line of date.
Changing Column Width:
1. First
place in Datasheet view.
2.
Give
double click on the edge of each column heading.
Or,
3.
Click
on the required edge and drag it either up to right or up to left as per
requirement
Or,
4. Select
required column(s).
5. Go
to format menu.
6. Click
on Column Width.
Then,
column width dialog box will appear.
7. Click
on Best fit button to fit the column width automatically as per data entries.
Changing Row Height:
1. First
place in Datasheet view.
2.
Click
on the bottom edge of required row and drag it up to destination.
Or,
1. Select
required row(s).
2. Go
to format menu.
3. Click
on Row Height.
Then,
Row Height dialog box will appear.
4. Enter
the required height value.
5. Click
on ok.
Changing Font:
You can’t format the fields and
records separately. It you apply any effect, and then its effect will apply to
all entire entries. Font changes you make in Datasheet view will not appear in
your reports, queries, or forms; they are for Datasheet view only.
Some Formula and its syntax
used in Ms. Access:
Numeric Formula:
This formula is used for all types
of arithmetic operation like: Addition, Subtraction, Multiplication, Division
etc.
Used sign (operator) in
Numerical Formula:
1. +
(plus) = For Addition
2. - (Minus) = For
Subtraction
3. *
(Asterisk or Star Mark) = For Multiplication
4. / (Slash)
= For Division
Solve the following problem
by using Numeric formula:
1.
Calculate
the amount of each item.
Item Code
|
Particulars
|
Quantity
|
Unit
|
Rate per
|
Amount
|
01
|
Rice
|
5
|
K.g
|
50
|
|
02
|
Sugar
|
3
|
K.g
|
80
|
|
03
|
Salt
|
5
|
K.g
|
10
|
|
04
|
Tomato
|
10
|
K.g
|
20
|
|
05
|
Potato
|
15
|
K.g
|
25
|
|
Solution:
Amount:
[Rate Per unit]*[Quantity]
Make a Project using the following
criteria.
TABLE: Staff
(Design View)
Fields Data
Types
Staff
Id Text
Name Text
Address Memo
Post Lookup
Wizards (Director, Engineer……)
Salary Lookup
Wizards (Male, Female)
Bonus Currency/
Number
Tax Currency/
Number
Net
Salary Currency/
Number
2.
Calculate
Bonus, Tax and Net Salary of the personnel by using given criteria:
Staff Id
|
Name
|
Address
|
Post
|
Basic Salary
|
Bonus
|
Tax
|
Net Salary
|
PTH01
|
Sandesh
|
Ktm
|
Manager
|
9500
|
|
|
|
PTH02
|
Prem
|
Palpa
|
Accountant
|
8000
|
|
|
|
PTH03
|
Pooja
|
Pokhara
|
Engineer
|
7500
|
|
|
|
PTH04
|
Pallabi
|
Dhangadhi
|
director
|
12000
|
|
|
|
PTH05
|
Sushant
|
Attariya
|
poen
|
3975
|
|
|
|
Criteria
1. Bonus
will give 20% of salary per month.
2. Tax
will pay 10% of (basic Salary and Bonus) per Month.
Solution:
Bonus:
[Basic Salary]*20/100
Tax ([Basic Salary+
[Bonus])*10/100
Net Salary:
[Basic Salary] + [Bonus]-[Tax]
Logical Formula:
This
formula is used for Decision-Making. Various Logics are used while solving any
problems.
Uses Sing (Operators) in Logical
Formula:
1. =
(Is equal to)
2. >
(Greater than)
3. >=
(Greater than and equal to)
4. < (Less than)
5. <=
(Less than and equal to)
6. <
> (Not equal to)
Calculate
the Total marks, Result, Percentage, Division and Remarks of the following
students by using standard SLC result criteria:
Make a project using the following
criteria.
Table 1: Registration (Design
View)
Fields Data
Types
Symbol
no Text
(primary key)
Name Text
School
Name Text
Address Memo
Exam
Year Number
Date of Birth Date/ Time
Then fill some data in datasheet view
Table 2: Result (Design
View)
Fields Data
Types
Symbol
no Text
(No primary key)
Eng Number
NEP Number
Math Number
Sci Number
Total Number
Result Text
Pct Number
Div Text
Remarks Text
Sym
|
Name
|
Eng
|
Nep
|
CM
|
Sci
|
OM
|
Acc
|
H.T
|
Total
|
Result
|
Pct
|
Division
|
Remarks
|
101
|
Pinki
|
55
|
40
|
68
|
60
|
52
|
43
|
46
|
|
|
|
|
|
102
|
Devi
|
63
|
60
|
85
|
73
|
71
|
69
|
88
|
|
|
|
|
|
103
|
Pratik
|
65
|
55
|
99
|
70
|
80
|
28
|
62
|
|
|
|
|
|
104
|
Sujan
|
33
|
35
|
50
|
45
|
40
|
34
|
36
|
|
|
|
|
|
105
|
Ajay
|
64
|
45
|
65
|
63
|
50
|
50
|
42
|
|
|
|
|
|
Solution:
Total:[Eng]+[Nep]+[CM]+[Sci]+[Om]+[Acc]+[Health]
Result:
iif([Eng]>=32 and [Nep]>=32 and [CM]>=32 and [Sci]>=32 and [OM]>=32 and [Acc]>=32 and [Health]>=32,"Pass","Fail")
Percentage:
iif([Result="pass",[Total]/7,0)
Division: iif([pct]>=60,"First",
iif([pct]<60 and [pct]>=45, "Second", iif([pct]>=32 and [pct]<45,"Third","No
Div")))
Remarks: iif([Division]="First","Excellent",
iif([Division]="Second","Well", iif([Division]="Third","Good","Try
Again")))
Searching, sorting and
filtering data:
Searching. For Data (Using the find
Feature):
The find feature is useful for
locating a particular record that you have previously entered. For instance, if
you keep a database of customers, you might want to find a particular
customer's record quickly when he is ready to make a purchase, so you can
verify his address.
Sorting Data:
Even though you enter your records
into the database in some sort of logical order. But at some point, you will
want them in a different order. This Sort command is the perfect solution to
this problem. With sort, you can rearrange the records according to any field
you like. You can sort in either ascending (A to Z, 0 to 9) or descending (Z to
A, 9 to 0) order. You can sort the data as
per requirement by using Table or Form.
Follow these steps to sort records:
1. Click
any where in the field on which you want you sort.
2. Click
the sort Ascending or Sort Descending button on the toolbar. Or, Choose Sort
from the Records menu then choose Ascending or Descending.
3. To
restore the records to their presorted order, choose Remove Filter/ Sort from
the Records menu.
Filtering Data:
Filtering
is for those times when you want to get many of the records out of the way so
that you can see the few that you are interested in. filtering temporarily
narrows down the number of records that appear, according to criteria you
select.
You can apply a filter in three ways:
filter by selection (or filter excluding selection), filter by form, and
Advanced Filter/Sort. The first two are the most common for casual (informal) users,
so the following sections cover them. The third method is for advanced users
only.
Creating a
Form:
A better data-entry method is to
create a form. With a form, you can use as much space as needed for each field,
and you can enter information into several tables at once you can also avoid
the headaches that occur when you try to figure out which record you are
working with on a table; generally, each form shows only one record at a time.
There are three ways to create a
form:
1.
Auto For
a.
Auto
Form Columnar
b. Auto
Form Tabular
c.
Auto
Form Datasheet
2.
Form
Wizard
3.
Design
View (Form Scratch)
§ Auto
Forms provide very quick and simple forms that contain all the fields in a
single table.
§ The
Form Wizard helps you create a form by following a series of dialog boxes and
choosing the required fields and style for the form.
§ Creating
a form from scratch provides a layout grid on which you can place fields at
required position. It is the more difficult way, but it provides the most
controls.
Creating a form with Auto Form:
The easiest way to create a form is with Auto Form.
1. Form
the Database window, choose Forms at first.
2. Click
on New button.
Then,
New Form dialog box will appear.
3. Choose
required Auto Form style.
Auto Form: Columnar –
to create a columnar
Form
(the most popular kind). This creates a form that contains your fields in a
single column, form top to bottom.
Auto Form: Tabular –to
create a form that contains your fields in multi column, from left to right.
Auto Form: Datasheet – to
create a form with as per Datasheet View.
4.
Click
on drop-down arrow of next option and choose the table or query to use as the
source of the form’s data
5.
Click
on ok.
Then,
the ready-made form appears ready for data entry.
Creating a
Form with Form Wizard:
This Form Wizard offers a good
compromise between the automation of Auto Form and the control of creating form
scratch. Follow these steps to use the Form Wizard
1. Form
the Database window, choose Forms at first.
2. Direct
give double click on Create form by
using Wizard. Or, Choose Create form by using wizard and click on Open of
Design button.
Or,
1. Click
on New button.
2.
Direct
give double click on Create form by
using wizard. or, choose Create form by using wizard and click on open or
Design button.
Or,
1. Click
on New button.
Then,
New Form dialog box will appear.
2. Choose
Form Wizard.
3. Click
on drop- down arrow of next option and choose the table or query to use as the
source of the form's data.
4. Click
on ok.
5. Then
Form Wizard dialog box will appear.
6. Click
on drop-down arrow or Tables/Queries and choose required Table or Query name
for the fields.
7. Then,
select the required fields from required Table or Query by using > or
>> button.
8. Click
on Next button.
9. Choose
required layout either Columnar or, Tabular or, Datasheet or, Justified by
viewing its sample.
10. Click
on Next button.
11. Choose
required background style.
12. Cline
on Next button.
13.
Enter required title for the form in
the text box at the top of the dialog boxc.
14. Choose
either Open the form to view or enter information or, modify the form's design
as per requirement.
15. Click
on Finish button.
Switching between Form
view and Design view:
' Direct
click on open button for form View and Design button for Design View.
' Or,
Direct click on drop-down arrow of View icon on the toolbar and choose required
view.
' Or,
Go to View menu and choose required one.
Practical Exercise:
Create a form of Staff Query by using wizard and save
with Staff Record.
Creating a From Scratch:
The most powerful and difficult way
to create a form is with Form Design view. In this view, you already have to
decide exactly where to place each field and how to format it.
1. Choose
Forms from Database window.
2. Click
on New button.
Then,
the New Form dialog box will appear.
3. Choose
Design View.
4. Select
a required table or query from the drop-down list at the button of the dialog
box.
5. Click
on ok.
Then, A Form Design
win
dow appears with fields of selected table or query, Now you are ready to
create your form.
Or,
1. Choose
Forms from Database window.
2. Double
click on Create form in design view.
3. Right
click on the Ruler as shown in below figure.
4. Click
on properties.
5. Click
on dropdown of Record Source and choose required Table or Query name.
The End
