ECDL Form4 JL

ECDL Syll abus 5.0

ECDL Co r e 2012

ECDL Syllabus 5 Courseware

Form 4

Contents MODULE 1 CONCEPTS OF INFORMATION & COMMUNICATION TECHNOLOGY . 1 OPERATING SYSTEM SOFTWARE ............................................................................. 1 APPLICATIONS SOFTWARE .................................................................................... 1 ENHANCING ACCESSIBILITY .................................................................................. 2 Activity 1.............................................................................................. 3 SECURITY .................................................................................................................................................. 3 INFORMATION SECURITY ...................................................................................... 3 COMPUTER VIRUSES........................................................................................... 4 LAW .............................................................................................................................................................. 6 COPYRIGHT ..................................................................................................... 6 DATA PROTECTION LEGISLATION ............................................................................ 7

MODULE 3 WORD PROCESSING ...................................................................... 9 MAIL MERGE ................................................................................................... 9 PREPARING THE MAIN DOCUMENT ........................................................................... 9 PREPARING THE DATA SOURCE FILE....................................................................... 10 Activity 2............................................................................................ 16 MERGING DATA .............................................................................................. 17 Activity 3............................................................................................ 18 MODULE 4 SPREADSHEETS........................................................................... 19 CREATING & SELECTING CHARTS .......................................................................... 19 CHANGING THE CHART TYPE ............................................................................... 20 MOVING, RESIZING & DELETING A CHART ............................................................... 21 Activity 4............................................................................................ 22 ADDING, EDITING & REMOVING CHART TITLES ......................................................... 23 ADDING DATA LABELS ...................................................................................... 24 CHANGING CHART COLOURS ............................................................................... 24 PRINTING A SELECTED CHART ............................................................................. 24 Activity 5............................................................................................ 25 MODULE 5 USING DATABASES ..................................................................... 25 UNDERSTANDING DATABASES.............................................................................. 25 DATABASE ORGANIZATION ................................................................................. 25 OPENING & CLOSING MS ACCESS ........................................................................ 27 OPENING & CLOSING DATABASE FILES ................................................................... 28 CREATING NEW DATABASES ............................................................................... 30 Activity 6............................................................................................ 31 MINIMISING & RESTORING THE RIBBON .................................................................. 32 USING HELP .................................................................................................. 32 OPENING, SAVING & CLOSING DATABASE OBJECTS .................................................... 33 CHANGING BETWEEN OBJECTS & VIEW MODES .......................................................... 34 Activity 7............................................................................................ 34 CREATING A NEW TABLE .................................................................................... 35 SETTING THE PRIMARY KEY ................................................................................ 37 Activity 8............................................................................................ 38

i

DELETING A TABLE, QUERY, FORM & REPORT ........................................................... 39 NAVIGATING BETWEEN RECORDS IN A TABLE, QUERY OR FORM ....................................... 39 SORTING RECORDS.......................................................................................... 40 ADDING & DELETING TABLE RECORDS.................................................................... 41 42 CHANGING THE COLUMN WIDTH ........................................................................... 42 Activity 9............................................................................................ 42

ii

ECDL Syllabus 5 Courseware

Form 4

APPLYING FIELD PROPERTY SETTINGS .................................................................... 43 INDEXING FIELDS ............................................................................................ 49 ADDING FIELDS TO TABLES ................................................................................ 50 Activity 10 .......................................................................................... 50 CREATING & RUNNING QUERIES ........................................................................... 51 ADDING CRITERIA USING COMPARISON OPERATORS ................................................... 54 Activity 11 .......................................................................................... 55 CREATING FORMS ........................................................................................... 56 ENTERING, MODIFYING & DELETING RECORDS IN FORMS .............................................. 59 ADDING & MODIFYING TEXT IN FORM HEADERS & FOOTERS .......................................... 59 Activity 12 .......................................................................................... 60 CREATING REPORTS ......................................................................................... 62 CHANGING THE ARRANGEMENT OF DATA FIELDS & HEADINGS......................................... 65 MODIFYING REPORT HEADERS & FOOTERS ............................................................... 66 Activity 13 .......................................................................................... 66 MODULE 6 PRESENTATION........................................................................... 69 INSERTING GRAPHICAL OBJECTS IN MASTER SLIDES ................................................... 69 ADDING FOOTER TEXT & AUTOMATIC SLIDE NUMBERING .............................................. 71 Activity 14 .......................................................................................... 72 CREATING CHARTS .......................................................................................... 72 SELECTING CHARTS & SETTING CHART TYPES ........................................................... 73 ADDING, REMOVING & EDITING CHART TITLES ......................................................... 75 ADDING DATA LABELS ...................................................................................... 76 CHANGING CHART COLOURS ............................................................................... 76 Activity 15 .......................................................................................... 77 CREATING ORGANISATION CHARTS ....................................................................... 78 ADDING & REMOVING CO-WORKERS & SUBORDINATES ................................................ 79 CHECKING THE PRESENTATION SPELLING ................................................................. 80 PRINTING ..................................................................................................... 81 Activity 16 .......................................................................................... 83

Courseware compiled by James Cilia 2012

iii

ECDL Syllabus 5 Courseware

Form 4

Module 1 Concepts of Information & Communication Technology

Operating System Software Systems software is a program that controls the computer and enables it to run applications software (discussed further down). Systems software allows the computer to manage its internal resources.

This software is designed to allow the computer system to manage its own resources (disks, monitor, keyboard, and printer). This software runs the basic computer operations - it tells the hardware what to do and how, and when to do it. Applications software cannot run without system software.

There are several brands of system software: MS Windows is the most common operating system on Computers. MS Windows provides an easy interface between the computer and the user. It uses pictures (graphical representations) which look like push buttons on the screen and you can use the mouse to press them. This interface is known as a Graphical User Interface (GUI).

MS Windows comes in a variety of versions. It has been updated over the years to make it more powerful and easier to use.

Macintosh Operating System (Mac OS) is the standard operating for Apple Corporation s Macintosh computers.

Like Windows, the MAC OS has a GUI interface.

Besides, MS Windows and MAC OS there are other operating systems for example Linux, Unix and OS/2 Warp.

Applications Software Applications software are programs that help the user carry out specific tasks on the computer. Such software has been written for a specific application - such as word-processors, spreadsheets, databases and presentation software.

Application software falls into two categories: 1.

Tailor-made (or Custom-written) software is software designed for a particular customer. Typically individual computer programmers or software houses are contracted to develop computerised systems for companies and organisations.

2.

Off-the-Shelf (or packaged) software is software designed for use by the general public. Typically off-the-shelf software is available from all software selling shops. Large software houses develop this software.

1

Off-the-shelf software includes: 1.

Word-processing programs (e.g. MS Word, Writer) based documents such as letters, memos, reports etc.

used to prepare text-

2.

Spreadsheet programs (e.g. MS Excel, Calc) used to analyse and summarise numerical data. Spreadsheets are commonly used in accounting environments to prepare balance sheets and financial reports.

3.

Database programs (e.g. MS Access, Base) used to organise and manage large quantities of data. Databases enable efficient manipulation of data.

4.

Presentation graphics programs (e.g. MS PowerPoint, Impress) used to organise text and numeric data in an appropriate format to be displayed to a group of people. Typically presentations are used in the preparation of onscreen displays, overhead transparencies and 35mm slides.

5.

Photoediting programs (e.g. MS PhotoEditor, Adobe PhotoShop, GIMP) used to alter images and graphics. These programs are used to change the size of pictures, crop pictures, adjust the colours of pictures etc.

6.

Desktop publishing programs (e.g. MS Publisher, Adobe InDesign) used to prepare high quality printed material e.g. flyers, invitations, posters, reports, magazines, and books.

7.

Internet Web Browsers (e.g. MS Internet Explorer, Mozilla Firefox, Opera) used to locate and display information at Web sites. Browsers display Web pages with text, graphics, sounds and video-clips.

8.

Communications software (e.g. MS Outlook, Mozilla Thunderbird) used for the transmission of electronic messages or documents between different computers.

Enhancing Accessibility Persons with special needs make use of specific hardware and software to help them work on a computer. Such hardware and software is often referred to as assistive technology.

Voice recognition software is a program used to convert spoken words to text. The term "voice recognition" is sometimes used to refer to speech recognition where the recognition system is trained to a particular speaker.

These programs are often used by persons with visibility impairment.

Screen reader is a program that reads the contents of a computer screen aloud to a user. Screen readers are used primarily by visually impaired persons.

Screen magnifier is a program that magnifies a portion of the computer screen, so that it can be more easily viewed. Screen magnifiers are used primarily by individuals with partial visual impairment.

2

ECDL Syllabus 5 Courseware

Form 4

On-screen keyboard is a program that displays a virtual keyboard on the computer screen that allows people with mobility impairments to type data by using a pointing device or joystick. Besides providing a minimum level of functionality for some people with mobility impairments, on screen keyboard can also help people who do not know how to type.

Activity 1 1.

Distinguish between operating systems software and applications software.

2.

Name two common operating systems software.

3.

List 5 categories of applications software.

4.

Briefly explain three types of assistive technology.

SECURITY Information Security In the IT environment security is concerned with the protection of hardware, software and data. There are two main categories of computer security: physical security and data security.

Physical security is concerned with protecting hardware from possible human and natural disasters. The following are some important measures to protect computer equipment: a. A clean dust-free environment safeguards computer equipment. Cover your equipment during office maintenance periods. Dust tends to get sucked into the system unit and damages electronic components. b. Ensure that the work place is well ventilated. Do not block/cover openings at the back of the system unit or monitor. This openings or ventilation holes safeguard against overheating of equipment. Avoid smoking. c. Computer equipment should be positioned on a stable, vibration free surface. Do not move the system unit while this is switched on. Doing so can damage the hard disk. d. Avoid plugging or unplugging cables while the computer is switched on.

Besides damaging the equipment, this constitutes a health hazard. e. Electrical surges (fluctuations in voltage) tend to damage computer equipment. Power surge protection devices can safeguard for electrical fluctuations. The UPS (discussed further down) also protects the computer equipment against power surges. f.

Avoid eating or drinking next to computer equipment. Crumbs and spilt drinks will damage computer equipment.

3

g. The computing equipment should be locked when not in use. Theft of a desktop/laptop computer, PDA etc may possibly lead to misuse of confidential files, loss of data files, loss of important contact details etc.

Data security is concerned with protecting software and data from unauthorised tampering or damage. IT departments often attach more importance to data protection rather than hardware protection. Recovery of lost data is often more expensive than replacing damaged hardware. The following are some important measures to protect unauthorised access to data or loss of data: a. Restricting access Sensitive data should be safeguarded against unauthorised access. In a network environment, the system administrator provides a unique user ID and a password to each computer user. The user ID and password are needed to logon to the networked computer.

Home users are able to set up a start-up password through their operating system. You should also set a password to unlock your screen saver. You can also set password protection to data files.

You should make use of passwords that are easy for you to remember but different for others to guess. Typically, passwords should be alphanumeric i.e. consisting of a mix of alphabet letters and numbers.

Avoid nicknames, date of birth, phone numbers etc. Passwords should be changed regularly. b. Backups Backing up data is the copying of data files to a secondary storage medium (USB flash disk, CD/DVD or magnetic tape streamer) as a precaution in case the first medium fails.

Most users store large quantities of data on the hard disk without backing this on other storage media. It is of utmost importance to backup your data regularly. It is suggested that you make two backups of all your data files.

To be especially safe, you should keep one backup in a different location from the other off-site storage. The latter protects data against theft and fire hazards.

