Chapter 6

MS ACCESS
This is an application classified as database management system (DBMS). It is database tool
used to store, maintain and use a collection of information that is organized to serve a specific
purpose e.g. Customer records, Personal records, Library records, Hospital records, School
records e.t.c

The benefits of using a database to store information are:

- Fast retrieval of information because data is stored in an orderly manner.
- Easy to maintain accurate and up-to-date data.
- Easy to analyze and make summary reports from the stored information.
- Easy to protect your data from unauthorized personnel.

In Ms Access, information is stored in a database. The components of an Ms Access database

are Tables, Queries, Forms, Reports, Macros and Modules. These are also referred to as
Database Objects.

Other examples of Database programs in the market:

- Fox pro
- Fox base
- Oracle
- Paradox

DEFINATIONS
Record: This is a complete detail about an entry; normally entered in a row in a datasheet
view of a table, queries or forms e.g. name, date of birth, country of origin.

Field: This is a cross of information in a record or database normally entered in a column or

cells in a datasheet.

Object: These are programs (modules) that controls the layout presentation of the data e.g.
Filters (query), a table, query, form e.t.c objects that are defined by the system information
from another application such as a chart (graph), a table that can be linked or embedded.
Primary key: This is a unique field whose value uniquely identifies each record in a table. It
gives records their identity.
A primary key may be referred as a foreign key in another table
Microsoft Access Window
It’s divided into 2 parts
1. Application window
Comprises of;
 Application title
 Menu bar
 Tool bar
 Status bar
 Control buttons
2. Database Window
It comprises of;
 Objects
 Selection tabs
 Command buttons
 Control buttons
 Scroll bars (open object)
 Application window

Database Window
window

Designing a database
Considerations
 Data integrity
 Relationship between fields
 Data and fields duplication
 Object sizes
Creating a database
 Open Ms access
 Click on File on the menu bar
 Point to New and click
 Select Blank database and click
 Enter the name in the File new Database dialogue box, choosing the location to save
in
 Click on Create
TABLE
- This is an object used to present data
- Data is entered in tabular form
- Data is categorized under the following types;
Text - Data with text or combination of text and numbers
Memo - long text entries with both alphabetic and numeric characters
Number - Numeric entries
Date/time - Chronological entries
Currency - Monetary values
Auto number - automatic listing numbering
Yes/No - Indicates whether a statement is true or false
OLE Object - Graphics and pictures
Setting field properties of a table
- This are set of properties used to decide how data in the field should be stored or
displayed
- This is also used to prevent incorrect entries
 - Examples; You can control the maximum number of characters or letters that can be
entered in a text field by setting its field size property
- For date/time field, specify a format form
- For number or currency fields, specify the number of decimal places e.t.c

Creating a Table
Procedure
 In the database window, select Tables tab
 Click the New button on the menu bar
 The New Table dialogue box appears
 Select Design View and click O.K
 The design window appears
 Enter the Field Names, Data Types, and Description if necessary.

 Close the design window and click yes to save the table
 Enter the name and click O.K

Adding records to a database table

- Adding records to a new table
Procedure
 In the database window, select the table to which you want to add information
 Click on open the table window appears
 Enter the data
 Close the table window and save the changes

Adding new records in an already existing table

Procedure
 In the database window, select the table to which you want to add information
 Click on open. the table window appears
 Select Data Entry from the Records menu. Access hides all the records and displays a
blank row in which you can enter the information for the new records.
 Enter the information in the first field then press the enter key to move to the next
field.
  To show all records, click on Remove filter/sort, from the Records menu. The new
record(s) will have been added at the bottom of the table.

Sorting records in a table

- This is the process of arranging records in a defined manner
- It helps to locate the highest or lowest value in a list
- Also helps in arranging data in some order of priority
To sort one column
Procedure
 Position the cursor in the field which you want to sort
 Click on records on the menu bar
 Point to sort
 Select the criteria of sorting i.e. Ascending or Descending and click
To sort more than one column
Procedure
 Click on records on the menu bar
 Point on advanced filter sort and click
 As filter window appears; select the fields and the criteria of sorting for each field
 Click on filter on the menu bar
 Point and click on apply filter /sort

Getting External Data

This is getting data from other databases or other programs
1) Importing from an Access database
Procedure
While the database window is open and the table selection tab is highlighted
 Click on file
 Point to get external data and click on import
 Select the source (folder or drive)
 Select the file or database containing the required table
 Click on import, then from the list of the tables provided select the table to import
click o.k.
2) Importing from a different software
 Click on file
 Point to get external data
 Click on import
 In the next dialogue box, indicate the source e.g. drive or folder
 Indicate different application which was to make the file e.g. Ms. Excel
 Select the file, then click on import
 Answer the quiz in the wizard clicking next after completing every stage then
 Click on finish

Linking tables
Importing tables leads to creating a separate copy which is then imported into the current
database.
Incase memory is limited; creating such copies may lead to lack of space in such instances the
user can use the link option instead of import option.
The link option provides a way of opening a table which would otherwise be imported when
the user is in the current database.
QUERY
- This is a question about data that is stored in the tables.
- The output got after filtering is an example of a query
- Apart from viewing the record, a query can also be used as the source of data for
creating a form or a report.
- Can also be used to
 Update records (calculations)
 Select and display particular records (filtering)
 Ask and answer quiz about your data
 Arrange or sort records

