FREE KCSE PAST PAPERS

Form 2 Computer Notes

Francis

Form 2 Computer Notes

WORD PROCESSORS

Chapter outline

1.1Definition of word processor. 1.2Purpose of word processing.

    1. Using a word processing package (Microsoft word).
    2. Editing and formatting a document.
    3. Creating and editing a table.
    4. Mail merge document.

1. 7 Inserting graphics.

    1. Printing a document.

Definition of a word processor

What is a word processor?
A word processor is an application software that enables a person to create, save, edit, format and print text documents. Word processing is the act of using a word processor.

Examples of word processors

Examples of word processors include, Microsoft Word, Corel WordPerfect, WordStar, Lotus WordPro and many others.
A word processor has a number of advantages over he type writer that include:

      1. A document can be stored in the computer for future reference in computer files that do not occupy physical space like the one on paper. This creates a paperless document- processing environment.
      2. Typing using a word processor is easier and more efficient because some actions are automated. For example, word- wrap feature automatically takes the cursor to the beginning of the next line once you reach the end of the current. Another automatic features common in most word processors is automatic insertion of a new page after reaching the end of the current page.
      1. Most word processors have special editing tools such as spelling and grammar checkers and thesaurus that help the user to easily Correct grammar and spelling mistakes. A thesaurus helps to find a word or phrase with similar meanings.
      2. With insert and type over modes available in most word processors, one can easily insert or replace a word or a phrase without affecting the structure and neatness of the document.
      3. Word processors have superior document formatting features. Formatting refers to the art of making the document more attractive and appealing to the eye. Such features include, underlining, boldfacing, italicization, applying different colors etc.
      4. While a typewriter may require that we retype the entire document when we want many copies, with a word processor, printing lets you produce as many copies per session as needed.
      5. Using the cut, copy and paste commands, you can incorporate other text without having to retype it.

Basic word processing features and concepts

  1. They allow the user to create a file, save it and open it again and again for reuse, editing or formatting.
  2. They have almost similar document windows with the following Features:
  3. Most are designed to look like a piece of paper with a few
  4. Most of the screen is blank before typing of text.
  5. There is a cursor, which blinks at the position where you can Begin entering text.
  6. There is a status bar or line that provides the user with current status information such as saving operation, the name of the file in use, the current page, the current imaginary horizontal-line and column cursor position.
  7. Word wrap: The feature that automatically moves a word or

 

cursor to the beginning of the next line if it does not fit at the end of the current line.

  1. Scrolling: This is the vertical movement of text document on the screen. We can scroll through a text document using the up or do n arrow keys, page up and page down keys and also using, a mouse in windows based word processors.
  2. Help: Sometimes we get lost, confused or need more information on how to use an application. Help contains instruction, tips, pointers, explanations and guidance. You can get help by either pressing F 1 on the keyboard or clicking Help from the menu.
  3. Editing Modes: Word processors have two editing modes, insert and type over mode. In insert mode, every character typed between words, lines or characters is placed at the cursor position. This pushes all the text in front of the cursor away without deleting it. However in type over mode every character typed deletes what was there before and replaces it with new text at the cursor position.
  4. Nearly all word processors have a spelling checker, thesaurus (Dictionary of synonyms) and grammar checker.
  5. They provide for the use of headers and footers, indexing, footnotes and references, typefaces (fonts) and character set.
  6. Most word processors have ability to create and import tables, text and graphics from other programs.

Mostly, word processors are used for writing letters, reports, projects, books, essays, memos curriculum vitae etc.
Factors to consider when choosing a word processor The choice of a word processor depend on:

  1. The type of operating system. For example, most microcomputers are currently running on Windows based operating system such as Microsoft Windows. This means that you should consider acquiring a graphical user interface based Word Processor,
  2. Its user-friendliness, i.e. ease of use.
  3. Its formatting and editing features. They should be good and varied.

 

Using a word processing package Microsoft Word
.
Microsoft Word is one of the components of Microsoft Office. Microsoft Office is integrated software with a number of interrelated programs. The programs include Microsoft Word, a spreadsheet called Microsoft Excel, and a database management system called Microsoft Access, communication software called Microsoft Outlook, and a presentation package called Microsoft PowerPoint among others.
Although there are several versions of Microsoft Word, the most common are Word 97, Word 2000, WordXP and Word 2003. These versions of Microsoft Word are found in Microsoft Office 97, 2000, XP and 2003 suites respectively.

Starting Microsoft Word

There are a number of ways you can start Microsoft Word. However the
Simplest is by using programs start menu.

Starting Microsoft Word from the start menu

  1. Click the start button.
  2. Point to programs/ all programs if you are using Windows XP
  3. Click from the programs menu

Microsoft Word screen layout

Once you start Microsoft Word, it provides you with a blank document window referred to as the new document template where you can create your document.
However you may need to use other specialised templates such as Faxes and letters, Memos and Web page. How? Click File then select New from the menu. The New dialog box in Figure 1.3 that appears enables a person to select a template by activating templates tab then double clicking its icon.

With Windows multitasking capability, you can have more than one document window at the same time sharing one application window. However, each document window will have its own title bar but both will share other facilities like the menu bar of the application window.

 

However only one document window will be visible on the desktop at a time while others remains minimized or covered. This window is referred to as the active document window. To switch between document windows, click their buttons on the taskbar.

Parts of a window

 

Title bar
A title bar is mostly a blue strip at the top of the window that displays the title of the currently running application or task. However it is possible to change the color of the title bar using the display properties.

The title bar also enables the user to move the window around the desktop. This is possible by pointing to it then dragging using the mouse.

On the right of the title bar are three tiny buttons called minimize, restore/ maximize and the close buttons.
The minimize button reduces a window to become a button on the taskbar.

The restore/maximize button stretches the window to cover the entire desktop or restore it to its original size respectively.
The close button is used to close and exit a window.

Menu bar
Provides a drop down list of commands that one can use to perform a task. Examples are File, Exit, Window and Help. Figure

    1. shows the File drop down menu.

Too/bars
These are rows of buttons or icons that represent commands. The command buttons are shortcuts to the same commands you can access from the menu bar. There are various toolbars available in Microsoft Word but the most common are the Standard and Formatting toolbars (Figure 1.5 and 1.6)
To see what other commands are, just point to the icon on the

 

toolbar and a text tip will be displayed showing the function of the icon.
Most of these commands will be discussed later.
Hiding and displaying the toolbars

  1. Click the View menu option.
  2. Position the pointer on Toolbars.
  3. Click the type of tool bar required to select it.

Selected toolbars have ticks or check marks next to them as shown in Figure 1.7.
Rulers
Microsoft Word provides the user with vertical and horizontal on screen rulers that helps the user position text or objects in the right position.

If the ruler is not visible on the screen, then display it using the following procedure.

  1. Click the View menu option.
  2. On the drop down menu that appears, click Ruler. A check mark or tick will appear next to it showing that the ruler is displayed on the screen.

Work area
This is the working area where you can enter text or graphical objects. All windows based application programs provide the user with a unique working area suited to that particular application.

Status bar
This is an interactive strip at the bottom of the screen that acts as a communication link between the user and the program. It displays interactive activities like saving, opening, background printing, cursor position etc.
Scroll bars/button_ and scroll arrows
Scroll bars or buttons are horizontal and vertical bars on the borders of a window that the user drags to scroll upward, downwards, to the right or left of a document. Scroll arrows are arrows at the end of the scroll bars that the user clicks instead of dragging the scroll button. Using scroll bars or arrows is equivalent to using the arrow keys on the keyboard

Worksheet labels: These are usually of the format Sheet 1, Sheet 2 etc. A workbook may have several sheets. It is also possible to rename the sheets by right clicking on the labels then choosing rename command from the shortcut menu that appears. The active sheet (one being used) has its label appearing lighter in colour than

the rest. To move to a particular sheet in the workbook, simply click its sheet label.

Vertical and horizontal scroll bars: Clicking the arrows at their ends moves the worksheet vertically and horizontally on the screen respectively.

Worksheet layout
The worksheet has the following components: Cells: An intersection between a row and a column.
Rows: Horizontal arrangement of cells. Columns: Vertical arrangement of cells.
Range: Is a group of rectangular cells that can be selected and manipulated as a block.

Navigating the Microsoft Excel screen
1. Click cell D5. Notice that the cell pointer immediately moves to the cell and the name box reads D 5. Typing on the keyboard now inserts entries in cell D5 as long as the pointer is still there.
2. Click letter A that heads the first column. Notice that the whole column is highlighted.
3. Double click cell EIO. Notice that the text cursor forms in the cell and you can now type characters inside the cell. Also the status bar will now read enter which means that Microsoft Excel expects you to enter a value in the cell.
4. Click the down arrow on the vertical scroll bar. The worksheet moves upwards on the screen. The opposite happens when you click the up arrow on the vertical scroll bar.
5. Click the right button on the horizontal scroll bar. The worksheet moves to the left. The opposite happens when you click the left button on the horizontal scroll bar.
6. Press the right arrow key on the keyboard. Notice that the cell pointer moves one column to the right on the same row. This can also be done by pressing the Tab key once.

7. Press the left arrow key on the keyboard. Notice that the cell moves one column to the left on the same row. Pressing gives the same results.

8. Press the up arrow key on the keyboard. Notice that the cell pointer moves one row up on the same column.
9. Press the down arrow key on the keyboard. Notice that the cell pointer moves one row down on the same column.
10. Press the end key. The status bar will display the message “END”. If you press the right arrow key, the cell pointer will move right to the last cell on the row. If the left up or down keys were to be pressed instead, the cell pointer would move to the last cell to the left, top or bottom respectively.