You can back up files using operating system commands, or you can buy a special-purpose backup utility (program). Backup programs often compress the data so that backups require fewer disks. c. Shutting down the computer properly Data can be lost/damaged if you switch off the power supply without going through the proper procedure of switching off your computer.

Occasionally the power supply may go off. This will result in loss of data, which have not been saved. Ideally your computer equipment should include an uninterruptible power supply (UPS) device. The UPS is a unit that contains a rechargeable battery. It keeps a computer running for several minutes after a power outage, enabling you to save data and shut down the computer properly.

4

ECDL Syllabus 5 Courseware

Form 4

It is also recommended that you use the automatic save features that are available in most application programs. For example, you can set MS Word to automatically save your work every 10 minutes.

Computer Viruses A computer virus is a piece of program designed and written to make additional copies of itself and spread from location to location, typically without user knowledge or permission.

Viruses are written by programmers with malicious intent to annoy computer users.

There are different categories of viruses some of these may corrupt or destroy data files stored in the hard disk.

Protecting the Computer System against Viruses 1. Install an anti-virus program in your computer system. Anti-virus programs are able to detect and in most cases remove viruses in the computer system.

Typically anti-virus software alerts you of viral infections. Apart from scanning the hard disk for viruses, you should also scan floppy disks and CDs (except for originals).

2. Update the anti-virus program regularly. Unfortunately, new viruses are being developed all the time. Thus, an anti-virus program that is not updated on a regular basis will not detect new viruses. Nowadays most anti-virus programs can be updated via the Internet.

3. Some e-mail client programs can automatically open Word macros, JavaScript or any other executable code in, or attached to a message. It is strongly recommended that you turn these automatic features off.

4. Treat all files (in particular those carrying .exe and .vbs extensions) attached to e-mails with caution. Just because an e-mail appears to come from someone you trust, this does not mean the file is safe or that the supposed sender has anything to do with it. Do not open attached files before scanning these with updated anti-virus software.

5. Be careful with program or file downloads from the Web. Such programs or files may be infected.

6. Avoid sharing of data files or programs via USB flash disks, CDs/DVDs etc.

Some computer users may unknowingly transmit viruses via disks.

7. Make regular backup copies of all data files present in your computer. As indicated earlier on viruses can damage or destroy data stored in the hard disk.

Backups enable retrieval of lost data.

5

LAW Copyright Computer users should be aware of the copyright issues with regards to software and files such as graphics, text, audio and video. A copyright is the exclusive legal right that prohibits copying of intellectual property without permission of the copyright holder.

Computer software is considered as intellectual property and is protected by the copyright law.

The Internet and the World Wide Web present tremendous opportunities for sharing information but it is important to remember that what is freely available does not imply that it can be copied. You should assume that images, text, logos, software, sounds, movie clips, email and postings to newsgroups are copyrighted. Under copyright law, you cannot copy work/files unless you have been given permission to do so. In some cases, there may be permission statements included with the work/files that allow you to use the work/files for the stated purposes.

Software piracy - is the unauthorised distribution and use of copyrighted computer programs. Software and data files can be easily copied and transmitted via disks.

Making a copy of commercial/propriety software (discussed further down) for a relative or friend is an act of piracy. Unfortunately this unauthorised copying is not helping software houses to cut down on prices for the programs they produce.

Software and data files can also be downloaded from a network and copied. This is known as network piracy.

Propriety software is software whose rights are owned by an individual or business, usually a software developer. The ownership is protected by the copyright, and the owner expects you to buy a copy in order to use it. The software cannot legally be used or copied without permission.

Nearly all applications are licensed rather than sold. There are a variety of different types of software licenses. Some are based on the number machines on which the licensed program can run whereas others are based on the number of users that can use the program. Most personal computer software licenses allow you to run the program on only one machine and to make copies of the software only for backup purposes. Some licenses also allow you to run the program on different computers as long as you don't use the copies simultaneously.

Users have to purchase the appropriate software licences to use propriety software.

There are different types of software licences: 1. Shrink-wrap licences/End User Licence Agreements (EULA) are printed licenses found inside software packages. Users are duty bound to use the software according to the conditions set out in the license sheet. There is no need for users to sign up any contracts with the software house. Users are encouraged to send the registration card included in the package. This registration entitles users to information and minor upgrades that are released from time to time by the software manufacturer.

6

ECDL Syllabus 5 Courseware

Form 4

2. Site licences permit organisations to make multiple copies of a software product for use on different computers within the organisation or institution (e.g. university, government department, company). Usually the license stipulates a maximum number of copies.

Most people make use of commercial software packages produced by Microsoft and Lotus. However there are other categories of software that people can make use of.

Freeware is software that is available free of charge. Although it is available for free, the author retains the copyright, which means that you cannot do anything with it that is not expressly allowed by the author. Usually, the author allows people to use the software, but not sell it.

Shareware is copyrighted software that is distributed free of charge but requires users to make a contribution in order to receive technical help, documentation or upgrades.

Shareware is inexpensive because it is usually produced by a single programmer and is offered directly to customers via the Internet. Thus, there are practically no packaging or advertising expenses.

Open Source is a method of software distribution where a programmer creates a program and makes it available for others to use without cost, as well as modify the source code and redistribute the modifications to the software user/developer community.

Most software installed on your computer system has a product ID associated with it.

Typically product ID may be accessed via the option About in the Help menu of the program.

Data Protection Legislation The ease with which computers can process, store and transfer data (including personal data) has necessitated some form of legislation to protect the privacy of individuals. Computer users dealing with personal data will soon be required to treat this data according to the legal framework outlined in the Data Protection Act. The Data Protection Act (2001) attempts to make provision for the protection of individuals against the violation of their privacy and personal integrity by the processing of personal data.

Data controllers (users having personal data on their computer) should ensure that: Personal data is processed fairly and lawfully; Personal data is always processed in accordance with good practice; Personal data is only collected for specific, explicitly stated and legitimate purposes; Personal data is not processed for any purpose that is incompatible with that for which the information is collected; Personal data that is processed is adequate and relevant in relation to the purposes of the processing;

7

No more personal data is processed than is necessary having regard to the purposes of the processing; Personal data that is processed is correct and, if necessary, up to date; All reasonable measures are taken to complete, correct, block or erase data to the extent that such data is incomplete or incorrect, having regard to the purposes for which they are processed; Personal data is not kept for a period longer than is necessary, having regard to the purposes for which they are processed.

8

ECDL Syllabus 5 Courseware

Form 4

Module 3 Word Processing

Mail Merge Sometimes you may need to send a standard letter to a large number of contacts.

Typically you will address each of these letters specifically to the intended recipient and you may also need to prepare labels with individual recipient addresses to affix to the envelope.

The novice user might type the standard letter, save it and then personalize each of these letters manually by typing the recipient contact details on every single letter.

However, this task is expensive in terms of time especially if the same letter is sent to many recipients.

The mail merge facility in MS Word makes the task of generating mass mailing letters and labels relatively easy.

Preparing any type of merged document typically involves two files: The main document contains the standardised text and graphics to be included on the letters or labels. You insert special instructions, known as merge fields, in this document to indicate where you want the variable information to be printed from the data source file.

The data source file contains the information that varies with each version for example, names, addresses, account numbers etc.

When you merge the data source file and the main document, MS Word inserts the appropriate information from the data file in the main document s standard text.

Preparing the Main Document The first step when you perform a mail merge is creating a main document. If you want to use an existing document as a mail merge main document, open it before you choose mail merge.

To create the Main Document: 1.

2.

3.

4.

5.

6.

Type the main document or letter and save it.

Click the Mailings tab.

Click Start Mail Merge button.

Click Step by Step Mail Merge Wizard... The Mail Merge task pane is displayed.

Tick the option Letters.

Click Next: Starting document (located at the bottom of Mail Merge task pane).

9

7.

8.

Tick the option Use the current document if you want to use the active letter/document displayed on the screen.

Click Next: Selecting recipients (located at the bottom of Mail Merge task pane).

The Mail Merge wizard will prompt you to select the recipients to be later included in the letter (refer to next section).

Preparing the Data Source File As indicated earlier on, the data source file contains the text and graphics that vary with each version of a merged document. The second step when you perform a mail merge is creating a new data source file or use an existing data source file. In this section you will create a new data source file. However, before creating a new data source file, you will learn about some basic concepts of data organisation.

Each set of related information makes up one record in the data file. One record in a person s mailing list, for example, contains all the information for one individual person.

The different types of information title, name, mailing address, father s name, and so on - are called fields. Each field in the data file must have a unique name. In most cases, you list the field names in the first record of the data file, called the header record.

The remaining records in the data file, the data records, contain the field information corresponding to each field name in the header record.

Field Names

Name

Surname

Address1

Address2

Town

John

Borg

44,

Main Street

Qormi

Mary

Vella

St. Philip Street

Zebbug

Il-Bejta

You can go through the following steps to create a new data source file. The following steps continue from the previous section.

10

1.

Tick the option Type a new list if you want to create a new data source file. If you want to use an existing data source file, tick the option Use an existing list.

2.

Click Create below Type a new list. The New Address List dialog box is displayed. This dialog box lists the fields you are likely to use in letters, labels

Header record

Data records

ECDL Syllabus 5 Courseware

Form 4

and envelopes. You can remove any of these fields or add new fields to the data source file.

3.

Click Customise Columns button to remove, add or rename fields in the data source file. The Customise Address List dialog box is displayed.

4.

To remove a field: i. Click the field name to delete. ii. Click Delete button in the Customise Address List dialog box. The underlying message will be displayed. iii. Click Yes button. The deleted field will no longer be displayed in the Customise Address List dialog box.

11

To add a field: i. Click Add button in the Customise Address List dialog box. The Add field dialog box is displayed. ii. Type a name for your field. iii. Click OK button. The fieldname will be displayed in the Customise Address List dialog box.

To rename a field: i. Click Rename button in the Customise Address List dialog box. The Rename Field dialog box is displayed. ii. Type in the new name in the To: field. iii. Click OK button. The fieldname will be displayed in the Customise Address List dialog box.

To arrange the order of field: i. Click the field name to adjust its position in the Customise Address List dialog box. ii. Click Move up or Move down button.

5.

6.

12

Following changes made in the Customise Address List dialog box (step 4), click OK button. The Customise Address List dialog box will be closed. The New Address List dialog box will display the added or renamed fields.

Click OK button. The Save Address List dialog box is displayed.

ECDL Syllabus 5 Courseware

7.

8.

9.

Form 4

Browse to the drive/folder where the file will be saved.

Type in a name for the data source file in the File name: field.

Click Save button. The Mail Merge Recipients dialog box will be displayed.

10. Click OK button to close the Mail Merge Recipients dialog box.

13

Following the creation of the data source file you will proceed with typing in the data:

14

1.

Click Edit recipient list in the Mail Merge task pane or Edit Recipient List button in Mailings tab. The Mail Merge Recipients dialog box is displayed.

2.

3.

4.

Choose the Data Source e.g. Address List.mdb to edit.

Click Edit button. The New Address List dialog box is displayed.

Type in the data in the fields.

ECDL Syllabus 5 Courseware

Form 4

5.

6.

7.

Click New Entry button to type in the data of the next record.

Repeat step 4-5 (in this section) for further record entries.

Click OK button. The following message is displayed.

8.

Click Yes button to save the data and return to the Mail Merge Recipients dialog box. The latter will display all record entries.

Click OK button to close the Mail Merge Recipients dialog box.

9.

