MICROSOFT ACCESS.
Ms-Access is a Window-based program used to manage information, which is
in form of databases.
It helps in storing information
about different subjects in separate tables.
It also enables the user to add
and edit records, sort, query and also print records.
Note. Ms-Access can be installed as a stand-alone program, but it is mainly
found within the Microsoft Office suite. It is very useful for routine and
simple database management tasks.
Starting Microsoft Access.
1. Click Start, point to Programs,
then click Microsoft Access.
-OR-
Click on the Microsoft Access
icon on the Microsoft Office Shortcut
Bar, if it is displayed on the desktop.
Features (Parts)
of the Microsoft Access Window.
(1). Menu bar.
It appears
horizontally at the top of the window.
It is used to issue Ms-Access commands.
(i). Toolbars.
They
appear after the Menu bar. They contain icons (buttons).
The toolbars provide many of the tools you need to find, edit, and print
records. You can use the buttons in the toolbars to:
¨
Add or delete records.
¨
Preview and Print data.
¨
Check spellings.
¨
Cut, copy, or paste selected
text, fields, whole records, or the entire datasheet.
¨
Sort records.
¨
Find or replace values, and also
Filter records.
(ii). Scroll bars.
They
enable the user to see data not visible on the screen.
(iii). Status bar.
It is a horizontal bar at the bottom of the screen that displays
information about commands, toolbar buttons, and other properties.
COMPONENTS OF A DATABASE (Database Terminologies).
(1). Data.
The
material (stuff) that a database program stores, organizes and manages for you.
(2). Table.
v A collection of related data organized in rows and
columns.
v
A collection of data about a
specific topic.
v
A collection of records that
describe a similar data.
(3). Field.
v An element of a table that contains a specific item
of information.
v
A single unit of information
within a table.
v
The place where data is placed
within a database.
v
A collection of related
characters.
v A group of characters that form a unit of information such as Age,
Telephone number, or a Job classification.
In a
datasheet, each column represents a Field.
One field holds one piece of data. E.g., in a Student record, the possible
fields are Name, Admission number, and Age.
All the
fields for one student constitute a Record.
(a). Field Name:
v This is the name that indicates each column (field).
v
It is the column title/ heading
or a label for a field.
(b). Field
Type:
v It is the type of data stored in a field.
(4). Record.
v A group of logically related fields treated as a unit. It can be a
collection of data about a person, a place, an event, or some other item.
v A set of all the fields for one row in a table.
Each record contains fields for storing your information. For example,
Name Age Origin Sex
John 23 Machakos Male
In a
database table, each row represents a Record.
DESIGNING A DATABASE STRUCTURE.
Good database design makes the database easy to maintain. Data is stored
in tables and each table contains data about only one subject, e.g., Customers.
Before using Ms-Access to build tables, queries, forms, and other
objects, it is advisable that you first sketch out and rework the design on a
paper.
The following are some of the
basic steps in designing a database.
1.
Determine
the purpose of the database file and how it has to be used.
·
Think about the questions that you would like the
database to answer.
·
Gather the forms used to record the data.
·
Sketch out the reports you would like the database
to produce.
This helps you to determine what facts (Fields) will be stored in the database and what table each fact belongs to.
2.
Determine
the fields needed in the database.
Each field is a fact about a
particular subject. E.g., if you are designing a database that will handle your
customers, you might need to store the following facts: Company name, Address, City, State, & Phone no.
Create a
separate field for each of these facts.
When determining the fields that are needed, ensure that the following
design principles are observed:
·
Include all of the information you will need.
·
Store the information in the
smallest logical parts. fields; First
Name, Middle Name & Last Name.
E.g., a name is usually split into 2 or 3 This makes
it easy to sort the data.
·
The Fields should not be similar to each other.
E.g., in a Suppliers table,
you should not create fields such as Product1,
Product2, & Product3, because it will be difficult to find all suppliers who
provide a particular product. This
will also force you to change the design of your database if a supplier
provides more than 3 products.
In the Products table, you need only one field for Products.
3.
Determine
the type of tables needed in the database.
Each table should contain information about one subject. The list of
fields you have will enable you to know what tables you need.
E.g., if you have a HireDate
field, its subject is an employee, and so it belongs in the Employees table.
You might
have a table for Customers, a table
for Products, and a table for Orders.
4.
Identify
the field or fields with unique values in each record.
In order for Ms-Access to connect information stored in separate tables;
for instance, to connect a customer with all the customer's orders, each table
in your database must include a Primary
key (a field or set of fields that uniquely identifies each individual
record in the table).
5.
Determine
the relationships between tables.
After dividing your information into tables and
identifying primary key fields, you need a way to tell Ms-Access how to bring
related information back together again in meaningful ways. This is done by
defining Relationships between the
tables.
6.
Refining
the design.
After designing the tables, fields, and relationships you need, study
the design and detect any mistakes that might remain. If there is any, change
your database design before entering the data.
7.
Entering
data and creating other database objects.
After
making sure that the table structures meet your needs, add your data to the
tables.
CREATING A MS-ACCESS DATABASE.
Database is a collection of data and objects, such as tables, queries, or forms,
related to a particular topic or purpose.
MS-ACCESS DATABASE FILES.
Using Ms-Access, you can manage
all your information from a single database file.
Database File:
v A collection of logically related records.
v A collection of all tables and objects used to manage data. A Database
file consists of rows and columns.
Within the file, you can use
database objects such as:
·
Tables - to store your data.
· Queries - to find and retrieve any data
you want. · Forms - to view, add, and
update data in tables.
· Reports - to analyze or print data in a specific layout.
Methods of creating an Ms-Access Database file.
(A). Create a new Blank database file.
You can create a Blank database
& then add the Tables, Forms, Reports & other objects later.
This requires the user to define each database element separately
allowing him/her to create a self-defined database.
1.
Click New
on the File menu or on the Standard toolbar (or press CTRL+N).
2.
Under New,
click Blank Database, then click the
OK button.
3.
In the File New Database dialog box, specify a name & location for the
database file, then click on the Create
button to display the Database window.
Note. All Ms-Access databases are
saved with an automatic extension of .mdb
4.
Click on the tab that has the
object you want to create, e.g., Tables,
and follow the instructions provided in the resultant dialog box.
Database window - The window that appears when you open an Ms-Access database. It
displays tabs/ buttons for the database objects, such as Tables, Queries, Forms, Reports, Macros and Modules. These shortcuts are used for
creating new database objects and opening existing objects.
(B). Create a database file using the Database Wizard.
The Database
Wizard allows the user to create in one operation the required Tables,
Forms, and Reports for the type of database you choose. The wizard provides a
set of databases where the user is free to modify them according to his/her
needs.
1.
On the File
menu, click New.
2.
Under New
from template, click General
templates.
3.
Click the Databases tab, then double-click the icon for the kind of database
you want to create, (or click the icon, then click the OK button).
4.
In the File
New Database dialog box, specify a name & location for the database.
5.
Click on the Create button, then follow the instructions in the Wizard to define
your new database.
DATABASE TABLES.
A Table is a collection of data about a specific topic.
Defining the structure of a database Table.
Tables organize data into columns
(called fields) & rows (called records).
E.g., in a Products table,
each field contains the same type of information for every product, such as the
Product's name.
Each record in that table contains all the information about one
product, such as the Product's name, supplier ID number, units in stock, and so on.
When defining a table structure,
enter the following:
(1). Field Name:
Each
column in a database table is called a Field.
Field name is the name that identifies each
column (i.e., it is the title of a field or column).
To enter names in the columns, for example, enter the First Name in its own column & Last Name in a separate column.
(2). Field Type:
This
specifies the type of data to be used/ stored in the field.
(3). Field Size:
Specifies
the maximum no. of characters that can be typed in that column.
Field width – the no. of spaces required to
hold the largest data item to be stored in the field.
FIELD DATA TYPES.
A Data type is the characteristic of a field that determines what
type of data it can store / hold.
Field Data types available in Ms-Access.
1.
Text.
A Text field can store text or combination of text & numbers, such
as names, addresses, or any numbers that do not require calculations, such as
Telephone nos., or Postal codes.
A Text
field stores up to 255 characters.
2.
Memo.
A Memo field is used to store lengthy text & numbers that are more
than 255 characters, such as notes or descriptions.
A Memo
field can store up to 65,536 characters.
3.
Number.
A Number field is used to store numeric data that would be included in
mathematical calculations.
Stores 1,
2, 4, or 8 characters.
Note. A Number field only stores Whole
numbers (i.e., numbers without decimal values) from 0 to 255.
4.
Currency.
A Currency field is used for currency values or to store any
calculations that involve money or that require a high degree of accuracy. This
prevents rounding off during calculations. Stores 8 characters.
A
Currency field is accurate to 15 digits to the left of the decimal point &
4 digits to the right.
5.
Date/Time.
A Date or Time field is used to store dates and times depending on the
formats chosen. It stores 8 characters.
6.
Yes/No.
Used for data that can be only one of two possible values, such as
Yes/No, True/False, On/Off.
Stores 1
character.
7.
AutoNumber.
AutoNumber is a field data type that automatically stores a unique number for each
record as it is added to a table.
It automatically numbers the records in the table. It is used for unique
Sequential (incrementing by 1) or Random numbers that are automatically
inserted when a record is added.
It stores
4 characters.
8.
OLE
Object.
An OLE Object field allows one to bring data & files from another
program into the current field.
Used for OLE objects (such as Ms -Word documents, Ms -Excel
spreadsheets, Pictures, or Sounds) those were created in other programs using
the OLE (Object Linking and Embedding)
protocol.
9.
Hyperlink.
A Hyperlink field links the fields to another table within the same
database or in a different database.
10. Lookup Wizard.
The Lookup Wizard is used to create a field that allows you to choose a
value from another table or from a list of values using a combo box.
Choosing data types and field sizes
The following considerations
determine the kind of data type to
use for a field:
(i). The kind
of values you want to allow in the field, e.g., you cannot store text in a
field with a Number data type.
(ii). The amount
of storage space you want to use for values in the field.
(iii). The types of operations you want to perform on the values in the field,
e.g., Ms-Access can sum values in Number or Currency fields, but not values in
Text or OLE Object fields.
Note. A field's data type defines
what kind of values you can enter in a field. E.g., if you want a field to
store numerical values or data that you can use in calculations, set the field‘s
data type to Number or Currency. A field whose data type is Text can store data consisting of
either text or number characters.
CREATING A DATABASE TABLE. Ms-Access provides 3 ways of creating a blank (empty) table;
(1). Use the Table Wizard. The
Wizard enables the user to choose the fields
for the table from a variety of predefined tables such as Business contacts,
Household inventory, or Medical records.
(2). Create a table in Design view
where you can add fields, define how each field appears or handles data, and
create a primary key.
(3). Enter data directly into a blank datasheet.
When you save the new datasheet, Ms-Access will analyze your data and
automatically assign the appropriate data
type and format for each field.
TO CREATE A TABLE USING THE DATASHEET VIEW.
Datasheet view - A window that displays data from a Table, Form, or Query, in a row-and-column
format. In Datasheet view, you can edit fields, add and delete data, and search
for data.
1.
Click New
on the File menu or on the Standard toolbar (or press CTRL+N).
2.
Under New,
click Blank Database, then click the
OK button.
3.
In the File New Database dialog box, specify a name & location for the
database file, then click on the Create
button to display the Database window.
4.
Under Objects,
click Tables, then click New on the Database window toolbar.
5.
Double-click Datasheet view (or click on Datasheet
view, then choose the OK
button). A blank Datasheet with rows and columns is displayed.
6.
Enter the Field names.
To rename each field/column.
Double-click the column name.
-OR-
Click in the column, then choose Rename
Column on the Format menu. Type
the Field name, then press the ENTER key.
7.
Click in the cells and enter your data. Press the TAB key to go to the next field or
record.
8.
After adding data to all the columns you want to
use, save the Datasheet table.
9.
If you do not set a Primary key
before saving the newly created table, Ms-Access will ask if you want it to
create a primary key for you. If you answer Yes, Ms-Access will create an AutoNumber primary key.
After saving the table, Ms-Access will assign data types to each field
based on the kind of data you have entered.
Note. Any columns you leave empty will be deleted when you save the Datasheet.
Customer Orders: Table
Order Number
|
Customer ID
|
Company Name
|
City
|
Required Date
|
11022
|
BSBE V
|
B‘s Beverages
|
London
|
25-May
|
11023
|
HANAR
|
Hanari Carnes
|
Rio De Janeiro
|
09-May
|
11024
|
EAST C
|
Eastern Connection
|
London
|
10-May
|
To add or edit data in a datasheet.
1.
To change data within a field, click in the field
you want to edit, then type the data.
To replace the entire value, move the pointer to the leftmost part of
the field until it changes into the plus
pointer, then click to select. Type the data.
Notes.
·
To correct a typing mistake,
press the BACKSPACE key.
·
To cancel your changes in the
current field, press the ESC key. To cancel your changes in the entire record,
press the ESC key again before you move out of the field.
·
When you move to another record, Ms-Access saves
your changes.
To rename a field in a table in Datasheet view.
Method 1.
1.
Double-click the
field selector of the field that you want to change.
2.
Type the new field name, then press the ENTER key
to save it.
Method 2.
1.
Click in the column that you want to change.
2.
On the Format
menu, choose Rename Column.
3.
Type the new field name, then press the ENTER key.
To change the Column Width in Datasheet view.
Method 1.
1.
Click in the column whose width you want to change.
2.
On the Format
menu, click Column Width.
3.
Type the desired width in the Field Size box, then choose the OK button. To make the column width fit
an entry, click Best fit.
Method 2.
1.
Point to the edge of the column
whose width you want to change. The pointer becomes a double-headed arrow.
2.
Drag the arrow to increase or decrease the column
width.
To change the Font, Appearance,
Font Size, Colour & Underline of text in a field or record in Datasheet
view.
Method 1.
1.
Select the text, Record or Field whose contents you
want format.
2.
On the Formatting toolbar, click the arrows to the right of the Font, Font Size, & Font Color
button, then choose the options you want, or click the Bold, Italic, Underline button.
Method 2.
1.
On the Format
menu, click Font, then choose the
options you want in the Font dialog
box.
To change the Row Height in Datasheet view.
1.
Click in the row / record whose height you want to
change.
2.
On the Format
menu, click Row Height.
3.
Type the desired height in the Row Height box, then choose the OK button.
SELECTING FIELDS & RECORDS IN A DATASHEET USING THE MOUSE.
A single field
Point to the left edge of the
field. When the pointer changes into a
Plus, click the left mouse button.
-OR-
Click in the field, then press F2.
Adjacent fields
click the left edge of a field,
then drag to extend the selection.
-OR-
Select the first field; hold down
the SHIFT key, then press the
Left or Right
arrow to extend the selection.
A column
Click the field selector.
Adjacent columns A record (row)
click the field name at the top
of the column, then drag to extend
the selection.
Click the Record selector.
-OR-
Click in the row, then click Select Record on the Edit menu.
Multiple records
click the record selector of the first record, then drag to extend the
selection.
All records
Click Select All Records
on the Edit menu (or press CTRL+A).
Field selector - A small box or bar that you click to select an
entire column in a datasheet.
Row selector - A small box or bar to the left
of a record that, when clicked, selects an entire row in table in a Datasheet
view.
To move between records by using record Navigation buttons in a
datasheet.
The navigation buttons are
located at the bottom of the window in Datasheet
or Form view.
You can use these buttons to move
quickly between records.
First
record |3
Next record 4
Previous record (1 record to the left) 3
Last
record 4|
New record 4Ã
Add a field (column) to a table in Datasheet view.
Note. A Datasheet view table consists of 30 columns. To insert an additional
column at any time in Datasheet view,
1.
Click in the column to the right of where
you want to insert a new column.
2.
On the Insert
menu, click Column.
To insert a new record (row) in Datasheet view.
1.
Click in the row below where you want to add
the new record.
2.
On the Insert
menu, click New Record, (or click New Record button on the toolbar).
Save a record in a datasheet.
Ms-Access automatically saves the record you are adding or editing as
soon as you move the insertion point to a different record, or when you close
the datasheet you are working on.
·
To save the data in a record while editing, click Save Record on the Records menu.
Delete a field from a table in Datasheet view.
1.
Select the column you want to delete.
2.
On the Edit
menu, click Delete Column.
Delete a record (row) from a table in a Datasheet.
1.
Click the row
selector of the record you want to delete.
2.
On the Edit
menu, click Delete Record (or click Delete Record on the toolbar).
Delete an item (contents of a field or record).
1.
Select the field, text, or other item that you want
to delete.
2.
Press DELETE
(or click Delete on the Edit menu).
CREATE A TABLE IN DESIGN VIEW.
Design view -A window that shows the design of the database objects: tables, queries,
forms, reports, and macros. In Design view, you can create new database objects
and modify the design of existing ones.
1.
Press F11 to switch to the Database window.
2.
Under Objects,
click Tables, then click New on the Database window toolbar.
3.
Double-click Design
View. Access takes you to the design view of your table.
4.
Define each of the fields in your table.
How?
•
Click in the Field
Name column and type a unique name for the field.
•
Click the arrow in the Data Type column, and select the data type you want.
•
In the Description column, type a description of the information the field
will contain. The description is optional.
5.
Define a primary
key field before saving your table.
Primary key - One or more fields that can be used to identify each record in a table.
A primary key is used to refer to related records in other tables (it is used
to relate a table to foreign keys in other tables).
Note. If you do not define a primary key, Ms-Access asks if you want Access
to create one for you when you save the table.
6.
To save your table, click Save on the File menu or on the toolbar, then specify a name for the table.
7.
Switch to Datasheet view, then start entering
records.
Switch between views of a table.
1.
On the View menu, click Design view
or Datasheet view (or click the View button on the Standard toolbar)
FIELD PROPERTIES.
The table‘s
Design view is divided into 2 parts. The lower part is used to display and assign
field properties to selected fields.
Field properties - a set of characteristics that provide additional control over how the
data in a field is stored, entered, or displayed.
Each data type has a different
set of properties.
The different field properties
include:
(i). FieldSize.
The FieldSize property
defines/ controls the maximum no. of characters that can be entered/ stored in
a Text or Number field.
(ii). Format.
The Format property specifies how (the way)
data is displayed in a field & printed.
Number & Currency fields provide predefined display formats. They
include Currency, Fixed, General, Percent, Scientific, General data, Medium
date, Medium time.
(iii). Decimal
places.
It is used to define the no. of decimal places in values. This option is
used on fields that already have the Fixed format such as in Number and
Currency fields.
(iv). Default
value.
It allows you to define a value that will automatically be inserted into
the field as you add new records. It is used in Text and Memo fields.
For
example, to insert the current date in the Admission date field use ―Date ( )‖.
The
default value affects only new records inserted.
(v). Validation
Rule.
It allows you to create an expression or a formula that will test data
when being entered into the field.
It automatically rejects faulty/flawed entries, e.g., entering very
large or very small figures in the Age or Salary fields.
Validation Rule
Description
Between 18 and 55
<10000
<=500 AND <=1000
>Date( )
<Date( )
Limits an age field from 18 to
55.
Allows any value less than 10000
Accepts any entry between 500 and
1000.
Allows entries whose dates are
the current date or later.
Allows the current date and
before.
Note. To test whether the Validation rule is applicable, choose Test Validation Rules on the Edit menu.
(vi). Validation
Text.
Defines
an error message that will be displayed when the validation rule is
violated/broken.
It must
be typed in earlier.
Enter a
useful message that can be clearly understood by the input clerk.
(vii). Required.
It
controls the entry of important fields.
When this option is set to YES,
an entry must be made within that field, i.e., it ensures that the field is not
left blank.
A YES
option is equivalent of typing IS NOT NULL as a field Validation
rule.
(viii). Input Mask.
Lets you
define a pre-formatted pattern for the entry of data into a Date or a Text
field.
The data
in that field must conform to a pattern.
Examples
of data include:
0#/0#/0# - this
forces a 2-digits entries for the year, month and date in a date field.
A
- Allows
letters or digits into a field, but an entry is required.
9
- Allows
letters or digits in a field, but no entry is required.
000000 - Allows a 6-digit number (not more or less than that). 999999 - Allows 6 or less digits.
AAAAA
- only 5 characters are to be entered.
>
- converts the field entry to Uppercase.
<
- converts the field entry to Lowercase.
(ix). Allow
Zero Length.
Applies
to Text and Memo field data types.
Setting the value in Allow Zero
Length property to YES and the Required property YES, the field must contain at least one character.
(x). Caption.
This is an alternative name used (other than the fieldname) in order to
appear in the fieldname header button in Table datasheet view and as a label in
Forms.
They provide a neat and descriptive name since the fieldname should be
kept small for practical use.
(xi). Indexed.
[Yes
(Duplicates OK)] – Gives sorted, indexed field and can allow data duplicates.
[Yes (No
duplicates)] – Gives sorted, indexed field but cannot allow data duplicates.
Note. It is not available for Memo or
OLE object data types.
(xii). New Values.
Applies
only to AutoNumber fields.
Ms-Access can increment the AutoNumber field by 1 for each new record, or fill in the field
with a randomly generated no., depending on the New Values property setting that you choose.
Note. To check the Field properties set for the records in a table, click Properties on the Edit menu.
Rename a field in a table in Design view.
1.
Double-click the
field name you want to change.
2.
Type the new field name.
3.
Click Save
on the toolbar or on the File menu
to save your changes.
Set or change the Primary key in Design view.
Primary key is a field that uniquely identifies each record in
a database table.
Examples of key fields include -
Identification numbers, Registration numbers, User codes, etc.
Using a Primary key is a way of
telling Ms-Access to:
v
Make sure no two records have the
same value within that field.
v
Keep records sorted by the
entries of the Primary key fields.
There are 3 kinds of primary keys
that can be defined in Ms-Access:
1.
AutoNumber
primary keys.
An AutoNumber field can be set to automatically enter a sequential no.
as each record is added to the table.
Choosing an AutoNumber field as the primary key for a table is the
simplest way to create a primary key.
2.
Single-field
primary keys.
If you have a field that contains unique values such as ID numbers, you
can select that field as the primary key. You can specify a primary key for a
field that already contains data as long as that field does not contain
duplicate values or Null values.
Null - A value you can enter in a field or use in expressions and queries to
indicate missing or unknown data. Some fields, such as primary key fields,
cannot contain Null values.
3.
Multiple-field
primary keys.
In situations where you cannot guarantee the uniqueness of any single
field, you can choose two or more fields as the primary key.
This commonly arises in situations where a table is used to relate two
other tables in a many-to-many relationship.
1.
Open a table in Design view.
2.
Select the field or fields you
want to define as the Primary key.
To select one field, click the row
selector for the desired field.
To select
multiple fields, hold down the CTRL
key, then click the row selector for each field.
3.
On the Edit menu, click Primary key.
(Or click the Primary Key button on
the toolbar). Note. A Primary key
field cannot be left empty when editing and entering records.
Once you assign a primary key for a table, Access will prevent any
duplicate or Null values from being
entered in the primary key fields.
Add a field (column) to a table in Design view.
1.
Click in the row below where you want to add the
field.
To add
the field to the end of the table, click in the first blank row.
2.
Click Insert Rows button on the toolbar (or on the Insert menu, choose Rows).
Double-click in the new column, then type a unique name for the field.
Delete a field from a table in Design view.
This permanently removes a field
plus all the data in it from the database.
1.
Select the field(s) you want to delete.
To select one field, click the field's row selector. To select a group of fields, drag through the row
selectors of the fields.
2.
On the Edit
menu, click Delete Rows (or click Delete Row on the toolbar).
Change a field's data type in Design view.
1.
Click the arrow in the Data Type column of the field you want
to change, then select the new data type.
2.
Click Save
on the toolbar. Ms-Access converts the
data type.
Caution. If the data type conversion would result in lost values, Access
displays a message telling you that errors occurred during conversion before it
actually saves the changes. Click Cancel
to cancel the changes. Click OK to
continue and save the changes.
Change the field size of a Text or Number field.
Field size controls the maximum no. of characters that can be entered into a text
field and the kind of numeric values that can be entered into a numeric field.
1.
In Design
view, click the field whose FieldSize
property you want to set.
2.
In the lower portion of the
window on the General tab, click the
FieldSize property box. For a Text field, type the maximum no. of
characters to allow in the field (up to 255). For a Number field, click the arrow and select the desired field size.
Set the data display format for a field in a table.
1.
In Design
view, click the field you want to format.
2.
In the lower portion of the
window, click the arrow next to the Format
property box, then click a format from the list.
Set the no. of decimal places to display for a field in table Design
view.
1.
In Design
view, click the field you want to define decimal places for.
2.
In the lower portion of the
window, click the arrow next to the DecimalPlaces
property box, then click the desired no. of decimal places.
CREATE A TABLE BY USING THE TABLE WIZARD.
1.
Click New
on the File menu or on the toolbar
(or press CTRL+N).
2.
Under New,
click Blank Database.
3.
In the File New Database dialog box, specify a name & location for the
database, then click Create to open
the Database window.
4.
Under Objects,
click Tables, then click New on the Database window toolbar.
5.
Double-click Table
Wizard, then follow the directions in the Table Wizard dialog boxes.
Note. If you want to modify or extend the resulting table, you can do so in Design view when you have finished
using the Table Wizard.
Open a database table.
1.
In the Database
window, under Objects, click Tables.
2.
Click the table you want to open.
To open the table in Datasheet
view, click Open. To open the
table in Design view, click Design.
Exercise (a).
1.
(a). Define
the following terms as used in Database Management systems:
(i). Table. (ii). Field.
(iii). Field Name. (iv). Field
Type.
(v). Record.
(vi). Database File.
(b). Give five different field data
types found in Microsoft Access and explain each.
2.
What are the procedures you have to follow when
designing a database?
Exercise (b).
1.
Differentiate between:
(a).
Table and Record.
(b).
Field and Field name.
2.
Create a simple record of a company, having the
following fields; Company‘s Name,
Address,
City, State, and Phone number.
3.
Create a Personal records database giving the
following details; Father‘s Name, Baptismal
Name, Last Name, Origin, Sex, Date of Birth, Age, Primary and Secondary
schools attended, College, Career and Hobbies.
Include
the relevant field data types and formats.
4.
Create a database giving the
following details; Name of company, Address, Phone number, Years of existence,
Location, Number of employees, and what it does (operations).
5.
Create a database of an employer
who decides to keep a record of all his employees, under the following lines;
Name of employee, Sex, Age, Origin, Date he/she was employed, Salary,
Department of work, Working hours for each employee, Marital status, Education
level, and ID card number.
6.
A farmer decides to keep a record
of his farm produce of a certain season. Create a database showing the
following; Name of grain, Produce in tonnes.
COPY OR MOVE AN ITEM.
1.
Select the items you want to copy or move.
To copy the item, click Copy
on the Edit menu or on the toolbar.
To move the item, click Cut on the Edit menu or on the toolbar.
2.
Click the location where you want to place the
item.
3.
Click Paste
on the Edit menu or on the toolbar.
Copying a table.
Copying can be used to reproduce
tables and/or data within tables.
1.
Select the table in Datasheet view.
2.
Click Copy
on the Edit menu or on the toolbar.
3.
Click the location where you want to place the
item.
4.
Click Paste
on the Edit menu or on the toolbar.
Access will then ask you to enter a name for the new table. It will then
ask whether you want to paste the structure and data or just to append the
database records.
Appending refers to adding records at the bottom of an existing table or file. To
append the records, choose Paste Append
on the Edit menu.
SORTING OF RECORDS.
This allows you to arrange your records in a logical order in the
database. It can either be in Ascending or Descending order – in Alphabetical,
Chronological, or Numeric.
1.
In Datasheet view, click in the field to be sorted.
Method 1.
Click Sort on the Record menu,
then click on the order to apply; Ascending or Descending.
Method 2.
On the Standard toolbar, click A-Z
button (to sort in Ascending order), or click Z-A button (to sort in Descending order).
HIDING AND UNHIDING OF COLUMNS WHEN EDITING.
Purpose.
You may want to hide a column for
3reasons;
ó Prevent
anybody from accessing the information.
ó Prevent
the data in those columns from being printed.
ó In case you want to suppress display of some columns in a database view,
i.e. reduce the size of a database table in order to view more columns at once.
To hide columns in Datasheet view.
1.
Move to the column you want to hide.
2.
On the Format
menu, click Hide column. The
selected column disappears from the screen.
To Unhide columns.
1.
On the Format menu, click Unhide
column. A list of hidden columns appears.
2.
Click to select the checkboxes of all columns that
you want displayed.
FREEZING AND UNFREEZING OF COLUMNS.
Freezing makes the selected column to remain fixed on the screen while
scrolling. This enables the user to see the data in a column especially in a
database table with very many columns.
When a
column is freezed, it stays at the left-most position while the rest of the
columns continue to move or scroll in and out of view.
Unfreeze is removing the Freeze command.
To Freeze column(s) in Datasheet view.
1.
Click on the column you want to freeze.
2.
On the Format
menu, click Freeze column.
To Unfreeze columns.
1.
Select the column to unfreeze.
2.
On the Format
menu, click Unfreeze All columns.
FILTERING OF RECORDS.
Filtering refers to sieving/ examining out particular records from the database.
The records you sieve/ select out can be printed, deleted, or edited.
There are 3 types of filters in
Access.
(i). Filter by Selection.
This filter allows you to choose a criteria for sieving out records by
selecting them directly from the table in Datasheet view.
1.
Open the table in Datasheet view.
2.
Select the cell that contains the
criteria that you need, E.g. under First
Name, select Philip.
3.
On the Records menu, click Filter,
then choose Filter By Selection (or
click Filter By Selection on the
Standard toolbar).
Access displays all the records that meet the criteria, e.g., all people
in the database whose first names is Philip.
Note. If you want to sieve in all records except the ones selected, click Filter Excluding Selection.
(ii). Filter by
Form.
This
filter opens up a Form where you can give a more elaborate criterion.
1.
Open the table in Datasheet view.
2.
On the Records menu, click Filter,
then choose Filter By Form, (or
click Filter By Form on the
toolbar).
A Form will appear where you can type in or choose the criteria you
want, e.g., To list all people called Philip, click under First Name, then type
‗Philip‘ or chose it from the drop down list.
The
following can be used;
First Name
|
Last Name
|
|
Peterson
|
Kamau
|
It looks for a person called Peterson Kamau.
|
Age
|
|
|
>18
|
|
Displays all people above 18.
|
Between 25 and 30
|
Displays records of all people aged between 25 & 30
|
Country
In (Uganda, Kenya, Tanzania) Displays people from Uganda, Tanzania or Kenya.
Date
>2004/01/31 Displays all records created on the specified date and after.
Between
93/10/31 and 95/7/1 Displays all records created
between the indicated dates.
Date of Birth
Date ( ) -30 Displays all records of people born 30 days ago.
Between Date ( ) and Date ( ) -30 Displays records of people born within the last 30 days.
Sports
Like *ball
Displays all records with all
sorts of ball games.
Not Football
Displays all records of people
who do not play football.
Is Null
Displays all records given the
award ‗Null‘.
Is Not Null
Displays all records that do not
have the award ‗Null‘.
3.
On the Records menu, click Apply
Filter/Sort, (or click the Apply
Filter button on the toolbar).
(iii). Advanced
Filter/Sort.
This
filter allows one to sort and filter at the same time.
Note. You can sort and filter at the
same time or you can perform each independently.
1.
Open the table in Datasheet view.
2.
On the Records
menu, click Filter, then choose Advanced Filter/Sort.
Access will take you to QBE grid. From this grid, you have a field list
in the top half and the grid table.
The grid table allows you to choose fields for filtering or sorting. To
insert a field(s), double-click it from the field list (or click once in the grid,
then select the field from the drop down list).
3.
When you have specified your
criteria, click Apply Filter/Sort on
the Records menu, (or click the Apply Filter button on the toolbar).
That
filter stays in effect until you delete it from the QBE grid.
Note. To remove a filter and get back the original table, click Remove Filter/Sort on the Records menu.
Advanced Filter/Sort window - A window in which you can create a filter from scratch. You enter
criteria expressions in the filter design grid to restrict the records in the
open form or datasheet to a subset of records that meet the criteria.
PRINTING.
Setting up the paper.
You can change the Page Setup for
your table to affect the way it will print.
1.
On the File
menu, choose Page Setup.
2.
From the Page Setup dialog box,
·
Click the Margins tab, to change the margins of the printing page. To include
the headings in the printed document, select the Print Headings checkbox.
·
Click the Page
tab, then change the Orientation of the
paper, and the Paper size.
Actual Printing.
Before you sent the job for printing, make sure you have set the print
options you want to apply to your print job.
1.
On the File
menu, choose Print (or press CRTL+‘P‘).
2.
From the Print dialog box,
·
Under Printer,
click the arrow next to the Name
box, then select the printer you are using.
·
Under Print range, select the specific pages to print.
To print only certain records in the datasheet, select the records, then click Selected Record(s).
·
In the Number of Copies box, select the desired copies to be printed per
page.
·
To change the Print Quality, Paper Type, Paper Size
and Orientation, click Properties.
3.
After setting the options, choose
OK to send the print job to the
printer.
RELATIONSHIPS IN A MS-ACCESS DATABASE FILE.
To store your data, create one
table for each type of information that you track.
After setting up the different tables for each subject, you can define
relationships between the tables.
Relationship - An association established between common fields
(columns) in two tables.
Defining relationships is a way of telling Ms-Access how to bring
information/ data from multiple tables back together again in a query, form, or
report.
After defining the relationship, you can create queries, forms, and
reports to display information from several tables at once.
Relating two tables.
In order to relate two tables, each table should include a field or set
of fields that uniquely identifies each record stored in the table. Such a
field is called the Primary key of
the table.
The Primary key field relates two tables so that Ms-Access can bring
together the data from the two tables for viewing, editing, or printing.
In one table, the field is a Primary
key that you set in table Design
view. That same field also exists in the related table as a Foreign key.
Suppliers: Table
Suppliers ID Company Name
1
Exotic Liquid
2
New Orleans
3
Grandma Kell
4
Tokyo Trade
Products: Table
Product Name
|
Suppliers ID
|
Units in Stock
|
Chai
|
1
|
39
|
Chang
|
1
|
17
|
Aniseed Syrup
|
1
|
13
|
Camarvon Tiger
|
2
|
53
|
A Product Key - A unique ID, such as a Customer ID, that distinguishes one record
from another within a table.
In the Suppliers table, enter
a Supplier ID, Company name, and so on, for each supplier. The Suppliers ID is the primary key.
In the Products table, you
include the Suppliers ID field, so that when you enter a new product, you can
identify its supplier by entering that supplier's unique ID number. The Suppliers ID is the foreign key in the
Products table.
Define relationships between tables.
When you create a relationship between tables, the related fields may
not have the same names, but must have the same data type.
A relationship
can be One-to-one, One-to-many, or Many-to-many.
Define a one-to-many or a one-to-one relationship.
1.
Close any tables you have opened.
You
cannot create or modify relationships between open tables.
2.
Press F11 to switch to the Database window.
3.
Click Relationships
on the Tools menu or on the Standard
toolbar.
4.
If you have not yet defined any
relationships in your database, the Show
Table dialog box is automatically displayed.
5.
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 name) called
the foreign key in the other table.
To drag
multiple fields, press the CTRL key, click each field, and then drag them.
6.
The Edit Relationships dialog box is displayed. Check the field names
displayed in the two columns to ensure they are correct. You can change them if
necessary.
7.
Set the relationship options if
necessary, then click the Create
button to create the relationship.
8.
Close the Relationships Window.
When you close the Relationships
window, Access asks if you want to save the layout. Whether you save the
layout or not, the relationships you create are saved in the database.
Relationships window - A window in which you view, create, and modify relationships between
tables and queries.
Many-to-many relationship.
This is an association between two tables in which one record in either
table can relate to many records in the other table.
To establish a many-to-many relationship, create a third table and add
the primary key fields from the other two tables to this table.
For example, an Order Details
table can relate the Orders and Products tables. Its primary key
consists of 2 fields: OrderID and ProductID. The Order Details table can
list many products and many orders, but each product can only be listed once
per order, e.g., 51 as shown below. So combining the OrderID and ProductID
fields produces an appropriate primary key.
Orders
Details: Table
Order
ID
|
Product
ID
|
10249
|
14
|
10249
|
51
|
10250
|
41
|
10250
|
51
|
10250
|
65
|
View existing relationships.
1.
Press F11 to switch to the Database window.
2.
Click Relationships
on the toolbar (or on the Tools
menu).
3.
Do one of the following:
To view all the relationships defined in the
database,
·
Click Show
All Relationships on the toolbar.
To view the relationships defined for a particular
table,
· Click the table, and then click Show Direct Relationships on the toolbar.
4.
Click Clear
Layout on the toolbar to remove all tables from the Relationships window.
5.
To add the table back, click Show Table on the toolbar, double-click
the table, and then click Close.
6.
Click Show
Direct Relationships on the toolbar.
Remove a table from the Relationships window.
·
Click the table you want to remove, then press the DELETE key.
This action affects only the display of the Relationships window. The table and relationships remain in the database.
Delete a relationship.
1.
Close any open tables.
2.
Press F11 to switch to the Database window.
3.
Click Relationships
on the Tools menu or on the toolbar.
4.
If the tables whose relationship
you want to delete are not displayed, click Show Table on the toolbar and double-click each table you want to
add. Then click Close.
5.
Click the relationship line for
the relationship you want to delete (the line will turn bold when it is
selected), then press the DELETE
key.
Exercise (a).
1.
(a). What are
Database management software?
(b). What are the advantages of storing data on the computer using a
database tool such as Microsoft Access as opposed to storing in paper files.
2.
Distinguish between the following terms in relation
to a database:
(i).
DBMS and database.
(ii). Records
and Fields. (4
marks).
3. Define the term Sorting. (2
marks).
4.
(a). Differentiate
between Primary key and Relationship.
(b). What is the importance of the
Primary key.
Exercise (b).
1.
(a). What are
Database management software?
QUERIES.
A Query is a question about
the data stored in your tables, or a request to perform an action on the data.
You use queries to view, change,
and analyze data in different ways.
A query can find & bring together data that meets conditions that
you specify from multiple tables. It can also serve as the source of data for a
Form, or a Report.
A query can also update or delete multiple records at the same time, and
perform predefined or custom calculations on your data.
There are 4 major types of
queries in Microsoft Access.
(1). SELECT
QUERIES.
A Select query is a query
that asks a question about the data stored in your tables and returns a result
set in the form of a datasheet—without changing the data.
You use a
select query to:
(i). Bring
together data from one or more than one tables by using the criteria you
specify and then display the data in the order you want.
(ii).
Update records in the datasheet of a select query (with some
restrictions).
(iii). Group records and calculate
Sums, Counts, Averages, and other types of totals.
A select
query is the most common type of query.
Creating a Select query.
You can
create a query with a wizard or from scratch in Query Design view.
In Design view, you specify
the data you want to work with by adding the tables or queries that contain the
data, and then by filling in the Design grid.
•
You add fields to the design grid by dragging them
to the field lists.
•
What you will see in the query's
results will be determined by the fields, sort order, and criteria you add to
the design grid.
(a). Create a Select query.
1.
In the Database window, click Queries
under Objects, then click New on the Database window toolbar.
2.
In the New
Query dialog box, click Design View,
then click OK.
3.
In the Show Table dialog box, click the tab that lists the tables or
queries whose data you want to work with.
4.
Double-click the name of each
object you want to add to the query, and then click Close.
5.
Add fields to the Field row in the Design grid, and if you want, specify criteria and a sort order.
6.
To view the query's results, click View on the toolbar.
Design grid: The grid that you use to design a query or filter in query Design view
or in the Advanced Filter/Sort window.
(2). PARAMETER
QUERIES
A Parameter query is a query
that when run displays its own dialog box prompting you for information, such
as criteria for retrieving records or a value you want to insert in a field.
You can design the query to prompt you for more than one piece of
information; For example,
F You can design it to prompt you for two dates. Ms-Access can then
retrieve all records that fall between those two dates.
F You can create a monthly earnings report based on a parameter query.
When you print the report, Ms-Access displays a dialog box asking for the month
that you want the report to cover. When you enter a month, Ms-Access prints the
appropriate report.
(3). CROSSTAB
QUERIES.
A Crosstab query is a
query that calculates a Sum, Average, Count, or other type of total on records,
and then groups the result by two types of information — one down the left side
of the datasheet and the other across the top.
Use
Crosstab queries to calculate and restructure your data for easy analysis.
Note. A Crosstab query displays the same information, but groups it both
horizontally and vertically making the datasheet more compact and easier to
analyze.
Creating a Crosstab query
You
create a crosstab query from scratch
in query Design view.
Create a Crosstab query.
1.
In the Database
window, click Queries, then
click New.
2.
In the New
Query dialog box, click Design View,
and then click OK.
3.
In the Show Table dialog box, click the tab that lists the tables or
queries whose data you want to work with.
4.
Double-click the name of each
object you want to add to the query, and then click Close.
5.
Add fields to the Field row in the Design grid
and specify criteria.
6.
On the toolbar, click Query Type, and then click Crosstab.
7.
In the Design grid, you specify the field's values that will become column
headings, the field's values that will become row headings, and the field's
values to sum, average, count, or calculate.
•
For the field(s) whose values you
want to appear as row headings, click the Crosstab
row, then click Row Heading.
•
For the field whose values you
want to appear as column headings, click the Crosstab row, and then click Column
Heading. You can choose Column
Heading for one field only.
You must
leave the default Group By in the Total row for these fields.
•
For the field whose values you
want to use in the cross-tabulation, click the Crosstab row, and then click Value.
Only one field can be set to Value.
8.
In the Total row for this field, click the type of aggregate function you want for the cross-tabulation (such as Sum, Avg, or Count).
Changing the Sort order of the Column headings in a
Crosstab query.
Usually, the column headings are sorted in alphabetic or numeric order.
You can set them to appear in a different order, or if you can limit which
column headings to display.
For example, in column headings containing the
months of the year, you can display the months chronologically rather than
alphabetically. Or, you can limit the columns to just January through June.
1.
Open the crosstab query in Design view.
2.
Click the background of query Design view, outside
the design grid and the field lists.
3.
On the toolbar, click Properties to display the query's property sheet.
4.
In the ColumnHeadings property box, enter the column headings you want to
display, in the order in which you want to display them. Between the column
headings, type a comma.
5.
To view the query's results, click View on the toolbar.
Run a Select or Crosstab query.
When you open a select or crosstab query, Access runs (executes)
the query for you and shows the results in Datasheet
view.
1.
In the Database
window, click Queries under Objects.
2.
Click the query you want to open, then click Open.
Open a query (select or crosstab) that shows records.
1.
In the Database
window, click Queries under Objects.
2.
Click the query you want to open.
To open the query in Datasheet
view, click Open. To open the
query in Design view, click Design.
Note. When you open a select or crosstab query in Datasheet view, you are actually executing the query.
(4). ACTION
QUERIES
An action query is a query
that copies or makes changes to or moves many records in just one operation.
There are
4 types of Action queries:
(a). Delete Queries.
A Delete query deletes a group of records from one or more tables. For
example, you could use a delete query to remove products for which there are no
orders.
Notes.
•
With delete queries, you always
delete entire records, not just selected fields within records.
•
Once you delete records using a delete query, you cannot undo the
operation. Therefore, you should preview the data that the query selected for
deletion before you run the query. To do this, click View on the toolbar, and view the query in Datasheet view.
(b). Update Queries.
An Update
query makes global changes to a group of records in one or more tables.
For example, you can raise prices by 10% for all dairy products, or you
can raise salaries by 5% for the people within a certain job category. With an
update query, you can change data in existing tables.
(c). Append Queries.
An Append query adds a group of records from one or more tables to the
end of one or more tables.
For example, suppose that you acquire some new customers and a database
containing a table of information on those customers. To avoid typing all this
information into your own database, you can append it to your Customers table.
Append
queries also helps in:
·
Appending fields based on
criteria. For example, you might want to append only the names and addresses of
customers with outstanding orders.
·
Appending records when some of
the fields in one table do not exist in the other table. For example, the Customers
table has 11 fields. Suppose that you want to append records from another table
that has fields that match 9 of the 11 fields in the Customers table. An append
query will append the data in the matching fields and ignore the others.
(d). Make-Table Queries.
A Make-table query creates a new table from all or part of the data in
one or more tables. Make-table queries also helps in creating a table to export
to other Microsoft Access databases or a history table that contains old
records.
Run an action query.
Unlike select and crosstab queries, you can't view the
results of an action query by
opening it in Datasheet view.
However, in Datasheet view you can preview the data that will be affected when
you run the action query.
Caution It is a good idea to make a copy of the data you are changing or moving
in an action query, in case you need to restore the data to its original state
after running the action query.
1. Open the
action query in Design view.
2.
To preview the records that will
be affected in Datasheet view, click View
on the toolbar and check the records. For each action query, you will see the
following:
For this query The datasheet displays
Update The fields to be updated.
Delete The
records to be deleted.
Make-table The
fields to be included in the new table.
Append The
records to be added to another table.
3.
To return to query Design view,
click View on the toolbar again.
Make any changes you want in Design view.
4.
Click Run
on the toolbar to run the query.
Add or remove tables, queries, and fields.
You can add a table or query if the data you need is not in the query,
or remove a table or query if you decide you do not need them. Once you add the
tables or queries you need, you can then add the fields that you want to work
with to the design grid, or remove them if you decide you do not need them.
Notes.
v A join line between field lists tells Microsoft Access how the data in
one table is related to the data in the other.
v You drag a field from the field list to a column in the design grid to
show the field in the query results.
Calculate amounts
You can
add the values in a field or do other computations with the data by specifying
the type of calculation to perform.
•
Use an aggregate function, such
as Sum or Avg, to calculate one amount for all the records in each field in
the design grid.
Aggregate function - A function, such as Sum, Count, Avg, or Var, that you
use to calculate totals.
•
Use Group By
to calculate separate amounts for groups of records in a field.
Limit results by using criteria
You can limit the records that you see in the query's results or the
records that are included in a calculation by specifying criteria.
For example;
(i). To limit
the records in the query's results, enter criteria in one or more fields.
Between #6/1/01# And #6/15/01#
(ii). Use the Or row for alternative criteria in the
same field.
Between
#6/1/01# And #6/15/01#
Or Between #7/1/01# And #6/30/01#
(iii). Enter criteria for different fields. For example,
for orders between 6/1/01 and 6/15/01 ...
Between
#6/1/01# And #6/15/01#
(iv). Calculate total order amounts, but display only those that are more
than $100,000. >100000
Sort records.
You can sort the query's results
by specifying a sort order in the design grid.
You can Sort by Ascending or
Descending order, or remove a sort.
Show only the high or low values in a query.
You can show in a query datasheet
those records with the highest or lowest values in a field or the highest or
lowest percentage of values in the field.
For example, you may want to show the top 10 Salespeople with the
highest total sales for a month, or the bottom 20 percent of students in a
class by grade average.
1.
Create a query in Design view.
2.
In the Design grid add the fields you want to display in the query's
results, including the field you want to display top values for.
3.
In the Sort cell of the field you want to display top values for, click Descending to display the highest
values or Ascending to display the lowest
values.
4.
Click in the Top
Values box on the toolbar.
5.
Enter the percentage or the
number of highest or lowest values you want the query results to display.
Note
To display a percentage, enter a number followed by a percent sign (%).
6.
To view the query's results, click View on the toolbar.
When can I update data from a query?
In some cases, you can edit data in query Datasheet view to change the data in the underlying table. In other
cases, you cannot.
You can update a query or query
field in the following cases:
• A query
based on one table
• A query
based on tables with a one-to-one
relationship.
• The
query's results contain a Memo, or OLE Object.
Set the data display format for a field in a query.
1.
Open a query in Design view.
2.
In the query Design grid, place
the insertion point in the column for the field you want to format (or place
the insertion point in any row for that field).
3.
Click Properties
on the Query Design toolbar to open
the property sheet for that field.
4.
On the General tab, click the arrow next to the Format property box, then click one of the predefined formats.
Set the no. of decimal places to display for a field in query Design
view.
1.
Open a query in Design view.
2.
In the query design grid, place
the insertion point in the column for the field you want to change.
3.
Click Properties
on the Query Design toolbar to
4.
On the General tab, click the arrow next to the DecimalPlaces property box, then click the desired no. of decimal
places.
FORMS.
A Form is an Access database
object on which you place controls for taking actions or for entering,
displaying, and editing data in fields.
A form is a type of a database object that is primarily used to enter or
display data in a database. To easily view, enter, and change data directly in
a table, create a form. When you
open a form, Microsoft Access retrieves the data from one or more tables, and
displays it on the screen with the layout you choose in the Form Wizard, or with the layout that
you created on your own in Design view.
Notes.
·
A form focuses on one record at a
time, and it can display fields from more than one table. It can also display pictures and other
objects.
·
A form can contain a button that prints, opens
other objects, or otherwise automates tasks.
Most forms are bound to one or more tables and queries in the database.
A form's record source refers to the fields in the underlying tables and
queries. A form need not contain all the fields from each of the tables or
queries that it is based on.
You create a link between a form and its record
source by using graphical objects called controls.
The most common type of control used to display and enter data is a text box.
You can also open a form in PivotTable view or PivotChart view to
analyze data. In these views, you can dynamically change the layout of a form
to present data in different ways. You can rearrange row headings, column
headings, and filter fields until you achieve the desired layout. Each time you
change the layout, the form immediately recalculates the data based on the new
arrangement.
·
In PivotTable view, you can view detail or summarized data by
arranging fields in the filter, row, column, and detail areas.
·
In PivotChart view, you can display data visually by selecting a chart
type and viewing data by arranging fields in the filter, series, category, and
data areas.
Creating a form
You can create a form quickly by using the AutoForm command or a wizard. AutoForm
creates a form that displays all fields and records in the underlying table or
query. A wizard asks you questions and creates a form based on your answers.
You can then customize the form the way you want it in Design view.
Customizing a form
In Design view
You can customize a form in Design view in the following ways:
Record
source. Change the tables and queries
that a form is based on.
Controlling and assisting the
user. You can set form properties to
allow or prevent users from adding, deleting, or editing records displayed in a
form. You can also add custom Help to a form to assist your users with using
the form.
Form window. You can add or remove Maximize
and Minimize buttons, short cut
menus, and other Form window elements.
Sections. You can
add, remove, hide, or resize the header, footer, and details sections of a
form.
You can
also set section properties to control the appearance and printing of a form. Controls. You can move, resize, or set
the font properties of a control. You can also add controls to display calculated values, totals, current date and
time, and other useful information on a form.
In PivotTable or PivotChart view.
You can customize a form in PivotTable or PivotChart view in the
following ways:
Add, move, or remove fields You can add fields to the filter, row, column, and detail areas in PivotTable
view, and to the filter, category, series, and data areas in PivotChart view.
You can also move fields from one area to another and remove fields from the
view.
Filter records You can filter data displayed in the view by adding or moving a field
to the filter area. You can also filter a field in the row and column area.
Sort records You can
sort items in row or column fields in ascending or descending order. You can
also sort items in custom order in PivotTable view.
Group records You can group items in row or column fields on intervals, or create
custom groups.
Format elements and change captions. In PivotTable view, you can change the font settings, alignment,
background color, and number format of a field. You can also change the
captions of fields and custom groups. In PivotChart view, you can change the
chart type, format data markers, and more.
Open a form or subform.
1.
In the Database
window, click Forms under Objects.
2.
Click the form or subform you
want to open. To open the form in Form
view, click Open. To open the
form in Design view, click Design.
Form view: A
window that displays a form to either show or accept data. Form view is the
primary means of adding & modifying data in tables. You cannot change the design
of a form in this view.
Switch between views of a subform.
When a
main form is open in Form view, you
can switch between the views of its subforms.
1.
Open the main form in Form view.
2.
Click the subform whose view you want to change.
3.
On the View
menu, point to Subform, and click
the view you want.
REPORTS.
A Report is an Access
database object that prints information formatted and organized according to
your specifications. Examples of reports are sales summaries, phone lists, and
mailing labels.). To analyze your data or present it a certain way in print,
create a report. For example, you
might print one report that groups data and calculates totals, and another
report with different data formatted for printing mailing labels.
·
Use a report to create mailing labels.
·
Use a report to show totals in a
chart.
·
Use a report to calculate totals.
A report is an effective way to present your data in a printed format.
Because you have control over the size and appearance of everything on a
report, you can display the information the way you want to see it.
Most reports are bound to one or more table and query in the database. A
report's record source refers to the fields in the underlying tables and
queries. A report need not contain all the fields from each of the tables or
queries that it is based on.
A bound report gets its data
from its underlying record source. Other information on the form, such as the
title, date, and page number, is stored in the report's design.
You create the link between a report and its record source by using
graphical objects called controls.
Controls can be text boxes that display names and numbers, labels that display
titles, or decorative lines that
graphically organize the data and make the report more attractive.
Creating a report
You can create different types of reports quickly by using wizards. Use
the Label Wizard to create mailing
labels, the Chart Wizard to create
charts, or the Report Wizard to
create a standard report. The wizard asks you questions and creates a report
based on your answers. You can then customize the report the way you want it in
Design view.
Customizing a report
You can customize a report in the following ways:
Record
source Change
the tables and queries that a report is based on.
Sorting and grouping data You can sort data in ascending or descending order. You can also group
records on one or more fields, and display subtotals and grand totals on a
report.
Report window You can
add or remove Maximize and Minimize buttons, change the title bar
text, and other Report window elements.
Sections You can add, remove, hide, or resize the header, footer, and details
sections of a report. You can also set section properties to control the
appearance and printing of a report.
Controls You can move, resize, or set the font properties of a control. You can
also add controls to display calculated values, totals, current date and time,
and other useful information on a report.
Open a report or subreport.
1.
In the Database
window, click Reports under Objects.
2.
Click the report or subreport you
want to open.
To open the report in Design view,
click Design. To open the report in Print Preview, click Preview.
Print preview -A view of
a document as it will appear when you print it.