UNIT-IV

4.1 MS-ACCESS: DATABASE OVERVIEW

A database is a set of information related to a specific application. A systematic
organization of data is called database. Fields and records are used to organize
databases.
 A record is a collection of fields or all the information about one item.
 A field is a category of information.
 A flat file database is a database programs that can only accommodate databases
consisting of a single table.
Database File: This is your main file that encompasses the entire database
and that is saved to your hard-drive or floppy disk.
Example  StudentDatabase.mdb
Table: A table is a collection of data about a specific topic. There can be
multiple tables in a database.
Example  Students
Example  Teachers
Field: Fields are the different categories within a Table. Tables usually
contain multiple fields.
Example 1) LastName
Example 2) FirstName
Datatypes: Datatypes are the properties of each field. A field has only 1
datatype.
FieldName  LastName, Datatype  Text
Creating table through the table wizard is much faster and easier than through
the design mode. Once you have created the table then use the form’s wizard to create
user friendly and layout for data entry. It also ensures that the user inputs only the
right kind of information and both, a data entry error as well as typing work is
minimized.
Once the forms have been created and relevant data has been entered, then use
the report wizard to generate any kind of report. It helps to organize and present data
in a more meaningful manner and also use various standard functions like subtotals,
total, sorting to summarize data.
Microsoft Access is a relational database management system through which you
can have multiple tables, all linked to each other through a common field, each table
containing a specific type of information.
4.1.1 Table.
 A table is a collection of records. A table is the storage entity for a
database.
 It is made up of records that contain data about a single thing such as
person or a sales transaction.
 The heart of each database is it is tables.
 A table is a lot like a spreadsheet that data is arranged in column and
rows.
4.1.2 Various Objects Required For A Database
There are seven objects in Microsoft Access database. All these objects for a given
database are saved in a single access database file with an extension name “.mdb”.
These objects are:
 Tables
 Query
 Forms
 Reports
 Data Access Page
 Macros
 Module
Tables
A table is a fundamental building block of an access database. All tables must
have at least one table because this is where the data is stored. Tables are grids of rows
and columns. Each column in a table is called a field. Each field contains a specific type
of information. Ex: Fname, Lname, email etc.
Query
A query is a filter through which data is evaluated. There are three types of
queries:
 Select query  displays a subset of the entire data, sorted and selected using the
criteria specified.
 Action Queries  It helps to create new tables or delete, append and update
records in existing tables.
 Cross Tab Queries  It displays spreadsheet like cross-tabular result forms.
Forms
Forms are used to simplify data entry work to display information in a specific
manner. It lets to add, modify and delete database data. Six different types of forms:
Types of Forms Purpose
Columnar Fields arranged as column
Tabular Displays data in row and column format similar to a datasheet
Datasheet Identical to table datasheet
Main/Subform Displays both main and related table data
Chart Displays information with a graph
Pivot Table
Creates a Pivot Table Form similar to an Excel Pivot Table Report
Form

Reports
It 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. It is also used to present
data in a meaningful and attractive manner and here we can combine data, charts and
images, even audio and video. There are six ways to create reports in Access.

Reports Purpose
Design View Begins with a blank page.
 Choice of fields, sort criteria and report layout used.
Report Wizard
Creates tabular or columnar report.
Columnar Auto One or more records displayed vertically. Selected from a
Report single table or query.
Tabular Auto Rows and columns grouped by field values. Selected from
Report a single table or query.
Leads us through creation of a report containing a chart
Chart Wizard
and associated data.
Label Wizard Helps you create mailing labels from data.

Data Access Page

It is a web HTML page created in Access that allows users to view, add or
edit data stored in an Access database. We can create interactive forms for users on a
website. Unlike other Microsoft Access objects, Data Access Pages are stored outside the
Access database file.
Macros
It is used to automate repetitive tasks. It is attached to buttons that appear on
forms, to make data entry work. Basically any operation that we do over and over
again by creating a macro, so that at a click of a button or by pressing a particular key
an entire sequence of commands can be executed.
Modules
It helps to develop a full fledged software application which automates a wide
variety of complex tasks. We should create a Visual Basic for Application (VBA)
module. For larger and complex projects VBA gives considerable flexibility, control,
power and ease of maintenance that is just not possible by writing simple macros.

4.1.3 Parts Of An Access Window

An active window has:
 Title bar
  Menu bar
 Standard toolbar
 Main Switchboard
 Title bar: It helps to know which application is currently running
and which slide is currently open.
 Menu bar: This is the drop down menu. Menus can be activated by
mouse click or by pressing the access keys from the keyboard. Click
any menu title once with the mouse; the menu will open,
displaying all the commands under this menu.
Ex: ALT + F  File Menu
 Standard toolbar: This Toolbars contain buttons, drop down