To edit a record: 1.

2.

3.

4.

Repeat steps 1-2 as above.

Click the cell containing the data to edit.

Edit the data as necessary.

Repeat steps 7-9 as above.

To delete a record: 1.

2.

3.

4.

5.

6.

7.

8.

9.

Click Edit recipient list in the Mail Merge task pane or Edit Recipient List button in the Mailings tab. The Mail Merge Recipients dialog box is displayed.

Choose the Data Source e.g. Address List.mdb to edit.

Click Edit button. The New Address List dialog box is displayed.

Click the cell containing data to delete.

Click Delete Entry button. You will be prompted to confirm whether you wish to proceed with the deletion or not.

Click Yes button. The record will be deleted from the data source file.

Click OK button.

Click Yes button to save the data and return to the Mail Merge Recipients dialog box. The latter will display all record entries.

Click OK button to close the Mail Merge Recipients dialog box.

15

Activity 2 1.

Start MS Word and open a new blank document.

2.

Save this document as announce.docx in the folder Module 3 Exercises.

3.

Type in the following text: 18th February 2004 Attn.

Joseph Muscat, St. Philip, Main Street, Zebbug ZBG 1010 Dear Joseph, You are requested to call at Administration Building RM 205 to collect your invitation.

Sincerely yours, James

4.

Create a data source file containing the following fields: Name, Surname, Address1, Address2, City, Country, Postcode.

5.

Save the data source file as addresslist.mdb in the folder Module 3 Exercises.

6.

Enter the following data in this file:

Name

Surname

Address1

Address2

City

Country

Postcode

John

Vella

St. Peter

Main Street

Balzan

Malta

BZN3333

Mary

Bonanno

55

Mdina Road

Qormi

Malta

QRM2222

Phil

Costa

Il-Bejta

St. Joseph Street

Msida

Malta

MSD1111

7.

Edit the Surname of the second record from Bonanno to Borg.

8.

Delete the third record. Add the following record instead:

Name

Surname

Address1

Address2

City

Country

Postcode

Mario

Calleja

My Nest

St. Philip Street

Zebbug

Malta

ZBG4444

9.

16

Save and close announce.docx and addresslist.docx. Close MS Word.

ECDL Syllabus 5 Courseware

Form 4

Merging Data Once you have created the main document and attached to it a data source file it is very easy to perform the merge process. To tell MS Word where you want variable information printed, you insert the merge field names defined in the attached file.

When you merge the main document with the data file, MS Word replaces the merge field names with the corresponding field information from each record in the data file.

1.

2.

3.

4.

In the main document, place the cursor at the location where the merge field will be inserted.

In the Mailings tab, click Insert Merge Field button.

Click the appropriate field name. This will be inserted in the document.

Repeat steps 2-3 for the field names you need on your document.

«Title» «Name» «Last_Name» «Address_Line_1» «Address_Line_2» «Town» «Post_Code»

5.

Click Preview Results button. The document displays the data.

6.

Click Finish & Merge button.

7.

Click Print Documents to print the letters including the merged data. The Merge to Printer dialog box is displayed.

Tick the appropriate option.

Click OK button to print the letter/s.

8.

9.

Note that: Each merge field starts and ends with these special chevron symbols <<>>. You cannot insert a merge field from the keyboard; you have to use Insert Merge Field button.

Don t forget to include spaces between merge fields if they are separate words and remember the punctuation that needs to appear in the finished document.

If you see a field code such as {MERGEFIELD Title} instead of «Title», select it and press ALT+F9 key combination to display the field result.

17

Activity 3 You will use Module 3 practice files.

1.

In MS Word, open announce.docx (created during Activity 2) in the folder Module 3 Exercises. You will use this document as the form letter for a mail merge.

2.

Use addresslist.mdb (created during Activity 2) in the folder Module 3 Exercises as the data source to be merged with the announce.docx form letter.

3.

Replace the existing name and address lines at the top of the announce.docx document with the appropriate address block.

Joseph Muscat, St. Philip, Main Street, Zebbug ZBG 1010 Dear Joseph, with the following fields: <<Title>> <<Name>> <<Surname>> <<Address1>> <<Address2>> <<City>> <<Postcode>> Dear <<Name>>

18

4.

Merge the address list data source file with the letter to create a mail-merged document showing all the addressees.

5.

Save the mail merge document as merge.docx in folder Module 3 Exercises.

6.

Save and close all open documents. Close MS Word.

ECDL Syllabus 5 Courseware

Form 4

Module 4 Spreadsheets

Creating & Selecting Charts A chart/graph is a graphical representation of the numeric data in a worksheet. Each cell (or piece of data) represented in the chart is called a data point. Data points are represented on the chart by bars, columns, lines, or some other graphical device. A group of related data points is called a data series.

Name

English

Maltese

Maths

Anthony

55

80

74

Maria

63

43

84

Philip

62

57

65

Rita

43

7

95

19

Typically, values are plotted along the vertical plane (y-axis) and categories are plotted along the horizontal plane (x-axis). Labels that run horizontally under the various data series and display the categories represented are x-axis labels. Labels running vertically and listing the value increments are the y-axis labels.

To create a chart: 1.

2.

3.

4.

Highlight the data to be included in the graph.

Click the Insert tab.

In the Chart group, click the type of chart to use e.g. Column, Line, Pie, Bar etc.

Click the sub-type of chart to use.

To select a chart: Click anywhere in the chart. This displays the Chart Tools, adding the Design, Layout, and Format tabs.

Changing the Chart Type You can change the type of chart in a slide: 1.

2.

3.

20

Click the chart in the slide.

Click the Design tab, under Chart Tools.

In the Type group, click Change Chart Type. The Change Chart Type dialog box is displayed.

ECDL Syllabus 5 Courseware 4.

5.

6.

Form 4

Select the type of chart to use e.g. Pie. Click the chart sub-type.

Click OK button.

Moving, Resizing & Deleting a Chart To move a chart in a new worksheet: 1.

2.

3.

Select the chart to move.

Click the Design tab below Chart Tools.

Click Move Chart. The Move Chart dialog box.

4.

5.

6.

Click New sheet: option.

Enter a title for the sheet in the text box provided.

Click OK button.

To move a chart to a different sheet or workbook: 1.

2.

3.

4.

5.

Click Click Click Click Click

the Home tab. anywhere in the chart to move.

Cut (to move). in the sheet or workbook where the chart will be copied.

Paste.

To resize a chart: 1.

2.

3.

Click anywhere inside the chart.

Position the pointer on one of the corner handles. The pointer changes to a double-headed arrow.

Drag the mouse to resize the chart.

To delete a chart: 1.

2.

Click anywhere inside the chart.

Press DELETE key.

21

Activity 4 You will use Module 4 practice files.

1.

In MS Excel, open graphs.xlsx located in the folder Module 4 Exercises.

2.

Create a chart showing the following data: X-data series Y-data series Chart Type Chart Title Category Value Place chart

Student English History 3-D Clustered Column Results Group A Max. 100 marks As new sheet: First Results

3.

Switch to Sheet1.

4.

Create a chart showing the following data: X-data series Y-data series Chart Type Format Chart Title Category Value Place chart

Student English Science Column Clustered Column.

English & Science Results Group A Max. 100 marks As object in: Sheet1

5.

Move the chart created in the previous step such that the top left corner is in cell A13 below the data.

6.

Resize the chart such that the bottom right corner of the chart will be in cell G33.

7.

Delete the chart. Undo the previous command.

8.

Save and close graphs.xlsx. Close MS Excel.

Adding, Editing & Removing Chart Titles To add a chart title: 1.

2.

3.

4.

5.

6.

22

Click the chart to add a title to.

Click the Layout tab, under Chart Tools.

In the Labels group, click Chart Title.

Click Above Chart. A placeholder with the text Chart Title is displayed.

Click in the placeholder.

Edit the text in the title placeholder.

ECDL Syllabus 5 Courseware

Form 4

To edit a chart title: 1.

2.

Click the chart title placeholder.

Edit the text in the title placeholder.

To delete a chart title: 1.

2.

Click the chart title placeholder.

Press DELETE key.

Adding Data Labels You can add two kinds of labels to a chart: Value Labels these indicate the numerical values of the individual data points.

Text Labels these display the names of the data points. By default, Excel already displays these names on an axis.

To add data labels to a chart: 1.

2.

3.

4.

Click the chart to add labels to.

Click the Layout tab, under Chart Tools.

In the Labels group, click Data Labels.

Click the appropriate option.

Changing Chart Colours You can modify the colours of the chart area, plot area and data series: 1.

2.

3.

4.

5.

6.

7.

Click the chart.

Click the Format tab, under Chart Tools.

In the Current Selection group, click the drop down arrow and choose Chart Area, Plot Area, Legend or any data series.

In the Shape Styles group, click Shape Fill.

Select a standard colour or choose More Fill Colours The Colors dialog box is displayed.

Select a colour.

Click OK button.

Printing a Selected Chart To print a selected chart: 1.

2.

3.

4.

Select the chart to print.

Click File tab.

Click Print. The Backstage view is displayed.

In the Settings section, click Print Selected Chart.

23

5. Click Print button.

Activity 5 You will use Module 4 practice files.

24

1.

In MS Excel, open graphs.xlsx located in the folder Module 4 Exercises.

2.

In the First Results sheet, edit the chart title Results to English & History Results

3.

Edit the Value axis title from Max. 100 marks to 100 marks.

4.

Modify the chart such that all columns display value labels.

5.

Apply a light yellow colour to the chart area.

6.

Apply a white colour to the plot area.

7.

Apply a green colour to the red coloured columns.

8.

Save and close graphs.xlsx. Close MS Excel.

ECDL Syllabus 5 Courseware

Form 4

Module 5 Using Databases

Understanding Databases A database is an organised collection of data. We are familiar with many examples of databases that are not computerised. These include telephone directories, address books and TV programme listings. All of these have well organised data referred to as information.

A telephone directory book lists the name, surname, address and phone number of every subscriber. This data is sorted in alphabetical order according to the surname of the subscriber. Accordingly you can only search for a phone number provided you know the person s surname. However, a database program with the same information as that found on a telephone directory enables you to search a phone number using any piece (name, address and phone number) of information, which you know about the person.

Storing a database on a computer: increases the speed with which one can get information; makes it easy to store large amounts of information can be sorted; Typical uses of large-scale databases include airline booking systems, government records, bank account records and hospital patient details.

Database Organization A database file stores data in tables. A table stores and displays related data in a spreadsheet-like format with columns (called fields) and rows (called records).

Each column in a table is called a field and represents a specific piece of data, such as ID card, surname, firstname etc. Fields may contain text, numbers, dates etc.

Fields

Field Names

Record

IDCard

Surname

FirstName

DateOf Birth

Average Mark

PassFail

Phone

Fee

88286M

Abela

Maria

17/12/86

56

Y

21464646

245

78587M

Bonnici

Caroline

03/03/87

87

Y

21464545

245

67386M

Callus

Phyllis

04/04/86

35

N

21464343

245

Data Value

25

To identify each column of data, the first row in the database table contains the field names. Each field name is unique and represents the smallest unit of data.

Each row in a database table is called a record and represents all of the related fields on one line. For example, all of the information about one student, ID card, surname, name, date of birth, average mark, pass/fail, phone and fee make up one record. The diagram shown above displays three records.

Each item of data in a database table is referred to as a data value or data item.

Primary Key

Data type

ID Card

Surname

FirstName

Date Of Birth

Average Mark

Pass Fail

Phone

Fee

88286M

Abela