11. Pressing Ctr1+Home moves the cell pointer to the first cell of the worksheet i.e. cell AI.

Creating a worksheet
At its simplest level, creating a worksheet consists of starting the spreadsheet program and entering data in the cells of the current worksheet. , However, a person can decide to create a worksheet either using the general format or from a specially preformatted spreadsheet document called a template.

Using the general format
When a spreadsheet program is running it will present the user with a new blank screen of rows and columns. The user can enter data in this worksheet and save it as a newly created worksheet. If this is not available then click File menu option and select the new command. The dialog box shown in Figure 2.8 will be displayed on the screen. On the General tab, double click the workbook icon. Enter data in the new worksheet created.

Using a template
Click File menu option then new command. On the spreadsheets solutions tab, double click the template that you wish to create.

Figure 2.9 below shows some examples of templates that may be present for selection.

NB: If the template was saved previously on the hard disk, it will open as a new worksheet with all the preformatted features present allowing the user to enter some data. However, some templates may require the original program installation disk in order to be able to use them because they may not have been copied to the hard disk during program installation.

Editing a cell entry
Editing a cell means changing the contents of the cell. Before the contents in a cell can be. Changed, the cell must be selected by making it the current cell.
To edit a cell entry proceeds as follows:
.1. Move the cell pointer to the cell you wish to edit.
2. Double click the formula bar for the text cursor to appear in the bar. The status bar message changes to edit
3. Use the keyboard to delete and add contents to the formula bar then press enter key to apply. Click the save button on the standard toolbar to save the edited changes.

Selecting a range
As you have experienced with the previous two examples, working with one item at a time is tedious and time consuming. Using a range saves time when working with a large .amount of data.

A range is a rectangular arrangement of cells specified by the address of its top left and bottom right cells, ‘separated by a colon (:) ego Range AI:CIO is as shown in Figure 2.10.
Selecting multiple ranges
When using a mouse, you can select more than one range without removing the highlight from the previous. To do this:
Hold down the Shift key or the Ctrl key while you click on the row header of the second range you want to highlight. What happens?
Do you notice the difference when holding down the shift and the
ctrl keys?

1. Shift key will cause all columns/rows between the selected and the newly clicked cell to be highlighted.
2. Ctrl selects individually clicked cells or range.

Hiding rows/columns
You can hide some rows or columns in order to see some details, which do not fit, on the screen. To do this:
1. Highlight the columns/rows you want to hide
2. Click format menu, point on row or column and click hide command.

Saving a worksheet
To save a worksheet, one has to save the workbook in which it belongs with a unique name on a storage device like a hard disk. The procedure below can be used to save a workbook:
1. Click File menu option then select Save as’ command. Alternatively, click the save command on the standard toolbar. The save as dialog appears
2. Select the location in which your workbook will be saved in the Save in box then type a unique name for the workbook in the File name box. Make sure that the option Microsoft Excel Workbook is selected under the save as type box.
3. Click the Save button to save.

Retrieving a saved workbook
This means opening a workbook that was previously saved.
1. Click File menu option then the Open command. Notice that the Open command has three dots (called ellipsis) indicating that a dialog box will open, as the user is required to provide additional information. Alternatively just click the Open command on the standard toolbar. The open dialog box appears on the screen.
2. Click the Look in drop down list arrow and select the drive or folder where the workbook was saved. For example, if you saved in a diskette, insert it in the floppy drive then select 3 1/2-floppy (A:). A list of folders and files in the drive will appear in the list box.

3. Double click the icon of the workbook you want and the worksheet will be displayed in the Microsoft Excel window. Notice that the cell pointer is in the same cell it was in when the worksheet was last Saved.
Closing a worksheet
Click File then Close command. This closes the worksheet but does not
Close the Excel spreadsheet program. Alternatively, click the; close button of the worksheet window
Exiting from the spreadsheet
Click File then Exit command. This closes not only the worksheet but also the spreadsheet program as well. Alternatively click the close button of the main application window.
Cell data types
There are four basic types of data used with spreadsheets:
1. Labels
2. Values,
3. Formulae
4. Functions.

Labels
Any text or alphanumeric characters entered in a cell are viewed as labels by the spreadsheet program. Labels are used as row or column headings usually to describe the contents of the row or column. For example, if a column will have names of people, the column header can be NAMES. Sometimes, numbers can be formatted so that they can be used as labels. To achieve this add an apostrophe just before the most significant digit in the number. For example, the number 1990 will be treated as numeric. if typed in a cell but’ 1990 will be treated as a label.

Labels are aligned to the left of the cell and cannot be manipulated mathematically.

Values
. ,

These are numbers that can be manipulated mathematically. They may include currency, date, numbers (0-9), special symbols or text that can be manipulated mathematically by the spreadsheet.

Formulae
These are user designed mathematical expressions that create a relationship between cells and return a value in a chosen cell. In Microsoft Excel, a formula must start with an equal sign. For example, the formula
=B3+D4 adds the contents ofB3 and D4 and returns the sum value in the current cell.

Excel formulae use cell addresses and the arithmetical operators like plus (+) for addition, minus (-) for subtraction, asterisk (*) for multiplication and forward slash (I) for division.
Using cell addresses, also called referencing, enables Microsoft Excel to keep calculations accurate and automatically recalculates results of a formula in case the value in a referenced cell is changed. This is called automatic recalculation.

Functions
These are inbuilt predefined formulae that the user can quickly use instead of having to create a new one each time a calculation has to be carried out Microsoft Excel has many of these formulae that cover the most common types of calculations performed by spreadsheets. To add the contents of cell B3 and D4 the sum function can be used as shown below:

= Sum (B3:D4)

2.6

Cell referencing
A cell reference identifies a cell or a range of cells on the worksheet and shows Microsoft Excel where to look for the values or data needed to use in a formula. With references, you can use

data contained in different cells of a worksheet in one formula or use the value from one cell in several different formulae.
By default, Microsoft Excel uses the A 1 cell referencing style. This means that a cell is identified by its column label followed by the row number. However, the Rl Cl referencing style can be used. In this case, the cell is referencing by its row number followed by its column number. The table below gives a few examples of equivalent referencing using both styles.
A1 style R1C1 style
B2 R2C2
C10 R10C3
E20 R20C5

The RlCl style is useful when automating commonly repeated tasks using special recording programs called Macros.

Relative referencing
When performing tasks that require cell referencing, you can use formulae whose cell references keep on changing automatically depending on their position in the worksheet. This is called relative cell referencing. A good example would be if you type the formula
=Al+Bl in cell Cl. If the same formula is copied to cell C2 the formula automatically changes to =A2+B2.

Absolute referencing
These are cell references that always refer to cells in a specific location
, of the worksheet even if they are copied from one cell to another. To make a formula absolute, add a dollar sign before the letter and/or number,
such as $B$lO. In this case, both the column and row references are absolute. .

Referencing using labels and names
Labels of columns and rows on a worksheet can be used to refer to the cells that fall within those columns and rows. It is possible to

create a name that describes the cell or range then use it instead of having to specify a range with actual cell references. Such a descriptive name in a formula can make it readable and easier to understand its purpose. For example, the formula
=SUM(SecondQuarterProfits) might be easier to identify than
=SUM(AlO:C20). In this example, the name SecondQuarterProfits represents the rangeAlO:C20 on the worksheet. Names can also be used to represent formulae or values that do not change (constants). For example, you can use the name .Tariffs to represent the import tax amount (such as 7.0 percent) applied to imports.

To create a named range
To create a named range proceeds as follows: 1. Select the range to be named:
2. Click inside the name box to move the text cursor inside. Delete the Cell reference that is there and type a name for the range.
3. Press Enter key to apply. Figure 2.13 shows a worksheet range called sales that has values used in a formula to give the sum in cell C 11.

2.7 Basic functions and formulae
Formulae perform mathematical operations ranging from very simple arithmetic problems t9 complex scientific, financial and mathematical analysis.

Statistical functions.
1. Average: It returns the average (mathematical mean) of a set of values which can be numbers, arrays or references that contain numbers. If the value 20 is in cell DIO and 30 in ElO then:
=Average(D lO:E 1 0) returns 25 as the average of the two values.
2. Count: Counts the number of cells that contain values within a range e.g.
= count (AIO: EIO) many return a value 5 if all the cells have values.

3. Max: It returns the largest value in a set of values. It ignores text and logical values e.g. == Max (AlO:EIO) will return the maximum value in the range.
4. Min: It returns the smallest value in a set of values. It ignores text and logical values e.g. = Min (AIO:EIO) will return the minimum values in the range.
5. Mode: It returns the most frequently occurring value in a set of values. e.g. = Mode (AIO:ElO)
6. Rank: Returns the rank of a number in a list by comparing its size relative to the others. For example if A 1 to AS contains numbers 7, 3.8,3.8, 1 and 2 then RANK (A2, Al :A5,1) returns 3 while RANK (AI, AI:A5,I) returns
5. The general format is RANK (number to be ranked, range, order).

Logical functions
1. If: It returns a specified value if a condition is evaluated and found to be true and another value if it is false. If (marks > 50, “pass”, “fail”) will display a pass if values are more than 50 else it will display fail.
2. Countif: Counts the number of cells within a specified range that meet the given condition or criteria. e.g. suppose A 1 0 : E 1 0 contains eggs, beans, beans, eggs, eggs, countif(AIO:EIO, “Eggs”) will return 3.
3. Sumif: It adds values in the cells specified by a given condition or criteria. e.g. For example if AIO to ElO contains values 10,50,60, 30, 70, to sum all values greater than 50 = Sumif(AIO:EIO, “>50”). This returns 130.