menus and other controls that helps to alter the appearance and
arrangements by executing a variety of access commands. The
standard toolbar contains icons for basic functions like opening,
new files, printing files, sending and receiving e-mails, address
book, etc
 Main switchboard: When we open an access database, we can see
a screen labeled main switchboard that provides buttons to enable
to use the database easily
4.2 CREATING A DATABASE
4.2.1 Starting Microsoft Access
Click on Microsoft Access button from the Microsoft shortcut bar. (OR)
Click Start menu  Programs  Microsoft Office  Microsoft Access

4.2.2 Creating a new database

A well _designed database ensures convenient and fast access information that is
needed. While planning for creating a new database we must follow the points.
  Define the purpose of the database: Consider the present and future question
that is needed to answer from the stored data.
E.g.: If we want to run a query on a customer’s database to generate state
wise, city wise lists, create two separate fields for city and state instead of one address
field.
 Determine the tables: Each table contains information only about one subject
like customers, suppliers or items.
 Determine the fields: It contains a specific kind of information such as first
name, last name or account balance.
 Identify unique fields: Values that allows access to relate and store information
contained in different tables. These unique fields are also called the primary key
for the table.
E.g.: if two tables containing employees background information and if
we want the current month’s salary then it may be linked using primary field called
employee code.
 Determine the relationships between tables: A relationship works by matching
the data in two different tables using a common primary field. There are two
kinds of relationships.
 One to one
 One to many
When we open Microsoft Access the following window appear.
1. Click on Blank Access Database radio button.
2. Click on OK button to continue
To give name for Access database
3. Click in the Save in box and choose the folder where you want to save your file
in.
4. Give the name of the database in this box.
5. Click on Create button to create a new database.
4.2.3 Using The Database Wizard
Main switchboard provides buttons to enable to use the database easily.
  Click on the Table tab to create a new database table.
 Double click on the ‘Create table by using wizard’ to create a table by
using the table wizard.
4.2.4 Creating a Database through Table Wizard
 Click on “Blank Access database” radio button and then click “ok” to continue.
Now give the name of the access database.
 Save the file by clicking the save option. A dialog box will appear
 Create a new table by clicking on “table” tab and create a table using the table
wizard by double clicking the “Create table by using wizard”. It is much faster
and easier than creating through the design mode.
 Now, click on the “Business” radio button and choose the “students” from the
sample table by clicking it. To “Add fields “ into the table click on the fields in
the sample fields.
 Click on ‘>’ button to add the fields in the “fields in my new table” category.
 To take all the fields from the sample fields category to the new table then click
on the “>>” button.
 To remove the fields one by one, click on “<” button.
 To remove all the fields click on “<<” button.
 Click the “Next” button.
 Give the name of the table in the box provide there.
 Then set the primary key for the database by clicking the radio button provided
from primary key settings.
 Click on Next button to continue.
 Click on Finish button.
 Click on Close button to close the students.

4.2.5 Working With Record

Creating a new Table:
 Double click on ‘Create Table by entering data’ to create another table.
Rename columns:
 Choose Rename column command from format menu to rename the column
heading. We would also double click on the columns heading to change the heading.
Saving the database:
 Choose save command from File menu.
 Give the name of the new table in this box.
 Click on OK button to define a primary key for our database.
 We could still enter records in this database or close it now. Click on this close
button to close this database.
Relationships:
A relationship works by matching the data in two different tables using a
common primary field. There are broadly two kinds of relationships one-to-one and
one-to-many.
 Choose Relationships command from Tools Menu.
 Click on the StudentID field in the Stud table
Keep the mouse pressed and drag the mouse pointer on top of the ID field in the
students table. Now leave the mouse.
 Click on Create button to create the relationship between the two tables.
 Click on the close button to close the dialog box.
4.3 MODIFYING TABLES
4.3.1 Creating Table through design view
First create database structure – identifying all fields that are required with the
type of data (numbers, alphanumeric, data) and the maximum width of each field.
 Choose New command from File menu.
 Select the database icon by clicking on it.
 Click on OK button to continue.
 Give the name of the folder to save the database.
 Give the name of the database.
 Click on create button to create the new database.
4.3.2 Table creation
Double click on the ‘Create table in Design view option’.
  When we click in the data type column a drop down list
automatically appears. Choose the type from the list given here.
The emp code has been taken as auto number, fname as text type and so on.
 Click on save icon from standard toolbar to save the format of the table.
 Click here and give the name for the Table.
 Click on OK button to save the table.
 Click on Yes button to Access define the primary key.
 Click on Close button to close the table.
