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.

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.

Menu bar.

It appears horizontally at the top of the window. It is used to issue Ms-Access commands.

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.

Scroll bars.

They enable the user to see data not visible on the screen.

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).

The material (stuff) that a database program stores, organizes and manages for you.

A collection of related data organized in rows and columns.

A collection of data about a specific topic.

A collection of records that describe a similar data.
An element of a table that contains a specific item of information.

A single unit of information within a table.

The place where data is placed within a database.

A collection of related characters.

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.

Field Name:

This is the name that indicates each column (field).

It is the column title/ heading or a label for a field.

Field Type:

It is the type of data stored in a field.

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.

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.

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.

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. g., a name is usually split into 2 or 3 fields; First Name, Middle Name & Last Name. 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.

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.

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).

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.

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.

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:

A collection of logically related records.

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.

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.

Click New on the File menu or on the Standard toolbar (or press CTRL+N).
Under New, click Blank Database, then click the OK button.
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

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.

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.

On the File menu, click New.
Under New from template, click General templates.
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).
In the File New Database dialog box, specify a name & location for the database.
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:

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.

Field Type:

This specifies the type of data to be used/ stored in the field.

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)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)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) 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)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.

Date/Time.

A Date or Time field is used to store dates and times depending on the formats chosen.

It stores 8 characters.

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 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.

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)A Hyperlink field links the fields to another table within the same database or in a different database.

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:

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.
The amount of storage space you want to use for values in the field.
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

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;

Use the Table The Wizard enables the user to choose the fieldsfor the table from a variety of predefined tables such as Business contacts, Household inventory, or Medical records.

Create a table in Design viewwhere you can add fields, define how each field appears or handles data, and create a primary key.

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 typeand 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.

Click New on the File menu or on the Standard toolbar (or press CTRL+N).
Under New, click Blank Database, then click the OK button.
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.
Under Objects, click Tables, then click New on the Database window toolbar.
Double-click Datasheet view (or click on Datasheet view, then choose the OK button).

A blank Datasheet with rows and columns is displayed.

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.

Click in the cells and enter your data. Press the TAB key to go to the next field or record.
After adding data to all the columns you want to use, save the Datasheet table.
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.

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.

Double-click the field selector of the field that you want to change.
Type the new field name, then press the ENTER key to save it.

Method 2.

Click in the column that you want to change.
On the Format menu, choose Rename Column.
Type the new field name, then press the ENTER key.

To change the Column Width in Datasheet view.

Method 1.

Click in the column whose width you want to change.
On the Format menu, click Column Width.
Type the desired width in the Field Size box, then choose the OK

To make the column width fit an entry, click Best fit.

Method 2.

Point to the edge of the column whose width you want to change. The pointer becomes a double-headed arrow.
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.

Select the text, Record or Field whose contents you want format.
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

Method 2.

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.

Click in the row / record whose height you want to change.
On the Format menu, click Row Height.
Type the desired height in the Row Height box, then choose the OK

SELECTING FIELDS & RECORDS IN A DATASHEET USING THE MOUSE.

To select Do This

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 click the field name at the top of the column, then drag to extend the selection.

A record (row) 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.

To Move to Click

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,

Click in the column to the right of where you want to insert a new column.
On the Insert menu, click Column.

To insert a new record (row) in Datasheet view.

Click in the row below where you want to add the new record.
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

Delete a field from a table in Datasheet view.

Select the column you want to delete.
On the Edit menu, click Delete Column.

Delete a record (row) from a table in a Datasheet.

Click the row selectorof the record you want to delete.
On the Edit menu, click Delete Record (or click Delete Record on the toolbar).

Delete an item (contents of a field or record).

Select the field, text, or other item that you want to delete.
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.

Press F11 to switch to the Database window.
Under Objects, click Tables, then click New on the Database window toolbar.
Double-click Design View. Access takes you to the design view of your table.
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.

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.

To save your table, click Save on the File menu or on the toolbar, then specify a name for the table.
Switch to Datasheet view, then start entering records.

Switch between views of a table.

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:

The FieldSize property defines/ controls the maximum no. of characters that can be entered/ stored in a Text or Number field.

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.

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.

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.

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 Limits an age field from 18 to 55.

<10000 Allows any value less than 10000

<=500 AND <=1000 Accepts any entry between 500 and 1000.

>Date( ) Allows entries whose dates are the current date or later.

<Date( ) Allows the current date and before.

Note. To test whether the Validation rule is applicable, choose Test Validation Rules on the Edit menu.

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.

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.

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.

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.

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.