Mathematical functions
1. Sum: adds values in a range of cells as specified and returns the result in the specified cell. e.g Sum (AIO:EIO) adds values in the range
2. Product: multiplies values in a range of cells and returns the result in the specified cell. For example if A 10 has 30 and BIO has
3. Product (AlO:BIO) will return 90.

Arithmetic formulae – using operators
Operator Function

+ (plus) adds values as specified
– (minus) . subtracts values as specified
* (multiplication) multiplies values
/ (division) divides values.
( ) parenthesis encloses arguments to be calculated first.

For a formula =(Al +C3)/E20, if the value in E20 is not zero, the result is displayed in the current cell.

Order of execution
If several. Operators are used in a single formula; Microsoft Excel performs the operations in the order shown in Table 2.3. Formulas with operators that have same precedence i.e. if a formula contains both a multiplication and division operator are evaluated from left to right. Enclosing part of the formula to be calculated in parentheses or brackets makes that part to be calculated first.

Operator Name Precede nce
1. – Negation as in -1 1
2.% Percent 2
2. 1\ Exponentiation 3
3. * and / ! Multiplication and 4
division
4. + and – Addition and 5
subtraction
6. =,<>,>,<,<=,>= Relational 6

Table 2.3: Operators’ order of execution 2.8
Editing a worksheet

Coping and moving data
Spreadsheet software automates many processes that could have been tedious if done manually. For example with Microsoft Excel, you can do calculations using formulae fairly easily as you give the data and correct instructions to the program. Copying and moving of, data can also be done quickly and efficiently.

When data is cut or copied from the worksheet, it is temporarily held in a storage location called the clipboard.

Copying data
To copy a cell or a range of cells:
1. Highlight the cells or range you want copied
2. Click the Edit menu then select Copy command.
3. Select the cell in which you want to place a copy of the information 4. From the Edit again, click Paste command. The Paste command puts a copy from the clipboard on the specified location

Moving data
Unlike the Copy command where a duplicate copy is created, the Move command transfers the contents of the original cell (s) to a new location.

To move a range of cells:
1. Highlight the range you want to move.
2. From the Edit menu, select Cut.
3. Specify the location you want to move the contents to.
4. From the Edit menu again, click Paste.

2.9 Worksheet formatting
Worksheet formatting refers to enhancing the appearance of the worksheet to make it more attractive and appealing to the reader. Appropriate formatting should be used to lay emphasis, catch attention and bring otherwise II hidden detail to the fore of the document.

The golden rule of formatting is to use simple clear formats. It essentially consists of changing text colour and typeface (font), size, style and alignment. In Microsoft Excel, format the cells whether empty or not and their contents will acquire the set format automatically.

To format a single cell, make it the current cell then format menu option and select the cells command In the format cells dialog box, make the formatting specifications that you wish then click the ok button to apply. If it is a range of cells, they must be highlighted first before formatting them as a block of cells.

Formatting text
1. Highlight the cells that have the text to be formatted.
2. Click Format menu then cells command. The dialog box appears
3. Select the font tab as shown in the figure by clicking it.
4. Select the font type e.g. Times New Roman. Other font formatting features like style, size, underline and colour are available and can be selected.
5. Click button to apply.

NB: Alternatively, use the formatting toolbar to accomplish all your text formatting needs. Notice that the options in the font dialog box are commands on the formatting toolbar.

Formatting numbers
1. Highlight the cells that have the numbers to be formatted.
2. Click Format menu then cells command. The dialog box in Figure 2.15 appears.
3. Select the Number tab as shown in the figure below.
4. You can now choose number formats as explained below:
Number Meaning
General general format cells have no specific number format. Number Used for general display of numbers e.g. 2345.23.
Currency For displaying general monetary values e.g. $100, Ksh.10.
Accounting Lines up the currency symbols and decimal poin
s. Displays date in chosen format.

Date Displays time in chosen format.
Percentage Multiplies the value in a cell with 100 and display ‘ it as %.
Text Formats cells to be treated as text even when numbers are entered.
Custom For a number format not predefined in Microsoft Excel, select custom then define the pattern.

Worksheet borders
You may need to put a printable border around your worksheet or in a range of cells to make it more attractive and appealing. To put a border:
1. Highlight the range you wish to insert borders. From the format menu, click cells command.
2. Click the borders tab and specify the border options for left, right, top and bottom. .
3. From the style options, select the type of line thickness and style. Also select the preset options.
4. Click the ok button. The selected range will have a border around it.

Formatting rows and columns
Sometimes, the information entered in the spreadsheet may not fit neatly in the cell set with the default height and width. It therefore becomes necessary to adjust the height of a row or the width of a column. The standard width of a column in Microsoft Excel is 8.43 characters but can be adjusted to any value between 0 and 255.

Changing column width
1. Move the mouse pointer to the right hand side line that separates the column headers i.e. for instance the line between A and B.
2. Notice that the mouse pointer changes from a cross to a double arrow
3. Click the mouse button and hold it down so that you can now resize the width of the column by dragging to the size you wish. After Dragging to the required point release the mouse button and the Column will have a new size.

NB: Alternatively, move the cell pointer to one of the cells of the column then click Format, point to Column then click Width command from the sidekick menu. Type a width in the dialog box that resembles Figure 2.17 then click Ok.button to apply.
NB: To change the widths of several columns at the same time, highlight them first before following this method.

Changing row height
1. Point to the line that separates two row numbers e.g. the line between 1 and 2. The mouse pointer becomes a double arrow.
2. Drag the line until the height of the row is as required then stop and release the mouse button.
NB: Alternatively, click Format point to Row then click Height from the sidekick menu that appears. Type the height that you wish in the dialog box that appears and then click OK button to apply.

Inserting rows and columns
I, Click cell A5 to make it the current or active cell.
2.clik insert then columns to insert a ‘row above cell A5 and shift all the other rows downward.
OR

Click insert then Columns to insert a column to the left of column A and shift all the others to the right.

NB: Alternatively, click insert then cells to display the dialog box select the entire row or entire column options to insert a row or column respectively.

Global worksheet formatting
The word global in this case refers to the entire worksheet. In order to format the whole worksheet globally, it must be selected as a whole.
Two methods can be used to select a worksheet globally:
1. Click the top left comer of the worksheet that has a blank column header i.e. immediately on the left of A and just above I,

OR
2. Press Ctrl+A on the keyboard.

Notice that the whole worksheet becomes highlighted. It can now be formatted as one big block using format cells command.

Using autoformat
It allows the user to apply one of sixteen sets of formatting to & selected range on the worksheet. This quickly creates tables that are easy to read and are attractive to the eye..
1. Select a range e.g. B 1 :G7 to make it active.
2. Click format then select the auto format command on the menu that Appears. Select a format from the autoformat dialog box shown in Figure 2.19.
3. Click the ok button to apply the format to the selected range.

2.9 Data management
At times, it becomes necessary to use advanced data management tools to manage large ,data stored on a ‘worksheet. For example, if the worksheet has many records, it may become necessary to arrange them in a particular order using a method called sorting for easier access to data items. Other methods of data management include use of filters, total/subtotal function and forms.

Sorting
To carryout sorting proceed as follows:
1. Highlight the range that you wish to sort by clicking its column header letter.
2. Click Data then Sort . Notice that the Sort by field is already reading the field that you selected. This field is called the criteria field.
3. Select the field to be used as the key for sorting and the sort order as either descending or ascending then click OK button to apply.

Filtering data

Filtering is a quick and efficient method of finding and working with a subset of data in a list. A filtered list will only display the rows that meet the condition or criteria you specify. Microsoft Excel has two commands for filtering lists.
1. The auto filter: It uses simple criteria and includes filter by selection.
2. Advanced filter: It uses more complex criteria.

In this Pupil’s Book we will look at the autofilter.

Autofilter
Filters can be applied to only one list on a worksheet at a time.
1. Click a cell in the list that is to be filtered; usually the list is in a column.
2. On the Data menu, point to Filter, and then
3. To display only the rows that contain a specific value, click the arrow in the column that contains the data you want to display as shown in Figure 2.21.
4. Click the value that is to be displayed by the filter from the drop down list. e.g in the example below, the selected value is 34.
NB: Sometimes while looking through a list of values on a large worksheet, you may come to a value of interest and want to see all other occurrences of the value in the spreadsheet. Simply click the cell that has the value then click auto filter on the standard toolbar. Microsoft Excel turns on AutoFilter and then filters- the list to show only the rows you want.

Subtotals function
Consider the following scenario: A company that has many salespersons
will need to know how much each of them should be paid at the end of a period by looking at individual sales volumes. Also, the grand total for all the payments has to be calculated. Therefore, if the salespersons are held in a list, there would be need to calculate the amount due to each of them. This can be called a subtotal in the

