MS Access
What is MS Access
A component of MS Office package.
A relational database management system.
It allows users to manipulate large amounts of
information & retrieve any part of information.
It uses relational database concepts and data are
stored in the table.
Objects in MS Access
Tables:- a collection of data about a specific topic
that is stored in rows & columns or in relational
database a table is a data structure that is organized
using a structure of columns & rows.
Forms:- a form is a type of a database object that is
primarily used to enter or display data in a database.
Continued
Queries:- Queries are used to view, change, and
analyze data in different ways. They can also be used
as a source of records for forms, reports, and data
access pages.
Reports:- A report is an effective way to present
your data in a printed format means reports are
output. Anything we decide to print deserves a
report. It may be a list of names & addresses.
DATABASE FILE
An Access database file contains several different
types of database objects:
Saved queries for organizing data.
Forms for interacting with the data on screen
Reports for printing results.
All these database objects are stored in a single file
named <filename.mdb>
CREATING A DATABASE FILE
Step 1-Click
on the Office
button.
Step 2Select New
Option
Step 3Browse the
appropriate
location to
save database
Step 4-Once the
database is created
then basic objects
like Tables to
contain the data and
other objects like
Query, Form and
Report, etc., may be
created.
CREATION OF
TABLES
Create table by using Table wizard
Step 1-Click on office button.
Step 2-Then click on open button and open a
new database.
Step 3-Under the create tab in the tables group select
desired table template.
CREATE TABLE BY
DESIGN VIEW
A table can be created under full user
control, i.e., user can provide its own
field name and other attributes this is
accomplished by design view.
HOW TO CREATE TABLE
USING DESIGN VIEW
Step 1
Click on the NEW on the database
window toolbar.
Step 2
Then click on the CREATE ,new database file
will be created.
Step 3
then click on the create option & choose the
option TABLE DESIGN.
Step 4
(i) open the table in design view.
(ii) To insert field within table.
(iii) Click in the Field Name column and
type a unique name for the field.
(iv) Click in the Data Type column and
select the data type you want.
(v) Click in the Description column and
type a description of the information this
field will contain.
CREATE TABLE BY
ENTERING DATA
A simpler approach of creating table is using
datasheet which is a sheet with columns and rows.
HOW TO CREATE TABLE BY ENTERING DATA
Step 1
click on the NEW option on the
Database window toolbar.
Step 2
Click on the CREATE option, then new database file
will be created.
Step 3
Double click on Datasheet View A blank datasheet
is displayed.
Step 4
Rename each column you will use, then double click
the column- name, type a name.
Step 5
You can insert additional column by clicking
in the column to right of where you want to
insert a new column.
Step 6
Enter your data in datasheet.
QUERIES
Queries are used, change, and analyze data in different
ways. Queries may also used as source of records for
forms and reports.
Method to Create Queries
Design
Wizard
Step 1-Start Microsoft Access and open your
database.
Step 2-Click on the Queries tab of your
database
Step 3-Click on the New button and then select
Design View to start creating a query in Design view.
Step 4-Choose the tables or other queries you wish
to query.
Step 5-Choose the fields from the table/query you want.
Step 6-Run the query to make sure your query contains the results are you are looking
for.
STEP 7-Now, you need to change the type of query this is.
STEP 8-Change to Make-Table
STEP 9 -Specify the name of the new table and if it is going to be
created in the database you are currently working from, or another
database.
STEP 10-Run the query
STEP 11 -Save your query, and you're done.
Creating Queries using wizard.
As the wizard is used in other option of the access,
similarly it is used to create queries. The following are
the step to create queries using wizard.
Step 1- Click on the Create tab above the ribbon and
then click on Query Wizard
Step 2-In the New Query box, be sure that
Simple Query Wizard is selected and click Ok.
Step 3- In the Simple Query wizard box, select the
fields from each table needed to create the chosen
query.
STEP 4-Select the Pets table in the dropdown list.
Highlight the field Pet Name in the Available Fields
list
Click the single caret to send the field to the Selected
Fields list.
The wizard should now look like this:
STEP 5 -CLICK NEXT
STEP6-Change the name of the query to that given in
the Query building information for wizard document.
Leave Open the query to view information selected.
Click Finish.
STEP7-The query will then display in Datasheet view.
What is in an MS-Access file
VB + Macros Event Driven Automation, etc.
Forms (Active)
Reports (Static)
Queries
Tables
Demographics
Ethnicity
Labs
H&P
PA Harris, Vanderbilt University
Advanced Splitting
Front-End File - Contains all Application Entities (Forms, Queries, etc.)
and links to data tables in back-end file. Note you may have more than one
FE to accommodate different user types.
VB + Macros Event Driven Automation, etc.
Forms (Active)
Reports (Static)
Queries
Tables
Demographics Ethnicity
Labs
H&P
Back-End File - Contains all Data Tables
PA Harris, Vanderbilt University
Group and sort data
One of the more
powerful things you
can do in a report is
group and sort your
data.
For example, if you
Grouping and sorting data in a report.
Create reports for a new database
want to know which
supplier provided a
given set of
computers, then
grouping your assets
by supplier can give
you that information
quickly and easily.
Group and sort data
You can group
tabular or stacked
reports.
Open your report in
Layout view, and on
the Design tab, in
the Grouping &
Totals group, click
Group & Sort. The
Group, Sort, and
Total pane appears
below
your report.
In the pane,
click Add
Grouping and sorting data in a report.
Create reports for a new database
a group, and then
select the field by
which you want to
group your data.
Access groups your
data to reflect your
choice.
Group and sort data
If you want to sort
your data, click Add
a sort, select a field,
and again Layout
view shows you your
changes.
You can add 10
grouping levels to a
report, and you can
sort each level, if you
need to.
Grouping and sorting data in a report.
Create reports for a new database