[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.

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.

Double-click the field name you want to change.
Type the new field name.
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:

Make sure no two records have the same value within that field.
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:

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.

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.

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.

Open a table in Design view.
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.

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.

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.

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.

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.

On the Edit menu, click Delete Rows (or click Delete Row on the toolbar).

Change a field’s data type in Design view.

Click the arrow in the Data Type column of the field you want to change, then select the new data type.
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.

In Design view, click the field whose FieldSize property you want to set.
In the lower portion of the window on the General tab, click the FieldSize property

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.

In Design view, click the field you want to format.
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.

In Design view, click the field you want to define decimal places for.
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.

Click New on the File menu or on the toolbar (or press CTRL+N).
Under New, click Blank Database.
In the File New Database dialog box, specify a name & location for the database, then click Create to open the Database window.
Under Objects, click Tables, then click New on the Database window toolbar.
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.

In the Database window, under Objects, click Tables.
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).

(a). Define the following terms as used in Database Management systems:
- Field Name.
- Field Type.
- 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.

Create a simple record of a company, having the following fields; Company’s Name, Address, City, State, and Phone number.
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.

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).
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.
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.

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.

Click the location where you want to place the item.
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.

Click Copy on the Edit menu or on the toolbar.
Click the location where you want to place the item.
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.

Awards

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.

Close any tables you have opened.

You cannot create or modify relationships between open tables.

Press F11 to switch to the Database window.
Click Relationships on the Tools menu or on the Standard toolbar.
If you have not yet defined any relationships in your database, the Show Table dialog box is automatically displayed.
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.

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.
Set the relationship options if necessary, then click the Create button to create the relationship.
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.

Press F11 to switch to the Database window.
Click Relationships on the toolbar (or on the Tools menu).
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.

Click Clear Layout on the toolbar to remove all tables from the Relationships window.
To add the table back, click Show Table on the toolbar, double-click the table, and then click Close.
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

This action affects only the display of the Relationships window. The table and relationships remain in the database.

Delete a relationship.

Close any open tables.
Press F11 to switch to the Database window.
Click Relationships on the Tools menu or on the toolbar.
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.
Click the relationship line for the relationship you want to delete (the line will turn bold when it is selected), then press the DELETE

Exercise (a).

(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.

Distinguish between the following terms in relation to a database:
- DBMS and database.
- Records and Fields. (4 marks).
Define the term Sorting. (2 marks).
(a). Differentiate between Primary key and Relationship.

(b). What is the importance of the Primary key.

Exercise (b).

(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.

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:

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.
Update records in the datasheet of a select query (with some restrictions).
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.

Create a Select query.

In the Database window, click Queries under Objects, then click New on the Database window toolbar.
In the New Query dialog box, click Design View, then click OK.
In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
Double-click the name of each object you want to add to the query, and then click Close.
Add fields to the Field row in the Design grid, and if you want, specify criteria and a sort order.
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.

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,

You can design it to prompt you for two dates. Ms-Access can then retrieve all records that fall between those two dates.
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.

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.

In the Database window, click Queries, then click New.
In the New Query dialog box, click Design View, and then click OK.
In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
Double-click the name of each object you want to add to the query, and then click Close.
Add fields to the Field row in the Design grid and specify criteria.
On the toolbar, click Query Type, and then click Crosstab.
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.

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.

Open the crosstab query in Design view.
Click the background of query Design view, outside the design grid and the field lists.
On the toolbar, click Properties to display the query’s property sheet.
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.
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.

In the Database window, click Queries under Objects.
Click the query you want to open, then click

Open a query (select or crosstab) that shows records.

In the Database window, click Queries under Objects.
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.

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:

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.

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.

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.

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.

Open the action query in Design view.
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.

To return to query Design view, click View on the toolbar again. Make any changes you want in Design view.
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.

A join line between field lists tells Microsoft Access how the data in one table is related to the data in the other.
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;

To limit the records in the query’s results, enter criteria in one or more fields.

Between #6/1/01# And #6/15/01#

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#

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#

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.

Create a query in Design view.
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.
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.
Click in the Top Values box on the toolbar.
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 (%).

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.

Open a query in Design view.
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).
Click Properties on the Query Design toolbar to open the property sheet for that field.
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.

Open a query in Design view.
In the query design grid, place the insertion point in the column for the field you want to change.
Click Properties on the Query Design toolbar to
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.

In the Database window, click Forms under Objects.
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.

Open the main form in Form view.
Click the subform whose view you want to change.
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.