list. All the subtotals can then be added together to make the grand total. Consider the following list:
Name Amount Owed
Stephen ` 6000
Joy 3000
Stephen 2000
Virginia 5000
Joy 800
Stephen 200
Virginia 5000

Microsoft Excel can automatically summarise the data by calculating subtotal and grand total values of the list. To use automatic subtotals, the list must have labelled columns and must be sorted on the columns for which you want subtotals. In this example, the list is first sorted by name
1. Click a cell in the list that will have subtotals e.g. cell A3.
2. On the Data menu click Subtotals 3. Notice that all the data range is now selected.
3. In each change in box, select Name from the drop down list because we want a subtotal for each of the names.
4. In the Use function box select the sum function then select the list for which subtotals will be inserted in the add subtotals box by checking the appropriate label. In this case it is the amount owed field.
5. Click ok button to apply and the list will now have sub totals inserted
Totals function
Use theAutoCalculate feature in Microsoft Excel to automatically show the total of a selected range. When cells are selected, Microsoft Excel displays the sum of the range on the status bar. Right clicking this function displays other functions like Min, Max and Average that can also be used. To find the total of a range, highlight it then click the autosum icon ∑ on the standard toolbar.

Forms
A form is a specially prepared template that the users can use to enter data in a worksheet. It is specifically formatted to enable

users to enter data in a format that is more convenient to them. If data is collected on paper before entering in the computer, then a form can be created to have the layout of the data on the paper to quicken data entry procedures. To display a form: Click ‘Data, then form.

2.10 Charts/graphs
Charts/graphs are graphics or pictures that represent values and their relationships. A chart helps the reader to quickly see trends in data and to be able to compare and contrast aspects of data that would otherwise have remained obscure. Microsoft Excel has both two-dimensional and 3-dimensional charts that can be used instead of the raw data in the table that has to- be studied for a long time to understand it.

The various types of charts available include column, bar, line. Pie, bubble and area charts among others. Consider carefully the type of chart that would best represent the base data in the worksheet before creating one. For example, if the aim is to depict the performance index of a student from Form I-to 3, a line chart would be most appropriate because it clearly shows the trend in performance.

Types of charts
1. Line chart – represents data as lines with markers at each data value in the x-y plane.
2. Column chart- represents data as a cluster of columns comparing values across categories. .
3. Bar chart – data values arranged horizontally as clustered bars. Compares values across categories.
4. Pie chart – it displays the contribution of each value to a grand total.
5. Scatter chart – compares pairs of values on the same axis.
To view types of charts, right click the chart object then select the chart type command.

Creating a chart

A chart must be based on values that are already entered in the worksheet.

To create a chart:
1. Select the range of values for which you want to create a chart.
2. Click the Chart wizard button on the standard toolbar and the chart wizard dialog box will open as shown in Figure 2.25
3. Click the type of chart you wish to .create. If the office assistant appears, close it. The chart sub-type preview will show several styles of the selected chart type.

4. Click the Next button to move to the dialog in Figure 2.26.
5. Click the Series tab then the collapse dialog button on the labels text box.
This will shrink the dialog box so that only the category labels text box is shown. Highlight the data labels from the worksheet.
6. Click the Expand dialog button to bring the full dialog box into view then click the: Next button. In step 3 of the wizard, use the appropriate tabs to type the title of the chart, show a legend, select whether to display gridlines or not etc. After all these click the Next button.’
7. At step 4 determine whether the chart will be inserted in the current worksheet or a new worksheet then click Finish button (Figure 2.27).

Moving and resizing a chart
Once the chart is created, its size and location can be changed in the worksheet. The chart element is enclosed inside a boundary called the chart area and hence both can be resized independently. Simply click the object you wish to resize and use the object handles just like in objects to drag to size. To move the chart, click inside the chart area then drag to the desired position.

Data ranges
A data range is a rectangular block of cells that provides the base data that is used to create the chart. In charting, a data range is referenced as an absolute range e.g. .

=Sheetl !$B$2:$C$8 which means that the base data is found on Worksheet 1 and absolute range B2:C8.
To see the data range of a chart, right click it then select the Source data command. .

Labels
Each representation of data on a chart can either be labelled by a value
or text label. For example, in a bar chart that compares the height of pupils, each bar can be given a value label to make it more readable.

To label:
1. Right click the chart then select the Chart options command from
the shortcut menu.

2. Click the lables tab and choose whether you want value or text labels then click OK button to
apply. .

Headings and titles
Each chart must have a heading showing clearly what it represents. To I make the chart understandable, include axis titles.

. To include axis titles proceed as follows:
1. Right click the chart then select the. Chart options command.
2. Click the Titles tab then type the chart title (heading). And axis titles respectively.
3. Click OK button to apply.

Legends
The legend is like a key that explains what each colour or pattern of the data representation in the chart means. For example, Microsoft Excel may give red colour to one data value and green to the other. Without a legend it would be difficult to know how to differentiate the two sets of values.

: To create a legend:
.1.Right click the chart then select the Chart options command.
2. Click the legends tab and specify that it be displayed in the chart area.
3. Click OK button to apply.

2.11

Printing worksheets
A worksheet will finally be printed for sharing with others or for filing purposes. If it contains objects like charts, it may not fit on a standard printing page using the default printing options and settings. Therefore, Microsoft Excel allows the user to preview and set up the pages of a’ worksheet in order to fit them on the hard copy page.

Page setup
1. Click .File menu option then Page setup command to display the page setup dialog box.. .
2. On the Page tab, select the orientation of the page. Study the meanings of each buttons and options in Figures 2.28.
3. After making the necessary selections, click OK to apply.

Print preview
It displays the worksheet from the point of view of the printer i.e. exactly the way it will look when printed. Before using this command, make I sure the chart is deselected.
1. Click the Print preview button on the standard toolbar.
2. The worksheet will be displayed in the print preview window with the status bar reading preview.
3. Click Setup to start the page setup dialog box. To close the preview, click the Close. Button.
I Print options

To print click File then Print command. The print dialog, box appears as shown in Figure 2.29 .

1. Select printer – the name box in this dialog box enables a person to select the printer that will be used to print the document. All the printers that are installed on the computer will be available here.
2. The print what options are:
(a) Selection – this prints the selected worksheet area.
(b) Workbook – prints all the worksheets in the workbook.
(c) Selected chart – prints the selected chart only.

Page orientation
As explained earlier, page orientation refers to the layout of the text on the page. A worksheet can also be printed on either landscape or portrait depending on the number of columns across the worksheet.

Pages and copies .
The number of copies box specifies how many copies of a particular worksheet or workbook should be printed.

Sometimes only some specified pages in a workbook are specified for printing e.g. if a workbook has 100 pages and you wish to print only pages 50 to 60 select the page(s) range button then type 50 and 60 in the from, to boxes respectively before clicking the OK button.

Printing
After selecting all the options, click the OK button to print.

Some common printing problems
1. A message appears on the screen saying that the printer specified could not be found in the directory.

Possible problems and solutions
(a) The printer could be off. Switch it on and it will start printing.
(b) The data cable to the printer could be loose. Make sure it is firm at the ports.
(c) The wrong printer could have been selected. Select the right one in the print dialog box and send the print job again.

(d) A message appears on the screen reading that there is paper jam. I

The printer is clogged with a paper jam. Alert the lab, technician or the Teacher to clear the paper jam.

DATABASES

Chapter outline

3.1 Introduction to databases
3.2 Database concepts
3.3 Data organization in a database
3.4 Creating a database using Microsoft Access
3.5 Editing a database
3.6 Form design
3. 7 searching for specific records in a database
3.8 Creating reports
3.9 Creating labels

Introduction to databases
We are often faced with the need to keep, search for or give a report of daily experiences. This is the reason why many people use data storage methods like recording daily experiences in a diary. Traditionally, human beings used to manage data and information manually by using simple devices. However these methods have a number of weaknesses which include

1. Unnecessary duplication of data
2. Boredom and time wasting especially when searching for a Particular item.
3. Misleading reports due to poor data entry and organisation
4. Poor update of records etc.

Today, computerized systems have radically changec4the way data and information is managed by use of special programs called Database Management System (DBMS).

A database is a collection of structured and related data items organised so as to provide a consistent and controlled access to the items.

Database concepts .

Computerised database creation and manipulation is achieved using Database Management System software. This software facilitates the creation, organisation and maintenance of databases. Examples of database management software’s include Microsoft Access (Ms Access), Oracle, FoxPro, Dbase/V, Lotus Approach etc.

The functions of database management software are to:
1. Allow the user add or delete records.
2. Update or modify existing records.
3. Organise data for easy access, retrieval and manipulation of records.
4. Act as an interface between a database and other application programs.
5. Ensure security for the data in the database by safeguarding it against unauthorized access and corruption (damage).
6. Keep statistics of data items in a database.
Database models

Databases are classified according to the method used to organise data. The main database models are;
1. Flat file 2. Hierarchical
3. Network 4. Relational

NB: Current database models are called object relational and object databases. However these models will not be discussed in details in this book.

Flat files

In a flat file model, a database holds only one set of data and is not any different from the manual files. For example, the teacher’s assessment report may consist of performance cards for every student in a class. Another example of a flat file database are the cards used in a library books catalogue. The cards are arranged sequentially for easy access e.g. alphabetically using books’ titles or by authors’ names. Figure 3.1 below is a sample record of a flat file.

Name Serah Seki
Admission 649
number
Total marks 680
Number of 10
subjects
Average 68
Position 4
Fig. 3.1: Sample record in a flat file

Hierarchical model

In this model, data items are arranged in hierarchical (tree) form as shown in Figure 3.2. To access level two data items, you have to first access level 1 data items. Level 1 item is called the root component. A specific single path leads to each item at lower levels. Hierarchical model is rarely used in modem database systems.

Network model

In this type of organisation, links are used to express the relationship between different data items, forming a network of items as shown in Figure 3.3. Access to one item can be through multiple paths and from any item. This model is also rarely used in modem database systems.

Relational model

This is the most common type of model used on minicomputers and microcomputers. In this type of organisation, related data items are stored together in structures called relations or tables. Relationship can be created between tables such that a record or records from one table relates to another or other records in another table. Tables 3.1 and 3.2 show customers and orders tables that are related by two fields; the customer number in Table 3.1 and customer ID in Table 3.2. In this case, the orders table shows that one customer with number 450 (Bat Hori) has made several orders.

Object oriented models                                                   . !

 

The next generation of database model will look at records as objects that are independent and relate with the other objects in the database. This concept is a bit difficult to conceptualise because such databases are still at developmental stage. However, many databases today combine object and relational concepts to come up with object relation models.

Features of a database management software
Most database software contains a collection of features that provides the user with a means to manipulate data in a database. These features include Tables/files, Queries, Reports generators, Form interface and a computer programming language within it. Microsoft Access database software that will be considered in this book has two distinct features used to automate operations namely Macros and Modules.

Tables/ file structure
This is a database structure that is used to hold related records. Tables are organised in rows and columns with each row representing a record while each column represents common fields in each record. Table 3.3 shows a table of six records and each record is made up of four fields.

 

FIRST NAME MIDDLE NAME LAST NAME ADDRESS
MBUGUA STEPHEN KIMANI 250
KERUGOYA
ADERA NELSON ODUOR 396 HOMA
BAY
MARGAR JUDY WEKESA . 761 NAIROBI
ET
GATO PATRICIA MUILA 170 TALA
MWELU LUCY. MWENI 390
KANGUNDO
.
KOECH PETER KORIR 605 KERICHO

Table 3.3: Table of six records and four fields

 

Queries and query language

A query is a database tool used to search for or question a database on specific records. Because a query is a question posed to the database, it returns a result for the user. In Microsoft Access, this result is called a dynaset. For example, assume you work with the National Irrigation Board (NIB), you may wish to display all those farmers from Mwea Rice Irrigation Scheme who delivered rice in January to the National Irrigation Board. You can use a query object or statements to get the required records.
The query statements are written using a special language called Structured Query Language (SQL). The user creates a, query by writing Structure query language statements such as:
Select
Farmer Number, Name, Month of Delivery From
Delivery Table Where
Month of Delivery = “January”

Forms/screen input

 

A form is a graphical interface that resembles the ordinary paper forms used to collect data. However, a database form enables the user to view and enter data into a table. Figure 3.4 shows a sample form used for entering a student’s marks scored in four subjects and. the date of admission. Form interface is a more convenient and easy way of entering and viewing records from a table.

Reports

 

Most database systems provide the user with a tool for generating reports from an underlying table or query. It is the report generator that provides the user with a means to specify the output layout and what is to be output or printed on a report. Table 3.4 shows a sample report for employees’ salaries.

Net Pay Report

 

fay   rol/no .Last.Name First Name.    .   .Basic    salary
PAYE Net salary.

07    Wangui   Grace    sh 80,000.00           Kshs 7,300.00         sh
72,700.00
09    Ochieng Mbengash 37,000.00           Kshs 3,800.00         sh
33,200.00
13    Mumbua Peter     sh 22,000.00           Kshs 2,300.00         sh 19,700.00
20    Mbuvai   Sicily    sh 60,000.00           Kshs 5,700.00         sh 54,300.00
Table 3.4: Sample report

Macros

 

Some database software provides the user with a tool called a macro, that can be used to automate frequently performed procedures or tasks. For example, if you frequently use a particular form when you start a database program, you need to create a macro that automates the opening of the form.

Programming module

 

When your database becomes more and more complex, you may need a more powerful tool than the macros to automate your database operations further. Some database software come with their own computer languages associated with them. For example Microsoft Access comes with a language called Visual Basic included as a Module in the software. Using this feature, you can create a program that will print a query result over and over again until a certain condition is true. This can be illustrated using a simple statement like;

Print Student Report until NumberofStudents = 40 (Not a real command anyway).

 

NB: For the purpose of this book, only tables, forms, queries and reports, have been covered.

Data organisation in a database
One of the functions of a database system is to organise data for easy access, retrieval and manipulation. Data is organised from the simplest fQfll1 called a field to a very complex structure called a data,base.

Fields

A field is a character or a logical combination of characters that represent data item. For example, in a class list, the student name is a field.

Records

This is a collection of related fields that represent a single entity. An example of a record is the student report card that may contain the. student’s name, admission number, class, total marks, average and grade.

Files/tables

A file is a collection of related records. For example, the students’ file in a school database contains the details of all the students in the school.

Database

This is the highest in data organisation hierarchy that holds all related files ‘or tables. For example, a school database may contain students and staff tables/files.

Creating a database using Microsoft Access

Microsoft Access (Ms Access) is a package in Microsoft Office suite used for creating and manipulating databases. Because there are different versions of Ms Access such as Ms Access 95/97/2000 and XP, a general approach for carrying out tasks has been adopted in this book. However most of the illustrations are based on Access 2000 in order to maintain consistency.

 

Starting Microsoft Access

  1. From Windows desktop, click the start button.
  2. Select. Microsoft Access from the programs menu
  3. In the dialog box that appears when Microsoft Access starts, click Blank Access database as shown in Figure 3.5 then OK.
  1. The Save As dialog box appears. See Figure 3.6. This lets you specify the name and location where your database will be stored. Click Create to save the database.

 

  1. Microsoft Access database objects window is displayed from which I you can choose the type of object to create such as tables, forms, queries etc.

Microsoft Access screen layout

Like the other applications discussed earlier, Ms Access application Window has a title bar, menu bar, toolbars and status bar. However instead of having a work area like that ofMs Word or Excel, it provides the user with an object window from which you choose the type of an objectto work with such as tables, forms, reports e,tc. See Figure 3.7.

.Exiting from Microsoft Access

Because Microsoft Access consumes a large amount of computer memory it is advisable to exit from the application at end of each working session.

To exit:
From the File menu, click exit the close button on the title bar.

Guideline on designing a good databases

To design a good database which will require little time to maintain, consider the following:

    1. Carefully study the requirements of the user in order to define all the data inputs, outputs and relationships required.

 

    1. Design a draft database on the paper to determine the number of files or tables required.
    2. Divide the information into separate fields, records and tables to allow flexibility in manipulating the database. This process of dividing information into independent tables to avoid repetition of data entries items is referred to as normalizing a database.
  2. Define a field for each table that will be used to identify each record uniquely. This field is referred to as a primary key
  3. Give the most important fields the first priority when constructing a table structure. Important fields are those that are used in sorting’ and querying the database. “
  4. Design data entry forms needed for the database

Creating a table/file structure

To define a table structure:

  1. From the Microsoft Access objects window, click the tables tab then new
  2. From New table dialog box, select Design View Figure 3.8 appears. :
  3. using the grid displayed, enter a unique name for each field in the table. A field name must start with a letter and can be up to a maximum of 64 characters including letters, numbers, spaces and punctuation.
  4. Choose the correct data type before adding the next field. By default, Ms Access inserts Text as a data type. Figure 3.8 shows various data types used in Ms Access.
  5. To save the table, click the Save button on the standard tool bar or save from the file menu.
  6. Access will ask you whether you want to create a Primary Key, click Yes.

Description of field data types

The type of data to be used in a database must be clearly defined for the purpose of manipulation and storage. For example, if a field is to be used for calculation, it must be defined as a number. The data types allowed in Ms Access include:

Text

 

This type includes alphabetic letters, numbers, spaces and punctuation. Use this data type for fields that do not need to be used for calculations such as names, places, identification numbers etc. This type of field accommodates a maximum of 255 characters.

Number

These are fields made up of numeric numbers 0 to 9 that are to be manipulated mathematically.

Memo

This is a field made up of alphanumeric (both alphabetic and numeric) data. Instead of using Text use this data type if you need to enter several paragraphs of text because it accommodates a maximum of 32 000 characters.

Date/Time

Used to identify a field as either a date or time. This is because date/time values can be manipulated mathematically in a database. For example, you can calculate the age of a person from the date of birth to the current data.

Currency

Used to identify numeric values that have decimals or fractions. Use this data type especially when dealing with monetary values such as fees balance, amount sold etc.

AutoNumber

This is a numeric value used if you wish Ms Access to automatically increment the values in a field. For example when entering a list of forty students and you have a field labelled StudNumber, the numbers will increase by one every time you enter a new record.

Yes/No

This is a logical field where an entry is either a yes or a no, true or a
false. For example a field may require you to answer whether you are a male or a female.

 

OLE Object

OLE stands for Object linking and Embedding. This type of field is mostly used with graphical user interface applications for inserting graphical objects such as pictures, drawings charts etc. – – —

Field properties

As you create more and more complex tables, you will find a need to use the field properties to specify finer details related to fields and the table entries expected. The field properties depend on the type of-the field selected. For example when you click on a Text field then the General tab you will see properties associated to text data type as shown in Figure 3.9. The various properties are:

Field size

This allows the user set the number of characters in a field instead of the default 50 for text fields for numeric field’s integer and long integer, Byte, Single and Double.
Integer and longer integer: Accept numbers with no decimals.
Byte: Can only accept a number from 0-255.
Single and double: Accept numbers with decimals. Single accommodates up to 38 decimal places while double, accommodates up to 308.

Format

Determines how information appears on the screen and when printed. For example, you can format a number to scientific, currency, percentage or general format.

Decimal places

For number and currency fields you can specify the number of decimal places.

Input mask                                          ,

Input mask automatically formats the field entry into a specified format. F or example, if you enter a number such as 02000 I 00409874 and the input mask is set as 000-(00000)-000000, it is automatically displayed as 020-(00100)-409874. This property is mostly used to format phone and address entries.

 

Caption

This is a more descriptive name for a field to be used in a table or a form display. For example the caption for StuName could be Student Name.

Default value                                                                 ,

This is a value that will appear automatically in the datasheet or form if ‘ nothing is entered by the user to change it. For example = Date -( ) automatically displays the current date in a date field.

Validation Rule

Logical expression restricts the values to be entered in a field. ‘For example, if you want to restrict marks entered in a field to values between
, zero and a hundred, type >=0 And <= 100.

Validation Text

The message that appears once the validation rule is violated. For example, you may create a validation text for the above validation rule I to display “Enter a number between 0 and 100” whenever the user enters I a value outside this range.

Required

Determines if an entry must be made in the field before you proceed to the next field or record. For example, if a primary key is required, you must enter before you proceed.

Allow Zero Length

This allows the user to proceed without making any entry in the field set as Zero length.

Indexed

An Index, facilitates the organisation of records for easy search. A primary key is an example of an index set to No duplicates to control double entry of a record (redundancy).

Primary key and Indexes

 

An index is a database feature used to speed up search and sort operations in a table. A key field also referred to as a primary key is a special index that enforces uniqueness in a table so that one record is not entered twice.

Ms Access uses the primary key to search for data stored in a table as well as define relationships between tables. Once a field is set as primary key, its datasheet is automatically indexed or sorted using the primary key.
To set a primary key:

  1. Open the table in design view.
  2. Select the field you want to set as the primary key by clicking in the row header to the left of the Field Name.
  3. Click Set Primary key button on the tools bar. A key should appear on the left of the field name as shown in Figure 3.10.

To see another field as an index other than the primary key: 1. Open the table in design view.

  1. Ensure that non of the fields is selected as a primary key.
  2. Click the Indexes button on the tool bar that is located next to the primary key. The index design grid is displayed on the screen as shown in Figure 3.11.
  3. In the index name column, type in the name you want to give to the index.
  4. In the Field Name column, click the cell to display a drop down list. From the drop down list, select the field to use as an index.

6 In the Sort Order column, select either ascending or descending.

  1. In the lower portion, specify whether you want to make the field a primary key by selecting yes for primary and unique, and Ignore Nulls to ensure that data is entered into the field before proceeding.
  2. Close the dialog box.

Entering data into a table

 

To enter/append data into a table:

  1. Open the database in which the table is located

 

  1. Click the Tables tab then double click the table you want to add records to.
  2. Enter each record field by pressing the tab key to move to the next. Once you reach the end of the record and move the cursor to a new row the previous record is automatically saved.

Editing a database

 

Modifying the Datasheet view

To adjust the column size:

  1. Point to the column border between the field’s header then drag to the require size.
  2. Alternatively, click on the format menu then point to column and click on width
  3. Type the desired width To adjust row height
  4. Point to the border between two rows in the row header and then drag.
  5. Alternatively, use format menu, then Row height. To reorder fields:
  6. Select the column of the field you wish to move by pointing to the desired file name.
  7. Drag the column right or left to the top of the field where you want your field to appear and then drop.

Modifying the table structure

 

Once you create a table, you may need to add more fields, remove some fields, reorder the fields or change fields data types and properties. Before you modify the table it is important to save a copy to avoid losing everything in case you make a mistake
To make a copy of your table:

  1. From the file menu, click save as /export
  2. From the dialog box that appears, choose whether to save to another (external) database or the current.
  3. Type a new name for your table and click OK.

 

To modify the original table:

  1. Open the table in design view by clicking the button that looks like a pencil and a set square placed on the edge of a ruler.
  2. Select the field or fields to be modified and make the necessary changes.
  3. Click the Save button to save the changes.

NB: If a table contains data and you make changes to the field data type, Ms Access may refuse to implement the changes. To avoid this problem, exit without saving and delete all the records from the table then return to the design view. You can then import a copy or copies of tables you backed up.
To import a table from another database:

  1. From the File menu, point to Get External Data then click Import
  2. From the dialog box that appears, select the database you wish to

import data from then click the Import button. Objects dialog box is displayed as shown in Figure 3.12.

  1. From the objects dialog box displayed, click the Tables tab
  2. Select the table(s) you wish to import then click OK.

Form design

 

Tables display many records at once from the database and in some cases you may not be able to see some fields properly. A form is an interface that enables the user to view and make data entries into an underlying table more easily. Figure 3.13 shows a sample form used to enter records into a books table.

In Microsoft Access, a form is designed using graphical objects called controls. A control is an object such as a text box, check box, command button or shapes that you place on a form design grid to display data or perform actions. There are two types of controls you can place on the grid: bound and unbound controls. A bound control is one whose source of data is a field in a table or query while unbound control is a control that is not connected to any data source.

 

You design or modify a form layout by dragging these controls to the required position. Figure 3.14 shows a form layout grid for a table called exam entry.

Creating a form layout using form wizard

 

To create a form layout, you can either use the form wizard or start from scratch. Using the form wizard you can create either a columnar, a tabular a datasheet or a justified form layout.
Columnar form: The fields for each record are displayed down a column i.e. each value displays on a separate line with field labels to the left. Tabular: Records are displayed from left to right across the page and labels appear at the top of each column and each row represents a new record.
Datasheet: The form resembles a table datasheet view.
Justified: One record occupies the whole form. To create a form using the form wizard:

  1. Open   the   database   for   which   the   form   is   to   be   created e.g.”EMPLOYEES”
  2. Click the Form tab then, New. From the New form dialog box that appears, click Form Wizard.
  3. Select the table you want to create a form for then New, A dialog box appears.
  4. Click the name of the table or query that includes the data you want to add into the form, and then click OK.
  5. From the fields list window, select the fields to add into the form by clicking the> button or click>> to add all fields then
  6. From the layout dialog box, select the layout you wish to use

e.g. Standard then. click Next.

  1. In the Form title dialog box, type the name of the form then click Finish. Ms Access will automatically display the form on the screen.

Creating a form using autoform wizard

 

You can easily create a form using the Autoform wizard. This wizard creates a form for you automatically by asking you very minimal questions. The form includes all the fields from the selected table.

To construct an autoform tabular.

  1. Make sure your database is open
  2. Click the Form tab, then New.
  3. In the new form dialog box, select an auto form layout e.g. columnar
  4. In the “Choose the table or query where the object data comes” select the table you wish to create a form for then click OK. The form with all fields will be displayed.
  5. Click the Save button to save the form.

Creating a form from scratch

 

  1. Open your database
  2. In the database window, click the Form tab then New
  3. From the New form dialog box, select Design view
  4. In the “Choose the table or query where the-object data comes” select the table you wish to create a form for then click The form with all fields will be displayed.

To add controls onto the grid:

  1. Click the View menu, then. Field list command . The table you selected fields list is displayed.
  2. Drag and drop each field and arrange them on the grid.
  3. Save the form by clicking on the save button.

 

Data manipulating in a form

Adding and displaying records

 

The form provides the user with navigation buttons located at the bottom that can be used to navigate the form

 

The functions of the buttons can be summarized from left to right as follows

  1. Displays the first record in the table
  2. Displays the previous record.
  3. Displays the next record
  4. Displays the last record.
  5. Add a new record.

Formatting fields/controls in a form

To format fields in a form;

  1. Open the form in design view
  2. To resize, click the controls then position the pointer on the outline of the control then drag to the required size.
  3. To move, click the control and position the pointer onto the place holder or inside the control then drag to the required location.

Searching for specific records in a database

One major reason for use of a computerised database is the ability to search and retrieve specific information more efficiently. Rather than searching through endless filing cabinets, you simply enter an instruction and let the database do the rest. To search for data in a database, Ms Access provides the user with two search tools:
1.  Find command        2. Queries’

Using the find command

With a large database, the Find command saves your time when you need to move a record pointer to a particular record.
To search for a record:

  1. From Edit menu, click Find. The find dialog box appears in which you specify the field you want to search for in the” Find What” box as shown in Figure 3.16.
  2. Type the name of field you wish to search for in the Find What text box. .
  3. In the Search box (Figure 3.16), specify whether to: ( a) Search All
  4. Search Down to up or
  5. Search up to bottom

 

  1. In the match box, you can select one of the match types:
    1. Any part of field – As long as a string of characters to be searched are present in a word, then all the words having the string will

be searched e.g. “Berg” finds Berger, inselberg etc.

    1. Whole Field – Searches for the whole field for example, “Berg” will not return Berger, inselberg but specifically berg if it is in    the database.
    2. Start of Field – Specifies that, the searched word should start with the specified search string e.g. “Berg”
  2. In searching, you can also check or uncheck;
  3. Match case – Finds only text that has the same pattern of upper and lower case as the one specified in the Find What box.
  4. Search Field As Formatted – Select (check) to find data based on its display format e.g. ‘a date stored asa number 1/5/98 but displayed as 05-Jan-98
  5. Search Only Current Field:- Select to search through all records for the current field only.
  6. After you set the search conditions click the Find First button.
  7. If you want to search for the second record or other records with the same field, click Find Next button.

You can use a wildcard e.g. asterisks (*) if you are not sure of the correct search word that will find all records that match the specified search string. For example, if you wish to search for all names that start with letter “J” in a school database, type J *. All names that start with J e.g. John, Jane Joy, Joyce and James will be displayed.

Using Queries

Queries are the fastest way to search for information in a database. A query is a database feature that enables the user to display specific records as well as perform calculations on fields from one or multiple tables. You can analyse a table or tables by using either a select query or an action query. However, only the select queries will be discussed in detail in this book

Select query

 

This is the most common type query used for searching and analysing data in one or more tables. Select query lets the user specify the search criteria and the records that meet those criteria are displayed in a dynaset

Action query

These are queries that are used to make changes to many records once. They are mostly used to delete, update, add a group of records from one table to another or create a new table from another table.

The four types of a action queries found in Microsoft Access are:

  1. Update – Updates data in a table.
  2. Append query – adds data in a table from one or more tables.
  3. Make table Query – Creates a new table from a dynaset.
  4. Delete Query – Deletes specified records from one or more tables.

Creating a select query

  1. Ensure that the database you want to create a query for is open.
  2. Click the Query tab, then New.
  3. From the New query dialog box, choose either to create a query from in Design view or using a Wizard.
  4. To design from scratch, click Design View. The Show Table dialog box appears from which you can add a table or tables you wish to create a query for as shown in Figure 3.17.
  5. Click the table from the table/ query list and then click Add.’
  6. Click Close to close the show table dialog box.
  7. The query design grid opens. In Microsoft Access it is called

Query By-Example. This lets the user to design a query. Figure
3.18 is QBE grid for a table called Exam.

Parts of the query grid

Field row- Fields from a table or tables to be used are arranged in this row. Each field should occupy its column.
Sort row – By clicking the down arrow in the sort cell, you can specify the sort order i.e. ascending, descending or no sort.

 

Show row – By clicking the Show box, you specify whether to display the field in the query results: When the box is not checked, the field will not be displayed.
Criteria row – This is where you type conditional statement that will be used by the query to display specific records.
Or row – Used to specify an alternative condition e.g. if you want to display records with a field called City with items Nairobi or Embu, type Nairobi in criteria cell and Embu in the Or cell.
To add fields into the query grid:

  1. Open the query in design view
  2. From the field list of the underlying table, drag each field and place it in the field row

Specifying the search criteria

In order to search for a particular set of records, you have to enter a conditional statement or statements in the criteria row. For example if you have a table called employees with one of the fields as salary, you can display all the employees earning more than Shs. 5,000, by typing >5000 in the criteria row, salary column.

To define criteria, use either relational or logical operators. Relational operators include less than «), greater than (», greater than or equal to (>=), less than or equal to «=), not equal to «» and equal to (=). Logical operators include AND, OR and NOT.

Use AND to display values in a specific range. For example, to display records from the employees table with salaries above 4000 but less than 6000, type, >4000 AND < 6000 on the criteria row in the salary column. All the employees who meet this condition will be displayed.

Use OR if you wish to get either one of two values. For example if you wish to get those employees either in Nairobi OR Embu.

If you want to display data in a particular range use the word Between. For example, instead of typing , >4000 AND < 6000, type Between 4000 And 6000.

 

If you want to list all records except those that you do not want to see use NOT. For example if you type NOT 6000 in the salary column of the employees table, all employees records will be displayed except. those with their salary as 6000.

To display records you are not sure of the field name but at least you can remember a few characters, use LIKE and the wildcards.
Wildcards are
special symbols mostly an asterisk and a question mark used in place of other characters. For example, to display all names starting with “Sm” followed by any other character, type Like Sm? Like */*/1993 lists records created in 1993 regardless of the day or month.

Saving and running the query

  1. Click the Save button on the standard tool bar or save command from the file menu.
  2. In the name box that appears, type in the name of the query then click OK.
  3. To run the query in design view, click the Run button on the toolbar as shown in Figure 3.19 or Run command from the Query menu.
  4. You can view the results of your query any other time by selecting the query, then clicking the Open button from the database window.

 

Sorting the dynaset To sort a dynaset:

  1. In the sort row, click the down arrow that appears to specify the sort order i.e. ascending or descending of the desired field.
  2. Display the dynaset.

Modifying and updating a query

To delete fields from the query grid:

  1. Open the desired Query in design view.
  2. Select the field column you wish to delete
  3. Choose Delete from the Edit menu.

 

  1. Click the Save button to save the changes

To adjust the column size in a query

  1. Open the desired query in design view.
  2. Position the mouse pointer at the boundary that separates columns then drag it to the required size. Alternatively, double click the    boundary to auto fit cell content.
  3. Click the Save button to save the changes To modify a criteria statement ‘select query,
  4. Open the desired query in design view.
  5. Modify the criteria statements as desired, 3. Click the Save button to save changes.

4. To test whether the changes have been effected, click the Run button to display the results of the query.

Performing calculations in a query

Unlike tables, queries let the user perform mathematical calculations on numeric data. You can perform calculations in a query by using the Total functions or by creating basic formulas Creating basic formulae
To create a formula that calculates the total marks in an underlying table e.g. Exams table:

  1. Open your query in design view
  2. In an empty cell, preferably the immediate last field cell, type an

expression that includes a field name of your choice such as:
Total: [Math’s) + (Physics) + (Chemistry) as shown in Figure 3.20

 

Math’s Physics Chemistry Total: (Math’s)+(physics)
+(Chemistry)
Exam Exam Exam
.

√

 √ √ √

Fig. 3.20: Creating an arithmetic expression

  1. You can then set the criteria, and other query options.


Save the query and run it. The results of the calculations will be displayed in the dynaset as shown in Figure 3.21. However, this field is not added to the underlying table(s) because query results must always be based on the most current data in the database.

  1. Using Total functions        .

With a query, you can analyze all records fields using the inbuilt functions such as Sum, Average, Minimum and Maximum etc. To use the total functions:

  1. Open your query in design view.
  2. Click the Totals button on the query toolbar.
  3. Select the field you want to analyze.
  4. For each field to be analyzed, click its cell in the Total row, and then select any of the functions as shown in Figure 3.22.

Sum: Adds all the numerical data items.
Avg; Calculates the mean of all numeric data items in the field column.
Min: Returns the minimum value from the field column. Max: Returns the maximum value from the field column. Count: Returns the number of items field column.

  1. Set criteria and other options then click run to preview the results
  2. Save the query.

Printing a query:

  1. Open the database window of the database containing the query you want to print
  2. Click the query tab then the query you want to print.
  3. From the file menu, click print Set the printing options then click the OK. Button.

 

Creating a select query from multiple tables

When you use fields from more than one table in a query, there should be a common link or relationship between the tables. There are three type of relationships

  1. One – to – One
  2. One -to – Many
  3. Many -to – Many

 

In a one -to – one relationship, for a particular field in one table (the main table), there is only one matching record in the related table and vice versa. In a one -to – many relationships, for a particular field in one table there are several matching records in another table. This is the most common type of relationship. For example, a person may make one order, or several orders. For this instance, there is only one record with that person’s details say in customers’ table, yet there are several records related to the same person in the Orders table.
In a many -to – many relationship, for particular records in one table there are several matching records in the other table and vice versa. This is not very common.

Defining relationship between tables

  1. Make sure your database is open.
  2. From Tools menu, choose Relationships. The relationship dialog box appears.
  3. Select the tables to add then click the Add button. .
  4. To create relationship, click on the common field and drag it to the second table. The fields used to create the relationship must be of the same type and properties.
  5. To Enforce Referential Integrity, right click the line joining the two tables then click Edit. An Edit relationship dialog box such the one in Figure 3.23 will be displayed.
  6. Make sure Enforce Referential Integrity is checked to ensure that all records entered in the related table exists in the primary table.

 

  1. Click OK to close the Edit relationship dialog box.

To create a query based on related tables

  1. Start the database window and click the Queries tab,
  2. Select design view and click OK.
  3. From the show table dialog box, click the tables you want to base your query on and then click Add.
  4. Relationship between various tables will be displayed as shown in Figure 3.24. Add the fields from both tables into the QBE grid.
  5. Save and Run the query.

 

Creating reports

Reports are used to summarize and present information from a database. A good database software should enable the user to generate database reports and print them for presentation and distribution. This process is referred to as formal presentations. As with forms, a report layout is also designed by placing controls on to the report layout grid as shown in Figure 3.25.

Parts of a report layout in design view

Report Header-This contains unbound controls that displays title of the report.
Page Header – Contains heading or labels data items to be displayed in every column.
Detail – Holds bound controls that display data items for the table or query it was created from.
Page footer holds a control that is to be displayed on every page such as the page number and date. For example =Now() displays the current date and time as set in the system clock.
Report footer – Used to display summary from a report such as the grand total for numerical data in a particular field column.

Creating a report using report wizard

Just like the form wizard, report wizard takes the user through a number of steps by answering a few questions and Ms Access automatically does the rest.

To create a report layout:

  1. Ensure that the database is open.
  2. Click the Report tab, then New
  3. From the/New Report dialog box, click Report Wizard then
  4. Select a table or a query then the fields to be added to the report then click next.
  5. The wizard asks you whether you wish to add any group. Grouping is used to categorise records using a particular field for better presentation or if you need to perform calculations on a

 

group of common items. For example, to categorise books in a library database according to ISBNNumber, double click the field then click Next. (Figure 3.26).

  1. In .the dialog box that appears, select the Sort option if you want to sort the records. You can also click the Summary options button in case you want Ms Access to perform calculations on numerical fields. In summary options dialog box, select the Summary options you want performed e.g. Sum, Average etc. as shown in Figure

3.27. Click OK to close the summary options dialog box then Next.

  1. From the layout dialog box, select the type of layout such as stepped, block etc. Select page orientation i.e. either portrait or landscape then click Next.
  2. In style dialog box, specify the report style by selecting either

bold, casual etc.

  1. Finally enter the name of your report then click finish. The report will be displayed on the screen in print preview mode.

Creating a report in design view

Just like with forms, you can create a report in design view by placing controls on the report design grid.

To design a report in design view:

.’

  1. In the database window, click the Reports tab then New.
  2. In the New Report dialog box, click Design view.
  3. Click the name of the table or query you want to generate a report from.
  4. Click the OK button. You will get a report design grid where you can place data controls.
  5. From the view menu, click Field List.
  6. To design the layout, drag each field from the field list to the layout grid and drop it where you want the data column to appear.
  7. Once you finish placing controls, click the Save button.
  8. In the save as dialog box, enter the name of the report and click OK.
  9. To view the report, click the Print Preview button. Alternatively, click Print Preview from the file menu.

 

Modifying a report layout

To modify headers and footers:

  1. Open the report in design view.
  2. Click the report header or footer you want to modify.
  3. Make the necessary changes and then click the Save button.
  4. Click the Print Preview button to view the changes.

To add more controls onto the report layout:

  1. Open a report in design view.
  2. Display the field list by clicking the Field list button or using the View.
  3. Select one or more fields in the field list and drag view to the Report design grid.

To resize or move a control:

  1. Click at once to select it. Position the mouse pointer to on the place holder, until the mouse pointer changes to a double-sided arrow. Drag the pointer to resize the control
  2. To move a control, select it and place the mouse pointer on the place holder until it changes to a hand.

Creating labels

A label is a sticker or piece of paper put on an item for the purpose of identification. Examples of stickers are mailing labels, label on the floppy disk where you write your name etc. Using the report label wizard, Microsoft Access lets you easily create labels of different sizes.
To create a label using the report wizard,

  1. Open the your database
  2. From the database window, click the Report tab then New
  3. From the New Report dialog box, select Label Wizard the table or query from which the labels are to be generated from.
  4. Label wizard starts running as shown in Figure 3.28.
  5. From a series of dialog boxes displayed, specify the label size, font, and fields to be included in the label, whether to sort the labels, the name of the label then click Finish.

 

Modifying labels

Just as you can modify a report or a form, you can also modify a label by manipulating the layout controls.
To modify a label,

  1. From your database window, click the Report tab then the Design View button.
  2. The label design grid is displayed. Edit the layout as desired, save and close the design grid.
  3. To view the modified label, click the Preview button from the database window.

Printing the report and labels

Before you print a report or a label, you should first set the page options i.e. the margins, paper size and orientation.

  1. Open the database that contains the report you want to print.
  2. Click the Report tab, select the report you want to print then click the Preview button.
  3. From the File menu, click Print.
  4. Set the printer options i.e. the printer type, print range and number of copies.
  5. Click OK to print.

 

DESKTOP PUBLISHING (DTP)

Chapter outline

 

    1. Introduction.
    2. Definition of desktop publishing.
    3. Purpose of desktop publishing.
    4. Types of desktop publishing software.
    5. Designing a publication.
    6. Manipulating text using the toolbox
    7. Editing a publication.
    8. Formatting a publication.
    9. Printing a publication.

Introduction

Have you ever asked yourself how newspaper and book publishing companies manage to produce publications that are attractive with creative layouts and graphics in large volumes? They do this through a special process called publishing. Publishing is the process of producing publications like newspapers, cards, pamphlets, pictures, calendars etc. that have special text and graphical layouts and designs. The traditional method of publishing involved drawing and writing manually on wooden or metallic boards. The artwork would then be painted with ink and pressed on papers to produce a printout. This was a very tedious work.

Today the art of publishing has changed because of the advent of personal computers and smaller printers that are able to print high quality text and graphics even in an office or at home.

Definition of desktop publishing (DTP)

Desktop publishing refers to the process of producing publications like cards, newspapers etc. by designing their text and graphics layout and inserting, editing, formatting and printing of text and graphical objects using a special desktop publishing software installed on computers. The term desktop means that all the publishing processes can now be done on a desk in the office or at

 

home using a personal computer! The difference between a desktop publisher and a word processor is that a desktop publisher software gives the user more tools and control of the page layout, text manipulation and graphic design than a word processor.

However it is important to note that some word processors today have also been incorporated with text and graphic formatting and editing tools. Hence a thin line exists between them and the desktop publishers. For example you can design a full publication in Microsoft Word by using the available drawing and picture tools, create columns etc.

Examples of desktop publishers include Adobe PageMaker, CorelDraw, Microsoft Publisher and Ventura. This book will use Adobe PageMaker to demonstrate the concepts of desktop publishing.

Purpose of desktop publishing software

Desktop Publishing programs are very popular in publishing because they give the person designing publications (typesetter) a lot of control on:
Graphic design: Using desktop publishing software, a typesetter can create and edit very complex text and graphical objects like pictures to the finest details. For example, the software provides the user with rulers and ruler guides that can be used to place an object in exactly the correct position.
Page layout design: With a desktop publisher, the user can be able to design a page layout by setting consistent picture and object locations, dividing a page in a number of columns and creating layers. A layer can be viewed as the arrangement of objects on top of each other with the one on top being on the first layer e.g. text can be on one layer while graphical objects on another. Therefore the text layer can be manipulated separate from the graphical object layer like for example text moves separate from the objects. Another good example is you can make the graphical object layer transparent in order to make the text in a layer behind the object visible.

 

Printing: Because the main purpose of any desktop publisher is to produce publication s, this cannot be accomplished without printing. Desktop publishing software therefore helps the user prepare what is referred to as an artwork in commercial circles for printing.

Types of desktop publishing software

There are two main types of desktop publishing software:
Graphical based: They are specifically developed to edit and format graphic objects like pictures. Examples are Adobe Photoshop, Corel Draw, harvard graphic etc. This desktop publishers have superior image handling capabilities like setting resolution, brightness, contrast, cropping and filling images of all types with colour.

Layout based: These types of desktop publishers are specifically developed to create different page layout designs for text and pictures. Have you ever wondered how the complicated front page of a newspaper is developed? It is difficult to achieve such without a good layout based DTP. Good examples of layout based desktop publishers are Adobe PageMaker and Microsoft Publisher.

Designing a publication

Publications vary in design and formatting. For example, a card has a particular design layout that is different from a newspaper. Therefore, publication designs determine how they are classified.

Types of publications

It is possible to produce different types of publications using a desktop publishing software. There are several types of publications that can be published namely:
Cards: These may be for weddings and other special occasions like
graduations and for congratulations.
Certificates: To show completion of courses and special events. These type of publications are very sensitive because they must be designed to discourage any type of duplication or counterfeits.

 

Newspapers, magazines, pamphlets and newsletters: Have news targeting a group of people. They may have many text and graphical layouts and designs.
Books: Are bulky publications with many pages bound together in the same publication.
Calendars: Apart from showing dates, they are used by companies to advertise their products hence most of them have heavy catchy graphics.

Running Adobe PageMaker

Currently PageMaker versions 6.5 and 7.0 are the most common desktop publishing software developed by Adobe PageMaker. Other Adobe products includes Adobe Photoshop mainly used for editing and formatting photographs, Adobe illustrator used for creating complex freehand artwork such as drawings and logos etc.

Starting Adobe PageMaker

  1. Click the Start button and then point to Programs
  2. From the programs sidekick menu, point to Adobe group then select PageMaker. Figure 4.1 shows how to start PageMaker in a typical Windows environment.
  3. The PageMaker program starts and provides the Document Setup dialog box. If the Document setup dialog box is not displayed, then click File menu and select the New command.
  4. In the Document Setup dialog box similar to the one in Figure

4.5 specify the publication setup options.

  1. Click OK to apply the selected options. A publication window similar to the one in Figure 4.2 is now displayed.

PageMaker screen layout                                 .

The PageMaker application window looks like most application windows for programs you have used before. It also has a title bar, menu bar and scroll bars. However, you will note that unlike most of the Microsoft applications you have used before, Adobe PageMaker does not have a status bar on its application window. The most notable difference between a desktop publishing software such as PageMaker and a word-processing software such

 

as Microsoft Word is that a desktop publishing software has its printable work area centered on a pasteboard as shown in Figure 4.2.
The PageMaker publication window is made up of the following layout components:

  1. The pasteboard. 3. The toolbox. 5. Rulers

 

  1. The printable area. 4. Master pages.

6. Control pallete.

The pasteboard

A pasteboard is a large blank area where you place text and graphical objects before arranging them neatly on the printable work area enclosed with margins.

The printable area

It is the area that looks like a page surrounded by margins on the pasteboard. Any object or text that is placed on the printable area will be printed by the printer.

The Toolbox

This is a set of buttons that contains various tools you can use to create and manipulate your publication it has the following tools

 

Line tool Rectangle tool Ellipse tool Polygon tool Hand tool Pointer tool Rotate tool Text tool Cropping tool
Constrained tool Rectangle frame tool
Ellipse frame tool

2019 MOKASA English Paper 1

2017 KCPE

Free Statistics 2 Tutorial

2019 MOKASA Computer Studies Paper 1

form 3 biology notes

revision notes biology form 1

form 4 biology notes

2018 KCSE Past Papers

Elearning System

Form 1 Computer Notes

Exit mobile version