Queries are named according to their uses:-

a) Update queries- they change data into are required information
b) Delete queries-they delete selected data
c) Append queries-they are used to transfer selected data
d) Cross tab queries-they display summarized values
e) Select queries-they retrieve data from one or more tables and display the result in a
small table
f) Query from multiple table- used for creating a query using more than one table

Creating a Query
Procedure
 While the database window is open, click on the queries tab.
 Double click on Create Query in Design View.
 The show table dialogue box appears, add the tables and close.

 Ent
er
the fields to be used in the query.
 Type the criteria under the relevant field
 If there are any fields that will not be shown in the output, un-check them, i.e. in the
row for Show click on the check-box to remove the tick sign.
 To view the output after setting the criteria, click on the Datasheet View button or the
Run button.

Setting criteria
Decide on which record you want to show and which you want to use as a determining factor.
Type in the function which will filter only those particular records of interest e.g. you may
need to display the list of people who earn more than Kshs. 20,000 in this case the field to
filter is salary and the function is >20,000.
Other Criteria are:
> Greater than
< Less than
= Equal symbol
>= Greater than or equal to
<= Smaller than or equal to
<> Not equal to
*Represents more than one character
? Represents one character

Run

Saving a query
Procedure
 Close the query window
 Click on yes to save the query
 In the save as dialogue box enter the query name and click O.K

RELATIONSHIPS
- This is a way of linking up tables to enable data to be shared among tables.
- They are normally created to curb the duplication of records especially when a query
is created using more than one table.
- A relationship can either be created from the tools menu, then click on the
relationship command or query design screen.
- Before a relationship is created ensure that there are field names having the same data
without which no relationship can be created.
- There are 3 types of relationships
a) One to one- where one record from a table A can only be related to one
record in table B e.g. a person has only one ID No.
b) One to many- where one record in a table A is relate to at least 2 or more
records in table B e.g. an instructor to the students, a doctor to the patients.
c) Many to many- where a record in table A or B can relate to many records in
other tables e.g. many customers may purchase many products and many
products may be purchased by many customers.
Steps of Setting up a Relationship
 Click on the tools menu
 Select or click on the relationship command
 In the show table dialogue box click on the add button
 Select the records to be linked and close
 Click, drag and drop field from one table to the other (this is the field that the tables
have in common)
 Ensure that the link is abided to field name with common data types
 Click on create

  A join line appears to show that a relationship has been created.

 Close and save the link line
 Alternatively links or relationships can be created in query design screen using step 3
and above.
 To delete a join line click on it then press delete key on the keyboard

Form
- A form makes viewing of records and entering of data into a table easier because it
uses controls such as Text boxes and Drop-down lists.
- Forms are easier to complete and to read because the labels used are more descriptive
than the field titles used in the tables. In addition you can add help information so that
the person using the form may know exactly where to place certain information e.t.c

Controls
- Everything that is added to a form or created on a form is described as a control e.g.
text boxes, list boxes (Drop-down lists), lines e.t.c. They are used to control the
information that is typed into the form, or determine how the form should look like.
Labels
These are descriptive items in a form, such as form title and the text next to a text box. They
describe the information to be entered in the text boxes or to be selected from the drop down
lists.
Text boxes
This is an area in which the information from the table appears or where the information is to
be entered.
Drop-down lists
This is a list box that contains a list of choices or options for the user to choose from. It can be
used to restrict entry to that particular text box.
Creating a Form
Procedure
 Click on the form selection tab then click on new
 Select the view (design view, form wizard, outoform: columnar, tabular, datasheet,
pivot table, pivot chart, chart wizard, pivot table wizard)
 Select the table or query to use, then click o.k.
 In case you are using the wizard ensure that you select the field (s) to include.
 Select the layout format e.g. international. Give the form name and then click on
finish.

Some important symbols in the form wizards

> Picks the highlighted fields
>> Picks all the fields in the table
< removes the selected field from those which will be included in the form
<< removes all the fields

Outoform
A quick method of creating a form as Ms Access will automatically create all the controls and
position them in the form.

REPORTS
These are Ms Access objects and components used in
1) Print previewing
2) Analysis
3) Rearranging and grouping of data
4) Present data
Creating a report
 Click on the report selection tab, then click on new
 Select the view
 Select the table or query to use, and then click o.k.
 In case you are using the wizard ensure that you select the field (s) to include.
 Select the layout format e.g. international. Give the form name and then click on
finish

A report wizard contains a series of steps to be followed when creating a report

Sections of a report
1. Report Header- it’s the main heading of a report and its written once at the
beginning of a report.
2. Page Header- contains information to be printed at the top of each page on
the report.
3. Group Header – contain information that is printed at the beginning of each
record of the table. Its present only when there is grouped data in a report the
name given depends on the grouped fields.
4. Detail- This sections information that is printed for each record in the table i.e.
the main area of the report that contains the actual record.
5. Group Footer- This section contains information that is printed at the end of
every grouping of data e.g. subtotals .
6. Page Footer- Contains information to be printed once at the end of the report
e.g. the grand totals.
Ms Access automatically creates a present footer for the report.