4.4 CREATING FORMS
Forms are used to simplify the data entry work or to display information in a specific
manner. It lets us to add, modify, and delete database data. In access we can create six
different types of forms.
Types Purpose
Columnar - Fields arranged as column.
Tabular - Displays data in row and column
format
similar to data sheet.
Datasheet - Identical to table datasheet.
Main/ subform - Displays both main and related table
data.
Chart - Displays information with a graph.
Pivot table form - Creates a Pivot table form similar to an
excel pivot table report.
4.4.1 Adding fields, deleting fields, fields:
1. Click on “Forms tab” from the shortcut bar.
2. Double click on “Create form by using wizard” option.
3. Choose “students ” table from the “Tables/ queries” option by clicking on it.
4. Click on >> this button to take all the fields of students table to the selected
category.
5. Click on the layout that you like for your form.
 6. Click on the style of form that you want.
7. Click on Next button to continue.
8. Give the name of the form in this box.
9. Click on this radio button to enter data directly in the form.
10. Click on Finish button.
11. After entering all the records, click on close button to close and exit from the
form database.
12. Double clicking on “students query” that had created in the query tab.
13. Click on Yes button to confirm the action.
14. Now double click on the “students” table by going on the tables tab.
QUERY
A query is a filter through which data is evaluated. Three types of queries are:
a) Select query -- which displays a subset of the entire data, sorted
and selected using the criteria you specified.
b) Action queries – These queries create new tables or delete,
append and update records in existing tables.
c) Cross Tab queries – These queries display spreadsheet like cross
tabular result forms.
4.4.2 Various Steps To Create Queries
1. Click on “Queries” tab from the shortcut bar.
2. Double click on “Create query in design view” option.
3. Click on “students” option from the table category.
4. Click on Add button to add the table in the query.
5. Click on Close button.
6. Choose “Update query” from “Query” menu
7. Click in the “Update to “column.
8. Click on the “Build icon” from the formatting toolbar.
9. Double Click on the Tables option given.
10. Click once on students option given here.
11. Double click on the Basic option.
 12. Choose the function from the function bar.
13. Click on OK button to continue.
14. Click on Close button to close this query.
15. Click on Yes button to save this query.
16. Give the name “myquery” of the query in the query Name box.
17. Go to the table again by clicking the “tables” option and enter into the field
“sdv” option given there. Fill up the formulas for the rest of the field.
18. Close the query by clicking the “close” button. A dialog box appears whether to
save the query or not.
19. Save the query by clicking on “yes” button and give the name of the query that is
created in the query name box.
4.5 REPORTS
A report summarizes data in a format suitable for publishing, it to view data on
the screen or to print it in a printer or to publish it on the web. Reports are used to
present data in a meaningful and attractive manner and we can combine data, charts
and images, even audio and video. There are six ways to create reports in access.
4.5.1 Reports and their purpose

 Design view Begin with a blank page.

 Report wizard Choice of fields, sort criteria and report layout used. Creates
tabular or columnar report.
 Columnar Auto report  One or more records displayed vertically. Selected
from a single table or query.
 Tabular Auto format Rows and columns grouped by field values. Selected
from a single table or query.
 Chart wizard Leads to a creation of a report containing a chart and
associated data.
 Label wizard Helps to create mailing labels from data.

4.5.2 Creating Reports

1. Click on the “Reports” tab.
2. Double click on the option “Create reports by using wizard “.
3. Choose the “students” table from the “Tables/query “option.
4. Click on the button present there to select the fields in the report. Click on
a field then click on >> this button to move the field into the report.
5. Specify the grouping level by clicking on the fields and then clicking on
‘>’ button.
6. Continue by clicking the “Next” button and specify the sorting sequence
in the records.
7. Click on the field on the basis we like to sort and continue by clicking the
“Next” button.
8. Click on the “Columnar” radio button from the layout option to choose
the layout of the report created. Then continue by clicking the “Next”
button.
 9. Modify the style of the report by clicking the “corporate” option or any
other option available there. Continue by clicking the “Next” button.

10. Give the name of the report and click “Finish” button.
4.6 MAILING LABELS
There is another way to create a report is mailing labels. It is used to create
mailing labels from data. This wizard creates a report format for printing on labels.
Steps to create label wizard:
1. Open the desired database.
2. Create the table or query where the objects data comes from: Table Name
(Employees) and Press OK button.
3. Start a new report with the “New report” toolbar button shown in left.
4. Click on Label wizard and click Ok.
5. First select the kind of label we would like to produce and click Next.
6. In the next dialog box we have to specify font and size of text.
7. As the next step select the prototype label and click Next button
8. Then choose the field name which you want to sort and press Next button.
Select the fields that will appear in our labels and adds text or punctuation

in the dialog box.

9. To add punctuation, press the desired character keys (comma, period and
so on)
10. To add repeating text. Click Next button.
11. It will ask to enter field for sorting.
12. Click finish when we are ready to preview our new label layout.