Maria

17/12/86

56

Y

21464646

245

text

text

text

date/time

number

Y/N

text

currency

The data type determines the kind of data that can be stored in a table field. Data types include text, number, date/time, yes/no, currency etc.

Referring to the above diagram, the data type for the: ID Card field is text because the data consists of a mix of numbers and an alphabet letter; Date of Birth field is date/time; Average Mark field is number; Pass Fail field is y/n. The data values allowed in this field are Y or N ; Phone field is text even though the data value consists of numbers. Typically the number data type is reserved for fields containing numbers that will be used for calculations. It is unlikely that you will perform calculations on phone numbers; Fee field is currency.

The field properties determine how the data in a particular field will be displayed. For example you can determine the format of the Date of Birth field i.e. whether you will enter a long date, short date or medium date. You can also set the maximum number of characters that can be entered in the id card field by setting the field size. You can also set a default value in a field. There are other field properties which will be discussed later on.

A primary key is a field that uniquely identifies each record in a database table. It is a field containing data that is different for every record in a database. Examples of primary keys include identity card numbers, passport numbers, index numbers, and item code numbers.

Indexes are commonly used in books to look for any particular information quickly.

Similarly, databases use indexes to speed up searches on a table using a given field.

An index speeds up searches on the indexed fields as well as sorting and grouping operations. For example, if you search for specific students using the surname field, you can create an index for this field to speed up the search. By default, the primary key field in a table is automatically indexed. However, you can set indexing on other fields in a table.

26

ECDL Syllabus 5 Courseware

Form 4

Opening & Closing MS Access MS Access is an example of a database management system an application that enables you to create and manage a database on a computer. MS Access comes in a variety of versions. Over the years the program has been updated, making it more powerful and easier to use. These course notes are based on MS Access 2010.

To open MS Access: 1.

2.

3.

4.

Click Start button.

Select All Programs.

Click Microsoft Office.

Click Microsoft Access 2010.

On entering MS Access you are presented with a Getting Started with Microsoft Office Access screen. From here you can create a new database, open an existing database, or view content from Microsoft Office Online.

To close MS Access: 1.

2.

Click File tab.

Click Exit.

27

Opening & Closing Database Files To open a database file: 1.

2.

3.

Click File tab.

Click Open. The Open dialog box is displayed.

Select the drive and/or folder (e.g. Desktop) that contains the database to open.

4.

Double-click the database you want to open. MS Access displays the Security Warning.

5.

Click Enable Content button.

Note that: MS Access database files end with the extension .accdb.

You can also open a recently used database file by clicking the File tab and choosing Recent. A sub-menu showing a list of recently used documents is displayed. Click the name of the document you want to open.

You can open a database file using the shortcut key combination: CTRL+O keys and follow steps 4 and 5 as above.

When you open an existing database (or create a new database), the database window will be displayed.

28

ECDL Syllabus 5 Courseware

Form 4

Object list within Tables

Navigation Pane with Database Objects

An Access database contains several components known as objects: A table stores and displays related data in a spreadsheet-like format with columns and rows.

A query is a question you ask about your data that retrieves specific records from one or more tables.

A form is a customised view of the data used to facilitate the entry, viewing or editing of data in tables by displaying one record at a time.

A report is used for designing a printed copy of database information, grouping records into several levels and performing calculations.

To close a database file: 1. Click the File tab.

2. Click Close Database.

29

Creating New Databases To create a new database file:

30

1.

2.

3.

4.

Click File button.

Click New. Click Blank database.

In the Blank database pane (right), type a name for your database in the File Name field e.g. one.

5.

Click Browse to a location to put your database button. The File New Database dialog box is displayed.

ECDL Syllabus 5 Courseware 6.

7.

8.

9.

Form 4

Browse to the drive/folder where the database file will be saved.

In the File name: field type the name of the database file e.g. test.

Click OK button.

In the Blank Database pane, click Create button.

Note that: Database file names can have up to 255 characters including spaces. File names cannot include any of the following characters: forward slash (/), backslash (\), greater than sign (>), less than sign (<), asterisk (*), period (.), question mark (?), quotation mark ("), pipe symbol (|), colon (:), or semicolon (;).

Activity 6 You will use Module 5 practice files.

1.

Start MS Access.

2.

Open the database file first.accdb in the folder Module 5 Exercises.

3.

Close first.accdb.

4.

Open a new (blank) database file. Save this file in the folder Module 5 Exercises using the name second.accdb.

5.

Close second.accdb.

6.

Close MS Access.

31

Minimising & Restoring the Ribbon You can minimise the Ribbon i.e. the row of buttons below each tab: 1.

2.

Right-click on one of the tabs e.g. the View tab.

Click Minimise the Ribbon. This will hide the Ribbon, leaving only visible the Ribbon's tab headers.

Note that: Clicking on any of the tabs will now display the tab's commands, and hide the ribbon once you have clicked on a command, or placed your mouse cursor (pointer) back inside the Access screen.

Using Help 1.

Click Help button. The Access Help window is displayed.

2.

Click one of the main topics e.g. Access 2010 database design basics.

This will display sub-topics. Click the sub-topic to display.

You can type a keyword or question e.g. relationships in the search field.

Click Search button. Click the topic to display.

3.

32

Click Close button to close the Access Help window.

ECDL Syllabus 5 Courseware

Form 4

Opening, Saving & Closing Database Objects As indicated earlier, the database window displays the objects: tables, queries, forms, reports etc.

To open a table, query, form or report: 1.

2.

In the Navigation pane, click All Access Objects drop-down menu.

Click Tables, Queries, Forms or Reports.

3.

Double-click the name of the table, query, form or report to open.

To save a table, query, form or report click the Save button in the Quick Access toolbar.

To close a table, query, form or report click Close button.

33

Changing between Objects & View Modes Each database object has two different view modes: Object

View Modes

Tables

Datasheet view and Design view

Queries

Datasheet view and Design view

Forms

Form view and Design view

Reports

Print Preview and Design view

To switch between view modes in a table or query: 1.

2.

3.

Click the Home tab.

Click View.

Click Datasheet View or Design View.

Note that: The Design View shows the design of the table i.e. the fields and field properties making up a table.

The Datasheet View displays data from a table in a rowand-column format. In Datasheet view, you can edit fields, add and delete data, and search for data. In Access 2010, you can also modify and add fields to a table in Datasheet view.

To switch between view modes in a form or report: 1.

2.

3.

Click the Home tab.

Click View.

Click Form View / Report View or Design View.

Activity 7 You will use Module 5 practice files.

1.

In MS Access, open second.accdb (created in Activity 6) in the folder Module 5 Exercises.

2.

Use the Help facility to find information about tables. Display the topic Introduction to tables.

3.

Use the Help facility to find information about forms. Display the topic Introduction to forms.

4.

Close second.accdb in the folder Module 5 Exercises.

5.

Open first.accdb.

6.

Switch between the Tables, Forms and Reports objects.

7.

Open the table tblBook. continued

34

ECDL Syllabus 5 Courseware 8.

Switch to the Design View mode.

9.

Switch back to the Datasheet View mode.

Form 4

10. Close the table tblBook.

11. Open the form frmBook.

12. Switch to the Design View mode.

13. Switch the Form View mode.

14. Close the form frmBook.

15. Minimise the Ribbon.

16. Restore the Ribbon.

17. Close first.accdb. Close MS Access.

Creating a New Table A database file stores data in tables. You always start your database through the table object. Tables can be created in Design View.

The Design View is used to define the field names, the order the fields will appear in the table, the type of data each field will contain, and the size and format of each field. You can also add field descriptions to help you remember what information should be entered into a field. These descriptions display in the lower left corner of your screen as you enter records in Datasheet view.

To create a table in Design View: 1.

2.

Click the Create tab.

In the Table group, click Table Design. A new window appears for inputting field names, their data types and descriptions.

35

3.

5.

For each row in the table enter the field name, select a data type (read further down to learn about data types) and type a description (optional). Use the TAB key to move from column to column.

Once you enter all field names, data types and descriptions, select a field and set this as the primary key. (Read further down to learn how to set the primary key).

When you finish click Save button. The Save As dialog box is displayed.

6.

7.

In the Table name: field type a name for your table.

Click OK button.

4.

Note that: In a table, a row represents a record. In Design view a row represents one column in the table. For example, if in Design view you name a row Index No , the table will have a column called Index No and each record in the database will need to have an Index No field entry.

When you save your table, remember to start its name with tbl . This naming convention will later help you to identify this database object as a table.

Field Names Listed below are some general rules for naming your fields: Use any combination of letters, numbers, and special characters, except a period (.), exclamation mark (!), accent mark (`), or brackets ([]).

Use a maximum of 64 characters.

Choose a unique name for each field.

Data Types For every field name entered in the first column of the Design view window you need to specify its data type. The data type determines the kind of data that you can store in a field. MS Access provides you with a list of data types to choose from. The default data type is text . The following table summarises the field data types to be used during this course.

Data Type

Description

Examples

Storage Size

Text

Allows you to enter alphabetic or numeric data. Typically used where there is a limit on the amount of data. No calculations can be done on numeric data entered as text data type.

Name, postcode, address & telephone number.

Up to 255 characters.

Number

Allows you to enter numeric data that may be used for calculations.

Quantity in stock, amount sold & marks.

1, 2, 4, 8 or 16 bytes.

36

ECDL Syllabus 5 Courseware

Data Type

Form 4

Description

Date/Time

Allows you to enter Date or time data.

Yes/No

Allows you to enter yes/no, true/false or on/off values to a field.

Examples Date of birth, Production & expiry date, time lap.

Promoted, Married.

Storage Size 8 bytes.

1 bit.

To choose a data type for a field: 1.

2.

3.

In Design view, tab to the Data Type column for the desired field.

Click on the drop-down arrow to display a list of data types.

Press the first character of the data type to be used e.g. T for a Text field.

Setting the Primary Key The primary key is the field that uniquely identifies each record in a database table. It is a field containing data that is different for every record in a database. Primary keys prevent duplicate records because the field must contain a unique data item.

To define a primary key field in a table: 1.

2.

3.

In Design view, click the field to define as a primary key.

In the Tools group, click Primary key button. A key symbol will be displayed in the record selector for the field name.

Click Save button.

Note that: By definition, the primary key field cannot contain any duplicate entries. For example, a surname field cannot be defined as a primary key because the surname field may contain the same data values in different records. For many of the tables you design, there will be a field that provides unique values (e.g. identity card number , passport number , index number , and item code number ) that can function as a primary key. If not you can create a field that contains artificially unique values to be your primary key.

Once you define a field as a primary key, MS Access will test the contents of the field each time you add or edit a record. An error message will be displayed if there is a duplicate or a blank entry. The primary key cannot be left blank for any records of the table.

The data present in a table is automatically sorted by the primary key field.

By default, the primary key field (if there is only one key field in a table) is indexed. The index speeds up searches on the primary key fields as well as sorting and grouping operations.

To delete the primary key setting from a field: 1.

2.

3.

In Design view, click the field to remove its primary key setting.

In the Tools group, click Primary key button. The record selector will no longer display the key symbol.

Click Save button.

37

Activity 8 You will use Module 5 practice files.

1.

In MS Access, open the database file second.accdb (created in Activity 6) in the folder Module 5 Exercises.

2.

Create the following table in Design View: Field Name IndexNo

AutoNumber

Surname

Text

Name

Text

DateOfBirth

Description

Date/Time

TelNo

Text

Email

Hyperlink

Fee

Currency

AverageMark

Number

Promoted

Yes/No

Shows the average mark in the annual exams.

3.

Set the IndexNo as the primary key field.

4.

Save the table as tblStudents.

5.

Close tblStudents.

6.

Create another table in Design View within the same database file second.accdb. Use the following field names and data types: Field Name CodeNo

Data Type

Description

AutoNumber

Title

Text

Artist

Text

Price

Currency

Release

Date/Time

Label

Text

Rank

Number

7.

Set the CodeNo as the primary key field.

8.

Save the table as tblMusic.

9.

Close tblMusic.

10. Close second.accdb.

11. Close MS Access.

38

Data Type

ECDL Syllabus 5 Courseware

Form 4

Deleting a Table, Query, Form & Report To delete a table, query, form or report: 1.

2.

In the Navigation Pane, click the table, query, form or report to delete.

Press DELETE key. A warning message will be displayed prompting you to confirm whether you want to proceed with deleting the object or not.

3.

Click Yes button to delete the object.

Navigating between Records in a Table, Query or Form MS Access provides several methods to move between records in a table, query or form.

Click the mouse directly in the cells of the table/query.

Use the navigation buttons in the lower left corner of the Datasheet view.

Moves to the first record

Moves to the last record Moves to a blank record

Moves to the previous record

Moves to the next record

Type a record number to move to

Use the keyboard to move around the table/query.

Do this

Cursor moves

TAB, ENTER or RIGHT CURSOR key

To the next field in the table.

SHIFT+TAB or LEFT CURSOR key

To the previous field in the table.

UP CURSOR key

Up one record.

DOWN CURSOR key

Down one record.

HOME key

To the beginning of a record.

END key

To the end of a record.

CTRL+HOME key

To the beginning of the table.

39

Do this

Cursor moves

CTRL+END key

To the end of the table.

Double click the Record Number box, type the record number and press ENTER key.

To the specified record.

Note that: If you are editing the contents of a field, the HOME, END, LEFT CURSOR and RIGHT CURSOR keys will move the cursor within the characters typed in the field. The entire field must be selected for these keys to move the cursor between fields in the record.

You can navigate within the fields of a record in a form as indicated here: Press

To move

TAB key

To next field in the record.

SHIFT+TAB keys

To previous field in the record.

HOME key

To move to the first field in the record.

END key

To move to the last field in the record.

Sorting Records By default, the records are sorted by the primary key field. You can change the order of records in tables, forms or query results through sorting. Records can be sorted in ascending or descending order by any field.

To sort records in tables, forms or query results: 1.

2.

3.

4.

Click the Home tab.

Display the data in the Form View or table/query Datasheet view.

Click the field you want to use for sorting records.

In the Sort & Filter group, Click Ascending or Click Descending.

Note that: When you sort a field in ascending order, any records in which that field is blank are listed first.

To sort dates and times from earlier to later, use ascending order. Use descending order to sort from later to earlier.

40

ECDL Syllabus 5 Courseware

Form 4

Adding & Deleting Table Records To add records in a table: 1.

2.

3.

4.

Open the table in Datasheet view.

Click New (blank) record button. The cursor will move to the first empty cell after the last record.

Type the data in the fields of the next row.

Repeat steps 2-3 to enter more records.

Note that: As you work with records, the record selector button to the left of the record will indicate the record s status.

Record Selector

Description A new record that you can enter information in.

The record that is being edited; changes are not yet saved.

A blank record will show at the end of the table with an asterisk (*), indicating the location where any new record will be entered. As you add or edit data in a record, a pencil will display, indicating you are entering or editing data that has not been saved. When you move to another record or close the datasheet, the pencil will disappear and the data will automatically be saved. There is no need to save the table unless you have changed its design or layout.

Pressing ESC key will undo the current field. If pressed twice, it will undo the current record.

Occasionally, you may need to delete a record(s) from your database. When you delete a record, or row, from the database, the records below the deleted row are automatically moved up.

To delete a record(s): 1.

2.

Select the record(s) to be deleted by clicking the Record Selector buttons.

Press DELETE key. MS Access displays a warning dialog box prompting you to confirm or cancel the deletion of the record(s).

41

3.

Click Yes button to confirm the deletion or No button to cancel the deletion request.

Note that: When you delete a record this will be permanently deleted i.e. you cannot undo a deleted record.

Adding, Modifying & Deleting Record Data To add data in a record: 1.

2.

3.

4.

5.

Open the table in Datasheet view.

Type the data in the first field.

Press TAB or ENTER key to move to the next field.

Repeat steps 2-3 to enter data in the other fields.

When you reach the end of the first record, press TAB or ENTER key to save the data and automatically move to the next record.

Editing a record is similar to editing text in MS Word or MS Excel. You can insert, delete and replace data in any field.

If you want to add text to a field, click where the new text will be inserted and begin typing. Use the BACKSPACE and DELETE keys to delete characters to the left or right of the cursor. To delete an entire field, select the field and press the DELETE key. To replace text, select the text to be replaced and start typing.

If you make a mistake as you are editing and the changes have not been saved yet (i.e. the pencil icon shows in the Record Selector button), click the UNDO button or ESC key to cancel your changes. Pressing ESC key once will cancel changes made to the current field. Pressing ESC key twice will cancel all changes made in the record.

42

ECDL Syllabus 5 Courseware

Form 4

Changing the Column Width You can change the column size to accommodate a field heading or field entry in the Datasheet view: 1.

2.

Position the mouse pointer over the right edge of the column heading for the column you want to resize. The pointer changes to a double-headed arrow.

Drag the pointer to the right to increase the column width or drag it to the left to decrease the column width.

Note that: If you double-click over the right edge of the column heading, the width of that column will change to fit the longest entry in the field.

Activity 9 You will use Module 5 practice files.

1.

In MS Access, open second.accdb (created in Activity 6) in the folder Module 5 Exercises.

2.

Open the table tblStudents in Datasheet View.

3.

Type in the following data Field Name

Record 1

Record 2

Record 3

IndexNo

1

2

3

Surname

Cilia

Mifsud

Camilleri

Name

John

Maria

Felicita

DateOfBirth

17/11/87

11/10/87

12/12/87

TelNo

21464646

21454545

21444444

Email

jcilia@ecdl.com

mmifsud@ecdl.com

fcamilleri@ecdl.com

245

245

245

66

55

32

Fee AverageMark Promoted 4.

Edit the data as follows: Record 1

TelNo

From: 21464646 to 21434343

Record 2

Name

From: Maria to Marianne

5.

Adjust the width of the Email field such that all email addresses will be visible.

6.

Save tblStudents.

7.

Delete record 3 from the table tblStudents.

8.

Close tblStudents.

9.

Close the database file second.accdb. Close MS Access.

43

Applying Field Property Settings Apart from specifying different data types for each field in your table, you can also set the field properties to customise the appearance or contents of a field.

An example of a field property is the size of the field. Suppose, for example, you are entering local postcodes of the format ZBG2409 or VCT1030. Because you know that a local postcode is not going to be over 7 characters, you set the field size property to 7.

As another example, suppose you are entering exam marks. Because you know that an exam result will not exceed 100, you set the field size property to Byte. The Byte property accepts numbers in the range 0 to 255.

The properties and options available for a field are dependent on the field data type.

For example, a Date/Time field does not have a Field Size property.

Field Properties for a field with a text data type.

The following table summarises the field properties that will be used during this course: Field Property

Use

Field Size

Limits a text field to a certain size, or limits a number field to values within the data type s range.

Format

Displays dates, text, and numbers in a specific display format.

Caption

A name to be used as a column heading in a datasheet or as a label on a form or report. The field name is used as the default label for a field if no caption is specified.

Default Value

Automatically inserts this value in all new records.

44

ECDL Syllabus 5 Courseware

Form 4

Field Property

Use

Validation Rule

Limits data entered to a specific value that meets a certain criteria.

Displays a dialog box if an acceptable value is not used.

Validation Text

Used to display a customised error message when the validation rule is violated.

Required

Can be Yes or No. If Yes, data is required in the field and cannot be left blank.

Indexed

Simplifies sorting. Also can be used to prevent duplicate values from being entered in a field.

Changing the Field Size The Field Size property limits the size of a field. You establish a field size to ensure that the data entered in a field does not exceed a specified size.

Field size options are different for text and number fields: For a text field, you can type a number to indicate the maximum number of characters that can be entered in the field. For example, using the field properties, you can change the size of a phone number field to 8 characters.

The default size for a text field is 50 characters with a maximum field size for a text field of 255 characters.

For a number field, the field size indicates the range of values and whether the field can contain fractional values. The default field size for a number field is long integer , which means that the field must contain a whole number.

The following field size options are available for number fields: Field Size Label

Accepts

Storage size

Byte

0 to 255

1 byte

Integer

32,768 to 32,767

2 bytes

Long Integer

2,147,483,648 to 2,147,483,647

4 bytes

Single

Any number with up to 7 decimal places

4 bytes

Double

Any number with up to 15 decimal places

8 bytes

Decimal

Any number with up to 28 decimal places

12 bytes

To change the field size in a table: 1.

In Design view, click the field to set its field size.

2.

In the Field Properties (lower pane), on the General tab card, click the Field Size box.

3.

Change the field size: a. For a text field, type the number of characters that will be allowed in the field. b. For a number field, click the drop-down arrow to the right of the field size label. Select the appropriate field size property (refer to the table above).

4.

Click Save button.

45

Note that: It is advisable to consider the data to be entered in a field and set the field size property accordingly. Using a field size larger than needed can greatly increase the size of the database file.

If your table already contains data and you reduce the field size, the following dialog box will be displayed. If you decrease the size of the field that contains data, this will be truncated if it exceeds the new width of the field. Click Yes or No button as appropriate.

Changing Number Field Format For number and currency there are several formatting options. For the number field, the General format is automatically selected. For the currency field, the Currency format is automatically selected. The following format options are available for Number and Currency fields: Format Property

Displays

General

Displays numbers as they were entered e.g. 7000. This is the default for Number fields.

Euro

Displays as currency and uses the Euro symbol .

Fixed

Displays two decimal places e.g. 7000.00.

Standard

Displays comma and two decimal places e.g. 7,000.00.

Percent

Multiplies the value by 100 and shows % e.g. 50%.

Scientific

Displays standard scientific notation.

To change the field format of number or currency fields: 1.

2.

3.

46

In Design view, click the number or currency field to set its format.

In the Field Properties (lower pane), on the General tab card, click the Format box.

Click the drop-down arrow to the right of the Format label. Select the appropriate format property (refer to the table above).

ECDL Syllabus 5 Courseware 4.

Form 4

Click Save button.

Note that: The percent format multiplies the number entered by 100 and adds the % symbol. If you type the number 7, this will be displayed as 700%. To enter 7% type 0.07% or 7%.

Changing Date/Time Field Formats The format of a date/time field can include only the date, both the date and time, or only the time. MS Access provides several date/time options to choose from.

Format Property

Displays as

General

If no time is entered, only the date is displayed. If no date is entered, only the time is displayed. This is the default and is a combination of the Short Date and Long Time settings.

Long Date

Wednesday, December 17, 2003

Medium Date

17-Dec-03

Short Date

17/12/03

Long Time

09:43:30 PM

Medium Time

09:43 PM

Short Time

09:43

Mm/dd/yyyy

17/12/03 will display as 17/12/2003

To change the field format of date/time fields: 1.

2.

3.

4.

In Design view, click the date/time field to set its format.

In the Field Properties (lower pane), on the General tab card, click the Format box.

Click the drop-down arrow to the right of the Format label. Select the appropriate format property (refer to the table above).

Click Save button.

47

Applying & Modifying Default Values In addition to changing the field sizes and formats, you can also use the field properties screen to set default field values.

Default field values are values that automatically fill in a field in new records. You can either leave the default value, or change the data to a new value.

Defining a default value for a field simplifies data entry when a certain value is entered frequently. For example, in a student table, you might have a field for Town. If most of the students come from Zebbug, you could set the default value to Zebbug. Then you will only enter data in this field if the town is other than Zebbug.

1.

2.

48

In Design view, click the field that will have a default value applied to it.

In the Field Properties (lower pane), on the General tab card, click the Default Value field.

ECDL Syllabus 5 Courseware 3.

4.

Form 4

Type the value to be displayed automatically.

Click Save button. When you enter data in new records, the default value will automatically appear in the field.

Indexing Fields An index speeds up searches on the indexed fields as well as sorting and grouping operations. For example, if you search for specific students using the surname field, you can create an index for this field to speed up the search. By default, the primary key field in a table is automatically indexed. However, you can set indexing on other fields in a table.

When deciding which fields to use as indexes, look at the fields you expect to search frequently for particular values, or that you will want to sort by. To set an index to a field: 1.

2.

3.

4.

In Table Design View, select the field be indexed. You can select a field by clicking on its name in the Field Name column.

In the Field Properties (lower pane), on the General tab card, click the Indexed field.

Click the drop-down arrow and select the appropriate option (refer to the following table).

Click Save button.

Option

To Do This

No

Deletes the existing index or leaves the field unindexed.

Yes (Duplicates OK)

Indexes the field, allowing duplicate values among fields in the table.

Yes (No Duplicates)

Indexes the field prohibiting duplicate values among fields in the table.

Note that you cannot index an AutoNumber or Memo field.

You can view and modify existing indexes in the table design view as follows: 1.

2.

Display the table in Design View.

In the Show/Hide group, click Indexes button. The following dialog box will be displayed:

49

The following table describes the possible changes you can make to properties of the indexes: Option

To Do This

Sort Order

Select either Ascending or Descending.

Primary

Indicates whether the selected index is the primary key field.

Unique

Indicates whether the selected index prohibits duplicate field contents.

Ignore Nulls

Indicates whether Access searches through fields that do not contain any data.

If Access ignores null values in an indexed field, it makes searches and sorts more efficient.

Indexes can be deleted by selecting the index and pressing the DELETE key.

Adding Fields to Tables To insert a new field in Design view: 1.

2.

3.

4.

5.

Click the Record Selector button to select the field row where you want the new field to be inserted.

In the Tools group, click Insert Rows.

Type the name of the field and press TAB key.

Set a data type for the field.

Click Save button.

Note that: If you add a field after data has been entered, the new field will be blank for all existing records.

When you add a new field, you are changing the table s design. You must save the changes to the table before you can switch to Datasheet view or close the table.

Activity 10 You will use Module 5 practice files.

1.

In MS Access, open second.accdb (created during Activity 6) in the folder Module 5 Exercises.

2.

Open the table tblStudents in Design View.

3.

Below the field Promoted, add the fields: Field Name

4.

Data Type

EnglMark

Number

MaltMark

Number

MathMark

Number

Delete the field AverageMark.

Continued

50

ECDL Syllabus 5 Courseware

Form 4

5.

Move the field Promoted after the field MathMark.

6.

Rename the field TelNo to PhoneNo.

7.

Insert the field Gender (Data type - Text) before the field PhoneNo.

8.

Set the field properties as follows: Field Name

Data Type

Field Size

Format

Surname

Text

25

>

Name

Text

25

Date/Time

n/a

Gender

Text

1

PhoneNo

Text

8

DateOfBirth

9.

Field Properties

Email

Hyperlink

Fee

Currency

EnglMark

Number

Byte

MaltMark

Number

Byte

MathMark

Number

Byte

Promoted

Yes/No

Medium Date

<

Set indexing (duplicates allowed) on the Surname and Name fields.

10. Save and close tblStudents.

11. Close the database file second.accdb. Close MS Access.

Creating & Running Queries In the previous section you have seen how you can search for specific data. MS Access provides a powerful tool the query object - to extract particular information from your database file. A query allows you to select and display records that meet a specified set of criteria. For example, showing only those records of students born during 1983 and residing in Qormi .

A query cannot be created unless the database file has at least one table. You can base a query on a single table, on multiple tables, or on other queries. Queries are particularly effective when you want to use fields from multiple tables that have a relationship. You cannot use filters when you want to extract data from multiple tables.

In MS Access you can create different types of queries. For the purpose of this course, you will learn how to create select queries. A select query displays a datasheet of all the records and fields matching the criteria you entered without changing any of the data. The results of a query are known as a recordset.

51

Queries can either be created in Design View or by using one of the query wizards. In this course you will learn how to create a query in Design View: 1.

Click the Create tab.

2.

In the Other group, click Query Design. The Select Query window and the Show Table dialog box will be displayed.

3.

In the Show Table dialog box, double-click the table/s on which the query will be based. When you double-click on a table, a list of available fields will be displayed at the top pane, known as the table pane, of the query window.

4.

Click Close button in the Show Table window.

The Select Query window is divided into two areas: the table pane (top pane) and the QBE grid (bottom pane).

The QBE (query by example) grid is a collection of cells where you define the query. Use the first row (Field) for the query s field names. Use the third row (Sort) for sorting options. Use the fourth row (Show) to determine which fields appear in the query results. Use the rest of the rows (Criteria, and so on) to set up your criteria.

52

ECDL Syllabus 5 Courseware

Form 4

Table Pane Field List

QBE Grid

5.

6.

In the table pane, double-click the field name/s (in the field lists). The Field: (first row), in the QBE grid, will display the field name selected. The Table: (second row), in the QBE grid, will automatically display the name of the table which has the selected field.

Repeat this step for the other fields that will be included in the query. The order in which you select your fields is the order in which they will appear in the recordset (the results of a query).

7.

Click the drop-down arrow in the Sort: box and select the sorting order [ascending, descending or (not sorted)] for the data in that field. Use ascending order to sort dates and times from earlier to later. Use descending order to sort from later to earlier.

8.

In the Criteria: box type an expression (read further down) and press ENTER key. Criteria are used to extract a specific set of data. The basic idea is that you select a field and then enter an expression that defines your criteria. For example, to extract details of students who got less than 55 in their English exam type <55.

9.

Deactivate any check box/es in the Show: cells for the field/s that will not be displayed in the recordset. In most cases such fields are added to the QBE grid for criteria purposes only. You can unhide the field/s by unchecking the Show: check box.

10. In the Results group, click Run to display the results of the query.

53

11. Click Save button to save your query. The Save As dialog box will be displayed.

12. In the Save As dialog box, type a name for the query in the Query Name: 13. Click OK button.

Note that: When you save a query, remember to start its name with qry . This naming convention will later help you to identify this database object as a query.

You should save your query if you plan to re-use it. Saving your query will save the query s structure, not its results. If you add data to a table that matches the criteria (read further down) in the query, and then you run the query, a new up-to-date recordset is generated.

Adding Criteria Using Comparison Operators Criteria expressions are short statements that tell MS Access what subset of your data you want to see. The following table displays the comparison operators that can be used in criteria expressions: Symbol

Description

Symbol

Description

>

Greater than

=

Equal to

<

Less than

>=

Greater than or equal to

Less than or equal to

<>

Not equal to

<=

Typically comparison operators are used on numeric or date fields. However you can also use them on text fields. For example, the expression < D will match field values that begin with letters A, B or C. Expression =Borg <>John <=G =78772M <30

54

Field Surname Name Surname ID English

Description Displays records where Surname is Borg .

Displays records where Name is not equal to John .

Displays records where the Surname begins with letters A through G. Displays the record where ID is 78772M. Displays records where English is less than 30.

ECDL Syllabus 5 Courseware

Expression >=45

Field Maltese

>1/1/95

Date of Birth

Form 4

Description Displays records where Maltese is greater or equal to 45.

Displays records where Date of Birth is after 1/1/95.

Note that: If you want your data to match an exact value, there is no need to use the equal symbol (=) in your criteria expression.

Example: You add a single selection criterion to the Criteria: cell in the QBE grid:

Running the above query displays all students whose surname is Mifsud'. Note that when you type Mifsud and click another cell, Mifsud will be displayed as =Mifsud .

Activity 11 You will use Module 5 practice files.

1.

In MS Access, open fourth.accdb in the folder Module 5 Exercises.

2.

Create a query using the Design View based on the tblAlbum. The query should display the following fields in the order: asin, price, release, rank and label.

3.

Run the query.

4.

Save this query as qryAlbums.

5.

Close the qryAlbums.

6.

Re-open the qryAlbums.

7.

Remove the fields asin and rank from the qryAlbums. Run the query. continued

55

8.

Add the fields artist and title as the first two fields in the qryAlbums. Run the query.

9.

Set the criteria such that those records where the artist is The Beatles are displayed. Run the query.

10. Add the appropriate expressions such that only those records where the artist is The Beatles and the price is ÂŁ13.49 and the release date is before 31/12/1966. Run the query.

11. Save and close the qryAlbums.

12. Save and close fourth.accdb. Close MS Access.

Creating Forms A form is a type of a database object that is primarily used to enter or display data in a database. Earlier in this course, the table object was used for data entry, editing and viewing. The form object provides a more user-friendly interface for data entry, editing and viewing.

A form is another way of looking at data in a table. If you add or edit records in a form, you are actually changing the data in the underlying table. When you work with data in a form, all the field properties in the table s design will apply, including default values and validation rules.

The form wizard helps you create a form quickly. The wizard asks you questions and creates a form based on your answers. You can then customize the form the way you want it in Design view.

To create a form using the form wizard: 1. Click the Create tab.

2. In the Forms group, click Form Wizard. This will start a wizard that asks you questions and creates a form based on your answers.

3.

56

Click the drop-down arrow below Tables/Queries: and select the table or query (discussed in a later section) that contains the data to be used in the form.

ECDL Syllabus 5 Courseware

4.

Form 4

The left part (under Available Fields:), displays a list of fields that make up the table or query selected in the previous step.

Double-click the field to include in your form. This field will move to the Selected Fields: list. Repeat this for all fields that you wish to include in your form.

You can remove fields from the Selected Fields: list by double-clicking the fields to remove.

Click Next button. The next Form wizard dialog box is displayed.

5.

Select one of the four layout options Columnar, Tabular, Datasheet or Justified. When you select a layout, a sample appears in the left pane.

57

Click Next button. The next Form wizard dialog box is displayed.

6.

In the What title do you want for your form? type a name for your form.

Select whether you want to start entering data in the form or you want to modify the form s design. If you select the option Open the form to view and enter information the Form view window will be displayed.

Click Finish button.

Note that: When you save a form, remember to start its name with frm . This naming convention will later help you to identify this database object as a form.

58

ECDL Syllabus 5 Courseware

Form 4

Entering, Modifying & Deleting Records in Forms To add a new record in Form view: 1.

2.

Click New Record button. A blank record will be displayed.

Type in the data.

Note that: Data entry through a form is equivalent to data entry in a table. Therefore data entered via the form object also goes in the table on which the form is based on.

To modify/edit data within a field: 1.

2.

In the Form View, click in the field you want to edit.

Type in the data.

Note that: When you move to another record, MS Access saves your changes.

To delete a record: 1.

2.

3.

4.

Click the Home tab.

In the Form View, position the cursor in any field of the record to be deleted.

In the Records group, click Delete.

Click the Delete Record.

5.

Click Yes button to proceed with deleting the selected record.

Adding & Modifying Text in Form Headers & Footers The Form Wizard is an essential tool for creating a form. However, you may need to modify the design of a form. For example, to add/edit a header or footer you will need to use the Form Design View.

The Form Design View window displays: Form Header containing the text or graphic that is displayed at the top part of every form.

Detail containing the labels and fields. The labels hold the field names or caption from the fields properties. The fields hold the table data. Each label and its corresponding field are collectively known as bound controls.

Form Footer every form.

containing the text or graphic that is displayed at the bottom of

59

Labels

Fields

To add a header and/or footer to the form: 1.

Switch to the form Design View. To view the footer increase the size of the Form window through the bottom right corner border of the Form window.

By default the frmBorrowers.

2.

Form

Header

displays

the

name

of

the

form

e.g.

Click in the header text. Do one of the following: Edit or delete the header text.

If there is no header text:

3.

4.

5.

6.

7.

8.

60

a.

In the Controls group, click Label.

b.

Create a text box in the Form Header. The cursor will be displayed in the text box.

c.

Type the header text.

d.

Click outside the text box.

Click below the Form Footer.

In the Controls group, click Label.

Create a text box in the Form Footer. The cursor will be displayed in the text box.

Type the footer text.

Click outside the text box.

Click Save button.

ECDL Syllabus 5 Courseware

Form 4

To edit the form header or footer text: 1.

2.

3.

4.

Switch to the form Design View.

Click in the header or footer text box to edit.

Edit the text.

Click Save button.

Activity 12 You will use Module 5 practice files.

1.

In MS Access, open third.accdb in the folder Module 5 Exercises.

2.

Using the Form Wizard, create a form based on the table tblBorrowers.

Include all fields in the table tblBorrowers.

Apply a Columnar layout.

Save this form as frmBorrowers.

3.

Through the Form View, add the: Field Name

Record 5

Record 6

25776M

3476M

Surname

Callus

Costa

Name

Mario

Phil

Address1

St. Philip

St. Mary

Address2

Bishop Borg Street

Long Street

Zebbug

Hamrun

Postcode

ZBG1111

HMR2222

PhoneNo

21464141

21424344

IDNo

Town

4.

Move to the first record.

5.

Move to the last record. Delete the last record.

6.

Type again the details of the record deleted in step 5. Refer to step 3 to enter the data in the last record.

7.

In the form frmBorrowers, insert the text Rexton School Library in the Form Header. Apply the font style Verdana pt. size 16 and red colour to the text. Save and display the form in Form View.

8.

In the form frmBorrowers, insert the text (c) J. Borg 2004 in the Form Footer. Apply the font style Verdana pt. size 12 and red colour to the text.

Save and display the form in Form View.

9.

Close frmBorrowers.

10. Using the Form Wizard, create a form based on the tblBooks.

Include fields CopyNo, BookTitle and Author in the table tblBooks.

Apply a Columnar layout.

Save this form as frmBooks.

11. Save and close the database file third.accdb. Close MS Access.

61

Creating Reports The records in a table or query can be printed in a spreadsheet-like format. The records in a table or query can be printed using the reports object. The reports object enables you to select, organise and print records a customised format.

The report wizard helps you create a report quickly. The wizard asks you questions and creates a report based on your answers. You can then customize the report the way you want it in Design view.

To create a report using the report wizard: 1.

Click the Create tab.

2.

In the Reports group, click Report Wizard. This will start a wizard that asks you questions and creates a report based on your answers.

3.

Click the drop-down arrow below Tables/Queries and select the table or query that contains the data to be used in the report.

4.

The left part (under Available Fields:), displays a list of fields that make up the table or query selected in the previous step.

Double-click the field to include in your report. This field will move to the Selected Fields: list. Repeat this for all fields that you wish to include in your report.

You can remove fields from the Selected Fields: list by double-clicking the fields to remove.

Click Next button to display the next Report wizard dialog box.

5.

To add a grouping level, double-click the field you want to group with.

Grouping fields allows you to organise the content of your report. For example, if you wish to group the records by surname , double-click surname.

62

ECDL Syllabus 5 Courseware

Form 4

When you group a field, MS Access separates all of the records that relate to that particular field. By grouping fields, you can create reports that are easier to read when you need specific information grouped together.

Click Next button to display the next Report wizard dialog box.

6.

Determine how the report will display the sorting of the records. You can sort the records by up to 4 fields in either ascending/descending order. Select a field from the drop-down list box.

If you have number or currency fields in your report, you can include summations, averages, minimum, and maximum in your report. Click Summary Options button.

63

The Summary Options dialog box will be displayed. Tick the appropriate options and click OK button. You will be returned to the previous Report Wizard dialog box.

Click Next button to display the next Report Wizard dialog box.

7.

Select one of the layout options. When you select a layout, a sample appears in the left pane.

Select whether you want to print your report in Portrait or Landscape format.

Click Next button to display the next Report Wizard dialog box.

8.

64

In the What title do you want for your report? type a name for your report.

ECDL Syllabus 5 Courseware

Form 4

Select whether you want to preview the report or you want to Modify the report s design. If you select the option Preview the report the Print Preview window will be displayed.

Click Finish button.

Note that: When you save a report, remember to start its name with rpt . This naming convention will later help you to identify this database object as a report.

65

Changing the Arrangement of Data Fields & Headings The Report Wizard is an essential tool for creating a report. However, you may need to modify the design of a report. For example, you can change the arrangement of data fields and headings.

The Design View displays the different sections in a report: Report Header, Page Header, Detail, Page Footer and Report Footer.

Considering the rptResults: the Report window (displayed above) shows the headings (Surname, Name, StudentID, Promoted etc.) in the Page Header section and the corresponding data fields in the Detail Section. The headings hold the field name or caption from the field s properties. The data fields hold the table data.

MS Access groups all the fields in a layout. The layout is a specialised container that lets you easily work with groups of fields. When you move a column header, the column data moves with it. When you move a column to a new position, MS Access rearranges all the other columns accordingly.

To change the arrangement of headings and data fields: 1.

2.

3.

4.

5.

6.

7.

66

Switch to Layout View.

Click the column header of the field to move.

Press and hold SHIFT key.

Click the corresponding data field. All the data will be highlighted.

Position the pointer in one of the selected data fields. The pointer changes to a 4 pointed arrow.

Drag the 4 pointed arrow to the new location.

Click Save button.

ECDL Syllabus 5 Courseware

Form 4

Modifying Report Headers & Footers You may also need to edit or format the text in headers and footer objects in the report. For example you may wish to edit the header of a report which by default is the title name entered in the final step of the last Report wizard dialog box. To format or edit report headers and footers: 1.

2.

3.

4.

In the report Design View, click in the report header/footer text box. Sizing handles will be displayed.

Apply formatting options (e.g. typestyle, font style, size and colour) in the header/footer text box.

To edit the text, click again the header/footer text box. The cursor appears in the text box. Edit the text as necessary.

Click Save button.

Activity 13 You will use Module 5 practice files.

1.

In MS Access, open third.accdb in the folder Module 5 Exercises.

2.

Using the Report Wizard, create a report based on the tblBorrowers.

Include all fields in the table tblBorrowers.

Set your report to print the records in ascending order according to Surname, then by Name and then by IDNo. Apply a Tabular layout and a Landscape orientation.

Save this report as rptBorrowers. continued

67

3.

Edit the report header text from rptBorrowers to Rexton School Library.

Preview the report.

4.

Apply the font style Verdana pt. size 16 and bold typestyle to the header text Rexton School Library. Preview the report.

5.

Save and close rptBorrowers.

6.

Close third.accdb.

7.

Open sixth.accdb in the folder Module 5 Exercises.

8.

Using the Report Wizard, create a report based on the tblAnimals.

Include the fields AnimalID, AnimalType, FoodType and CostPerMonth fields.

Group the data by the type of food required and sum the cost per month.

Set your report to print the records in ascending order according to AnimalID. Apply a Stepped layout and a Portrait orientation.

Save this report as rptAnimals.

9.

Edit the report header text from rptAnimals to Cost Per Month. Preview the report.

10. Save and close rptAnimals.

11. Save and close sixth.accdb. Close MS Access.

68

ECDL Syllabus 5 Courseware

Form 4

Module 6 Presentation

Inserting Graphical Objects in Master Slides A slide master is the top slide in a hierarchy of slides that stores information abou t the theme and slide layouts of a presentation, including the background, colour, fonts, effects, placeholder sizes, and positioning. The slide master enables you to add background items that you want to appear on every slide in a presentation. If you make a change to the slide master (e.g. choosing a special font for the text, italicising the slide title or changing the bullet styles), the change affects all slides in your presentation that follow the master. You can also add a logo or page number to a slide.

To display the Slide Master: 1.

2.

Click the View tab.

In the Master Views group, click Slide Master.

The Slide thumbnail pane displays the slide master and a number of slide layouts associated with it.

To return to the normal view: 1.

2.

Click the Slide Master tab.

Click Close Master View.

To insert a picture from the clip art gallery in the slide master: 1.

2.

3.

4.

5.

6.

7.

8.

Click the View tab.

In the Master Views group, click Slide Master.

Select the slide master i.e. the first slide at the top of the slide thumbnail.

Click Insert tab.

In the Images group, click Clip Art. The Insert Clip Art task pane is displayed.

In the Search text box, type a word or phrase that describes the clip you want or type in all or some of the file name of the clip.

Click Go button.

Click the graphic to insert.

Note that: You can resize and re-position the picture as necessary.

69

To insert a picture from file in the Slide Master: 1.

2.

Repeat steps 1-4 as above.

In the Images group, click Picture. The Insert Picture dialog box will be displayed.

3.

4.

5.

Browse to the disk/folder where the image file is located.

Click the image to insert.

Click Insert button. The picture will be inserted.

To insert a drawn object in the slide master: 1.

2.

3.

4.

5.

6.

7.

Click the View tab.

In the Master Views group, click Slide Master.

Select the slide master i.e. the first slide at the top of the slide thumbnail.

Click Insert tab.

In the Illustrations group, click Shapes. A gallery of shapes is displayed.

Select the shape to draw.

Click anywhere in the slide, and then drag to place the shape.

To delete the clip art, image or drawn object in the Slide Master: 1.

2.

70

Click the clip art, image or drawn object to delete.

Press Delete key.

ECDL Syllabus 5 Courseware

Form 4

Adding Footer Text & Automatic Slide Numbering Footers consist of text, slide numbers, and a date at the bottom of your slides. To add text, slide numbers and a date at the bottom of your slide/s: 1.

2.

3.

4.

Click the slide where the footer text or slide number will appear.

Click Insert tab.

In the Text group, click Header & Footer. The Header and Footer dialog box is displayed.

Tick Date and time checkbox if you want to insert a date and time.

Do one of the following: Click Update automatically and choose the appropriate data and time format to apply.

Click Fixed and type in a date in the field.

5.

6.

7.

Tick the Slide number checkbox to add automatic numbering to the slide/s.

Tick the Footer checkbox and type the text to appear in the footer of the slide/s.

Click Apply to All button so that all settings will be applied to all slides in the presentation or Apply button to apply the settings to the selected slide.

Note that: If you don't want the information to appear on the title slide, tick Don't show on title slide.

Option

To Do This

Date and Time Update automatically

Displays and updates the current date and time in the slide footer. Click the date and time format you want in the Update automatically list. This option is unavailable unless the Date and time check box is selected

Fixed

Displays the date and time in the slide footer. Type the date and time you want in the Fixed box.

Slide number

Adds the slide number to the slide footer.

Footer

Adds the text you type in the Footer box to the bottom of the slide.

71

Activity 14 You will use Module 6 practice files.

1.

In MS Access, open third.pptx located in Module 6 Exercises folder.

2.

Using the slide master insert the image www.gif located in the folder Module 6 Exercises at the top right side of the slide.

3.

Using the Header and Footer dialog box insert: a. a date which is automatically updated, b. a slide number and c. the footer text Putting the Puzzle Together.

Apply these settings to all the slides in the presentation except the title slide.

4.

Save and close third.pptx. Close MS PowerPoint.

Creating Charts MS PowerPoint gives you the ability to insert charts or graphs directly in your presentation. These become particularly important when you want your audience to visualise numeric data. Different charts may be used depending on what the numeric data represents: Pie charts are used for figures representing percentages.

Line charts are used to demonstrate a trend in numeric data.

Bar charts are used to compare different sets of numbers.

When you add a chart to a presentation a sample chart is embedded in the presentation. The data used to plot the sample chart is stored in a MS Excel worksheet that is incorporated into the MS PowerPoint file.

It is advisable to start MS Excel 2010 before you start creating your chart in MS PowerPoint: 1.

2.

3.

4.

Click Start button.

Select All Programs.

Click Microsoft Office.

Click Microsoft Excel 2010.

To insert a graph in a slide: 1.

2.

3.

4.

72

Click Click Click Click

the Home tab.

Insert New Slide arrow.

Title and Content slide layout.

Insert Chart icon in the slide. The Insert Chart dialog box is displayed.

ECDL Syllabus 5 Courseware

Form 4

5.

6.

Select the type of chart to use e.g. Column.

Click the chart sub-type.

7.

Click OK button. The screen will display MS PowerPoint and MS Excel windows arranged next to each other.

73

The chart displayed in MS PowerPoint is based on the data in the MS Excel sheet.

8.

9.

Click in MS Excel window.

Replace the data in MS Excel. Replacing the data in MS Excel updates the chart in MS PowerPoint.

10. Click outside the chart in MS PowerPoint and continue with your work in the presentation.

Selecting Charts & Setting Chart Types You can change the type of chart in a slide: 1.

2.

3.

74

Click the chart in the slide.

Click the Design tab, under Chart Tools.

In the Type group, click Change Chart Type. The Change Chart Type dialog box is displayed.

ECDL Syllabus 5 Courseware

4.

5.

6.

Form 4

Select the type of chart to use e.g. Pie. Click the chart sub-type.

Click OK button.

Adding, Removing & Editing Chart Titles To add a chart title: 1.

2.

3.

4.

5.

6.

Click the chart to add a title to.

Click the Layout tab, under Chart Tools.

In the Labels group, click Chart Title.

Click Above Chart. A placeholder with the text Chart Title is displayed.

Click in the placeholder.

Edit the text in the title placeholder.

To edit a chart title: 1.

2.

Click the chart title placeholder.

Edit the text in the title placeholder.

To delete a chart title: 1.

2.

Click the chart title placeholder.

Press DELETE key.

75

Adding Data Labels To add data labels to a chart: 1.

2.

3.

4.

Click the chart to add labels to.

Click the Layout tab, under Chart Tools.

In the Labels group, click Data Labels.

Click the appropriate option.

Changing Chart Colours You can modify the colours of the chart area, plot area and data series:

76

1.

2.

3.

Click the chart.

Click the Format tab, under Chart Tools.

In the Current Selection group, click the drop down arrow and choose Chart Area, Plot Area or any data series.

4.

5.

In the Shape Styles group, click Shape Fill.

Select a standard colour or choose More Fill Colours is displayed.

The Colors dialog box

ECDL Syllabus 5 Courseware

6.

7.

Form 4

Select a colour.

Click OK button.

Activity 15 1.

In MS Access, open a new presentation file. Save the presentation as fourth.pptx in Module 6 Exercises folder.

2.

Apply a Title and Content layout to the first slide. In the title placeholder type the text: Marks.

3.

Create a Column chart using the data: English

Maltese

Maths

Borg James

45

72

55

Callus Maria

56

65

62

Fenech Josef

60

48

88

4.

Change the chart type to a Bar chart.

5.

Add the title Mid-Year 2009 Results to the chart.

6.

Edit the title Mid-Year 2009 Results to End of Year 2009 Results.

7.

Add value labels to the chart.

8.

Change the colours of the bars to yellow, green and orange.

9.

Save and close fourth.pptx. Close MS PowerPoint.

77

Creating Organisation Charts You can insert organisation charts, cycles, pyramids etc. using the SmartArt graphics feature in MS PowerPoint. A SmartArt graphic is a visual representation of your information and ideas.

To create an organisation chart: 1.

2.

3.

4.

Click the Home tab.

Click Insert New Slide arrow.

Click Title and Content slide layout.

Click Insert SmartArt Graphic icon in the slide. The Choose a SmartArt Graphic dialog box is displayed.

5.

6.

Click Hierarchy.

7.

8.

9.

Click an organization chart layout e.g. Organization Chart.

Click OK button.

To enter your text, do one of the following: Click in a box in the SmartArt graphic, and then type your text. For best results, use this option after you add all of the boxes that you want.

Click [Text] in the Text pane, and then type your text.

Copy text from another location or program, click [Text] in the Text pane, and then paste your text. If the Text pane is not visible, click the control.

10. Click outside the organisation chart.

78

ECDL Syllabus 5 Courseware

Form 4

Adding & Removing Co-workers & Subordinates To add managers, co-workers and sub-ordinates: 1.

2.

3.

4.

5.

Click the SmartArt graphic that you want to add another shape to.

Click the existing shape that is located closest to where you want to add the new shape.

Click the Design tab.

Under SmartArt Tools, in the Create Graphic group, click the arrow under Add Shape.

Do one of the following: To insert a box to the right of the selected shape i.e. a co-worker, click Add Shape After.

To insert a box to the left of the selected shape i.e. a coworker, click Add Shape Before.

To insert a box above the selected shape i.e. a manager, click Add Shape Above.

To insert a box below the selected shape i.e. a sub-ordinate, click Add Shape Below.

To add an assistant to the selected shape, click Add Assistant.

6.

Click outside the SmartArt graphic.

Note that: To add a shape from the Text pane, click an existing shape, move your cursor before or after the text where you want to add the shape, and then press ENTER. To delete a shape from your SmartArt graphic: 1.

2.

Click the shape you want to delete.

Press DELETE key.

79

Checking the Presentation Spelling By default, MS PowerPoint checks the spelling of the text in your slides and presenter s notes. Every single word in each slide and notes is compared to a built-in dictionary available in MS Office. A wavy red line appears beneath words that are not found in the dictionary.

However, at times a word can be spelled correctly yet MS PowerPoint does not find it in its dictionary. Proper nouns (Rita), specialised terms (phototranspiration), acronyms and abbreviations (UOM) etc. are not likely to be in the MS Office dictionary. If you do not want MS PowerPoint to question such words during spell checks, you can add them to a custom dictionary. In addition to spelling mistakes, MS PowerPoint also alerts you to repeated words - for example the the - and words that have an unusual pattern of capitalisation, such as PLay .

To use the automatic spelling check: 1.

2.

Right-click the mouse over the word having a wavy red underline.

A pop-up menu appears listing any suggestions Word may have about the correct spelling or grammar, as well as the following choices: Ignore, Delete repeated word, Add, and Spelling. Click any suggestion and the appropriate option.

You can also resolve errors without using the pop-up menu by simply editing the text.

MS PowerPoint checks the spelling of the word again as soon as you move your cursor away from it, and if the word is now spelled correctly, the red line disappears.

To run the spelling and grammar check: 1.

2.

3.

4.

Click the Review tab.

Click Spelling & Grammar button. The Spelling dialog box is displayed.

Click the appropriate options (refer to the following table).

Click OK button when MS PowerPoint displays a message that it has checked all text in the document.

Option

To Do This

Not In Dictionary:

Displays a possible error, such as an incorrectly spelled word, a duplicate word, a grammar error, or incorrect capitalization. Edit the text in this box to correct it, and then click Change, or click the correct word in the Suggestions box and then click Change.

Suggestions:

Lists a number of words close to the incorrectly spelled word. Click the appropriate suggestion to choose it.

80

ECDL Syllabus 5 Courseware

Form 4

Option

To Do This

Ignore

Leaves the word unchanged and continues with the checking. The spell checker stops if the same word is encountered within the same document.

This button changes to Resume if you click in the document to edit the document.

Ignore All

Leaves the word unchanged and skips any occurrences of the same word within the entire document throughout the rest of the current Word session.

Add

Adds the word in the Not In Dictionary box to the custom dictionary.

Change

Replaces the incorrectly spelled word with the highlighted word/phrase in the Suggestions: box. When the selected error is a repeated word, this button changes to Delete so you can easily remove the second instance of the word.

Change All

Replaces all occurrences of the same incorrectly spelled word with the word/phrase in the Suggestions: box.

AutoCorrect

Adds a word to the AutoCorrect list so that MS PowerPoint can correct any incorrect spelling of it automatically as you type.

Options

Displays a dialog box in which you can specify the rules that Word uses to check spelling and grammar.

Note that: If the insertion point or selection was not at the beginning of your document when you begin checking spelling, MS PowerPoint asks if you want to continue checking from the beginning of the document.

MS PowerPoint doesn't check the spelling in embedded objects such as charts or in inserted objects such as MS Word documents.

If the insertion point is in the notes pane or slide pane, the spelling check alternates checking between the two. If the insertion point is on the Outline tab, all the slides are checked first, then all the notes.

Printing Like all other computer application programs, MS PowerPoint has its printing features.

These are somewhat different from MS Word, because you can print different components of your presentation. You can print slides, outlines, notes pages, and handouts.

1.

2.

3.

Click File tab.

Click Print. The Backstage view is displayed.

In Settings, click the appropriate option: Print All Slides Print Selection to print only the selected slides.

Print Current Slide.

Custom Range to print specific slides.

4.

Click Print button.

81

To print handouts, notes pages or outline view of slides: 1.

2.

3.

4.

5.

82

Click File tab.

Click Print. The Backstage view is displayed.

Click the drop-down arrow next to Full Page Slides.

Choose the appropriate option: Click Print button.

ECDL Syllabus 5 Courseware

Form 4

To print multiple copies of a presentation: 1.

2.

3.

Click File tab.

Click Print. The Backstage view is displayed.

Next to the Print button, set/type the number of copies in the Copies box.

4.

Click Print button.

Note that: To print a complete copy of the presentation before the first page of the next copy is printed, under Settings select Collated. If you prefer to print all copies of the first slide and then print all copies of subsequent slides, select Uncollated.

Activity 16 You will use Module 6 practice files.

1.

In MS Access, open fourth.pptx (created during Activity 15) located in Module 6 Exercises folder.

2.

Insert a new slide at the end of the presentation. Use the Title and Diagram or Organization Chart layout for this slide.

3.

In the title placeholder type IT Department.

4.

Create the following organization chart.

Executive Director

User Services

Corporate Services

Technical Services

5.

Edit the text Executive Director to Chief Information Officer.

6.

Delete the box containing Corporate Services.

7.

Add a sub-ordinate box containing Administration below User Services.

8.

Save and close fourth.pptx. Close MS PowerPoint.

83