MTN SOUTH AFRICA, THROUGH MTN SA FOUNDATION IS A PROUD SUPPORTER OF CAT AND IT DIGITAL BOOKS.

As an organisation rooted in technology, we believe in providing a new bold digital world to communities we operate in. This unique digital book provides the fundamental knowledge necessary for a sound grounding from which to make practical use of the complete and indispensable application-oriented information regarding Computer Applications Technology (CAT) and Information Technology (IT). It is a foundational reference for today’s secondary school learners and teachers alike – as well as for the next generation of CAT and IT students.

Computer Applications Technology Grade 12 Practical Book

ISBN: 978-1-928388-65-4

First published in 2019 © 2019. Copyright in the text remains with the contributors.

Restrictions

You may not make copies of this book in part or in full – in printed or electronic or audio or video form – for a profit seeking purpose.

Rights of other copyright holders

All reasonable efforts have been made to ensure that materials included are not already copyrighted to other entities, or in a small number of cases, to seek permission from and acknowledge copyright holders. In some cases, this may not have been possible. The publishers welcome the opportunity to redress this with any unacknowledged copyright holders.

Quality Assurance team for Computer Applications Technology

Carina Labuscagne, Claire Smuts, Deidré Mvula, Edward Gentle, Estelle Goosen, Feroza Francis, Hendrik Hahn, Ighsaan Francis, Magdalena Brits, Natasha Moodley, Peter Davidson, Reinet Barnard, Tyran Ferndale and Zainab Karriem

Dear learner

Welcome to the Practical Book for Computer Applications Technology Grade 12. Please read the information below about the book before you start using it. This book provides you with all the information you need to cover the Grade 12 Computer Applications Technology curriculum. It will also help you develop the skills, attitudes and values you need to be successful in your studies.

Each chapter in the book has the following features, which are meant to help you learn:

Overview
This is an overview of the contents and the units of the chapter.

Learning outcomes
These are the objectives that are addressed in the chapter. It states what you should be able to do by the end of the chapter and gives you an idea of what will be expected of you as a learner. After completing each chapter, ask yourself, ‘Am I able to do everything stated in the learning outcomes?’. If not, you should revise the content covered in the chapter.

Introduction
The introduction is a brief summary of why you will be learning the content that is covered in the chapter. Sometimes it will give you a brief background of how far technology has come, while at other times it will just give you a short summary that introduces the content that will follow in the chapter.

Activities
These cover questions you have to answer based on the content presented before them. Activities will help you check whether you have understood the content presented in the book or not.

‘Something to know’, ‘Take note’ and ‘Tip’ boxes
The boxes provide extra, interesting content that might caution you to ‘take note’ of something important; or give you additional information. Note that the content in these boxes will not be part of your exams.

QR Codes, Videos and Screen captures
These will link you to online content. When you are in the eBook, you can easily access the links.

Revision activities
This is a revision activity based on what you have covered in the chapter. Take time to answer the questions on your own. You teacher may also use these to assess your performance during class.

At the end of the chapter
This is a checklist to ensure you understand all the content cover in the chapters. These criteria are linked to the learning outcomes at the beginning of the chapter.

Before getting started, watch the video in the QR code in the margin.

INTRODUCTION

Before getting started, watch the video in the QR code.

https://www.youtube.com/watch?v=nKIu9yen5nc

You can use the QR codes on these pages to link to online content for further information on these topics.

Chapter 1

THE POWER OF E-FORMS

TO CREATE A NEW TEMPLATE

Chapter 2

MAIL MERGE WIZARD

Chapter 4

FUNCTIONS

CHAPTER OVERVIEW

Unit 1.1 | Line breaks
Unit 1.2 | Bookmarks
Unit 1.3 | Reviewing and tracking changes
Unit 1.4 | Electronic forms

By the end of this chapter, you will be able to:

Apply pagination techniques in a word document.

Apply bookmarks to keep your place in a word document.

Identify mistakes and indicate changes in a document by using reviewing and tracking tools.

Create and use electronic word processing forms to gather data.

INTRODUCTION

Microsoft Word might be the most well-known word processing program, but did you know that there are many other word processing programs that you can use? Google Docs is a web-based word processor that can be used free of charge, and LibreOffice is a free, opensource office suite that can be downloaded and installed from the internet.

In this chapter we will be taking a closer look at some of the more specialised functions of a word processor. This include using bookmarks to help you navigate the document, using line breaks to better shape paragraphs, and using the review and track changes options to edit the document. Finally, we will take a look at file management and how you can prepare and publish the document.

In previous years, you learned about the ribbon and how it can be used to quickly navigate to the various features that you can use to edit a document. To help you refresh this knowledge, complete the following activity.

Activity 1.1

Open the Act1.1(Advanced Techniques).docx document. The image below shows the HOME tab.

1.Use the following editing options on the HOME tab and complete the following questions:

Table 1.1: The editing options on the HOME tab

The image below shows the INSERT tab.

2.Use the following editing options on the INSERT tab and complete the following questions:

Table 1.2: The editing options on the INSERT tab

The image below shows the LAYOUT tab.

3.Use the following editing options on the LAYOUT tab and complete the following questions:

Table 1.3: The editing options on the LAYOUT tab

Save and close your document.

UNIT
1.1 Line breaks

In long documents and paragraphs lines of text are sometimes left at the top of the next page or at the bottom of the previous page. These lines usually belong with the previous or following paragraphs or headings.

The following line breaks would solve the problems:

1. Widow/Orphan control:

A Widow is the last line of a paragraph left by itself at the top of a page. An Orphan is the first line of a paragraph left by itself at the bottom of a page.

To prevent Widows and Orphans, do the following:

Guided Activity 1.1

This is a built-in feature of Word and functions to prevent widows and orphans. It is applied to the whole document.

1.Open the file GAct1.1.docx and go to the Pagination in the Paragraph options and tick the Widow/Orphan control option.

Save and close your document.

If no paragraphs are selected, it will be applied to the whole document. If paragraphs are selected it will be applied to only those paragraphs.

2. Keep with next:

This feature prevents breaks between paragraphs you want to stay together.

Guided Activity 1.2

This option keeps all paragraphs of a certain style together on a single page.

1.Open the file GAct1.1.docx and select the paragraphs you want to keep together.

2.Open the Paragraph option from the Home tab and go to the Line and Page break tab.

3.Go to Pagination and tick the Keep with next option.

4.Click OK.

Save and close your document.

3. Keep lines together:

To prevent a paragraph from being separated over two pages, the keep lines together feature is used.

Guided Activity 1.3

To keep all the text in a paragraph together and prevent the paragraph from being split over a page, do the following:

1.Open the file GAct1.1.docx and select the paragraph lines that you want to keep together.

2.Select the Home tab and click the Paragraph options button.

3.Select the Line and Page Breaks tab.

4.Go to Pagination and tick the Keep lines together option.

5.Click OK.

Save and close your document.

Page breaks are also used to organise text in long documents. Do revision on the use of page breaks and section breaks done in Grades 10 and 11.

Guided Activity 1.4

This option can be used to insert a page break before a specific style, such as a Chapter heading.

1.Open the file GAct1.1.docx and select the paragraph that you want to follow the page break.

2.Go to Pagination in the Paragraph options and tick the Page break before option.

Save and close your document.

Activity 1.2

Open the file called Act1.1(Old Sultan).docx and perform the following activities:

1.Activate track changes.

2.Replace “Sultan” with “Tiny”.

3.Replace “wolf” with “weasel”.

4.Reject every second change of “Sultan” with “Tiny”.

5.Leave a comment at each rejection to say that you rejected the name “Tiny”.

6.Save the document.

7.Compare it to the original document.

Save and close the file.

Activity 1.3

1.Open the file called Act1.3(Old Sultan).docx and perform the following activities:

a.Insert a section break before the paragraph, “The blackmail”.

b.Make sure that the paragraph “The revenge” is kept on the same page.

c.The paragraph “The end” should be on its own page.

d.Insert automatic page numbering in the left of the footer from page two onwards.

e.Apply widow and orphan control to the text.

UNIT
1.2 Links: bookmarks, hyperlinks, cross referencing

BOOKMARKS

Bookmarks have been used to help keep the reader’s place in a book so that he/she can come back and quickly find where he/she left off. These traditional bookmarks were made of cloth, leather or cardboard. However, as the written word has been adapted for the digital age, so too have bookmarks.

In Chapter 9 of your theory studies (E-communication tools), you learned that a bookmark can be used to tell the browser to go to a specific website address that you have visited previously. Similarly, bookmarks can be used in a word processing program to mark a word, section, location or picture for future reference. Using a bookmark in this manner will allow you to find the marked section without having to scroll through the whole document, saving you both time and effort.

INSERTING BOOKMARKS

Guided Activity 1.5

1.Open the GAct1.5(webcams).docx document and select the word, paragraph or picture that you want to bookmark.

2.Click on the Insert tab and go to the links section. Click on Bookmark to open the bookmark information box.

3.Under the Bookmark name, type the name of your bookmark and click Add.

One thing you might notice when looking at the bookmark box is that there is an option to display hidden bookmarks. These are bookmarks that are not normally visible and are used as a reference point for headings and the table of contents.

Save and close your document.

Something to know

Hidden bookmarks in Microsoft Word will have an underscore as the first character of their name.

When naming the bookmarks take note of the following:

The name must start with a letter.

The name cannot be longer than 40 characters.

The name may not contain a space.

The name can contain letters, numbers or underscores (_).

USING BOOKMARKS

Bookmarks can be used to navigate to a selected place in the word document. This can be done in one of two ways. The first is to make use of the bookmark box and the second to use the Find and Replace function.

Guided Activity 1.6

How to navigate between the bookmarks using the bookmark box:

1.Open the GAct1.5(webcams).docx document and open the bookmark information box.

2.Select the bookmark you want to go to and click the Go To option.

Save and close your document.

Guided Activity 1.7

How to navigate between the bookmarks using the Find and Replace function:

1.Open the GAct1.5(webcams).docx document and select the Home tab.

2.Go to the Editing group.

3.Click the Find option and select Go To from the dropdown list. This will open the Go To tab of the Find and Replace function window. (This tab can also be opened by pressing Ctrl+G.)

4.Select Bookmark from the list of options.

5.Select the bookmark that you want to navigate to by making use of the dropdown menu and click Go To.

6.Once you have clicked Go To, the screen will jump to the selected bookmark that will also be highlighted.

Save and close your document.

DELETING A BOOKMARK

If a bookmark is no longer required in a document, or the text the bookmark is referring to has been deleted, it might be necessary to delete the unused bookmark.

Guided Activity 1.8

1.Open the GAct1.5(webcams).docx document and open the information box.

2.Select the bookmark you want to delete and click the Delete option.

Save and close your document.

HYPERLINKS

One of the drawbacks of using bookmarks is that you have to use either the bookmark box or the Find and Replace feature in order to navigate to a specific bookmark. This process can be time consuming. In order to help make it easier to navigate between bookmarks, you can use hyperlinks as well as cross referencing.

Hyperlinks work in very much the same way as bookmarks, with the major difference being that a hyperlink allows you to click on a marked word, image or symbol which will then take you to a specific place in the same document, another document, or external source such as a web page.

Guided Activity 1.9

1.Open the GAct1.5(webcams).docx document and select the text, image or symbol you want to use as a hyperlink.

2.Click on the Insert tab and go to the Links section. Click on Links to open the Hyperlink box.

3.Select the Place in This Document option.

4.Select the bookmark you want to link to from the list of bookmarks and click OK.

The same procedure can be used to insert a hyperlink to another document or web page by selecting the Existing File or Web Page option.

Save and close your document.

CROSS REFERENCING

When a hyperlink is used to navigate to another section within the same document, it is known as cross referencing. Using cross referencing will allow you to jump to another part of the document without having to update pagination, header or footer numbers.

Guided Activity 1.10

1.Open the GAct1.5(webcams).docx document and select the text, image or symbol you want to use as a cross reference.

2.Click on the Insert tab and go to the Links group. Click on Cross-reference to open the Cross-reference box.

3.Select bookmark from the Reference type dropdown menu. This will display the bookmarks in the document.

4. Select the bookmark you want to link and click Insert.

Cross referencing allows the user to link a bookmark to a specific word, paragraph or page number. This can be selected by choosing the correct option from the Insert reference dropdown menu. Save and close your document.

UNIT
1.3 Reviewing and track changes

The final part of writing any document is to review it for mistakes and inconsistencies. Previously this would have been done by marking any mistakes with a red pen and writing comments in the margins. Technically you are still able to follow this method by printing out the document and making comments with a pen, however, there is a far more efficient method available. Modern word processing software have Track Changes and Comments features included to allow the reviewer to indicate potential mistakes.

WORD COUNT

The word count function allows the user to determine the number of words, paragraphs and lines contained within the document.

Guided Activity 1.11

Here is how to use the word count function.

1.Select the text which words you want to count.

2.Go to the Proofing section of the Review tab.

3.Click Word Count. This will bring up a pop menu indicating the number of words, paragraphs and lines in the text.

You can also view the number of words in the text by looking at the bottom left of the word document as indicated in the image below.

SPELLING AND GRAMMAR

One of the most useful word processing tools is the Spelling & Grammar tool. This tool allows the user to check their document for any spelling and grammar mistakes. It also assists the user in fixing the mistakes by supplying a list of possible corrections. The user can then choose one of the correction options or ignore the mistake.

Guided Activity 1.12

Here is how to use the spelling and grammar check:

1.Open GAct1.12.

2.Go to the Proofing section of the Review tab.

3.Click on Check Document. This will open the Editor pane in the right margin of the document.

4.Click on either Spelling or Grammar to see all the mistakes that have been identified.

5.Select one of the suggestions presented to correct the mistake.

If you do not find a suitable suggestion, or you believe that the word/phrase is correct, you can select one of the supplied options.

Spelling mistakes have the following options.

Ignore Once: This option will skip this word without changing it.

Ignore All: This option will skip all instances of this word in the document without changing it.

Add to Dictionary: This option will add this word to the dictionary. Next time you use the word, it will not be recognised as an error.

Grammar mistakes have the following options.

Ignore Once: This option will skip this grammar mistake without changing it.

Don’t check for this issue: This option will skip all instances of this grammar mistake.

The Spelling & Grammar check is not always correct! This is especially applicable to grammar, as the word processor won’t pick up on these errors easily. There are also instances where this tool will indicate that something is an error but actually it is not. This happens, for example, with names and other proper nouns which do not exist in the dictionary. Proper nouns (names of people, countries, etc) are usually not autocorrected. It is your choice to either add it to the dictionary or ignore it.

TRACK CHANGES

The Track Changes option allows the user to make changes to the document, including correcting spelling mistakes, adding, removing and shifting information. These changes are then recorded and can be viewed by the author and reader. The author and reviewer will then have the option to either accept the changes or reject them by clicking the relevant option.

HOW TO ACTIVATE TRACK CHANGES

Once the document is ready to be reviewed, the reviewer can turn on track changes as follows:

Guided Activity 1.13

1.Open the file GAct1.13(trackChanges).docx and select the Review tab and go to the Tracking section.

2.Click the Track Changes option. This will turn on track changes.

3.Once track changes have been turned on, any changes made to the document will appear as coloured mark-ups.

Save and close your document.

HOW TO VIEW TRACK CHANGES

One of the drawbacks of working with track changes is that it might become difficult to see exactly what is going on in the text. This is because the track changes option does not delete text when you remove or change it, but rather indicates the change by drawing a line through the old text and replacing it with the new text in red.

In order to make it easier to work with track changes, you can change the way you view the changes by using either the Review box or Show Markup option.

Guided Activity 1.14

1.Open the file GAct1.13(trackChanges).docx and go to the Tracking section of the Review tab.

2.Click the dropdown menu of the Display for Review box and select one of the following options:

Simple Markup: This option shows the final version of the document without displaying the coloured mark-ups. Any changes will be indicated by a red marker that will appear in the left margin of the document.

All Markup: This option shows the final version of the document, including the coloured mark-ups that indicate the changes made.

No Markup: This option shows the final version of the documents without displaying the coloured mark-ups or red markers indicating changes made to the document.

Original: This option shows the original version of the documents without any changes made.

Save and close your document.

Guided Activity 1.15

1.Open the file GAct1.13(trackChanges).docx and go to the Tracking section of the Review tab.

2.Click the dropdown menu of the Show Markup option. This will display the following options.

Comments: This option hides/displays the comments made by the author and reviewer.

Insertions and Deletions: This option hides/displays all the information that has been added or removed.

Formatting: This option hides/displays all formatting changes that have been made to the document.

Balloons: This option changes the way track changes are displayed in the document. Instead of displaying the changes in text, most of the revisions are moved to the right-hand margin. This helps to make the document easier to read and provides more detail about some of the changes.

Specific people: This option provides more information on who made the revisions to the document. It also allows you to only view, accept and reject revisions made by a specific reviewer.

3.Select the option that you want to display in the document.

Save and close your document.

HOW TO REVIEW THE CHANGES

Using track changes does not permanently change the content of a word processing document. This is because track changes are more a suggestion than an actual change. To convert these suggested changes to be permanent, the changes need to be accepted or rejected based on whether the author agrees or disagrees with the changes.

Guided Activity 1.16

1.Open the file GAct1.13(trackChanges).docx and go to the Changes section of the Review tab.

2.Click on Next. This will take you to the next change. If you just opened the document, it will take you to the first change.

3.If you agree with the change click Accept, if not click Reject.

4.Repeat Steps 2 and 3 until you have gone through all the changes.

5.If you are unsure about a specific change, click Next and come back to the specific change once you have completed reviewing all other changes. You can also return to a previous change by making use of the Previous button.

Save and close your document.

It should be noted that you can accept or reject all changes in one go, by clicking the dropdown menu at either the Accept or Reject option and selecting Accept All Changes or Reject All Changes. However, this is not recommended as the whole idea of track changes is to allow you to decide whether you agree or disagree with the proposed changes.

Once you are done accepting and rejecting the changes you have made to the document, make sure to save the document and turn off track changes by clicking the Track Changes option in the Tracking tab.

COMMENTS

Comments allow the reviewer to provide more detailed feedback that would not be possible using the Track Changes option. This might include a list of extra information that should be included, a question that the author should answer, or even a link to a website that the author can use to get more information.

Guided Activity 1.17

1.Open the file GAct1.13(trackChanges).docx and highlight the text that you want to comment on.

2.Select the New Comment option in the Comments section of the Review tab. This will open a comment box. (You can also right click on the selected text and select the New Comment option for the pop-up menu.)

3.Type your comment.

4.Press die Esc key to close the comment box.

Save and close your document.

Guided Activity 1.18

1.Open the file GAct1.13(trackChanges).docx and select the comment you want to delete.

2.Click the Delete option in the Comments section of the Review tab. (This can also be done by right clicking the comment and selecting delete from the pop-up menu.)

3.You also have the ability to delete all comments in one go. This can be done by clicking the dropdown menu of the Delete option and selecting Delete All Comments in Document.

Save and close your document.

The use of comments is not restricted to only the reviewer. The author can also use comments to leave notes for themselves or the reviewer.

COMPARING DOCUMENTS

It is possible to use the track changes option even though you or someone else has edited the document without making use of track changes. This can be done by using the document compare option.

Document compare functions by taking the edited version of the document and comparing it with the original document in order to identify any changes that have been made. Once done, you can then accept or reject the changes in the new document. The reviewer is able to respond or reply to a comment.

Guided Activity 1.19

1.Open the original document.

2.Go to the Compare section in the Review tab.

3.Click on the dropdown menu and select Compare. This will open a new dialog window.

4.Select the revised document by clicking on the File icon.

5.Click OK.

Word will now compare the two documents in order to determine which changes have been made. Once it is done a new document will be created containing the revised information as well as the changes made to the document. These changes will be displayed in exactly the same manner as using the track changes option.

To get out of this mode, just click on the close button on the top right side of the screen.

When comparing documents, make sure that you have both the original and edited document. It is also important that it is saved using different file names.

Activity 1.4

1.Open the file called Act1.3 (Old Sultan).docx and perform the following activities:

a.Hyperlink the word “Sultan” at the start of the paragraph “The plan” to the bookmark “Wildboar”.

b.Search for the phrase “I will be true to my master” and bookmark it by the name “Loyalty”.

c.Search for the word “revenge” and bookmark it as such.

d.Rename the bookmark “Loyalty” to “Betrayal”.

e.Insert a cross-reference between “revenge” and “Betrayal”.

f.Delete the bookmark “DOG”.

Save and close your document.

UNIT
1.4 Electronic forms

An electronic form is a digital version of the standard paper form that you have most likely completed multiple times in your life. It is designed to capture, validate and submit data in forms.

Unlike the paper form which requires the user to obtain, complete and hand in the form, the electronic form can be completed and submitted from anywhere as long as the user has access to a computer and an internet connection. Other advantages of using electronic forms include:

Saving on printing costs.

Easy to send between various people to obtain or update the relevant information.

Quality control of the data that is collected, for example a control can be set up to make sure that the user enters a valid email address, phone number, etc.

The form can be integrated with other programs, ensuring that all information is always up to date.

Data is automatically formatted, calculated, validated and recorded.

Forms software allows you to track the form’s progress through each step of a process.

The form can be set up to create an order or invoice, automatically calculating prices, tax, shipping costs, etc. This feature reduces calculation errors and saves time.

IMPORTING DATA

In previous years you have learned that the following data types can be imported into a Microsoft Word document:

Text files (.txt)

Word documents (.docx)

Excel spreadsheets (.xlsx)

Web pages (.html)

Image (.png).

These data types can also be imported to a word document by using the procedures discussed in the tables below.

THE POWER OF E-FORMS

If you want to know more about how e-forms are used, scan the following QR code.

https://www.documentlocator.com/resources/knowledge/what-are-electronic-forms.htm

Table 1.4: Methods to import data into Word

Table 1.5: Basic procedures for importing data

Activity 1.5

Open the file called Act1.5(Water).docx and perform the following activities:

1.Add a date field next to the word “Date”. Make sure that it will always be the current date.

2.Correct the spelling of “Female” in the dropdown form field control next to the text “Gender”.

3.Add a check box form field control to the left of the text “Older than 60”.

4.Add the text form field control next to the text “Number of adults in the household”.

5.Edit the format of the control next to “How much do you pay a month?” so that it will always display an “R” in front of the amount(s) displayed.

6.Apply leader tabs at the bottom of the form so that it displays as below.

Save and close the file.

IMPORTING DATA USING ELECTRONIC FORMS

Microsoft Word allows the user to create a customised electronic form in order to gather data. This data is then stored as a text file which can be exported to a spreadsheet or imported into another word document.

In this section we will look at how you can create an electronic form to gather data and how that data can be exported to a spreadsheet.

TO CREATE A NEW TEMPLATE

We recommend that you have a quick look at how to create a form and add content to it, before going through the step by step procedure.

https://www.youtube.com/watch?v=oHHuGPNeQ7Y

Guided Activity 1.20

1.Open a New document.

2. Type the details that you want the users to fill out such as their Name, Email Address, etc.

You can use a table to help organise the data fields. Use one cell to enter field labels and then use the cell adjacent or below that cell to enter the fillable fields. Fillable fields allow the form users to enter data.

When creating your own template make sure to:

Determine the fields that the form will have and their placement in the form.

To set each field type, you must decide whether the field will collect data in text form, date form, or a number. It is also possible to create a check box or a dropdown list to choose an answer from.

Enter the right questions. Determine what type of data you are looking for and tailor the questions to gather that specific data.

A simple way to lay out a form is to draw a table and then insert the fields within the cells of the table.

3.Once you have created the template, make sure to save the file using a relevant file name. The following is an example of such a template.

Step 3 Add content to the form

Here is how you can add content to the electronic form:

1.Select the cell in which you want to place a field.

2.Make sure the Developer tab is enabled.

3. Open the Legacy Tools from the Controls section on the Developer tab.

To enter text in a field select the Text control (first icon).

Double click on the text field you have inserted.   

Adjust the properties of this field by opening the different options to choose from:

If the answer requires a Yes/No answer, choose the Tick Box.

Double click on the Check Box field to open the options: .

Choose the options needed for your form.

To add a dropdown list from which to choose the answer, a dropdown field can be inserted.

Double click on the field to add the options to the dropdown list.

Note that to all the fields above a Help Text can be added.

It could be displayed on the Status Bar or as the Help Key.

A frame can be added to the fields by selecting the Insert Frame option. By selecting the Form Field Shading option, all fields will be filled with gray shading.

The last option, Reset Form Fields, resets the fields inserted.

Save the form.

The form needs to be restricted so that people, who will be filling it in, are not able to change anything on the form:

On the Developer tab choose Restrict Editing, tick the box at the first option and choose Filling in Forms at the second option.

Choose Yes, Start Enforcing Protection. It is optional to enter a password. When the form is protected it can only be filled in and not edited. Beware that if you enter a password and forget what it is, it is impossible to open the form to edit it again.

The form is sent to people you want information from. They complete the form electronically and return it to you. You save each form individually.

Guided Activity 1.21

The following activity will guide you to create an electronic form:

Open the file called Act1.5(Water).docx and perform the following activities:

1.Add a date field next to the word “Date”. Make sure that it will always be the current date.

2.Correct the spelling of “Female” in the dropdown form field control next to the text “Gender”.

3.Add a check box form field control to the left of the text “Older than 60”.

4.Add the text form field control next to the text “Number of adults in the household”.

5.Edit the format of the control next to “How much do you pay a month?” so that it will always display an “R” in front of the amount(s) displayed.

6.Apply leader tabs at the bottom of the form so that it displays as below.

EXPORTING THE DATA TO EXCEL

Using electronic forms is a nice and easy way to collect data from users. However, in order to process and analyse this data it is recommended that you export the data to a spreadsheet program such as Excel. To avoid having to manually enter the data in the Excel spreadsheet, we recommend making use of the Excel text import wizard. This tool examines the text file in order to make sure that the data is imported in a way that you want.

Guided Activity 1.22

In the most recent versions of Excel, the text import wizard has been disabled. You can enable the wizard as follows:

1.Open an Excel document.

2.Click File.

3.Click on Excel Options.

4.Select the Data option.

5.Under Show legacy data import wizards, tick From Text (Legacy).

6.Press OK.

Save and close your document.

Guided Activity 1.23

1.Go to the Data tab, select From Text on Get External Data.

2.Select the data file, and click on Import.

3.Select the text file containing the data and click Import. The text import wizard will now open. Remember that this only works if the data has been saved as a .txt file. Refer to Guided Activity 1.20, Question 5.

4.Select the Delimited option under data type and click Next.

5.Tick the delimiters that were used to separate the data for example Tab and click Next.

6.When you are done setting the format for all the columns click Finish. This will open a dialog box asking you where you want to import the data.

7.Select the correct sheet and cell and click OK. The data will now be imported into the spreadsheet.

8.After the data is imported into the spreadsheet, make sure to save the file with a relevant name. The data in the sheet can now be processed and analysed, after which it can be exported to the main word document using the mail merge feature.

Save and close your document.

REVISION ACTIVITY

Open the Chapter 1_Revision Activity word processing document, which contains an overview of some of South Africa’s famous visual artists.

1.Follow the instructions below to change the cover page.

a.Insert your name & surname in the author placeholder. (1)

b.Change the format of the date field to: June 12, 2019. (1)

c.Apply any shadow style of your choice to the picture. (1)

2.Change the page numbers of the document as follows:

a.Start the numbering on the page after the table of contents with 1. (1)

b.Change the page numbering format to “Page X of Y”. (1)

c.The cover page and the table of contents page should NOT be numbered. (1)

3.Insert an automatically generated table of figures on the second page below the text “Figures”. (2)

4.Remove the comment on the third page. (1)

5.Display only page 5 on a landscape page. (2)

6.Insert a cross reference to the caption “Figure 3: Irma Stern” to replace the text “(Insert cross reference here)” at the end of the document. (2)

7.Find the text “Gallery Viewing Form” on the last page of the document and do the following:

a.Find the check box form field next to the text “Female” and set the default value of the check box to checked.

b.Add “Thursdays” as an option to the combo box control next to the text “Days”.

c.Format the text form field control next to the text “Cell Number” to:

i.Accept only numbers

ii.Display only 10 characters

iii.Display help text “Cell” when the Help key (F1) is pressed

iv.Add a text form field next to Name & Surname and make sure that when the user types their name and surname will appear in capital letters. (7)

8.There are several methods to import data into Word. Describe the electronic form method and supply an example. (2)

TOTAL: [22]

AT THE END OF THE CHAPTER

CHAPTER OVERVIEW

Unit 2.1 | Mail merge
Unit 2.2 | Objects

By the end of this chapter, you will be able to:

Use mail merge to merge a word document with a datafile.

Import data as a linked or embedded object

INTRODUCTION

One of the big advantages of modern living is that it is easier than ever before to collect data on all kinds of things from around the world. However, just having access to the data is not very useful as you still need to analyse the data in order to obtain relevant information that you can use for your project, research or personal interest, as well as present the information in a way that can be understood by others.

In this chapter we will look at how data can be merged with a word processing document without having to retype any of it. This will not only save you time and effort, but also ensure that the data is kept intact and that no mistakes are made. We will discuss some of the techniques you can use in order to import the data, such as mail merge, as well as how you can embed and link the data to a word document.

UNIT
2.1 Mail merge

One of the many advantages of using a word processing program such as Microsoft Word is that it allows you to merge data with a variety of data sources, including:

Outlook contact lists

Excel spreadsheets

Word data files

Access databases

Text files.

A standard data source consists of rows and columns, with the first row used to indicate the Field names of each column.

The process used to link this data is called a mail merge. Mail merge is traditionally used to create multiple documents at the same time. These documents are identical in layout, formatting, text and graphics with only specific declared sections being personalised. Examples of documents that can be created using a mail merge include letters, envelopes, email labels and reports.

It is possible to add two fields next to each other. These fields can then be separated by using a space or punctuation marks.

HOW TO MAIL MERGE IN MICROSOFT WORD

Mail merge is a word processing tool that allows you to easily produce and send personalised letters, labels, envelopes, statements and other information stored in a list, database or spreadsheet to your audience.

When performing a mail merge using a mailing list, you will use the following three documents:

Main document: The first is the main document containing the text and graphics that you want to send. The sections contained in this document will be identical for each user on the email list.

Mailing list: The second document is the mailing list containing the personal and contact information for each person that you want to send the email to. This document will be used to populate the placeholder fields in the merged document.

Merged document: The final document is the merged document containing information from both the main document and mailing list.

Figure 2.1: Documents in the mail merge process

In simple terms, the mail merge process takes the information from the mailing list and puts it in the main document. This results in a personalised message for each person on the contact list, while keeping all the core information the same.

Guided Activity 2.1

1.Prepare the document by opening a new blank document. (It is also possible to use an existing letter. Browse for it on your computer and open it before you open Mailings.) Click the Start Mail Merge option located in the Start Mail Merge section of the Mailings tab. This will open a dropdown list.

2.Select the document type from the dropdown list. In this case it will be Letters.

3.Type the letter. You will only be typing the common parts of the letter, i.e. the text that does not change. Type the fields you need in ALL CAPS. This is to make it easier for you to find the text that must be replaced. Use short descriptive phrases. Save the main document.

4.Click the Select Recipients option and select Use an existing list. Browse for the list on your computer. (It could be a word processing table, spreadsheet or database.)

5.Edit the recipient list. Here you can sort or filter to find only those recipients you want to include. For example, only Grade 12 learners who have achieved less than a certain percentage, or only recipients whose surnames start with certain letters.

6.Insert placeholders by going to the place where you want to insert personalised data and clicking the Insert Merge Field option. Select the field that you want to insert, for example Title, First Name or Email Address.

The Insert Merge Field menu will appear, showing the fields according to the recipient list.

To insert the field into the main document, highlight the placeholder in the text and choose the relevant field to replace the text in the document.

Continue adding fields until the document is complete.

7.Preview the final mail merge document by clicking Preview Results.

8.If you are happy with the document, click the Finish & Merge option, select Edit Individual Documents.

Select Complete Merge.

In the bottom left hand corner of your screen you will see that many pages are created, each containing personalised information.

Save the merged document (Letters) with a new name.

MAIL MERGE WIZARD

You can also do a mail merge using the mail merge wizard. If you want to know how to do it this way, scan the following QR code:

https://www.youtube.com/watch?v=UusH-4DvFaw

If this step seems too complicated, you can use the Step by Step Mail Merge Wizard located on the Start Mail Merge dropdown menu. The wizard will open a mail merge panel on the right margin which will take you step by step through the whole mail merge process.

Activity 2.1

Do the following on your computer and save it as Act 2.1 (Your initials and surname).

You are turning 18 on the 10th of the month and are planning a party for the coming Saturday evening.

1.Create an invitation (your main document) to your friends, inviting them to the party. To make it easier, you decided to do a mail merge for the invitation.

2.Add the following fields in the invitation:

Your guest’s name

Contact Number of guest

Name of partner

3.Make the mailing list for at least 12 names. Type a new list.

4.Save the merged document as Act2.1(Merged).

Save and close all documents.

USING AN EXCEL SPREADSHEET FOR A MAIL MERGE

Excel spreadsheets can be used to store many different types of data including text, calculations and graphs. In order to make use of this data in reports, you will need to know how to import the data to a word document. It can be done in one of two ways. The first is to make use of the mail merge option, and the second to link or embed the data in the word document.

In this section we will take a look at how to merge an Excel spreadsheet with a word document. Embedding the data in the word document will be discussed later in the chapter.

PREPARING THE DATA

The first step of importing data to a word document is to set up and prepare the data. This includes:

Ensuring that the column names in the spreadsheet are the same as the field names you want to insert into the word document.

Checking that all the data that you want to import is present on the same sheet.

Changing or adding additional data.

Formatting all data so that Word can read their values. This includes percentages and currencies.

Saving the spreadsheet where you will be able to locate it with ease.

Once the spreadsheet data source is ready and saved, it is ready for the mail merge process.

Guided Activity 2.2

1.Open the word document.

2.Choose the mail merge type from the Start Mail Merge dropdown list.

3.Click Select Recipients and select Use Existing List. This will open a new window asking you to choose the data source.

4.Select the Excel spreadsheet and click Open.

5.Select the sheet where the data is located and click OK. This will now connect the spreadsheet to the word document.

6.Click Insert Merge Field and select the field that you want to be included in the document from the Insert Merge Field dialog box.

7.Repeat Step 6 until all the relevant fields have been inserted.

8.Preview the final mail merge document by clicking Preview Results. You can navigate through the document by using the Next and Previous buttons.

9. If you are happy with the document, click the Finish & Merge option and select the option that will best work for you.

Always remember to save your work once you completed the mail merge. The saved file will stay connected to the data source allowing you to reuse the document in future.

Save and close your document.

Activity 2.2

Open a new word document and complete the following activity:

1.Open the Act2.2(Dance) word document and complete the following activity.

2.Prepare a mail merge using the spreadsheet Score.

3.Insert merge fields for the “Name”, “Surname” and “Final score” in the appropriate positions.

4.Complete the merge and save the merged document as Act 2.2 Merged.

Save and close all documents.

Activity 2.3

Complete the following activity:

1.Open a new Excel document and import the data from the file Export.txt into Excel. Save the document as Export_Salary.

2.Use this data as your data source to create a mail merge.

a.Insert merge fields for Name, Player ID, Game, and Salary.

b.Complete the merge and save the document as Act2.3(Merged).

Save and close all documents.

IMPORTING THE DATA TO A WORD DOCUMENT

It is not always necessary to transfer the data to Excel, instead you might want to transfer the data directly to a word document so that you can edit it.

Guided Activity 2.3

Importing data to word can be done like this:

1.Click the Object dropdown list located in the Text section of the Insert tab.

2.Click Text from File.

3.Select the file and click Insert.

The contents of the file has been inserted in a new document.

Save the file with a new name and close your document.

UNIT
2.2 Objects

Microsoft Word allows the user to personalise and enhance a text document by enabling it to insert objects such as pictures, graphs and tables. These objects can then be manipulated, linked or embedded as the user requires.

MANIPULATING A TABLE

Once you have created a table, you can edit it. Select the table by clicking on it. This will enable the Design and Layout tabs on the ribbon. The design tab allows you to change the way the table is displayed using the options such as table styles, as well as table borders and shading. The layout tab allows the user to apply different formatting commands such as adding and removing rows and columns, adjusting the size of cells, merging cells, and allowing the user to distribute the data.

MANIPULATING AN IMAGE

When you insert an image into a document, you can wrap or resize the picture. The sizing handles are tiny circles that are found on the corners and edges of an image once you have selected the image. You can also right click on the image to bring up a quick menu allowing you to further edit the image.

When you select an image, the Format tab appears on the ribbon. This tab contains different formatting options such as:

Resizing and cropping pictures: Cropping is when you cut the edges of a picture, while keeping the part of a picture you want. For example, if you have an image with a frame around it, you can crop the frame out of the image so that only the main image is displayed.

Rotating pictures or applying text wrapping options: The type of text wrapping used will show the way text flows. This could determine if text will flow around a picture, in front of it or behind it.

Applying different styles and other formatting effects to the picture: This includes changing the border of the picture.

Adjusting the brightness, contrast and the colour of the picture.

EMBEDDING OBJECTS

Embedded objects allow the user to take the information from another document and make it part of their main document. Due to the nature of this process, the data will not be affected by any changes to the source document. Instead you will have to edit the data as you would a word table. Use Insert, Object, Text from the file. Select the document or picture from your computer.

LINKING OBJECTS

The data for a linked object is stored in the source document. This means that any changes in the source document will be reflected in the main document, while any changes to the main document will not be reflected in the source document.

Here are a couple of things that you should be aware of when using linked objects:

You can update the object by selecting the object, right clicking and choosing the Update link.

If you move or store the linked spreadsheet file in a new location, it will break the link. You will then have to relink the files.

If you double click on the spreadsheet data in the word document, it will open the spreadsheet file allowing you to edit the data.

Activity 2.4

Open new word document and complete the following activity:

1.Import that data from the Export_Salary.docx document that you created in the previous example. The data must be imported as a linked table.

2.Format the table as follows:

a.Add a border with a thickness of 2 to the table.

b.Change the spacing between the cells to 1.

c.Change the background colour of the first row to any colour of your choice. Save and close the file as Act2.4(Export_Salary).

REVISION ACTIVITY

The development of South Africa has always been closely connected to the country’s gold resources. You found an article about gold, but it needs to be edited and a mail merge needs to be performed.

Open the Chapter 2_Revision Activity word processing document and do the following changes.

1.Change the page size of the document to A4. (1)

2.Set the top margin of the page to 2 cm AND the gutter position to left. (2)

3.Find the heading “AN UNSPEAKABLE LOVE OF GOLD” at the beginning of the document.

a.Create a new style called “LoveGold” as follows:

Base the new style on the “Heading 1” style.

Centre the text of the new style horizontally.

Apply any type of paragraph border.

Apply the new style to the heading. (5)

4.Find the SmartArt diagram towards the end of the document. Complete the diagram to appear as follows:

a.Fill the last shape with the 1Gold_Goud picture.

b.Add the text “Gold Bricks” to the last shape.

c.Change the SmartArt layout to a chevron shape as in the example above.

d.Add the text “Processing Gold” as a caption to the diagram, as shown above. (4)

5.Copy the graph from the Graph spreadsheet to page 2 of this document at the placeholder. Make sure that changes to the graph in the source document can be updated in your word document. (3)

Save and close the document.

6.A statement must be prepared for clients of a jewellery business. Open the Statem_Staat document which will be used for a mail merge.

a.Link the Statem_Staat document with the data source, the tbClients table, in the Acc_Rek database.

b.Replace the text in the cell next to the “BILL TO” area to display the initials and surnames of the clients listed in the tbClients table.

c.Replace the text in the cell next to the “CITY” area to display only the city of the clients from Durban listed in the tbClients table.
NOTE: Ensure that the merge fields have the correct spacing.

d.Complete the merge and save the document as Statement_Merge. (5)

Close the Statem_Staat and Statement_Merge documents.

TOTAL: [20]

AT THE END OF THE CHAPTER

CHAPTER OVERVIEW

Unit 3.1 | Advanced word processing techniques
Unit 3.2 | File management
Unit 3.3 | Integration techniques

By the end of this chapter, you will be able to:

Apply advanced word processing techniques in a word document.

Prepare and publish a document.

Explain integration techniques.

INTRODUCTION

Word processing applications have evolved over time, from software that allowed the user to simply type a letter, to powerful software applications that allow the user to create and publish a detailed report containing data, images, and tables. In this chapter we will be taking a closer look at some of the more advanced techniques that you can use in order to improve a document.

REVISE WORD PROCESSING SKILLS

Complete the following activity to revise your knowledge on word processing skills.

Activity 3.1

Open the document Act3.1(Effect) and complete the following activity.

1.More information should be added to the cover page. Complete the cover page as follows:

a.Insert your name and surname under the heading “Author” in the text content control. Make sure not to change the document properties.

b.Add the Climate picture found in the Chapter 3 folder in the picture control.

c.Apply the “Global” style to the title “10 Most Common Effects of Global Warming”.

2.Adjust the quote “One of the most controversial topics in today”s age is global warming” so that it will NOT appear in the table of contents. Note that the table of contents contains only one level of heading styles.

a.Update the entire table of contents on page 2.

3.Locate the text “global warming” in the first paragraph of the document and apply any text outline to this text.

a.Make sure not to use WordArt/Fontwork.

4.Apply automatic numbering to the heading “Rising Sea Levels”.

Save and close the document.

UNIT
3.1 Advanced word processing techniques

In the previous chapter, we have taken a look at some of the more specialised tools that you can use to edit and improve a word document. In this section, we will take a step further and look at some of the more advanced tools.

NON-BREAKING SPACES

One of the built-in features of word is that it automatically moves the cursor to the next line once you reach the end of a line. This could lead to some potential problems as words that should go together can be broken up and displayed over two lines. In order to prevent this from happening, you can make use of a non-breaking space which prevents the automatic line break at its position.

Guided Activity 3.1

1.Go to the space between the two words that you want to keep together.

2.Instead of pressing space, press Ctrl+Shift+Space.

3.A non-breaking hyphen can be used in a similar manner to the non-breaking space by pressing Ctrl+Shift+-.

ADVANCED COPYING

When copying text from one document to another, you must be aware that the text is not the only thing that you are copying. You are also copying the styles of the selected text. This is because by default, word documents retain the formatting of the source material.

In order to prevent this and save yourself from having to re-format the text, you can select to copy text only. This option will let you copy the selected text, without copying over the styles and other formatting.

Guided Activity 3.2

1.Open the GAct3.2(webcams).docx and select the text you want to copy to a new blank document.

2.Copy the text by right clicking and selecting copy or by pressing Ctrl+C.

3.Right click where you want to paste the text in the document.

4.Select the Text Only option from the Paste options.

Save and close your document.

LINE SPACING

Line spacing is the space between each line in a paragraph. The default line spacing for most word documents is 1.15, with paragraphs followed by a blank line and headings with a space above them. However, this does not mean that you must only use these settings. If you are looking to use a different line spacing based on publication requirements or personal taste, you can change the line spacing for a specific part of the document, or for the document as a whole.

Guided Activity 3.3

1.Select the Home tab.

2.Go to the Paragraph section and click Line Spacing.

3.Select Line Spacing options from the dropdown menu. This will open a new paragraph window.

4.Go to the Indents and Spacing section, select the line spacing you want to use from the dropdown list and click OK.

5.The Indents and Spacing section also allows you to change the spacing before or after a paragraph, as well as include or exclude a space between paragraphs with the same style. Use paragraph spacing between paragraphs instead of entering.

DOCUMENT STRUCTURE

It can get very frustrating to navigate between the various headings of a large word document. Fortunately, word processors have an easy solution to this problem; namely the Navigation Pane. The Navigation Pane is a toolbar that appears on the left side of the word document and gives the user the following options:

View the different headings in the document: This allows the user to easily navigate between the various heading in a document, re-organise the document by moving, deleting or adding headings and content, and format headings by right clicking on the heading and selecting one of the various options, such as promote heading (moves it up one level) and demote heading (moves it down one level).

View a thumbnail of each page: This allows the user to have a visual representation of the page layout, images and text of each page, as well as an easy way to navigate between different pages.

View search results: This allows the user to search for specific words or phrases, as well as navigate through the document to find each instance the word is used.

Guided Activity 3.4

1.Select the View tab and go to the Show/Hide section.

2.Tick the Navigation Panel option. This will open the Navigation Panel on the left side of the word document.

3.Select the option you want to use by clicking on either Headings, Pages, or Results.

4.Use the Search document bar to search for a specific word or phrase in the selection option.

Activity 3.2

Open the document Act3.2(Gold) and complete the following activity:

1.Change the page size of the document to A4.

2.Set the top margin of the page to 2 cm and the gutter position to left.

3.Format the first two paragraphs in the document as follows:

a.Change the line spacing of the first paragraph in the document to multiple, set at 1.2.

b.Indent the FIRST line of the second paragraph to 2 cm.

4.Replace all occurrences of the text “Nano” with the text “nano” in small caps.

5.Find the word “alchemists” in the sixth paragraph of the document.

a.Replace the word with a synonym from the list of suggestions given by the word processor.

Save and close the document.

UNIT
3.2 File management

The first thing that comes to mind when talking about a word processing document usually includes some kind of text that can be printed. Examples of this include CVs, written assignments, and reports. However, you might not always want to print the word document but instead share it with a friend or colleague.

When looking to share the word document with someone else you need to take one important thing into consideration, they might not be able to read the word document. To prevent this problem, you can export/publish the word document to another file format such as a PDF (Portable Document Format) or XPS (XML Paper Specification).

PREPARING THE DOCUMENT

The first and most important step for exporting any word document is to make sure that the document is ready to be published. This includes:

Making sure that all track changes have either been accepted or rejected

Deleting all comments

Deleting all highlights

Checking that all links (hyperlinks and bookmarks) are working as intended

Completing the final formatting of the document.

PUBLISHING THE DOCUMENT

Once the document has been written, edited, formatted and all changes implemented it is finally ready to be published. The method used to publish the document will depend on your goal. If you wish to publish the document electronically, you will make use of the Share option. If you wish to publish the document as a PDF, you can make use of either the Print to PDF, or export to PDF options.

Guided Activity 3.5

1.Open the GAct3.5(Share).docx file.

2.Click the File tab on the menu and select the Share option. This will open a new window giving you the ability to share the word document using either Microsoft Outlook or OneDrive.

3.Select the option that best suits your needs.

4.When sending a word processing document using Microsoft Outlook, you can choose to send the file as either a Word document or PDF.

Guided Activity 3.6

1.Open the GAct3.6(pdf).docx file.

2.Click the File tab on the menu and select the Print option. This will open a new print menu.

3.Click the dropdown menu under the Printer section and select Microsoft Print to PDF.

4.Click the Print icon. This will open a new window asking you to name the new PDF document as well as choose a location where the document will be saved.

5.Name the PDF document, select a file location, and click Save to generate the PDF.

Guided Activity 3.7

1.Open the GAct3.7(Export).docx file.

2.Click the File tab on the menu and select the Export option. This will open the export menu.

3.Select the Create PDF/XPS Document option and click the Create PDF/XPS icon. This will open a new window asking you to name the new PDF document as well as choose a location where the document will be saved.

4.Name the PDF document, select a file location, and click Save to generate the PDF.

Guided Activity 3.8

This method can also be used to convert the word document to other file formats such as Word 97-2003, Plain text, Web page, and Rich Text Format.

This can be done as follows:

1.Open the GAct3.8(Convert).docx file.

2.Open the Export menu.

3.Select the Change File Type option.

4.Select the file type you want to convert the word document to.

5.Click Save As. This will open a new window asking you to name the new document as well as choose a location where the document will be saved.

6.Name the document GAct3.8(<<YourName>>).docx, select a file location, and click Save.

UNIT
3.3 Integration techniques

The integration of Microsoft programs enables you to take data from a database and make it part of the word processing document. It saves the user both time and effort and promotes the use of integrated programs.

However, not all data stays the same for a long period of time.

Linked objects allow the user to automatically update a document based on any updates that were made in the linked document. An example is using a graph in a word processing document that is linked to external data in a spreadsheet. If you update the data in the spreadsheet, the graph in the word document will automatically be updated too.

The main difference between embedded and linked objects is that embedded objects do not change if you modify the source file.

Other examples of integration techniques include hyperlinking and mail merging.

EMBEDDING OBJECTS

Embedded objects allow the user to take the information from another document and make it part of their main document.

Guided Activity 3.9

How to insert an embedded object:

1.Open the GAct3.9(Insert).xlsx spreadsheet.

2.Copy the data that you want to link to the word document.

a.Highlight the data.

b.Right click and select copy. (You can also use Ctrl+C).

3.Open the word document.

4.Select where you want to insert the data.

5.Click the dropdown menu of the Paste option. This is located in the Clipboard section of the Home tab.

6.Select Paste Special. This will open a new window.

7.Select Paste.

8.Select Microsoft Excel Worksheet Object format.

9.Click OK.

The information is imported into the word document.

Save and close your document.

LINKING OBJECTS

The data from linked objects is stored in the source document. Any changes to the source document will thus also affect the linked object.

Guided Activity 3.10

HOW TO INSERT A LINKED OBJECT:

1.Open the GAct3.10(Linked).xlsx spreadsheet.

2.Highlight the data.

3.Right click and select Copy. (You can also use Ctrl+C.)

4.Open the word document.

5.Select the place where you want to insert the linked data.

6.Click the dropdown menu of the Paste option. This is located in the Clipboard section of the Home tab.

7.Select Paste Special. This will open a new window.

8.Select Paste link.

9.Select the format in which you want the data to be inserted.

10.Click OK.

The following data has been inserted from the Excel sheet.

HYPERLINKING

Hyperlinks allow the user to link an object or piece of text in the document with an external program, data set or website. It can also be used to link one part of a document to another part in the same document.

Guided Activity 3.11

Here is how to insert a hyperlink in a document.

1.Select word or object that you want to link.

2.Click the Link option in the Links section of the Insert tab. This will open the Insert Hyperlink menu.

3.Select the type of hyperlink you want to create by clicking one of the options under Link to:

a.Excise File or Web page

b.Place in This document

c.Create a New Document

d.Email Address

4. Select the place or document, or type in the email address or web page address and click OK. Just remember, after you create a hyperlink you need to make sure that it works!

Activity 3.3

Open a new word document and complete the following activity:

1.Import the data from the Data.xlsx spreadsheet to the word document as an embedded object.

2.Export the word document as a PDF.

Save and close the documents as Act3.3(Data).

REVISION ACTIVITY

A document was created to give background information on cyber bullying.

Open the Chapter 3 Revision_Activity word processing document and do the following changes:

1.Format the first page as follows:

a.Insert a table of contents and do the following:

Display TWO levels in the table of contents.

Use all other default settings. (2)

2.Format the document as follows:

Change the bottom page margin to 2 cm.

Add a page border of your choice to the document.

Include a watermark in the document as follows:

Use the Bully image in your folder.

Apply a washout effect.

Set the scaling to 150%. (5)

3.Find all occurrences of the words “cyber bullying” and ensure that these two words appear as ONE word, in other words “cyberbullying”. Also change the formatting to a red font colour and italics. (2)

4.Justify the text of only the first paragraph in the document and change the line spacing of the first paragraph to Multiple 1.2. (2)

5.Insert the table Incidents from the Report spreadsheet as a linked object under the heading Cyber Bullying Statistics. (2)

6.Locate the sentence “Despite the ....” to “... foundation” at the bottom of page 2. Apply a 2 pt shadowed border and green shading (highlight) to this paragraph. (2)

7.Locate and select all the text below the heading “Reducing cyber bullying statistics” at the bottom of page 3.

a.The selected text must appear in two equal columns with a space of 0.5 cm between the columns.

b.With a line between the two columns. (3)

8.Export and save a copy of this document as a PDF document with filename: Export_PDF. (2) Save and close all documents.

TOTAL: [20]

AT THE END OF THE CHAPTER

CHAPTER OVERVIEW

Unit 4.1 | Advanced functions
Unit 4.2 | Date and time calculations

By the end of this chapter, you will be able to:

Use advanced functions in order to analyse data.

Use text functions to modify data

INTRODUCTION

In the past, data was hard to retrieve because you had to physically collect it using techniques such as interviews, reviews and observation. The data was then physically logged in a book, processed and analysed by hand in order in extract useful information. However, as times changed and data became more abundant due to the internet and computers, data processing applications such as Excel were created.

Excel allows the user to digitally store data in a worksheet. The data can be imported from other sources or manually entered into the worksheet. Once it has been collected, the user can use a variety of functions and formulas to process and analyse the data quickly and efficiently. The analysed data can then be represented as graphs or even exported to other applications to be used in presentations. Programs such as Excel enable the user to save a lot of time and effort.

Figure 4.1: Graphs representing data captured and analysed in Excel

In this chapter we will take a look at some of the more advanced functions to use when analysing the data. We will also go through text functions used to organise data, as well as look at how you can combine functions to do more in depth analyses. Finally, we will present you with a scenario, demonstrating how you can use functions to solve a problem.

FUNCTIONS

In previous years, you learned about operators, functions, and error indicators. Operators allow you to do basic calculations such as addition, multiplication, subtraction and division. While functions use built in formulae to allow you to do more advanced calculations, error indicators are notifications that help you determine what is wrong with the calculations.

To help you get a better understanding of these, we have listed the most commonly used operators, functions and error indicators in the tables below.

Table 4.1: Operators

Table 4.2: Functions

Table 4.3: Error indicators

FUNCTIONS

To learn more about functions, watch the following video.

https://youtu.be/-9d4m79twdA

UNIT
4.1 Advanced functions

Spreadsheet programs such as Excel allow the user to make use of advanced formulas and functions in order to analyse data and get answers to more complex questions. In order to help you make use of these advanced functions, we will look at the following:

Complex functions such as Nested IF and vertical lookup

Error indicators such as #N/A

Variations on known functions such as rounding up and rounding down

Subtotal outline feature.

COMPLEX FUNCTIONS

Complex functions enable the user to analyse data quickly and efficiently. This allows the user to easily interpret the data so the information can be obtained, decisions made and questions answered.

Note

Remember to always start the function with an equal sign (=) and to separate each argument with a comma. In this case the function will be =if(C2>=80,“Yes”,“No”).

NESTED IF

The standard IF function can be used to look at the data in a cell and to see if it meets a basic condition. For example, the formula if(C2>=80,“Yes”,“No”) can be used to determine which learners have achieved a distinction for a specific subject.

The first part of the function is the IF statement. This implies that you are looking for a specific condition. The condition is contained in the first argument. The argument is the information in the parenthesis and consists of three parts. The first part is the condition, the second is the response if the condition is true and the third is the response if the condition is false. In this case the condition looks at the learners’ mark located in Column C2. If the mark is equal or above 80, then the condition is true and Yes will be displayed. If the answer is below 80%, the condition is false and No will be displayed.

Figure 4.2: Example of the IF function used to determine if a learner has obtained a distinction

To help you get a better understanding of the IF function, let us take a look at the following example.

Guided Activity 4.1

=IF(A2=”rainy”,“umbrella”,“cap”)

You are using an IF function to determine if you should take an umbrella or cap based on the weather. In order to do this, the IF function will consist of the following:

Indicating a function, =

The function name, IF

The condition you are looking for, e.g. equals rainy

The response if the condition is true, e.g. umbrella

The response if the condition is false, e.g. cap

One of the advantages of using a spreadsheet program such as Excel is that it provides you with an option to insert a function, without having to type the function manually. This can be done by making use of the insert (fx) function option that is located in the Function Library section of the Formulas tab.

Here is how to use the insert function option to create an IF function:

1.Select the cell in which you want to add the function.

2.Go to the Function section of the Formulas tab.

3.Click the Insert Function option. This will open the Insert Function window.

4.Select the function from the Select a function list.

5.If you do not find the function that you are looking for, type the function you want to use into the Search a function box and click GO. This will add the function to the Select a function list.

6.Once you have found the function, Click OK. This will open the Function Arguments window.

7.Add the logical_test, value_if_true, and value_if_false by typing the conditions in the appropriate space.

8.Click OK to insert the function.

If you are having difficulties finding the function you are looking for, change the function category by selecting the category where the function will be located from the Select a category dropdown list.

As you can see, the IF function is very useful for analysing data sets. But what if you have more than one condition that you want to check? Then you can use a nested function. A nested function is an advanced function where one function is placed within the arguments of the other. To better illustrate this, let us look at some examples.

Guided Activity 4.2

Imagine you are a teacher at a school. In order to motivate your learners to study and improve their marks, you decide to give them a free period based on the marks they achieved in their semester test. These are the conditions for each subject:

Lower than 70%, no free period

Between 70% and 90%, 1 free period

More than 90%, 2 free periods.

In order to identify how many periods each learner is allowed to take off, you can make use of a nested IF function to look at each subject the learner has written. For example:

Start the IF function as normal: =IF(
Add the first argument by looking if the learner has obtained a mark greater than 90%: =IF(B3>=90
Add the response if the condition is true: =IF(B3>=90,“2 free periods”
Add the response if the condition is false. In this case it will be our second (nested) IF function: =IF(B3>=90, “2 free periods”,IF(

Add the first argument for the nested IF function by looking if the learner has obtained a mark greater than 70%: =IF(B3>=90, “2 free periods”,IF(B3>=70
Add the response of the condition is true: =IF(B3>=90, “2 free periods”,IF(B3>=70, “1 free period”
Add the response if the condition is false: =IF(B3>=90, “2 free periods”,IF(B3>=70, “1 free period”, “0 free periods”

End the formula by closing all parentheses: =IF(B3>=90, “2 free periods”,IF(B3>=70, “1 free period”, “0 free periods”))

Figure 4.3: Example of a nested IF function used to determine the number of free periods for each subject

Guided Activity 4.3

Here is how to use the Insert Function option to create a nested IF for the previous example:

1.Select the cell in which you want to add the function.

2.Go to the Function section of the Formulas tab.

3.Click the Insert Function option.

4.Select the IF function from the Select a function list and click OK.

5.Add the logical_test, and value_if_true for if a learner achieves a test score of equal or above 90 by typing the conditions in the appropriate space.

6.For the value_if_false condition, add a second IF function by clicking the function name box located just under the Insert Function option. This will open a new Function Arguments window.

7.Add the logical_test, and value_if_true for if a learner achieves a test score equal or above 70 by typing the conditions in the appropriate space.

8.Add the value_if_false for if a learner achieves a test score below 70.

9.Click OK to insert the function.

Activity 4.1

Open the document Act4.1(Policy).xlsx and complete the following activity:

1.Format the worksheet as follows:

a.Apply a red-coloured fill to the merged cells A1:H1.

b.Cells A2:G2 have been merged. Change this merged cell so that cell H2 is also included in the merged cell.

c.Change the font colour of the text in the merged cells A2:H2 to white and left align the text.

d.Place a double line outside border around cells A1:H2.

2.Insert a formula in cell G4 to determine how long a policy has been in existence at Bona Secondary School.

3.As cyberbullying is a newer type of behaviour, it is not included in some policies. Add an IF statement in cell H4 to display an asterisk (*) if the policy includes cyberbullying (column F), otherwise the cell must remain blank or empty.

4.Insert a function in cell M4 to determine the average number of years (column G) that schools have been implementing the policies.

Save and close your spreadsheet.

VERTICAL LOOKUP (VLOOKUP)

At some point you will be looking for a very specific piece of information within the spreadsheet, like looking up the price of coffee on a menu. In order to find it you can scroll through and look for the information in the spreadsheet, or you can make use of the vertical lookup function.

The vertical lookup (or VLOOKUP) function allows the user to search for information in a spreadsheet by going through the data in a table or range of cells. It does this by searching vertically down the first column of the cell range for the given search value. Once it finds the value it moves to the column index number to look for the data. The data can then be displayed in the cell of your choice. The VLOOKUP function consists of the following:

=VLOOKUP(“Item name”,A2:B1000,2,FALSE)

An indication that you are using a function: =

1.The function name: VLOOKUP

2.The value you are looking for: name of an item

3.The cell range that contains the data: A2:B1000

4.The column index number: the column where the data is found and can be determined by looking at the column range; first column is 1, second 2 etc.

5.The appropriate match: argument can be either true or false. If you select true then it will accept any data that is similar to the value you are looking for. If you select false it will look for an exact match to the value you are looking for.

Figure 4.4: VLOOKUP

There might come a time where you are not looking for an exact match, but rather for a similar value. This is especially useful in a situation where you are looking for a value closest to your specified value. For example, you are provided with a list of numbers and asked to find the number closest to 25. Using the True argument in the Vlookup function will cause the search to compare each value to the specified value. If the value is lower than the specified value, the search continues. Once the VLOOKUP function finds a value that is higher than the specified value (29 in the image below), the search stops and the pervious value is returned (22 in the image below).

To better illustrate how this function works let’s look at the following example.

Guided Activity 4.4

One of the customers at your shop won a mystery prize. You need to look up the email address of Neron McCaffery. Open file GA 4.4(Personal Info).

To answer this question, you can either scroll through all the names, which will take hours because there are 1000 names on the list, or you can make use of the VLOOKUP function as follows:

Start the VLOOKUP function as normal: =VLOOKUP()

Add the value you are looking for: =VLOOKUP(E2,… )

Add the cell range that contains the data: =VLOOKUP(E2, A2:B1000,… )

Add the index number: =VLOOKUP(E2, A2:B1000,2,… )

Indicate that you are looking for an exact match: =VLOOKUP(E2, A2:B1000,2, FALSE)

End the formula by closing all parentheses, =VLOOKUP(E2, A2:B1000,2, FALSE).

Figure 4.5: How to use VLOOKUP to find the price of your favourite game

Remember, once the function finds the name of the game it moves to the price column to find the price of the game. This price is then displayed in the cell where you put the function.

HORIZONTAL LOOKUP

Horizontal lookup or HLOOKUP functions are very similarly to the VLOOKUP function. The major difference is that instead of searching for information by going vertically down the first column of the cell range, the HLOOKUP function searches for information by going horizontally across the topmost row of the cell range for the given search value. Once it finds the value it moves to the row index number to look for the data. The data can then be displayed in the cell of your choice. The HLOOKUP function consists of the following:

=HLOOKUP(“Item name”,A2:E3,2,FALSE)

An indication that you are using a function, =

The function name, HLOOKUP

The value you are looking for, e.g. name of an item or a cell containing the value

The cell range that contains the data, e.g. A2:E3

The row index number. This is the row where the data is found and can be determined by looking at the row range. The first row is 1, the second 2 etc.

The appropriate match. This argument can be either true or false. If you select true, then it will accept any data that is similar to the value you are looking for. If you select false, it will look for an exact match to the value you are looking for.

Guided Activity 4.5

To complete this task, Jacob can retype all the data or he can make use of the HLOOKUP function as follows:

1.Start the HLOOKUP function as normal, =HLOOKUP(… )

2.Add the value you are looking for, =HLOOKUP(A3, … )

3.Add the cell range that contains the data, =HLOOKUP(A3, F1:M2, … )

4.Add the index number, =HLOOKUP(A3, F1:M2,2, … )

5.Indicate that you are looking for an exact match, =HLOOKUP(A3, F1:M2,2, FALSE)

6.End the formula by closing all parentheses, =HLOOKUP(A3, F1:M2,2, FALSE).

Activity 4.2

Open the spreadsheet Act4.2(Books).xlsx and use functions to complete the following activity:

1.Use a function to determine:

a.Which books have a hard copy that costs less than R200?

b.The eBooks that costs less than R150?

2.Use a function to determine the price of:

a.the ebook “James City”

b.the ebook “Dance of Doom”

c.the ebook “Words and Lists”.

3.Use a function to determine the name of the book that was published on 2018/10/30.

Save and close your spreadsheet.

AND/OR

You have a holiday work position available for female students over the age of 18.

You realise that you might also need male workers. Use the OR function to select male or female applicants over 18.

ERROR INDICATORS

Error indicators are Excel’s way of highlighting when something is wrong with the formula that you are trying to use. Understanding what each of these indicators mean will help you determine why the error occurred and how to fix it. For example, the error #N/A is an indication that the formula cannot find what it has been asked to locate, such as using LOOKUP functions to find a value that does not exist. Other possible causes of the #N/A error include:

looking for data that is similar, instead of an exact match

looking for the wrong value due to a spelling mistake

specifying an incorrect cell range that does not contain the value you are looking for.

To ensure you do not get this error, make sure each part of the LOOKUP function is correct and referencing the correct values and cell range.

VARIATIONS ON KNOWN FUNCTIONS

We have previously indicated that functions play an important role in allowing the user to process and analyse their data. One such function is the ROUND function. The ROUND function allows the user to round off a number based on whether the decimal value is above or below 5. Any number with a decimal value of 5 or above will be rounded up, while any number with a decimal value below 5 will be rounded down. The ROUND function consists of the following:

=ROUND(A1,0)

An indication that you are using a function: =

The function name: ROUND

The cell that contains the data

The number of decimal places you want to round to.

Figure 4.6: Example of using the ROUND function to round numbers off to the first decimal

As you can see the ROUND function can be very useful to help you manage how the data is displayed. However, did you know that Excel also contains variations on the ROUND function? ROUNDUP and ROUNDDOWN are two variations on the ROUND function that allow the user to have more control over how their data is displayed.

ROUNDUP allows the user to roundup all numbers no matter what the decimal values are, while the ROUNDDOWN function always rounds the number down to the nearest zero. The ROUNDUP and ROUNDDOWN functions consist of the following:

=ROUNDUP(A1,0)

An indication that you are using a function: =

The function name: ROUND

The cell that contains the data

The number of decimal places you want to round to.

Figure 4.7: Example of using the ROUND, ROUNDUP and ROUNDDOWN functions to round off numbers

Other examples of variations of known functions include:

SUMIF, this is a variation of the sum function which adds the cells within a specific range if specific criteria are met.

The SUMIF function is a combination of the SUM and IF functions. It is used to look at the data in a specified cell range and see if it meets a basic condition. If the condition is satisfied, the sum of the values within a second specified cell range is returned. The SUMIF function consists of the following:

=SUMIF(A2:A10,B4,C1:C10)

An indication that you are using a function, =

The function name, SUMIF

The cell range where you want to look, e.g. A2:A10. This cell range must contain numbers or arrays as blank or text values will be ignored.

The condition you are looking for. This can be a number (10), function (today()), cell reference (B4) or expression (“>2”)

The cell range that will be added. This is normally used to add additional cells that are not specified in the cell range. It is an optional addition to the function and leaving it out will cause the function to only add the values in the target cell range.

Guided Activity 4.6

Calculate the total sales of Vodacom airtime only. This can be done as follows:

1.In cell D2, enter the formula =SUMIF(B2:B10,”Vodacom”,C2:C10)

2.Press ENTER to see the result

Guided Activity 4.7

1.Open the file GA 4.7(Applications). You have to count all the female applicants who is 18 year and over.

Use COUNTIFS in cell B18.

2.Open the file GA 4.7(Tickets). You have to count all the tickets sold by learners whose name starts with an “A” in Grade 6

Use SUMIFS in cell C23.

COUNTIF is a variation of the count function which counts the number of cells that meet a specific criterion.

The COUNTIF function is a combination of the COUNT and IF functions. It is used to count the number of cells that meet a specific criterion, for example this function can count the number of times a certain item was bought in a shop during the day. The COUNTIF function consists of the following:

=COUNTIF(A2:A10,B2)

An indication that you are using a function, =

The function name, COUNTIF

The cell range where you want to look, e.g. A2:A10

The condition you are looking for, this can be a specific condition (“hat”) or a cell reference containing the condition (B4).

Guided Activity 4.8

Calculate the total number of Hats sold. This can be done as follows.

1.In the target cell, enter the formula =COUNTIF (B2:B11,C2).

2.Press ENTER to see the result.

You will notice that the function looks at the condition in cell C2, in this case Hat. The function goes through the target cell range, B2:B11, and counts how many times Hat occurs.

SUBTOTAL OUTLINE FEATURE

The Subtotal Feature allows you to automatically create groups and use function like SUM, COUNT, AVERAGE, MIN and MAX to summarise your data.

Guided Activity 4.9

1.Open the spreadsheet GA4_9(Subtotal).xlsx.

2.Sort the worksheet by the data you want to subtotal. In this example, we are sorting the spreadsheet data according to Birth Country.

3.Select the Data tab and click on the Subtotal command in the Options group.

4.Select a single cell in the range you want to apply the Subtotal command to.

5.The Subtotal dialog box will appear.

6.Click the drop-down arrow for the Use function: field to select the function you want to use. In this instance, select Sum and Number of Artworks_Aantal kunswerke.

7.To remove this feature, click on Remove All on the dialogue box.

Activity 4.3

Answer the following questions in your own words.

1.Explain what the following functions will do in Microsoft Excel.

a.COUNTIF

b.SUMIF

c.Nested IF

2.What is the difference between the VLOOKUP and HLOOKUP functions?

3.What is a complex function? Give two examples of complex functions.

4.Open the Act4.3(BookPrices) spreadsheet and use the appropriate function to determine:

a.The total price of all the ebooks published by Penguin Books.

b.How many books were ordered on 10th October 2018.

c.The name of the hard copy book that costs R100.00

d.The publisher of the book “Square Times”.

e.The total price of all the hard copy books published by Zondo Prints.

Save and close your spreadsheet.

SUBTOTAL OUTLINE FEATURE

The Subtotal Feature allows you to automatically create groups and use functions like SUM, COUNT, AVERAGE, MIN and MAX to summarise your data.

Guided Activity 4.10

1.Open the spreadsheet GA4_8(Subtotal).xlsx.

2.Sort the worksheet by the data you want to subtotal. In this example, we are sorting the spreadsheet data according to Birth Country.

3.Select the Data tab and click on the Subtotal command in the Options group.

4.Select a single cell in the range you want to apply the Subtotal command to.

5.The Subtotal dialog box will appear.

6.Click the drop-down arrow for the Use function: field to select the function you want to use. In this instance, select Sum and Number of Artworks_Aantal kunswerke.

7.To remove this feature, click on Remove All on the dialogue box.

UNIT
4.2 Date and time calculations

Spreadsheet programs such as Excel come equipped with the ability to insert the current date and time into the spreadsheet. This is useful because it allows you to do calculations, such as determining how long it has been since a certain date. However, before we take a look at those calculations it is important to understand how Excel handles date and time values.

In Excel the date is treated as the number of days since the start of the Excel calendar, which is 1 January 1900, and is represented by a numerical value. This is important as the difference in numerical values will help you determine the number of days since a certain date. For example, the date 2 November 2018 has a numerical value of 43406 and the date 25 July 2017 has a numerical value of 42941.

To calculate the number of days between these two dates, you subtract the values from each other: 43406−42941= 465 days.

Figure 4.8: Figure showing the date and time calculations

Unlike the date, time is not represented by a numerical value but rather by a decimal value. This decimal value is based on the fractions of 1, for example 0.5 will be 12:00 pm, and 0.75 will be 6 pm.

Now that you have a better idea of how date and time is represented in Excel, here are some additional things to take into consideration when doing date and time calculations.

You can insert the current date into the spreadsheet by pressing the control and semicolon keys at the same time (Ctrl+;).

You can insert the current time into the spreadsheet by pressing the control, shift and semicolon keys at the same time (Ctrl+Shift+;).

You can edit the way the date is displayed as follows:

Right click the cell containing the date.

Select the Format Cells option. This will open a new window.

Select the way you want the date to be displayed by choosing one of the options located under the Type section.

You can also customise the way the date is displayed by selecting the Custom category. Under the Type section select one of the custom display options or create your own unique date by typing the code of how it should be displayed.

You can edit the way time is displayed as follows:

Right click the cell containing the time.

Select the Format Cells option. This will open a new window.

Select the way you want the time to be displayed by choosing one of the options located under the Type section.

You can also insert the date and time by using the following date and time functions:

=Today(). This function will display the current date into a cell.

=Year(). This function will display the current year based on the date contained in another cell.

=Month(). This function will display the current month based on the date contained in another cell.

=Day(). This function will display the current day based on the date contained in another cell.

=Date(Year,Month,Day). This function will display a date based on year, month and day information contained in other cells.

=Now(). This function will display the current date and time into a cell.

=DAYS(). This function returns the number of days between two dates.

=HOUR. This function returns the hour component of a time as a number between 0–23.

=MINUTE. This function returns the minutes from a time value (a number from 0 to 59).

=SECOND. This function returns the seconds (a number between 0 and 59) from a time value.

=TIME. This function creates a time with individual hour, minute and second components.

DOING CALCULATIONS USING DATE INFORMATION

Date calculations can be done using basic operators such as plus, minus and divide. However, you should take note that when subtracting two dates from each other, the answer will be given in the number of days. This is due to the way dates are represented, as discussed in the first part of this section. In order to determine the number of years, you will have to divide the number of days by the number of days in a single year, 365.25.

To better illustrate this, let’s look at an example.

Guided Activity 4.11

Your boss has instructed you to invite all employees older than 40 to attend a special seminar on how to stay young. This can be done as follows:

1.Open the spreadsheet GAct4.8(Age).xlsx.

2.Add two columns, one for the current date and one for their age.

3.Calculate their age by subtracting their birthday (contained in cell C2) from the current day (contained in cell D2) using the formula =D2−C2. This will give you the number of days.

4.In order to determine the age of the person in years, you have to divide the value by 365.25 (the number of days in a year). For example, =(D2−C2)/365.25.

You should now be able to invite all the employees who are older than 40 years to the seminar.

Save and close your spreadsheet.

Something to know

Always look at what you have (days) and to what you want it converted to, e.g. weeks. How many days in a week? Thus, you will divide by 7.

Guided Activity 4.12

Roundup is a very useful function that allows the user to round up all numbers. This function can also be used in combination with the month function to determine which quarter of the year a specified date falls into, for example:

You are supplied with a list of tasks that need to be completed by a specific date.

You must sort the tasks according to which quarter of the year it needs to be completed. To do this, you will need to make use of both the roundup and month functions as follows:

1.Start the ROUNDUP function as normal, =ROUNDUP(.

2.Add the cell that contains the data. In this case you will have to use the month function to indicate that you are looking for the month information from the date given, and divide that number by 3, =ROUNDUP(month(B2)/3.

a.The month function will return the month number as a value, for example January would be 1.

b.The month value needs to be divided by the number of months in a year, divided by the number of quarters in a year, e.g. 12months/4quarters=3.

3.Add the number of decimals you want to round up to, =ROUNDUP(month(B2)/3,0.

a.We are looking at which quarter the month falls into and thus need whole numbers without any decimals. Therefore, we round up to 0 decimal spaces.

4.End the formula by closing all parentheses.

DOING CALCULATIONS USING TIME INFORMATION

Like date calculations, time calculations can also be done using basic operators such as plus, minus, divide and multiply. When doing time calculations you should always remember that the answer is given as a fraction of one day. Thus in order to determine the number of hours, you will have to multiply the answer by the number of hours in a single day, 24.

To better illustrate this, let’s look at two examples.

Guided Activity 4.13

You have volunteered to help at the 10 km fun run that your school has organised in order to raise funds for a new hockey field. The organisers of the run have assigned you the task of determining how long each of the runners took to complete the run. In order to do this, open the GAct4.10(hours).xlsx spreadsheet:

Here’s how you can determine how long each of the runners took to complete the race.

1.Start by adding an additional column to the worksheet.

2.Calculate how long each runner took to complete the race by subtracting the start time from the end time. This will give you the fraction of a day it took to complete the run.

3.In order to calculate the number of hours, you will have to multiply the answer by 24.

You should now be able to inform the organisers how long each runner took to complete the 10 km run in number of hours.

Save and close your spreadsheet.

Guided Activity 4.14

You have volunteered to help at the 10 km fun run that your school has organised in order to raise funds for a new hockey field. The organisers of the run have assigned you the task of determining how long each of the runners took to complete the run. In order to do this, open the GAct4.11(hoursMinutes).xlsx spreadsheet:

Here’s how you can determine how long each of the runners took to complete the race.

1.Start by adding an additional column to the database.

2.Calculate how long each runner took to complete the race by subtracting the start time from the end time. This will give you the fraction of a day it took to complete the run.

3.In order to calculate the number of hours, you will have to multiply the answer by 24.

You should now be able to inform the organisers how long each runner took to complete the 10 km run. However, what if they wanted to know how many minutes each runner took? To determine this, you can simply multiply the answer by 60 (the number of minutes in 1 hour).

Save and close your spreadsheet.

Activity 4.4

Open the spreadsheet Act4.4(Gym).xlsx and complete the following activity.

1.Add a function in cell B3 that will always display the current date.

2.Wrap the column headings in row 6.

3.Enter a function in cell G43 to determine the total subscriptions Step Up Gym receives per month based on the Subscriptions Per Month (column G).

4.Enter a formula in cell C7 to calculate the age for the first member in completed years using the Today’s Date in cell B3 and the member’s Date of Birth in cell B7.

REVISION ACTIVITY

The votes cast for contestants have been stored in a spreadsheet. The winner is determined by processing the votes.

Open the Chapter 4_Revision Activity 1 spreadsheet.

1.Remove Sheet1 from the spreadsheet. Work in the Winners_Wenners worksheet. (1)

2.Add a formula in cell E4 to calculate the score out of 100 that Thembisa Barnard obtained in the Introduction category. Use the raw score in cell D4 and the maximum possible score in cell D2 to determine the score out of 100.

a.Round off the answer to the next whole number, with NO decimal places.

b.Ensure that the formula will work correctly if copied down to the rest of the cells in the same column. (6)

3.    a.Contestants are scored on each of the four categories as follows:

i.Column E Introduction/Bekendstelling

ii.Column F Dance costume/Danskostuum

iii.Column G Evening wear/Aanddrag

iv.Column H Interview/Onderhoud

b.Column I contains the final score of the contestants out of 100. The Interview category (column H) is the most important part of the scoring process and therefore counts twice as much as the other categories. In cell I5, calculate the final score of Nomhle Baxter by adding the categories and then dividing by 5. (3)

c.Column K displays the best category for each contestant. Use a nested IF function in cell K6 and the value in cell J6 to display the name of the category in which Thembani Benjamin received her highest score. (5)

4.In cell O7, use a function to determine the 2nd highest final score (column I).

5.Insert a function in cell O9 to calculate the average of the maximum scores (column J). (2)

Save and close the spreadsheet.

The organisers of a cycle race would like to improve their systems for the following year’s event. Open the Chapter 4_Revision Activity 2 spreadsheet.

Work in the Race_Reis worksheet

1.Improve the readability of the spreadsheet by applying the following formatting features to the worksheet:

a.Format the heading in row 1 so that it stretches from column A to K. (1)

b.Adjust the font size of row 1 to 20pt and the font colour to red. (2)

c.Use the freeze pane feature to ensure that the contents in only rows 1 and 2 remain on the screen if the user scrolls down. (1)

d.Display the values in column J as South African Rand. (1)

e.Format the information from column C to column K so that it appears in the centre of each column. (1)

2.Each participant will receive a medal based on the prize money in column J. The medal categories can be found in the Medal_Medalje worksheet. Use a spreadsheet function in cell K3 to determine the type of medal Marco Abrahams will receive. (4)

Save and close the spreadsheet.

TOTAL: [27]

AT THE END OF THE CHAPTER

CHAPTER OVERVIEW

Unit 5.1
Text functions

By the end of this chapter, you will be able to:

Explain text functions.

Use text functions to find, manipulate and extract specific information from data.

INTRODUCTION

A big part of processing and analysing data in a spreadsheet is to move, join and extract any relevant text data that you want to analyse or export to be used in a presentation. In order to make this process easier, Excel has been equipped with text functions. These functions allow the user more freedom when it comes to selecting and finding specific pieces of text, as well as how that text is represented.

UNIT
5.1 Text functions

In this section we will take a look at some of the most useful text functions that you can use. This includes the following functions:

Left, right and mid

Concatenate

Value

Len

Find.

LEFT, RIGHT AND MID

The first text functions that we will take a closer look at is the left, right and mid functions. These functions allow the user to extract a specific part of the text information that is contained within a cell. The LEFT and RIGHT text functions consist of the following:

=LEFT(A1,3)

=RIGHT(A1,3)

An indication that you are using a function: =

The function name: LEFT and RIGHT

The cell that contains the text data

The number of characters that should be returned

It is not necessary to specify the number of characters that you want to return. However, if you leave out this specification the function will simply return the first character of the original text. For example, if the target cells contain “Hello” and you are using the formula =LEFT(A1), the function would only return the first letter “H”.

The MID text function consists of the following:

=MID(A1,4,3)

An indication that you are using a function: =

The function name: MID

The cell that contains the data

The position of the first character you want to return

The number of characters that should be returned

Unlike the LEFT and RIGHT functions, you always need to specify the position of the first character as well as the number of characters that should be returned when using the MID function. If you do not do this, you will receive an error notifying you that too few arguments were entered for the function.

Guided activity 5.1

Open the spreadsheet GAct5.1(TextFunctions).xlsx. To extract the first two characters of each person’s first name, you will do the following:

1.Start the text function as normal: =LEFT

2.Add the cell that contains the data: =LEFT(B2

3.Add the number of characters you want to return: =LEFT(B2,2

4.End the formula by closing all parentheses: =LEFT(B2,2)

Save and close your document.

Guided activity 5.2

Open the spreadsheet GAct5.1(TextFunctions).xlsx. To extract the last four characters of each person’s last name, you will do the following:

1.Start the text function as normal: =RIGHT

2.Add the cell that contains the data: =RIGHT(A2

3.Add the number of characters you want to return: =RIGHT(A2,4

4.End the formula by closing all parentheses: =RIGHT(A2,4)

Save and close your spreadsheet.

Guided activity 5.3

Open the spreadsheet GAct5.1(TextFunctions).xlsx. To create a special code for each person, you will extract two characters from each person’s name starting from the second character. This can be done as follows:

1.Start the text function as normal: =MID

2.Add the cell that contains the data: =MID(B2

3.Add the position of the first character that you want to return: =MID(B2,2

4.Add the number of characters you want to return: =MID(B2,2,2

5.End the formula by closing all parentheses: =MID(B2,2,2)

Save and close your spreadsheet.

Activity 5.1

Open the spreadsheet Act5.1(WaterGames).xlsx and use a function to display the following:

1.Each athlete’s code in Column H based on:

a.the athlete’s gender

b.separated by a hyphen (-)

c.the first 3 letters of the athlete’s Name

d.the last 2 letters of the athlete’s surname

e.Separated by a hyphen

f.the Team the athlete belongs to, as an example, the code for athlete Kerry Kirby (row 20) will be: F-Kerby-Indigo.

Activity 5.2

Open the spreadsheet Act5.2(Client).xlsx and use functions to do the following:

1.Determine the service provider of each client by obtaining the first three digits of their cell phone number.

2.Creating a unique customer code by taking the middle two digits of each name and the first two letters of the Surname.

Save and close your spreadsheet.

CONCATENATE

The concatenate function allows the user to combine text data that is located in different cells into a single cell. An example is when looking at a spreadsheet that contains names in one cell, and surnames in another. Using the concatenate function, you can combine the names and surnames in order to display both in a single cell. This can save the user time and effort, as it allows him/her to work with one set of data instead of two.

The concatenate function consists of the following:

=CONCATENATE(B2,A2)

An indication that you are using a function: =

The function name: CONCATENATE

The cell that contains the first piece of data that you want to combine

The cell that contains the second piece of data the you want to combine

Take note that the CONCATENATE function joins the data exactly as it is. Thus, the formula =CONCATENATE(B2,A2) will combine the data located in these two cells without a space between them. If you want to insert a space, you will have to add another argument as follows: =CONCATENATE(B2,“ ”,A2). The new argument consists of two double quotes with a space between them to indicate that a space should be inserted between the two cells.

Guided activity 5.4

Open the spreadsheet GAct5.4(Concatenate).xlsx. Here is how to combine the data into a single cell:

1.In C2, start the text function as normal: =CONCATENATE

2.Add the position of the cell that contains the first piece of data: =CONCATENATE(B2

3.Add a space between the first and second piece of data: =CONCATENATE(B2,“ ”

4.Add the position of the cell that contains the second piece of data: =CONCATENATE(B2,“ ”,A2

5.End the formula by closing all parentheses: =CONCATENATE(B2,“ ”,A2)

You can also add some text between the first and second piece of data by typing the text between the double quotes instead of the space. For example, adding a comma between the name and surname: =CONCATENATE(B2, “,”,A2).

Make sure you save your files so that it can be used in further activities.

CONCATENATE AND THE & OPERATOR

The concatenate function is not the only way to join two pieces of data. This can also be done by making use of the ampersand (&) operator. For example, the formula =B2&” ”&A2 can be used to join the information contained within cell A2 with the information in cell B2.

Note that we make use of double quotes to add a space between the two pieces of information.

Activity 5.3

Open the spreadsheet Act5.3(Names).xlsx and do the following:

1.Change the heading in cell D1 to Updated Customer code.

2.Use a function to generate an update customer code for each client.

a.The code must consist of:

the middle two digits of each Name and the first two letters of the Surname

as well as, the last three digits of the client’s cell number.

Save and close your spreadsheet.

LEN

The LEN function allows the user to determine the length (number of characters) of a text string contained within the selected cell. The function can also be used to determine the length of numbers but will not take the formatting into consideration. For example, if you have formatted the cell to display a currency such as R50, the LEN function will not count the R as part of the string and thus indicate that the cell contains two characters.

The LEN function consists of the following:

=LEN(B2)

An indication that you are using a function: =

The function name: LEN

The cell that contains the text that you want to determine the length of.

Guided activity 5.5

1.Open the file saved with Guided Activity 5.4.

2.Start the text function as normal: =LEN

3.Add the cell containing the text that you want to determine the length of: =LEN(B2

4.End the function by closing all parentheses: =LEN(B2)

Save your file.

VALUE

The VALUE function allows the user to convert a text string into a numerical value. However, this is only possible if the text string has the ability to be converted to a number (i.e. a recognised format). For example, the text string “Haloo” does not have the ability to be converted to a numerical value and will thus give an error. On the other hand, the text string 20% does have the ability of be converted to a numerical value and will give an answer of 0.2. Other examples of text strings that can be converted to numerical values include:

Numbers, for example R1000 will give a result of 1000

The VALUE function consists of the following:
=VALUE(B2)

An indication that you are using a function: =

The function name: VALUE

The text string that you want to convert to a numerical value. This can be a cell containing the text or a normal text string contained within two double quotes.

Guided activity 5.6

1.Open a blank spreadsheet and type the following:

2.In Cell B2, start the text function as normal: =VALUE

3.Add the text string or cell containing the text string that you want to convert to a numerical value: =VALUE(A2 or =VALUE(“20%

4.End the function by closing all parentheses: =VALUE(A2) or =VALUE(“20%)

All of the values in column A of the above image have been captured as text. The same effect can be obtained by using the value formulae as shown in the next image.

5.Save your file as GAct5.6(<<YourName>>).

You should note that the VALUE function is rarely used as Excel will automatically convert text strings to numerical values as is needed. The main function of the VALUE function is to help with Excel’s compatibility with other spreadsheet programs.

FIND

The FIND function allows the user to locate a specific character or text string located within another text string. In simple terms, it can help you find a specific letter within a word. The function provides a numerical output which indicates the position of the character or string that you are looking for. It is important to note that this function is case sensitive, so make sure that you know exactly what you are trying to find.

The FIND function consists of the following:

=FIND(“e”,A32,1)

An indication that you are using a function: =

The function name: FIND

The string or character that you are trying to find.

The cell containing the text that is to be searched. You can also search a text string by typing the text string within a double quote, for example =FIND(“e”,”Jane”,2) will look for the position of the letter “e” within the string “Jane”.

The position in the string where the search will start. This is an optional inclusion. If you do not include this in the formula, the search will start from the first letter of the string.

Guided activity 5.7

1.Open the spreadsheet GAct5.7(Find).xlsx. In B2, start the text function as normal: =FIND

2.Add the string of character you are looking for: =FIND(“e”

3.Add the cell or text string containing the text that you want to search through: =FIND(“e”,A32

4.Add the position where the FIND function will start searching: =FIND(“e”,A32,1

5.End the function by closing all parentheses: =FIND(“e”,A32,1)

Save and close your spreadsheet.

If the cell or text string does not contain the string or character that you are looking for an error indication of #VALUE! will be obtained. You will also notice that it gives the position of the first character or string that matches the search requirements. If you are looking for a specific instance of that character, make sure to start the search after the previous instance of that character. This can be done by adjusting the position where the FIND function will start its search.

For example, if we are looking for the second time the letter “e” occurs in the name “Josephine” we need to adjust the starting point where the FIND function will start. If we start the search from the beginning of the name, the FIND function will stop once it has found the first “e” in the name, namely position 4. Therefore, we adjust our function to start looking for the second time the letter “e” occurs by setting the starting position to 5, =FIND(“e”,A37,5). The FIND function will now indicate the position of the second “e” in Josephine as shown in the image below.

Looking for the second instance of “e” in “Josephine”. This is done by changing the start position of the FIND function to after the first occurrence of “e”.

Activity 5.4

Open the spreadsheet Act5.4(Contestant).xlsx and use functions to complete the following activity: The spreadsheet contains the Names of eleven contestants that have entered a raffle to win a new toaster. Each contestant has been assigned a random special code that will be used to determine who has won.

1.In order to pick a winner, you need to use a function to determine the length of each person’s Special Code.

2.There was an error that occurred when entering the contestant’s names into the spreadsheet. A # was included in some of the contestant’s names. Use a function to determine which names have the # included, and where the # is located so that it can be removed.

Save and close your spreadsheet.

FUNCTION COMBINATIONS

One of the most important skills that you will need to acquire when analysing data is to combine more than one function into a single formula, an example is analysing a spreadsheet containing Names and Surnames.

Figure 5.1: Spreadsheet containing Names and Surnames

In the above database the learner information of each leaner is contained within a single cell. However, we want to use the grade and class data separately. In order to do this, we will separate the grade and class data into their own cells by combining a couple of the functions that we have discussed previously in this chapter.

We know that the grade and class are separated with a comma and space between them. We want to take everything before the comma and move it to the cell called “Grade” and everything after the space to the cell called “Class”. In order to do this, we will make use of the functions LEFT, RIGHT, FIND and LEN.

Guided activity 5.8

Open the spreadsheet GAct5.8(Combinations).xlsx. To display the grade in a new cell:

1.Use the LEFT function to indicate that you are looking to extract information from the left part of text string: =LEFT

2.Add the location of the text string: =LEFT(B2

3.Add the number of characters that should be copied. To determine this, we use the FIND function: =LEFT(B2,FIND
Add the value we are looking to find (in this case the comma between the grade and class): =LEFT(B2,FIND(“,”
Add the location of the text string: =LEFT(B2,FIND(“,”,B2

4.End the formula by closing all parentheses: =LEFT(B2,FIND(“,”,B2)).

5.To remove the comma from the end of Grade 12, we add -1 to the end of the function =LEFT(B2,FIND(“,”,B2)-1)

Save your files for further use.

Guided activity 5.9

Open the GAct5.8(Combinations).xlsx spreadsheet you saved in Guided Activity 5.8 above. To display the class in a class cell:

1.Use the RIGHT function to indicate that you are looking to extract information from the right part of the text string: =RIGHT

2.Add the location of the text string: =RIGHT(B2

3.Add the number of characters that should be copied. To determine this, we start by using the LEN function to calculate the length of the whole text string (learner data) and the FIND function to identify the location of the space in the string. We then subtract the location of the space from the value of the whole string in order to determine the length of the class data:
=RIGHT(B2,LEN(B2)-FIND(“, ”,B2)).

Save and close your spreadsheet.

Take note

Take note that this is always the preferred method to use if you are looking to extract data from the right after a specific character.

Activity 5.5

Open the spreadsheet Act5.5(Learner-info).xlsx and use functions to complete the following activity:

1.Separate each learner’s name into name and surname.

2.Generate a special code for each learner. The code must be a combination of the length of the learner’s name, and the last 3 letters of the learner’s surname.

Save and close your spreadsheet.

REVISION ACTIVITY

The Chapter 5_Revision Activity spreadsheet contains data about a car sales company.

Open the Chapter 5_Revision Activity spreadsheet and work in the Sheet1 worksheet.

1.Rename Sheet1 to “Car Info”. (1)

2.Change the tab colour to any colour of your choice. (1)

3.Make the following changes to the first row:

a.Merge and centre cells A1:O1 and insert the text “MZANSI CAR SALES”. (2)

b.Add light blue shading to the merged cells. (1)

4.For security reasons every car needs a code. Create a code for each car in column A as follows:

a.The first two letters of the name of the car manufacturer (column B).

b.Followed by the last three digits of the year in which the car was manufactured (column F).

c.Followed by the owner’s name (column J).

i.Example: Chevrolet, 2007, J Bihl will have Ch007J Bihl as code. (8)

5.Insert a function in cell G4 to calculate the age of the car by using the current year and the date the car was released from the factory in column F. Ensure that the function will still produce the correct answer for any year, for example 2017 or 2018. The age must be displayed as a whole number. (3)

6.Insert a function in cell K4 to determine the position of the space located between the owner’s initials and surname in column J. (2)

7.Insert a function in cell L5 using the position of the space in column K to display only the surname from column J. For example, “HC Burger” will display “Burger” and “D Kotze” will display “Kotze”. (2)

TOTAL: [20]

AT THE END OF THE CHAPTER

CHAPTER OVERVIEW

Unit 6.1 | Using functions to solve problems for a specific scenario
Unit 6.2 | Charts

By the end of this chapter, you will be able to:

Explain how you can use functions, formulas and operators to solve a problem.

Combine functions to obtain better results.

Create a stacked bar and column chart in order to compare data.

Format a chart by changing the scale on the axes, re-labelling the axes, and changing the minimum and maximum values displayed on the chart.

Explain the importance of choosing the appropriate chart for a given scenario.

INTRODUCTION

During your studies you will be required to use various different functions to solve problems for a specific scenario. An example of this is the practical assessment task or PAT assignment. This assignment consists of different phases that require you to gather, analyse and present information on a given topic. For the data analysis you will be tasked to make use of various functions and formulas in order to extract information from the data. Here is an example of the functions and formula that you should include in such an assignment:

Date and time

Maths

Statistical

Text

Logical

Reference.

FUNCTIONS

Complete the following activity in order to revise your knowledge on functions.

Activity 6.1

Open the Act6.1(Participants).xlsx spreadsheet.

1.Insert a formula in cell E6 to determine the current age of Allen Hathaway. Use the date of birth in column D.
NOTE: Only the completed years must be displayed, AND the formula should work for any year.

2.Insert a function in cell L4 to display the most frequent position (column F) achieved in the competition.

3.Insert a function in cell L6 to determine how many times Carl le Clos has obtained first position.

4.Insert a function in cell L8 to determine the second highest amount that an athlete should be paid.

5.Insert a function in cell L10 to display a unique code for every entry(row) in the spreadsheet. The code must consist of:

The first 3 letters of the participant’s name

Separated by the “%” sign

First 2 letters of the sport, and

Separated by a hyphen (-)

The medal the participant achieved

Example: Row 16 will display – Car%Sw-Silver

Save and close your spreadsheet.

UNIT
6.1 Using functions to solve problems

In order to help you get a better idea of how to use these various functions and formulas, in this section we will look at an assignment and how we used functions and formulas to help complete it.

SCENARIO

The following scenario will help show you how to use functions to solve problems for a specific scenario. Working through this, you will learn how to identify, combine and use functions to solve the problem.

You are given a worksheet containing the following information:

Personal information: This is the client’s full name and email address.

Purchase information: This is the product and the quantity ordered.

Order information: This is the date the order is received as well as an indication of whether an overnight delivery is required.

Stock information: This is the price and number of stock available.

Overnight costs: This is an additional charge for overnight delivery.

Figure 6.1: Worksheet containing the personal, client and order information

Figure 6.2: Worksheet containing the stock information and overnight costs

The assignment indicates that any client who makes a purchase of more than R2 000 should receive a bonus gift, however, the client may only receive this gift once. Using this information as well as the supplied data, you are tasked to complete the table below for Amanda Carter, Josephine Persinger, and Ray Rodriguez as well as determine the average amount that these three clients spent on a single order. The table can only be completed using formulas and functions. You are not allowed to copy and paste any information.

Figure 6.3: Table to be completed for the assignment

SOLUTION

The spreadsheet GAct6.1(Scenario).xlsx is supplied as the basic source document for this example. Learners need to complete the missing aspects as they go through the Guided Activity. This worksheet can be used to show students how to create function combinations for a specific scenario.

Guided activity 6.1

Here is our solution:

1.Use the LEFT and RIGHT text functions in order to separate the clients full name into their first name and surname.

a.=LEFT(A2;FIND(“ ”;A2)) in order to obtain the first name.

=RIGHT(A2;LEN(A2)-FIND(“ ”;A2)) in order to obtain the surname.

2.Use the VLOOKUP function to search for the client’s name and to return the purchased product, =VLOOKUP(“Amanda Carter”;A:C;3;FALSE).

a.Make sure to adjust the cell range to not include the location of the previous purchase: =VLOOKUP(“Amanda Carter”;A3:C1000;3;FALSE).

3.Use the IF function to compare the purchase quantity to the amount of stock available: =IF(D2<=L2; “yes”; “no”).

4.Use the VLOOKUP function to search the client’s name and to indicate if an overnight delivery was ordered, =VLOOKUP(“Amanda Carter”;A2:E16;5;FALSE)

a.Make sure to adjust the cell range to not include the location of the previous purchase: =VLOOKUP(“Amanda Carter”;A3:E16;5;FALSE)

5.Calculate the total price of the order by making use of the VLOOKUP function to obtain the price of the order, and use multiplication to multiply the price of the order with the quantity of the order: =D3*VLOOKUP(C25,J3:K12,2,FALSE).

a.Use the IF function to determine if an overnight cost should be added and add that cost to the total cost: =D3*VLOOKUP(C25,J3:K12,2,FALSE) + IF(E3=”Yes”,N3).

6.Use the IF function in order to determine if the client qualifies for a gift: =IF(F24>=2000; “yes”;“no”).

7.Use the AND function to disqualify the client from receiving a gift if they have already received one previously: =IF(AND(F25>=2000;I24<1); “yes”;“no”).

The AND function can be used to evaluate two or more arguments. If all the arguments are true, the AND function will be true. If one of the arguments is not true, the AND function will return false. The AND function consists of the following:

=AND(argument 1,argument 2)

An indication that you are using a function: =

The function name: AND

The first argument

The second argument.

In our example, the AND function is used to identify clients who have spent more than R2000 and who have not yet received a gift. Therefore, the function is created as follows:

1.Start the function as normal, =AND

2.Add the first argument, =AND(F25>=2000

3.Add the second argument, =AND(F25>=2000,I24<1)

This function will not return true if the value in cell F25 is less than R2000 and if the client has yet to receive a gift. If one of these arguments are not met, the AND function will return false.

8.Use the TODAY function to insert the current date and the VLOOKUP function to retrieve the date on which the order was placed: =TODAY()-(VLOOKUP(“Amanda Carter”;A2:F16;6;FALSE)).

a.Make sure to change the formatting of the cell to number.

9.Use the COUNTIF function to determine the number of gifts that have been sent to the client: =COUNTIF(G24; “yes”).

10.Use the AVERAGE function to determine the average amount of money spent on a single order: =AVERAGE(F24:F31).

You will notice that we used of the following functions:

Date and time: =TODAY()-(VLOOKUP(“Amanda Carter”;A2:F16;6;FALSE))

Maths: =RIGHT(A2;LEN(A2)-FIND(“ ”;A2))

Statistical: =AVERAGE(F24:F31)

Text: =RIGHT(A2;LEN(A2)-FIND(“ ”;A2))

Logical: =IF(AND(F25>=2000;I24<1); “yes”;“no”)

Reference: =VLOOKUP(“Amanda Carter”;A3:E16;5;FALSE)

You should try and do the same during your PAT examinations.

Figure 6.4: Example of the completed assignment

Note

The PRODUCT function can also be used in this activity.

Activity 6.2

Open the spreadsheet Act6.2(Accommodation).xlsx and complete the activity. On the Accom worksheet, do the following:

1.Format row 6 as follows:

a.Change the height of row 6 to 45 pt.

b.Wrap the headings in row 6.

2.Insert a formula in cell H7 to determine the total number of nights Elmarie Raymond will stay at her accommodation.

3.Use a VLOOKUP function in cell I8 to determine the rate per night for the accommodation booked by Marsden Santos. Use the accommodation information in column D and the lookup table in the Rate_Tarief worksheet. Ensure that the function will work correctly if it is copied to the rest of the cells in the column.

4.Insert a formula in cell J9 to determine the total amount due for accommodation for the number of nights given in column H and at the rate per night given in column I. Change the format to currency.

5.Discount is awarded based on the star rating of the accommodation of a person (column K) and on the amount (column J). Each star represents 1% discount. For example:

A visitor who stays in accommodation with a one-star rating (‘*’) will get 1% discount on the amount due.

A visitor who stays in accommodation with a four-star rating (‘****’) will get 4% discount on the amount due.

a.Insert a function/formula in cell L10 to determine the amount of discount the guest will receive.

6.The 7th character of the identity number (column C) refers to the gender of a person:

a.The steps to obtain the gender of a person are:

i.Extract the character representing the gender from the identity number.

ii.Use a text function to convert this character to a number.

iii.Determine whether the number represents a male or female: A number greater than or equal to 5 represents a male, while a number smaller than or equal to 4 represents a female.

b.The function in cell M7 attempts to extract the character representing the gender from the identity number. The function, however, currently returns more than one character. Modify the function to correctly extract the character in step 1 and add steps 2 and 3 to the function to complete it, so that “Male” or “Female” is displayed in cell M7.

7.Insert a function in cell G2 to determine the third lowest rate per night (column I).

8.Insert a function in cell G3 to determine the most frequently occurring rate per night (column I).

9.Insert a function in cell G4 to determine the total amount earned (column J) by all the “Country” accommodation types (column E). For example, “Country House” or “Country Inn”.

Save and close your spreadsheet.

UNIT
6.2 Charts

One of the most useful features of spreadsheet programs such as Excel is that it allows the user to visually represent and analyse data by making use of charts. Charts are especially useful when looking to explain a large amount of data in a quick and efficient manner. However, do not use too many charts in your presentation or report as it can become confusing. In order to prevent this, always make sure that you choose the correct chart to represent the data, and only include charts that add value.

In order to assist with this, in this chapter we will be taking a closer look at how to compare different sets of data by creating a stacked bar and column chart. We will also discuss how you can format the chart by changing the scale on the axes, and re-labelling the axes. Finally, we will discuss the importance of selecting the appropriate chart for a given scenario.

Something to know

Here is how to select data that is not adjacent:

1.Start by selecting the column containing the first part of the data.

2.Hold in Ctrl and select the column containing the second part of the data.

3.Repeat step 2 until all the data has been selected.

CHARTS

In previous years, you learned about the different types of charts that can be created in Excel, and the steps required to create them.

The table below contains a summary of the different chart types.

Table 6.1: Different Excel charts

The charts might not always display exactly as you hoped. In order to help fix any potential problems and to ensure that the data is displayed in a way that helps you present your message in the most effective way, you can format the chart as follows.

Under chart options, select the tool you want to use to format the chart. This includes Fill & Line, Effects, and Size & Properties.

Fill & Line is used to edit the fill and borders of the chart.

Effects is used to add shadow, glow, soft edges and 3-D formats to the chart.

Size & Properties is used to change the height, width, scale and position of the chart.

CREATING STACKED CHARTS

A stacked chart allows the user to illustrate the relationship between similar data types by comparing the individual items as well as the data set. In simple terms, it breaks down and compares each individual part of the data with each other and with the data set as a whole. For example, you can use a stacked chart to compare how a cricket player has performed over a couple of games, as well as how that player performed when compared with the rest of the team.

Figure 6.5: This chart is used to compare each players performance in game one and two, as well as the performance of the player’s when compared to each other

Stacked charts make use of bars, with each bar representing a data point. The bars are then segmented to indicate the different parts that make up the data point with different colours being used to distinguish between the parts. You can also make use of pictures and other graphics in order to distinguish between the parts.

In order to help you get a better understanding of how a stacked chart functions, let us take a look at the following example.

Imagine you coach a cricket team at the local school. Your team consists of 11 players, each of whom has to played two games this month. At the end of the month the principal asks you to compare the amount of runs each of the players have scored so that you can determine if the batting line up is correct. To solve this problem, we will make use of a stacked chart.

Each column of the stacked chart will be used to represent a player. The column will then be divided into two, with the first part representing the first game and the second part representing the second game. Using this method, we are able to compare the performance of each player as well as their performance during the first and second games.

In this section we will be taking a closer look at two types of stacked charts: stacked bar and column charts. Both these charts use rectangular bars to compare two or more values with each other. The length of each part of the bar represents a specific data value. For example, the bar representing the player Bernard Towel will consist of two parts. The first part will represent game 1 and the second game 2.

The main difference between the column and bar charts is that bar charts are displayed horizontally, and column charts are displayed vertically. Due to their orientation each of these charts is better suited for a specific set of data. For example, bar charts are really good for displaying data with long labels as column charts have a limited label space available. On the other hand, column charts are really good for displaying data that have negative values. Now that you have a better idea of what stacked charts are, let us take a look at how you can create one.

Guided activity 6.2

To create a stacked bar chart, you need to do the following:

1.Open the spreadsheet GAct6.2(Cricket).xlsx and select the data.

2.Click on the Insert tab and go to the Charts section where you click the See All Charts option.

3.Select the All Charts tab.

4.Select the Bar option.

5.Change the chart type to stacked by clicking the Stacked Bar option in the sub-category.

6.Click OK to insert the chart.

Save and close your spreadsheet.

Guided activity 6.3

1.Open the spreadsheet GAct6.2(Cricket).xlsx and select the data.

2.Go to the Charts section of the Insert tab and click the See All Charts option.

3.Select the All Charts tab.

4.Select the Column option.

5.Change the chart type to stacked by clicking the Stacked Column option in the sub-category.

6.Click OK to insert the chart.

It important to know that once you have inserted the chart, you can still make changes to it. For example, changing the colour of the bars or replacing the bars with a graphic.

Save and close your spreadsheet.

Guided activity 6.4

1.Open the spreadsheet GAct6.2(Cricket).xlsx and double click the graph legend that you want to format. This should open the Format Legend Entry panel in the right margin of the document.

2.Click on the Fill option.

3.Select the Picture or texture fill option.

4.Click File under the Insert picture from option.

5.Select the graphic that you want to insert into the chart.

6.Click OK to insert the graphic.

Save and close your spreadsheet.

In the following sections we will be taking a look at some additional changes that you can make to the chart, including changing the scale on the axes, and re-labelling the axes.

Activity 6.3

You are in a school with a 1 000 learners. As part of your Grade 12 CAT practical, you have to find out how many learners are in each grade and how many of them have cell phones, as well as those who do not have cell phones. The results of the research are contained in the spreadsheet Act6.3(Practical).xlsx. Use this data to complete the following:

1.Create a stacked bar chart and name it as Chart 1.

2.Create a stacked column chart and name it as Chart 2.

3.Replace the bars of Chart 1 indicating learners who have cell phones with the graphic cell_phone.png. Tile the picture as texture.

Save and close your spreadsheet.

CHANGING THE SCALES OF THE CHART AXES

As you already know, each chart contains two axes, a vertical (y-axis) and horizontal (x-axis). One of these axes will contain numerical data such as percentage, while the other axis will contain the labels (text data or numbers, usually grades and years) such as learner names. The type of data that is displayed on each axis is dependent on the type of chart that you are using. For example, if you are using a column chart, the horizontal axis will display the text data and the vertical axis will display the numerical data. If you are using a bar chart, then the vertical axis will display the text data and the horizontal axis will display the numerical data.

Traditionally the axis that display numerical data includes more options that can be used in order to scale the axis, but that does not mean that you cannot personalise the axis containing the labels. Here are some options that you can use to scale the axis containing numerical data:

Change the values of where the numerical data will start and end. This can be done by adjusting the maximum and minimum values.

Change the interval of tick marks and chart gridlines.

Reverse the order of the numerical data.

Here are some options that you can use to scale the labels (text):

Reverse the order of the labels.

Adjust the distance between the labels and the axis.

Change the orientation of the text.

Choose to have the axis display text or dates. Selecting the Date axis option displays any dates in chronological order but will only work if the axis contains date information.

Choose to display the labels at certain intervals only. This option is very helpful if the label text is large.

Guided activity 6.5

In this example we will be formatting the horizontal axis of a bar chart. Thus, we will be formatting an axis containing labels (text data).

1.Select the chart by clicking on it. This will add two additional tabs on the ribbon, the Design and Format tabs.

2.Click the Format tab and select the dropdown list in the Current Selection section.

3.Select the Horizontal (Category) axis option from the dropdown list. This can also be done by right clicking on the labels and selecting Format Axis.

4.Click the Format Selection option in the Current Selection section of the Format tab. This will open the Format Axis pane in the right margin of the document.

5.Select one of the following options to edit the horizontal axis:

a.Tick the Categories in reverse order option under Axis Options. This will reverse the order of the labels.

This is what the chart will look like.

b.Change the axis type by selecting either Text axis or Date axis under Axis type in the Axis Options selection. Please note that you should only select Date axis if the chart uses dates as a label.

c.Change the placement of the axis by typing the distance you want in the Distance from axis box located under the Labels option.

d.Change the intervals at which data is displayed by selecting the Specify interval unit option under Labels. Insert the new interval in the interval box. Only use this option if it is relevant for the data. For example, when you have a set of dates and you would only like to display every other date.

e.Choose the position of the text labels by selecting an option from the Label Position dropdown list in the Labels section.

For example, this is what the chart will look like when you press High as a label position.

f.Change the direction of the label text by selecting Size & Properties and choosing the text direction from the dropdown list.

Guided activity 6.6

In this example we will be formatting the vertical axis of a bar chart. Thus, we will be formatting an axis containing numerical data.

1.Open the Format Axis pane. This can be done as in the previous example or by double clicking on the vertical axis.

2.Select one of the following options to edit the vertical axis:

a.Adjust the values where the numerical data will start and end by typing the value of the minimum and maximum Bounds in the Axis Option.

b.Change the intervals at which tick marks and gridlines are displayed by typing the value in the major and minor box located under Units in the Axis Option.

c.Tick Categories in reverse order under the Axis Option. This will reverse the order of numerical data.

d.Change the display units by selecting an option from the Display units dropdown list under Axis Options. Tick the Show display units label on chart option to display the unit label on the chart. Make sure to choose the option that is best suited for the data.

e.Change the way major and minor tick marks are displayed by selecting an option from the Major type and Minor type dropdown lists located in the Tick Marks section.

f.Choose the position of the numerical labels by selecting an option from the Label Position dropdown list in the Labels section.

Activity 6.4

Open the spreadsheet Act6.4(Axes_cricket).xlsx and complete the following:

1.Format the vertical axis as follows:

a.Reverse the order of the labels.

b.Move the labels to be displayed on the right of the graph.

c.Display the labels at a 45-degree angle.

2.Format the horizontal axis as follows:

a.Change the minimum amount displayed to 10 and the maximum to 150.

Save and close your spreadsheet.

EDITING DATA LABELS

Changing the scale of the chart axis is not the only change that you can make to the chart. You can also edit the chart title, axis titles and labels. However, it is not as easy as it sounds, as the method you use to change the labels will depend on whether the worksheet is linked to the chart. In the following section we will take a look at the following:

changing the chart title

changing the axis titles

changing the labels on a chart by changing the labels in the worksheet

changing the labels on a chart without changing the labels in the worksheet.

Guided activity 6.7

1.Click on the chart title.

2.Select the current name.

3.Type the new name over the current name.

4.Click on a spot away from the chart to save the new titles.

Guided activity 6.8

1.Enable the Axis Title.

a.Select the Chart.

b.Click on the + icon to the right of the chart. This will open the Chart Elements menu.

c.Tick Axis Titles. This will enable the Axis Title.

2.Click on the Axis Title.

3.Highlight the current name.

4.Type the new name over the current name.

5.Click on a spot away from the chart to save the new title.

Guided activity 6.9

1.Open the file eAct6.12. Select the cell containing the label that you want to change.

2.Highlight the name in the cell.

3.Type the new label over the current label and press enter.

4.Repeat Steps 2 and 3 for each label you want to update. Since the worksheet is linked to the chart, the labels on the chart will automatically update.

Due to the fact that the learners’ names are often quite long, we will use a stacked bar chart for this example.

Something to know

Remember, if you are using a column chart, the horizontal axis will display the text data and the vertical axis will display the numerical data. If you are using a bar chart, then the vertical axis will display the text data and the horizontal axis will display the numerical data. If the text data is long, for example, the names and surnames of the learners, it will not fit on the column chart’s axis. It is then better to use a bar chart.

Guided activity 6.10

1.Right click the axis containing the labels you want to format. This will open a pop-up menu.

2.Click Select Data from the new menu. This will open the Select Data Source menu.

3.Click Edit located under Horizontal (Category) Axis Labels. This will open the Axis Labels window.

4.Highlight the text under Axis label range.

5.Type the new labels separated by a comma.

6.Click OK. This will take you back to the Select Data Source menu.

7.Click OK.

The labels will now be updated without affecting the labels in the worksheet.

Something to know

You are able to swap the horizonal and legend entries using the Switch Row/Column feature. This function is very useful when Excel thinks the labels (such as years) are data points.

Guided activity 6.11

You can update a chart to include additional data points. This can be done as follows:

1.Right click the axis containing the labels you want to format. This will open a pop-up menu.

2.Click Select Data from the new menu. This will open the Select Data Source menu.

3.Click Add located under Legend Entries (Series). This will open the Edit Series window.

4.Choose the Series name and Series values of the new data series and click OK. This will take you back to the Select Data Source menu.

5.Click OK to update the chart.

Activity 6.5

Open the spreadsheet Act6.5(Rename_cricket).xlsx and complete the following:

1.Rename the chart to Team A.

2.Edit the labels to show only the first letter of each player’s name and their surname.

3.Add the data for the fourth game to the chart.

Save and close your document.

CHOOSING THE CORRECT CHART FOR THE DATA

If you are looking to make use of a chart in order to analyse or present the data, you have to take one very important factor into consideration. The data will determine the type of chart that you should use. This is due to each chart type having a specific set of data which it can best represent. For example:

Line chart: Best used to track changes over time. An example of this can be seen when looking at a line chart used to track a project’s development progress.

Pie chart: Best used when comparing values that adds up to a whole or to 100%. An example of this can be seen when looking at the results of a survey in which ten people were asked what their favourite colours were.

Bar chart: Best used to compare data between different groups. An example of this can be seen when comparing the test performance of learners.

Although these chart types are best used to represent a specific set of data, it does not mean that you cannot use them for other sets. Just make sure that the chart is not misleading or confusing and that it represents the data in a way that best illustrates the information you are presenting.

Activity 6.6

Choose from the following in which scenario would you use a line chart, a pie chart, a stacked chart and a bar chart?

1.The IT Head of Department wants to compare the marks of the different classes in each grade for 2017. Which chart would illustrate this best?

2.You are a long-distance athlete and you want to keep track of how many laps you are running around the track every day to see if your fitness increases over time. You run for an hour each day, Monday to Friday. Which of the three charts would you use to display this?

3.In the past month, there were 70 learners absent from school. Of these, 23 were in Grade 8, 17 in Grade 9, 9 in Grade 10, 19 in Grade 11 and 2 in Grade 12. Which chart would work best to visualise this?

4.One of a company’s employees is working on a project in a town far from the head office. The project consists of writing operating procedures for all the tasks in the various departments of the client’s organisation. There are ten different sections in the organisation, and each section has its own number of procedures to be written. To make sure that the employee is doing her job, the manager asked her to draw up a chart showing how many procedures have been written and how many are still outstanding. Which type of chart would she use to indicate this?

5.The school is raising funds to build a state of the art computer laboratory. The headmaster wants a chart in the courtyard to display how much each grade has raised each month of the school year. Which chart would show this the best?

6.To demonstrate how viable a new computer laboratory will be, the headmaster wants to determine the learners’ computer skills. He decides that the number of learners with smart phones will be a good indication of their basic skills. He asks all the learners to indicate on a yes/no questionnaire whether they have a smart phone or not. With this information, he wants to see whether a computer laboratory is going to work and which grade will benefit most. Which chart will give him the best overall indication of how many learners have smart phones and what the distribution of these phones are per grade?

7.Your Grade 12 class has been asked to develop a website for the school. To determine how successful it is, you have to send the headmaster a chart at the end of each month, showing how many people browsed the site. Which type of chart would show this information the best?

REVISION ACTIVITY

A spreadsheet has been created that contains data about car sales.

Open the spreadsheet Chapter 6 Revision_Activity and complete the following:

1.Insert a function in cell J15 to determine the total number of Toyota vehicles in the showroom. (3)

2.Insert a function in cell J18 to determine the second highest total of all the various types of vehicles in the showroom (column J). (3)

3.Insert a function in cell J19 to determine the total number of vehicles in the showroom. (2)

4.Insert a function in cell J20 to determine the total value of the VW vehicles in the showroom. (3)

5.Use an appropriate spreadsheet feature to format all cars with an engine size of more than 1400 with a green fill colour. (3)

6.A chart/graph was created using the data in the range I3:J16. Change the chart/graph to resemble the chart/graph below by carrying out the instructions that follow. (6)

a.Insert the text “Number of cars” for the vertical axis.

b.Change the chart/graph title to “Makes Available”.

c.Use a spreadsheet feature to format and display each column in a different/varied colour.

d.Display the borders of the columns as a solid black line.

e.Move the chart/graph to the Motor worksheet.

TOTAL: [20]

AT THE END OF THE CHAPTER

CHAPTER OVERVIEW

Unit 7.1 | Report grouping
Unit 7.2 | Querying a report
Unit 7.3 | Data validation

By the end of this chapter, you will be able to:

Create a report containing grouped data.

Use headers and footers to add additional information to the report.

Include some basic calculations when creating a report, for example, sum, average, counting, maximum and minimum.

Add fields with calculations in queries and reports.

Apply data validation techniques.

INTRODUCTION

Databases are one of the most important tools of the modern era. It allows the user to enter, access, analyse and store data in a quick and easy manner. But did you know that the database is not a new concept? In the past people designed very intricate systems to allow them to store data and information. A basic example is doctors’ patient records. Before computers, patient records were written on paper. Each patient’s written records were then kept in a le, which was stored in a ling cabinet. When the patient came for a consultation, the doctor would have had to go to the cabinet and look for the patient’s le in order to obtain their information. These days most patient information is stored and updated on a computer, although doctors still make use of patient les that contain printouts of the stored data.

Databases can be used to store a wide variety of data, from something as simple as a list of students in a class, to more complex data such as the academic performance of each student in the entire school. In order to ensure that the database functions as required, you need to make sure that it is properly designed for its purpose. In order to help you with this, in this unit we will take a look at how to design a database for a specific scenario.

DATA

As you already know, a database consists of a collection of data that can be processed and analysed to obtain information. A properly designed database not only makes this process easier but also prevents data redundancy and ensures data integrity.

Data integrity looks at how accurate and consistent the data is (for example, a field that is supposed to contain numeric values should not contain alphabetic data), while data redundancy refers to similar data being stored in multiple places (for example, saving the same data to five different folders on the same hard drive). Following are some additional characteristics of a good database:

Data should be up to date. This will ensure that the data is relevant and accurate. Data collected from 10 years ago will give very different information from data collected yesterday.

Data should be complete. Incomplete data can lead to inaccurate and misleading information. For example, data collected from a questionnaire where half the answers were left blank would not give very accurate results.

Data should be relevant. The database should only contain data that is relevant to the database users. For example, if the database is created to capture the age of people, data such as what is their favourite meal is not relevant.

Remember, a database is only as good as the data that is stored in it. records were then kept in a file, which was stored in a filing cabinet. When the patient came for a consultation, the doctor would have had to go to the cabinet and look for the patient’s file in order to obtain their information. These days most patient information is stored and updated on a computer, although doctors still make use of patient files that contain printouts of the stored data.

Figure 7.1: Traditional filing system

In this chapter we will look at one of the most useful modern-day database programs, Microsoft Access. We will investigate how you can use Access to group the data according to specific requirements, how to add headers and footers to display additional information, and how to use basic calculations and queries to analyse the data. Finally, we will give some advice on why and how you should validate the data in the database.

ACCESS IN A NUTSHELL

In previous years, you learned that Access can be used to store data using tables, collect data using forms, filter data using queries, and present data using reports.

Figure 7.2: The Access process

TABLES

In Access, data is stored in tables with each table consisting of a collection of records (rows) and fields (columns). Fields are normally used to organise the data according to the type of data and records that are used for data entry. For example, in the image below the First Name, Surname, Email and City fields are used to specify what information should be entered into the database with the following records containing the data for each entry.

Table 7.1: An example of an Access table

Examples of the types of data that can be stored in an Access database can be seen in the table below.

Table 7.2: Example of the types of data that can be stored in an Access database

Something to know

Primary key is the field selected by the database creator to uniquely identify each record in a table.

Guided activity 7.1

How to create a basic table:

1.Open Access, click create a blank database, and specify a location where the database should be saved.

2.Open the Create ribbon at the top of the Access window.

3.Click on the Table Design option.

4.Enter the fields for the Customer table under Field Name.

5.Select the data type for each field from the dropdown list under Data Type.

6.Choose the Primary key by selecting the Username field and clicking on the Primary Key option.

7.Save the database using the CTRL+S hotkey.

8.In the Dialog Box that opens, enter the name for the table and press the OK button.

FORMS

Forms can be used to enter, modify and view data stored in a table. A good example of this is the form you have to complete when registering to vote or visiting the doctor.

Something to know

If there is nothing special about the layout of the form, make sure to leave it on default.

Guided activity 7.2

How to create a basic form:

1.Open the database GAct7.2(Info).accdb.

2.Select the table that you want to add data to.

3.Click the Form Wizard option in the Forms section of the Create tab.

4.Add the fields you want to include in the form and click Next.

5.Select the layout of the form and click Next.

6.Type the title of the form and click Finish.

When collecting data using forms, take note of the following:

Decide what data needs to be captured before creating the form. For example, I want to obtain the person’s Name and Surname, Email address, Cell phone number and monthly salary.

Ensure the fields in the table correlate to the data you want to capture on the form. For example, use the short text data type to capture the name, surname and email data, and numerical data type to capture the cell phone number and amount earned per month.

Choose appropriate names for the fields on the form so that the user knows what should be entered. For example Name, Surname, Email, Cell number and Salary.

QUERIES

Once you have captured and stored the data in a table, you can make use of queries to return specific records for a specific field or result. For example, if you have a database containing data of users from around South Africa, you can use a query to identify all the users from a specific city.

The tables below contain some of the operators and criteria that you can use in a query in order to search the data.

Table 7.3: Operators

Table 7.4: Criteria

Guided activity 7.3

How to create a basic query:

1.Open the database GAct7.3(Queries).accdb.

2.Click the Query Wizard option in the Queries section of the Create tab.

3.Select the Table and Fields you want to query and click Next.

4.Enter a title for the query and click Finish.

5.Go to Design View.

6.Add operators and criteria to filter the data.

Save and close your database file.

REPORTS

The final step of any database is to represent the data in an easy-to-read format. For example, at school they give you a report that displays your performance for the semester. This report is a printout from a database that contains all students’ information.

Guided activity 7.4

How to create a basic report:

1.Open the database GAct7.4(ArtData).accdb.

2.Click the Report Wizard option in the Reports section of the Create tab.

3.Select the Table or Query you want to include in the report and click Next.

4.Select how you want the data to be sorted and click Next.

5.Select the Report Orientation and click Next.

6.Type the title of the report and click Finish.

Save and close your database file.

Activity 7.1

Open the Act 7.1(Accommodation_Akkommodasie) database and work in this database.

1.Create a query called Costs, which is based on the tblAccommodation table. Use the Query Wizard.

a.Display the following fields in the query: RefCode, Accommodation, Rate and Days

b.Display only the guests that will stay for four days. Save and close the query.

2.Create a form called Area, based on the tblAccommodation table. Use the Form Wizard.

a.Display the following fields in the form: Name, Surname, Accommodation and Rate.

b.Select any layout of your choice.

c.Change the background colour of the entire form to sky blue.

d.The heading of the form should read “Accommodation”. Save and close the form.

3.Create a report called Rates, based on the tblAccommodation table. Use the Report Wizard.

a.Display the following fields in the report: Name, Surname, Accommodation, Rate, Days and CellNum.

b.Change the orientation of the report to Landscape.

Save and close the report.

Close the database.

UNIT
7.1 Report grouping

Reports are generally used to present data in a way that is easy to understand. One of the methods that can be used to do this, is by grouping the data according to a specific topic or requirement. For example, if you are a teacher looking to see how each of your classes performed during the exams, you can take the database containing the information of all the students and group them according to class.

CREATE A REPORT USING GROUPED DATA

The following example will help show you how to create a grouped reports using Report Wizard options.

The Report Wizard will assist you with creating a grouped report by presenting you with a series of questions and options. The report will then be generated based on the answers.

Guided activity 7.5

To create a grouped report using the Report Wizard:

1.Open Access and the database GAct7.5(School).accdb.

2.Go to the Reports section of the Create tab and click Report Wizard. This will open the Report Wizard window.

3.Select the table or query containing the data that you want to group from the Table/Queries dropdown list.

4.Double click the data fields you want to include in the report or select the fields and make use of the add button to add the selected field.

You can add fields from another table by selecting the table from the Table/Queries dropdown list, selecting and adding the fields.

5.Click Next once you are finished adding all the fields. This will take you to the next window where you will be able to tell the wizard which field you want to group the data under.

6.Select and add the field you want to use to group the data. You can change the grouping intervals by clicking Grouping Options and selecting the intervals from the Grouping Intervals dropdown list.

7.Click Next. This will take you to a new window allowing you to organise the data by sorting it.

8.Select the field you want to use to sort the data from the dropdown list.

9.Select the order you want to organise the data in. Ascending is the default, but clicking the Ascending button will change it to Descending.

10.Click Next. This will open the layout window of the Report Wizard.

11.Select the layout of the table as well as the page orientation and click Next.

12.Type the title of the report in the space below What title do you want for your report? and click Finish.

You will notice that the report generated by the Report Wizard looks much nicer than the report generated by the quick report function.

Save and close the database.

Something to know

You can sort the data by selecting layout view and right clicking the column that you want to use to sort the data. Select Group On [Field name] to finish sorting the data.

GROUP HEADERS AND FOOTERS

Grouping the data is not the only way to organise and improve the layout of the report. Access also allows the user to modify the appearance of their reports by making use of headers and footers.

Headers and footers can be used to add additional information to the report in order to make it more visually appealing.

Guided activity 7.6

Here is how to add some text to the headers and footers:

1.Open the GAct7.5(School).accdb database you have saved in the previous activity and right click on the report and select Design View from the pop-up menu.

2.Click on the Label command in the Controls group of the Design tab.

3.Click and drag the mouse inside the white area below the header or footer bar to create the label.

4.Click the textbox and type the text.

5.Switch back to Report view in order to see if you are happy with the addition.

You will notice that the grouped headers and footers might not always be visible.

Guided activity 7.7

You can change the way the grouped headers and footers are displayed by opening the Group, Sort, and Total pane as follows:

1.Use the same database you have saved (GAct7.5(School).accdb) and click on the Group & Sort option in the Grouping & Totals section of the Design tab. This will open the Group, Sort, and Total pane at the bottom of the screen.

2.Click the More option to display the whole list of options.

3.Navigate to the Header and Footer section and select the way you want to display the grouped Headers and Footers from the dropdown lists.

Save and close the database.

GROUP CALCULATIONS

Access allows the user to include some basic calculations when creating a report. This includes adding values together (SUM), calculating the average value (AVERAGE), indicating the minimum or maximum values (MIN and MAX), and counting the number of values in a specific field (COUNT).

These calculations can be included in the report by using three different techniques. The first is to include them when creating the report using the Report Wizard, the second is to include them using the Totals option on the ribbon, and the third is to manually type in the calculation by inserting a textbox into the report.

Guided activity 7.8

You can include some basic calculation options when creating a report using the Report Wizard. This is done as follows:

1.Open the Report Wizard.

2.Select the report data.

3.Select how you want to group the report.

4.Indicate how you want to organise the data.

5.Click Summary Options and select the calculation you want to include.

6.Select the format of the report.

7.Type a name for the report.

You will notice that the report now contains an average for each class.

Guided activity 7.9

How to use the Totals option to include a basic calculation to an already existing report:

1.Open the database GAct7.9(Dam).accdb and open the report in Layout View.

2.Select the column that you want to use for the calculation.

3.Go to the Grouping & Totals section of the Design tab.

4.Click the Totals dropdown list and select the calculation that you want to display.

5.Select Design View and add a label to describe the calculation.

Save and close the database for further use.

You can use a textbox in order to add a calculation to an already existing report. When using a textbox to type in the formula, always remember the location of the textbox is very important. If you place the textbox in the report header or footer, Access will perform the calculation by looking at all records. If you place the textbox in the group header or footer, Access will perform the calculations by looking at all records contained within the specific group.

Guided activity 7.10

How to insert a calculation into a group footer:

1.Right click on the report and select Layout View from the pop-up menu.

2.Open the Group, Sort, and Total pane by clicking the Group & Sort option in the Design tab.

3.Click More and select the With a Footer section option from the dropdown list.

4.Right click on the report and select Design View.

5.Click on the Textbox command in the Controls group of the Design tab.

6.Click and drag the mouse inside the white area below the group footer bar to create the textbox.

7.Click the textbox saying Unbound and type the formula.

a.Start with an equal sign: =.

b.Add the variable: =MAX( ).

c.Add the field name in square brackets: =MAX([CAPACITY]).

8.Click the label saying Text and type a label for the calculation.

9.Switch back to Report view in order to see if you are happy with the addition.

Activity 7.2

Open the database Act7.2(Players) and create the following report based on the Players table:

1.Display all the fields.

2.Sort the data according to Player Name.

3.Group the data in the report according to Game played.

4.Calculate the average earned for each game in the footer of the report. Add an appropriate label for the calculation.

Save the report and close the database.

UNIT
7.2 How to add a calculation to a query

The queries function helps the user to obtain specific records for a specific field or result. One of the ways this is achieved is by making use of calculated fields as well as operators in order to filter the data. This not only increases the accuracy of the query, but also allows the user to generate reports that are much more specific.

ADDING CALCULATED FIELDS

You have a database containing students’ information as well as the marks they obtained for each test and exam. In order to calculate the average of each student, you need to add a field containing a calculation to the table query.

Something to know

You can also use text functions in a calculated field. For example, Code:(Left([field name],2)) will display the first two characters of the text contained within the specified field.

Guided activity 7.11

How to do that:

1.Open the database GAct7.11(Class).accdb.

2.Open the Query in Design View.

3.Select a new field and type the field name followed by a colon, Average:.

4.Type the calculation following the colon, Average: ([Test 1]+[Test 2]+[Exam])/3.

5.Switch back to Datasheet View and check if you are happy with the calculation.

Save and close your database for further use.

Here are some things that you should take into consideration when adding a field containing calculations to the query:

Put field names in square brackets.

Make use of basic operators such as plus (+), minus (−), divide (/), and multiplication (*).

Use brackets to help determine the order of calculations.

Brackets are calculated first, followed by to the power off (^), then division and multiplication, and finally addition and subtraction.

Guided activity 7.12

When doing calculations, you might want to display the results in a very specific way. For example, you might want the results to be displayed as a currency such as Rand. In order to do this, you need to change the field properties to currency. This can be done as follows.

1.Open GAct7.11(Class).accdb you saved in the previous activity.

2.Open the Query in Design View.

3.Right click the field containing the calculation and select properties from the pop-up menu. This will open the Property Sheet window.

4.Click the Format dropdown list and select currency.

5.Switch back to Datasheet View and check if you are happy with the calculation.

Save and close your database.

USING OPERATORS TO FILTER THE RESULTS

Calculations can be used to enhance how the data is displayed, but how can you find specific data to only display a specific result? This can be done by querying the data making use of operators such as logical, special, and wildcard operators.

Logical operators allow the user to combine two or more values and return a true, false or null result. Here are the logical operators that we will take a look at:

And: This operator only returns a result if both the values are true.

Or: This operator returns a result if one of the values are true.

Not: This operator returns a result if the value is not true.

Special operators are used to return a true or false result based on the operator. Here are the special operators that we will take a look at:

Is Null: This operator is used to determine if the value is null. If the value is null, it will return a result.

Is Not Null: This operator is used to determine if the value is not null (empty). If the value is not null, it will return a result.

Wildcard operators make use of special characters that stand in for unknown characters in a text value. Wildcards are most commonly used to find multiple results that contain similar, non-identical, data. Here are the wildcard operators that we will take a look at:

*: This operator can be included anywhere in a text string in order to find all results containing the text before the *. For example, “he*” will look for all words that start with he.

?: This operator looks for a specific singular alphabet character in a specific position. For example, “h?llp” will look for the specific character that comes after an “h” and before “ll”. This operator will only return a result of both of those requirements are met.

In order to help you get a better understanding of using logical, special and wildcard operators, let’s look at the following example. You have a database containing students’ information as well as the marks they obtained for each test and exam. Using this information, you want to do the following:

1.Determine which of the students in Class A was absent from the exam.

2.Determine which students either failed or passed the first test with a distinction.

3.Determine which of the students in Class A and B have a Gmail account.

To answer Question 1, we will make use of the following operators. We will use the NOT operator to eliminate all the students in Class B, and the IS NULL operator to determine who did not write the exam.

Guided activity 7.13

How to add these operators to the query:

1.Open GAct7.11(Class).accdb you saved in the previous activity.

2.Open the Query.

3.Go to Design View.

4.Add the NOT operator in the criteria row of the Class field, Not “Class B”.

5.Add the IS NULL operator in the criteria row of the Exam field, IsNull.

By entering both the operators in the criteria row, we are making use of the AND operator as the query will only return result that are true for both the NOT and IS NULL operators.

Save the database for further use.

Guided activity 7.14

How to add these operators to the query:

1.Open GAct7.11(Class).accdb you saved in the previous activity.

2.Open the Query1 and go to the Design View. Remove all current criteria.

3.Add the >= operator in the criteria row of Test 1, >=80.

4.Add the < operator in the or row of Test 1, <40.

By entering the one operator in the criteria row and the other in the or row, we are making use of the OR operator. The OR operator causes the query to be true if either of the operators we entered in these two rows are true.

Save the database for further use.

Guided activity 7.15

You can also make use of the OR operator by typing the operator in the same criteria field. For example, in order to identify the students that either failed or passed the first test with a distinction, but who also passed the exam you can do the following:

1.Open GAct7.11(Class).accdb you saved in the previous activity.

2.Open the Query1 and go to the Design View. Remove all current criteria.

3.Add the >= operator in the criteria row of Test 1, >=80.

4.Follow the >= operator with the OR operator in the criteria row of Test 1, >=80 or.

5.Add the < operator after the OR operator in the criteria row of Test 1, >=80 or <40.

6.Add the > operator in the criteria row of Exam, >40.

Remember when you add operators in the same criteria row of different fields, the AND operator is used. This operator only returns results if all the operators that were added to each field is true.

Save the database for further use.

To answer Question 3, we will make use of a wildcard operator. We will use the * operator to determine all the students who have a Gmail account.

Guided activity 7.16

How to add these operators to the query:

1.Open GAct7.11(Class).accdb you saved in the previous activity.

2.Open the Query1 and go to the Design View. Remove all current criteria.

3.Add the * operator in the criteria row of Email, like “*gmail.com*”.

Remember the wildcard operator * searches a text string and thus you should always put the search value between double quotes.

Save the database for further use.

Activity 7.3

Open the database Act7.2(Players) you created in Activity 7.2 and create the following queries:

1.Create a query called Salary based on the Players table.

Include all the fields.

List all players that earned more than 7 million, but less than 20 million rand.

Save and close the query.

2.Create a query called Games based on the Players table.

List all the players but exclude players that play Dota 2 and League of Legends.

Only use the following fields: “Player Name” and “Game played”.

Save and close the query.

3.Create a query called ID based on the Players table.

Display only the “Player ID”, “Player Name” and “Game played” fields.

List all players who’s PlayerID starts with an M or an S.

Save and close the query.

Close the database.

UNIT
7.3 Data validation

Data validation is the process of checking whether the data is accurate, in the correct format or the correct type before allowing the database to record it. This ensures that the data in the database is consistent and accurate.

In Access you can make use of the following data validation rules in order to make sure that the valid data is entered into the database.

Data type check: A data type check ensures that the data is of the correct data type (for example string, integer, or datetime).

Digit (or length) check: A digit check ensures that the data is a specific number of characters long before it is recorded. This is useful for data such as phone numbers or ID numbers.

Range check: A range check ensures that the data falls within a specific range. This is useful for items such as amounts or durations.

Format check: A format check ensures that the data is in the correct format. This can be used to ensure that an email address contains an @ sign and ends with a country code.

Lookup column: A lookup column presents the user with a list of predefined values to choose from. These values are normally represented in a dropdown list. This function can also be used to allow new values to be added.

Validation rule: A validation rule is used to specify requirements for data entered into the database. Only data that suits the rule requirements will be recorded. The validation rule is normally displayed to the user through a text message indicating what data to enter.

Required properties: A required property is used to specify when a value is required in a specific field. This prevents the field from being left blank.

InputMask: An input mask is used to guide users and to make data entry easier. It functions by showing the user exactly how many characters should be entered in a specific field (such as telephone numbers and dates).

Guided activity 7.17

You can add a validation rule to the database as follows:

1.Open GAct7.17(Validation).accdb.

2.Open the table containing the data.

3.Select the field you want to protect using a validation rule.

4.Go to the Fields tab and click Validation in the Field validation group.

5.Select Field Validation Rule from the dropdown menu. This will open the Expression builder dialog box.

6.Click the text box and type the validation rule using the appropriate operators.

The table on the next page contains examples of validation rules that can be used.

Table 7.5: Examples of validation rules and how they are used

7.Once you are done typing the validation rule, click OK.

The validation rule has now been set, however, a validation message still needs to be added. This message is important as it shows one what type of data should be entered into the specific field.

Save and close the database for further use.

Guided activity 7.18

To add the validation message, do the following:

1.Open the GAct7.17(Validation).accdb database you saved in the previous activity.

2.Go to the Fields tab and click Validation in the Field validation group.

3.Select the Field Validation Message option from the dropdown menu. This will open a dialog box.

4.Type the error message that you want to display in the dialog box for when the user has entered invalid data.

5.Once you are done typing the validation message, click OK.

The validation rule has now been added to the specific field. If the user attempts to submit invalid data, the set error message will appear.

Save and close the database for further use.

Guided activity 7.19

1.Open the GAct7.17(Validation).accdb database you saved in the previous activity.

2.Open the table containing the data.

3.Go to Design View.

4.Select the Field to which you want to add the Input Mask.

5.Under Field Properties, click the Input Mask property box. This will display a button on the right of the box.

6.Click the Input Mask property button to open the Input Mask Wizard.

7.Select the Input Mask that you want to use from the list provided or click Edit List to create your own.

8.Click Next.

9.Select how the data should be stored.

To help you with this, the table below contains the characters that define the Input Mask.

Table 7.6: Characters that define input masks

10.Once you are done selecting how the data should be stored, click Finish.

The Input Mask has now been added to the specific field. You will notice that the field now shows the user how the data should be entered.

Activity 7.4

Answer the following questions by filling in the missing words.

1.Data validation is the process of checking whether the data is (a)_____, in the correct (b)_____ or the correct (c)_____ before allowing the database to record it.

2.A data type check ensures that the data is of the correct data type, e.g. (d)_____, (e)_____, double or datetime).

3.A (f)_____ is used to guide users and to make data entry easier.

4.A validation rule is used to specify (g)_____ for data entered into the database.

5.A range check ensures that the data falls within a specific range. This is useful for items such as (h)_____ or (i)_____.

6.A (j)_____ ensures that the data is a specific number of characters long before it is recorded.

REVISION ACTIVITY

The Chapter 7_Revision Activity database contains details of the samba dance participants.

1.The results of the points for the samba dance competition are stored in the tbComp table. Open the tbComp table in design view and do the following:

a.Change the field size of the Entry field to 15. (1)

b.Ensure that the data of the Entry field is inserted in the following format:

i.One or two capital letter characters.

ii.One or two digits.

EXAMPLES: B4, A21, AX8, UN30 (5)

c.The validation rule set on the Class field does not work. Correct this validation rule by referring to the data in this field.

i.Provide suitable validation text.

ii.Do NOT test the new rule. (2)

d.Move the Year field so that it appears as the last field in the table. (1)

Save and close the tbComp table.

2.Create a form called frm5_2 based on the tbComp table, using any layout as follows:

a.Add all the fields from the tbComp table.

b.Add the heading “Entries” in the form header.

c.The Class field should display the options (A, B, C, D, E, F) in a combo box.

d.Add the name and surname to display in the form footer. (7)

Save and close the frm5_2 form.

3.Create a query called qry5_3 based on the tbComp table.

a.Add the School, Surname, Name and Position fields.

b.Sort the query alphabetically, first by Surname and then by School.

c.Display only the records of the participants that obtained first position.

d.Display only the School, Surname and Name fields. (4)

Save and close the qry5_3 query.

4.Open the qry5_4 query.

a.Display only the records from 2 March and 3 March 2017.

b.Display records where the value in the TotalPoints field is more than 265.

c.Create and display a calculated field called NewPoints where all participants’ total points will increase by 10%.

Save and close the qry5_4 query. (1)

5.Create a report named rpt5_6 based on the tbComp table to display as follows:

a.The report is grouped on more than one level.

b.Pay attention to the totals and groupings.

c.The PerformanceDay field displays per day.

d.The screenshot above displays only a part of the report.

e.Ignore if PerformanceDay appears twice. (7)

Save and close the rpt5_6 report.

Save and close the database.

TOTAL: [28]

AT THE END OF THE CHAPTER

CHAPTER OVERVIEW

Unit 8.1 | Design a database
Unit 8.2 | Reinforce database concepts and skills

By the end of this chapter, you will be able to:

Apply problem solving skills in the design and development of a database.

Design a database for a specific scenario.

INTRODUCTION

Activity 8.1

Open the database Act9.1(CFKData) and complete the following:

1.Change the design of the Plant table as follows:

a.Change the field size of the FamilyCommon field to 50.

b.Change the field properties of the EndemicSpecies field so that:

i.It is a required field.

ii.The data in this field is displayed in fixed format.

c.Change the properties of the Listing field so that the user can choose from the options “Endangered” or “Threatened” when entering data.

d.Create an input mask for the PlantCode field to accept data in the following format:

i.One compulsory letter.

ii.Followed by at least two but possibly three characters that could consist of either digits or letters.

iii.Display in capital letters.

Note: Ignore any warning messages that may appear.

e.Set the primary key on a more appropriate field.

2.Create a form called frm9_2 based on the Plant table, containing the following fields: FamilyCommon, FamilyBot, TotalSpecies and EndemicSpecies.

a.The form should resemble the example below.

b.Take the following into consideration:

i.Only add the fields required as shown on the form above.

ii.The font size of the heading is 36 pt.

iii.Note the alignment of the heading.

iv.The background of the form header is yellow.

v.The image 9Erica.jpg in the examination folder is displayed in the form header, as shown above.

vi.Add a button from the available controls to the form. The action of the button is to move to the next record.

c.Create a query called qry9_3.

i.The query must use the Plant table to only display the FamilyCommon and FamilyBot fields of all records where the number of EndemicSpecies is more than 1200.

d.Open the qry9_4 query.

i.Sort and display the FamilyBot field in descending order.

ii.Insert and display a calculated field called Perc, which will calculate the percentage that EndemicSpecies is of the TotalSpecies for each record.

e.Create a report called rpt9_5 based on the Plant table.

i.Show only the FamilyCommon, FamilyBot, EndemicSpecies and Genus fields.

ii.Group the records according to the EndemicSpecies field.

iii.Display the report in landscape format.

iv.Insert a function to calculate the average number of ALL the endemic species in the report.

v.Round off the answer to ONE decimal place.

vi.Add a descriptive label to the calculation.

vii.Ensure that all the data displays fully.

Save and close the database.

UNIT
8.1 Reinforce database concepts and skills

Complete the following activity in order to review your knowledge of databases.

REVISION ACTIVITY A

You have been asked to assist in the analysis of the places where people eat and the types of food that people eat.

Open the Chapter 8_Revision Activity database and do the following:

1.Open the Recipe_Resepte table and:

a.Assign suitable data types to all fields in the table:

i.The Recipe Name field should have a maximum size of 40.

ii.The Ingredient and Method fields will have more than 255 characters. (5)

b.Change the name of the Calories per serving field to Number of calories per serving. (1)

c.Add a new field called Photo. Use a suitable data type. (2)

d.Set a suitable validation rule on the Number of calories per serving field to ensure that only values between 0 and 50 are entered. Set suitable validation text to guide the user. (4)

e.Use a dropdown list in the Food Category field to ensure that only “Breakfast”, “Lunch”, “Snack” and “Supper” can be captured in the same order. (3)

Save and close the table Recipe_Resepte.

2.Create a form called frmInfo based on table Nutritional Value.

a.Only add the Type of Food, Healthy and Protein(g) fields to the form. (1)

b.Change the heading of the form to “Protein Content” and ensure that the text is centred in the form header. (1)

c.Add the date as a field in the form footer. (2)

Save and close the form frmInfo.

3.Create a query called qryNutVal based on the Nutritional Value table as follows:

Only the Franchise, Calories and Carbohydrates(g) fields must be displayed.

The calorie content must be between 350 and 1000 inclusive.

Carbohydrates(g) must be greater than 20.

Only Franchises starting with “Bu” must be listed.

Sort the Type of Food field in descending order. (6)

Save and close the query qryNutVal.

4.Open the query qryKilojoule. The Calories field supplies the total calories per food type.

Insert a calculated field called Kilojoules which will calculate the number of kilojoules for each meal if 1 calorie = 4.17 kilojoules.

Display only the records of Burger Queen Franchise and do not display any type of food with zero calories. (5)

Save and close the query qryKilojoule.

5.Create a report called CaloriesCounter based on the Nutritional Value table as follows:

Display only the Franchise, Type of Food and Calories fields.

Group the records in the report according to the Franchise field, keeping all the franchise groups together on one page.

Sort the records according to the Type of Food field.

Insert a function in the report, with an appropriate label, to determine the average number of calories for the food types produced by each franchise. Round these totals to the nearest whole number. (8)

Save and close the report.

TOTAL: [38]

Complete the following activity in order to review your knowledge of databases.

REVISION ACTIVITY B

South Africa has a wealth of artistic talent. You were asked to research some of the artists and their work.

Open the Chapter 9_Revision Activity database, which stores data of new artists at the gallery.

1.Edit the tblArtists table as follows:

a.Remove the ID field. (1)

b.Change the data type of the Name field to a more suitable data type. (1)

c.Set the field properties of the Cellnumber field so that the user is forced to enter data in this field. (1)

d.Set the field properties of the StreetName field so that it will not accept more than 25 characters. (1)

e.Insert a field named PostalCode between the City field and the Email field. (2)

f.Create an Input Mask on the ArtistCode field to ensure that the user inserts a code for the artist in the following format:

ONE compulsory letter

Followed by ONE compulsory letter or digit

Followed by at least ONE digit, but possibly TWO digits

Example of a valid ArtistCode include: x35, Aa3, gg52 (4)

Save and close the tblArtists table.

2.Create a new form called frm1_2 based on the tblArtists table.

Include ALL the fields.

Add your name and surname in the form header.

Change the font colour of the Name field so that ALL names display in red.

Save and close the frm1_2 form. (5)

3.Open the qry1_3 query based on the tblData table.

Modify the query so that it displays the total amount of each TypeDescription.

Sort the values from the highest to the lowest on the ArtistPrice.

Save and close the qry1_3 query. (5)

4.An error was made in the database when dates were captured incorrectly. Open the qry1_4 query and do the following:

Add a calculated field called NoDays, which will determine for how many days the work of art has been exhibited, using the DateIn field and the DateOut field. Errors will show as a negative value.

Display only the DateIn field, the DateOut field and the new calculated field, NoDays.

Save and close the qry1_4 query. (6)

5.Open the qry1_5 query and do the following:

Display only the records of those artists whose price (ArtistPrice) was more than R2 500 AND whose art pieces were sold (Sold).

Save and close the qry1_5 query. (4)

6.Open the rpt1_6 report and turn on “Group & Sort”, if it is not already displayed.

Add the current date and time in the report footer.

Insert a function to determine the total ArtistPrice for art pieces per TypeDescription.

Change the grouping of the DateOut field to display per month instead of per year.

Save and close the rpt1_6 report. (6)

Save and close the database.

TOTAL: [36]

CHAPTER OVERVIEW

Unit 9.1 | HTML tables
Unit 9.2 | Develop a web page for a specific scenario

By the end of this chapter, you will be able to:

Use various HTML tags to create an HTML table with various attributes.

Develop a web page for a specific scenario.

INTRODUCTION

In 2018, there were more than 1,8 billion websites in the world, which means that there is probably a website for every topic you can think of. Each of these websites are made up of separate web pages, which in turn are made up of building blocks called tags. These tags can be used to code all the web page information directly into the web page file, creating a static web page such as Wikipedia, or to create a web page that collects information from a database based on user inputs, such as Facebook. This kind of web page is called a dynamic page as it changes based on the data in the database.

Figure 9.1: Code, HTML script in a text editor

While web pages might look incredibly complex at first glance, remember that they are made up of simple building blocks. In fact, you can even use a text editor such as Notepad ++ to create your first web page. This is because most web pages are mostly made up of simple text instructions that tell the web page what to show, where to place it and how to show it.

In this chapter, we will look at how these building blocks can be used to add a table to the web page, as well as to edit the table to make it look visually appealing. Finally, we will look at an example of how you can create a webpage for a specific scenario.

WEB DESIGN

In the previous year, you learned that HTML or HyperText Markup Language is a computer coding language used to create web pages. It uses building blocks, called tags, to tell the web browser how to display words, images and videos. To make sure that this is done in a structured manner, a set of rules must be followed. These rules are determined by the HTML syntax which indicates how tags and text should be structured. For example:

A tag must be created for every item displayed on a web page. The type of tag will depend on what you want to display.

Most tags are paired (container tags). This means that an opening tag is for example enclosed by a pair of angle brackets (<start>) and a closing tag by a pair of angle brackets with a forward slash after the first bracket (</end>).

Tags that are not paired are called empty tags.

Tags can be compared to the words used to write a story in a book and the HTML syntax as the grammar rules.

The table below lists the most important HTML tags and describes how each tag can be used.

Table 9.1: HTML tags

As you can see from the table above, tags are very versatile and can be used to personalise the web page. One of the ways this can be done is by changing the attributes of the text. This includes changing the font size and colour, as well as paragraph spacing and indents. Tags can also be used to leave comments so that you can keep track of what is being displayed on the web page.

HTML attributes give the browser more information about the HTML elements you are creating. All attributes have the following characteristics:

Attributes are placed inside an opening tag, after the tag name.

All attribute names are in lower case.

The name of the attributes is followed by an equal sign and a value for the attribute.

The values of attributes are placed inside quotation marks.

More than one attribute is separated using a space.

UNIT
9.1 HTML tables

As you know, a web page consists of a collection of different tags working together. One of these tags is the HTML table. Like regular tables in a spreadsheet or database, the HTML table can be used to store data so that the user can quickly and easily interpret it by looking at the content in the rows and columns.

Another function of HTML tables is that it can be used to control the layout of a webpage by listing and positioning other web page elements such as text, graphics and videos. However, this is not recommended, as using HTML tables this way can make it harder to maintain and debug the web page. It can also cause problems for visually impaired users as their screen readers will struggle to interpret the web page and produce output that is confusing.

In the following sections we will look at how to create an HTML table, how to change the table captions and heading elements, and how to change table attributes.

CREATING AN HTML TABLE

To create an HTML table, use the tag <table> to specify where the table will start and </table> to indicate where it should end. Also specify each row using <tr> and </tr> and each column using <td> and </td>.

To better illustrate this, we will look at the following examples:

How to create a basic table.

How to add a border to a table.

How to name the table and add table headings.

Guided activity 9.1

The HTML file, GAct10.1(Basic_webPage).html, is given with this section as a basic web page structure and can be used with all Guided Activities for this chapter. Do the following to create a basic table.

1.Open Notepad ++

2.Insert the table tag.

<table>

</table>

3.Add rows and columns to the table.

<table>

<tr>

<td> Column A </td>

<td> Column B </td>

</tr>

</table>

4.Save as an HTML file.

This will create a basic table containing two columns, Column A and Column B.

You will notice that the table does not have a border. To add a border, you must specify the border attributes. This can be done by indicating how many pixels the border should be, for example <table border=“1”> will create a border with a width of 1 pixel.

Guided activity 9.2

Here is how to add a border to the table.

1.Insert the table tag.

<table>

</table>

2.Add the border attribute to the table opening tag.

<table border=”2”>

</table>

3.Add rows and columns to the table.

<table border=”2”>

<tr>

<td> Column A </td>

<td> Column B </td>

</tr>

</table>

This will create a basic table with a border around each of the cells. The border width will be 2 pixels.

You should now be able to create a basic table. However, no table is complete without a table name and table headings. To add a table name, use the caption tags (<caption> and </caption>), and to add table headings, use the heading tags (<th> and </th>).

Guided activity 9.3

Here is how to add a table name and table headings:

1.Start by naming the table using the caption tag.

<caption>

This is my table name.

</caption>

2.Insert the table tag and border attributes.

<caption>

This is my table name.

</caption>

<table border=”2”>

</table>

3.Add the table headings using the heading tag.

<caption>

This is my table name.

</caption>

<table border=”2”>

<tr>

<th> Heading A </th>

<th> Heading B </th>

</tr>

</table>

4.Add rows and columns to the table.

<caption>

This is my table name.

</caption>

<table border=”2”>

<tr>

<th> Heading A </th>

<th> Heading B </th>

</tr>

<tr>

<td> Column A </td>

<td> Column B </td>

</tr>

</table>

This will create a basic table named “This is my table” with the headings, Heading A and Heading B.

You might not always want to add a heading for each column, but instead add one for each row. This can be done by adding the heading tag to each table row as follows:

<caption>

This is my table name.

</caption>

<table border=”2”>

<tr>

<th> Heading A </th>

<td> Row A </td>

</tr>

<tr>

<th> Heading B </th>

<td> Row B </td>

</tr>

</table>

FORMATTING AN HTML TABLE

In the previous section, we looked at how to create a basic table. This table used the browser’s default table width, colours, and cell/column spacing. However, you do not have to rely on the default values and can change the format of the table by changing the table attributes.

To better illustrate this, we will look at the following examples:

Changing the table width and height.

Changing table colours.

Changing the spacing between table cells and cell borders.

Changing the row and column span.

Adding a picture to a table.

You can control the size of the table by setting the width and height attributes.

Guided activity 9.4

Formatting HTML tables can be done as follows:

1.Start by naming the table using the caption tag.

2.Insert the table tag, border, width and height attributes.

<caption>

This is my table name.

</caption>

<table border=”2” width=”400” height=”150”>

</table>

3.Add the table headings using the heading tag.

4.Add rows and columns to the table.

<caption>

This is my table name.

</caption>

<table border=”2” width=”400” height=”150”>

<tr>

<th> Heading A </th>

<td> Column A </td>

</tr>

<tr>

<th> Heading B </th>

<td> Column B </td>

</tr>

</table>

This will create a table with a width of 400 pixels and a height of 150 pixels. Take note that you can also specify the width and height of the table using percentages. This will adjust the table in terms of the percentage of available screen area.

You can also adjust the height and width of the columns by setting the attributes in the column tags:

<caption>

This is my table name.

</caption>

<table border=”2”>

<tr>

<th width=”25%” height=”50”> Heading A </th>

<td width=”25%” height=”50”> Column A </td>

</tr>

<tr>

<th width=”25%” height=”50”> Heading B </th>

<td width=”25%” height=”50”> Column B </td>

</tr>

</table>

Save and close your web page.

Guided activity 9.5

You can change the colour of the table’s border by setting the border colour attribute. This can be done as follows:

1.Start by naming the table using the caption tag.

2.Insert the table tag, border, and border colour attribute.

<caption>

This is my table name.

</caption>

<table border=”2” bordercolor=”blue”>

</table>

3.Add the table headings using the heading tag.

4.Add rows and columns.

This will create a table with a blue border.

You can also change the background colour of the entire table or a single table cell by setting the bgcolor attribute:

Here is how to change the colour of the entire table:

<caption>

This is my table name.

</caption>

<table border=”2” bgcolor=”yellow”>

<tr>

<th> Heading A </th>

<td> Column A </td>

</tr>

<tr>

<th> Heading B </th>

<td> Column B </td>

</tr>

</table>

Here is how to change the colour of a single cell:

<caption>

This is my table name.

</caption>

<table border=”2”>

<tr>

<th bgcolor=”yellow”> Heading A </th>

<td> Column A </td>

</tr>

<tr>

<th> Heading B </th>

<td> Column B </td>

</tr>

</table>

You can control the white space in the table cells by setting the cell padding and cell spacing attributes. Cell padding adjusts the distance between cell borders and the content within the cell, and cell spacing changes the space between table cells.

Figure 9.2: The difference between cell padding and cell spacing.

Guided activity 9.6

Here is how to adjust the table cell spacing:

1.Start by naming the table using the caption tag.

2.Insert the table tags, cell spacing, and cell padding attributes.

<caption>

This is my table name.

</caption>

<table border = “1” cellpadding = “5” cellspacing = “5”>

</table>

3.Add the table headings using the heading tag.

4.Add rows and columns to the table.

This will create a table with a distance of 5 pixels between the cell borders and the cell content, and 5 pixels between each table cell.

You can create a cell that spans over multiple rows or columns by setting the row span and column span attributes of the <th> and <td> tags. Rowspan allows you to create a table cell that spans over multiple rows, while colspan allows you to create a cell that spans over multiple columns.

Guided activity 9.7

Here is how to create a cell that spans over multiple rows and columns:

1.Start by naming the table using the caption tag.

2.Insert the table tag, border, width and height attributes.

<caption>

This is my table name.

</caption>

<table border=”2” width=”400” height=”150”>

</table>

3.Add the table headings using the heading tag.

4.Add rows and columns to the table.

<caption>

This is my table name.

</caption>

<table border=”2” width=”400” height=”150”>

<tr>

<th>Main Heading</th>

</tr>

<tr>

<th> Heading A </th> <td> Column A </td>

</tr>

<tr>

<th> Heading B </th>

<td> Column B </td>

</tr>

</table>

5.Add the rowspan and colspan attributes to the <th> and <td> tags.

<caption>

This is my table name.

</caption>

<table border=”2” width=”400” height=”150”>

<tr>

<th colspan=3 style=”text-align:center”>Main

Heading</th>

</tr>

<tr>

<th rowspan=2 style=”text-align:center”>Main

Column</th>

<td style=”text-align:center”> Heading A </th>

<td style=”text-align:center”> Column A </td>

</tr>

<tr>

<th style=”text-align:center”> Heading B </th>

<td style=”text-align:center”> Column B </td>

</tr>

</table>

This will create a table with the cell containing the text “Main Heading” spanning three columns and the cell containing the text “Main Column” spanning two rows.

Something to know

When specifying the source attribute, it is important to indicate the correct name of the image. Any mistakes will prevent the image from displaying.

Guided activity 9.8

In the activity you will learn how to change the alignment of text in table cells.

1.Create a new web page in Notepad++ and save the file as alignment.html.

2.Add the tags to create a table consisting of two rows and three columns.

3.Add the text “Top”, “Middle” and “Bottom” to the first row of the table.

4.Add the text “Left”, “Centre” and “Right” to the second row of the table.

5.Surround the table with a <font> tag and set the font colour to white.

6.Add the attributes to the <table> tag to set the border size, background colour and table width.

<body>

<h2>Cell Alignment</h2>

<font color=”white”>

<table border=”2” bgcolor=”darkblue” width=”70%”>

<tr height=”40”>

<td>Top</td>

<td>Middle</td>

<td>Bottom</td>

</tr>

<tr height=”40”>

<td>Left</td>

<td>Centre</td>

<td >Right</td>

</tr>

</table>

</body>

7.The valign attribute is added to <td> tags to align text vertically in the table cell. It has three values, “top”, “middle” and “bottom”. Add attributes to the <td> tags in the first row of the table to align the first cell to the top, the second cell to the middle and the third cell to the bottom.

<tr height=”40”>

<td valign=”top”>Top</td>

<td valign=”middle”>Middle</td>

<td valign=’bottom”>Bottom</td>

</tr>

8.The align attribute is used to align text horizontally in a table cell. It has values of “left”, “center” and “right”. Add attributes to the second row of the table to align the first cell to the left, the second cell to the centre and the third cell to the right.

<tr height=”40”>

<td align=”left”>Left</td>

<td align=”center”>Centre</td>

<td align=”right”>Right</td>

</tr>

9.Save the file and view in a browser.

This will create a table which shows the different cell alignments.

When creating a webpage, you will most likely want to add one or two images in order to help make the website visually more appealing. To do this, you will make use of the <img> tag. You also need to specify a source attribute (the src attribute) that tells the browser which picture to display, as well as a height and width attribute to indicate the size of the picture. Image files must be saved in the same folder as the HTML files.

For example, to create an image of a smiley face (named smiley.jpg) with a height and width of 100 pixels, you can use the following code: <img src=“smiley.jpg” height=“100” width=“100”></img>.

Something to know

Copy the images that you want to add to the web page/table to the same folder you use to save the web page. If you want to use an image from the internet, you need to specify the web address (where the image can be found) in the source attribute.

Guided activity 9.9

Here is how to insert a smiley face into the table:

1.Open Notepad ++ and save the file as smiley.html.

2.Copy the smiley image that you want to use to the same folder as the smiley.html.

3.Start creating the code by naming the table using the caption tag.

4.Insert the table tag, border, width and height attributes.

5.Add the table headings using the heading tag.

6.Add rows and columns to the table, including the smiley image and its attributes.

<caption>

This is my table name.

</caption>

<table border=”2” width=”400” height=”150”>

<tr>

<th> Heading A </th>

<td> <img src=“smiley.jpg” height=“100”

width=“100”></img> </td>

</tr>

<tr>

<th> Heading B </th>

<td> Column B </td>

</tr>

</table>

7.Save the file.

This will create a table with a cell containing a smiley image.

Activity 9.1

Complete the following practical activity on a computer.

1.Create a new web page by opening Notepad ++. Type the following tags in your text editor:

<html>

<head>

<title>My best friends</title>

</head>

<body>

</body>

</html>

2.Design a table after the title with the following information:

a.Names of 10 learners in your class.

b.Their favourite TV show.

c.Their favourite book.

d.Their favourite game.

Example:

3.Use appropriate tags so that you include the following elements:

a.Headings for each column.

b.A table border with a width of 3 pixels.

c.A table width of 300 and height of 150.

d.A different background colour for each column.

Save the Notepad file as Friends.html.

UNIT
9.2 Creating a website

Websites can be used for a variety of different purposes. For example, a news website is designed to provide up to date information on current affairs from around the world. If the news website did not do this, but instead displayed images of dogs and cats, it would not be a very successful news website. Therefore, it is very important to take the needs of the users, and the purpose of the website, into consideration when creating a website.

Part of your Gr 12 examination will be to create a web page for a given scenario. To assist you with preparing for this, we will use a scenario to demonstrate how to create a web page.

Case study 9.1 Creating a website

Mandla is a young entrepreneur that has decided to start his own business. The business will focus on making and selling baked goods to customers in the Pretoria and Johannesburg areas. The baked goods include the following:

To make sure that his business is managed in the best way possible, Mandla decided to create a website for his business. However, since he has no experience in creating websites, Mandla has come to you for help. He would like you to create a simple web page that can be used to display the goods he has for sale.

The first step of creating any web page is to determine the purpose of the web page, what the web page should display, as well as what users want to gain from the website.

We know that Mandla wants to use the web page to enable users to see what products he has for sale. To do this, the web page should display the name of Mandla’s company, a table containing the available products and prices, as well as contact information where they can place an order.

Guided activity 9.10

Here is how to create this simplistic website:

1.Start by opening a new Notepad ++.

2.Create the basic HTML structure (<html> and </html> respectively), then add a few empty lines in between.

3.Create the opening and closing head tags, then the opening and closing tags for the body.

4.Inside the head tags, add an opening and closing title tag (<title> and </title> respectively) and enter a title for the page between them.

5.Save the Notepad file as Mandla.html in an appropriate folder. At this stage, the code should look similar to the following code:

<html>

<head>

<head>

<title>Mandla’s Baked Goods</title>

<body>

</body>

</html>

6.Add some empty lines between the opening and closing body tags.

7.Using these lines at opening and closing <h1> tags and enter the main heading for the page.

8.Using the paragraph tags (<p>) add a few paragraphs of text to the page.

Once you have done this, you may have a web page that looks similar to this one:

<html>

<head>

<title>Mandla’s Baked Goods</title>

</head>

<body>

<h1>Freshly baked goods only a click away.</h1>

<p>Do you live in the Johannesburg or Pretoria area?</p>

<p>Do you have a love of Freshly Baked Goods?</p>

<p>Then Mandla’s Baked Goods is the place for you.</p>

</body>

</html>

9.Using the paragraph tags (<p>) add a sentence to indicate that the table below contains a list of products.

10.Using the table tags (<table>) add a table containing Mandla’s products and prices. In order to centre the text in a table, you must set the alignment attribute as follows: style=“text-align:center”.

Once you have done that, you will have a web page that looks similar to this one:

<html>

<head>

<title>Mandla’s Baked Goods</title>

</head>

<body>

<h1>Freshly baked goods only a click away.</h1>

<p>Do you live in the Johannesburg or Pretoria area?</p>

<p>Do you have a love of Freshly Baked Goods?</p>

<p>Then Mandla’s Baked Goods is the place for you.</p>

<p>Please have a look at all the yummy goods that we have to offer</p>

<table>

<tr>

<th>Product </th>

<th>Price </th>

</tr>

<tr>

<td>Cakes </td>

<td> R 230</td>

</tr>

<tr>

<td>Cupcakes </td>

<td>R 80 </td>

</tr>

<tr>

<td>Scones </td>

<td>R 75 </td>

</tr>

<tr>

<td>Milktart </td>

<td>R 95 </td>

</tr>

<tr>

<td>Apple Pie </td>

<td>R 80 </td>

</tr>

</table>

</body>

</html>

11.Add Mandla’s contact details where the client can place an order.

12.Format the web page by setting the attributes for the paragraphs and table.

To change the background colour and font colour of the heading, you must set the background-color and color CSS attributes as follows: <h1 style=“background-color: blue; color: white”>Freshly baked goods only a click away</h1>.

Once you have done that, the final web page will look like this:

<html>

<head>

<title>Mandla’s Baked Goods</title>

</head>

<body>

<center>

<h1 style=”background-color: blue; color:

white”>Freshly baked goods only a click away</h1>

<p>Do you live in the Johannesburg or Pretoria area?</p>

<p>Do you have a love of Freshly Baked Goods?</p>

<p>Then Mandla’s Baked Goods is the place for you.</p>

</center>

<p>Please have a look at all the yummy goods that we

have to offer</p>

<table border=”2”>

<tr>

<th width=”25%” height=”50”>Product </th>

<th width=”25%” height=”50”>Price </th>

</tr>

<tr>

<td style=”text-align:center” width=”25%”

height=”50”>Cakes </td>

<td style=”text-align:center” width=”25%”

height=”50”> R 230</td>

</tr>

<tr>

<td style=”text-align:center” width=”25%”

height=”50”>Cupcakes </td>

<td style=”text-align:center” width=”25%”

height=”50”>R 80 </td>

</tr>

<tr>

<td style=”text-align:center” width=”25%”

height=”50”>Scones </td>

<td style=”text-align:center” width=”25%”

height=”50”>R 75 </td>

</tr>

<tr>

<td style=”text-align:center” width=”25%”

height=”50”>Milktart </td>

<td style=”text-align:center” width=”25%”

height=”50”>R 95 </td>

</tr>

<tr>

<td style=”text-align:center” width=”25%”

height=”50”>Apple Pie </td>

<td style=”text-align:center” width=”25%”

height=”50”>R 80 </td>

</tr>

</table>

<b><p> Place your order now by contacting Mandla at

MandlaBakes@mail.com or phoning 012 111 558</p></b>

</body>

</html>

Save your web page and close it.

Throughout this section you have taken a brief look at how to design and create a web page for a given scenario. The example that we used is very basic and you may be told to create a more complex web page. Use the techniques you have learned over the last two years to design and create the best web page you can.

Activity 9.2

Complete the following practical activity.

1.Use an HTML editor such as Notepad++ and the information from the previous section to create a web page on a topic of your choice. Save the web page as Act10.2(Own topic).

2.The web page must include the following elements:

A title

Headings (2 different heading styles)

An image (with Alt text)

Links (to at least two outside websites)

Tables (table headers, at least 3 columns and 3 rows)

Basic styling (with square styled bullets).

Save and close the web page.

REVISION ACTIVITY

You are required to assist in designing an information pamphlet to help save the elephants. Open the incomplete Elephant_Olifant.html web page in a web browser and also in a text/HTML editor and do the following activity.

Take note of the following:

Question numbers are inserted as comments to show approximately where the answer(s) should be inserted.

An HTML tag sheet has been attached for reference. See Appendix (Tag sheet).

The final web page should look like the example below.

1.Correct the HTML tags to display the text “Save the Elephant” in the browser tab. (1)

2.Add HTML tags to display the Eleph_Olif.jpg image found in the examination folder.

a.Align the picture to the left, as shown in the example above. (4)

3.Format the text “SAVE THE ELEPHANT” with the font “Elephant”. (1)

4.Increase the font size of the text “FROM THE KIDS ANIMAL PROTECTION” by 1. (1)

5.Add a horizontal line with the following properties below the body tag, as indicated in the file:

a.Set the line colour to a yellow colour.

b.Set the line size to 4. (3)

6.Add a row with the text “Asian Elephant” to the table.

a.The table should appear as follows: (3)

7.Format the text “Physical description” with a Heading 2 style. (1)

8.Display the text “Proboscidea” in italics. (1)

9.Apply numbering to the text under the heading “Threats to elephants” as follows:

a.Poaching is still a problem.

b.Habitat loss is a concern. (2)

10.Insert a link on the words “Save the Elephant” that links to the document Save_Red.docx found in the practise documents folder. (3)

Save and close the web page.

TOTAL: [20]

AT THE END OF THE CHAPTER

CHAPTER OVERVIEW

Unit 10.1 | Good website/page design
Unit 10.2 | Reinforce concepts and skills

By the end of this chapter, you will be able to:

Explain the principles of good website/page design.

Develop a website/page with a good design.

INTRODUCTION

When it comes to web design, not all websites are created equally. Two web sites could have exactly the same information, but millions of people would use one site and almost nobody another site. This all due to good website design that keeps the reader’s attention and motivates them to continue exploring.

UNIT
10.1 Good website/page design

A web page consists of a collection of tags and attributes that tell the web browser how the page should be displayed. However, how can you be certain that the web page you are creating is any good? Web pages that are completely fair can be difficult to read or difficult to understand and it can be difficult to find the correct information.

To help you create a good and easily understandable web page, here are some things you should take into consideration:

Keep it simple: Simpler is better, so web pages should not contain too many design features, especially if those features serve no purpose.

Make it understandable: The target audience of a web page should be able to understand everything on the web page without effort.

Make sure it has purpose: Web pages should have a clear purpose, and every design and programming decision should work towards that purpose.

Make it useful: A web page should be useful, which means that users whose purpose align with the web page’s purpose should be able to use the web page easily, without delays and without difficulty.

Keep it consistent: The design on a web page should be consistent. This will make the web page easier to understand and make the web page’s identity clearer.

Create a thing of beauty: As stated in the Zen of Python programming guide, “beautiful is better than ugly”. When designing a web page, it is important to keep in mind that even the most simple, understandable, purposeful, useful and consistent web page will not be used if people do not like looking at it.

Something to know

High-quality web pages should have good information and good design.

UNIT
10.2 Reinforce concepts and skills

Complete the following activities in order to revise your knowledge on website/page design.

Activity 10.1

You need to complete a web page about rhodium. Open the incomplete Rhodium.html web page in a web browser and also in a text/HTML editor (NOT a word processing program such as Word).

Take note:

Question numbers are inserted as comments in the coding to show approximately where the answer(s) should be inserted.

An HTML tag sheet has been attached for reference.

The final web page should look like the example below.

1.Format the text “Rhodium Investment: The Rarest of Precious Metals” so that it appears centred and is formatted in a “heading 1” style in a red font colour.

2.Add a horizontal line with a 90% width below the heading “Rhodium Investment: The Rarest of Precious Metals”.

3.The tag used to insert an image as a background is <background=“name of file”>.

a.Insert the image Rh as a background for the (entire) BODY of the web page.

4.Add the following items in a list with square bullets AND leave line breaks between the two list items, as shown on the previous page.

a.Diesel catalytic converters.

b.Non-diesel catalytic converters.

5.The RMine image has been inserted below the bulleted list. Edit the image as follows:

a.Set the height to 100 pixels and the width to 200 pixels.

b.Set the border to 5 pixels.

6.Display the text “South Africa” in bold and italics.

7.An attempt was made to create a link on the text “www.ResourceInvestor.com” to link to the website www.ResourceInvestor.com.

a.Modify the HTML code so that the website can be opened when the user clicks on the text (“www.ResourceInvestor.com”) in the web browser.

b.Take note, you will know that the link works correctly if the web browser opens and the address displays in the address bar.

8.The web page includes a table consisting of three rows and three columns. Modify the table as follows:

a.Set the cell spacing of the table to 10 pixels.

b.Place the text “Rhodium Prices” in the first row, as shown in the example. NO other cells should appear in the first row.

Save and close the web page.

REVISION ACTIVITY

Open the incomplete Beware.html web page in a web browser and also in a text editor (NOT a word processing program such as Word) and complete the activity.

Before starting the activity, take note of the following:

Question numbers are inserted as comments in the coding as guidelines to show approximately where the answer(s) should be inserted.

The web page may display differently, depending on the browser used.

An HTML tag sheet has been attached for reference.

The final web page should look like the example below.

1.Format the heading “Unsafe Driving in the Cell phone Era” as follows:

a.Change the font type to Tahoma.

b.Change the font size to 24 pt. (2)

2.Insert a horizontal line under the heading “Unsafe Driving in the Cell phone Era”. (1)

3.Centre the picture horizontally and place a border of 5 pt around it. (2)

4.Find the text “Driver Behaviour: Distracted While Driving”. Change this to a level-two heading. (1)

5.Find the text “driver distraction” under the heading “Rise in Potentially Dangerous Vehicular Events”.

a.Create a link on this text to the anchor “Distraction” that already exists in the document. (3)

6.Find the text starting with “Handling the phone ..” and ending with “… on the road”.

a.Place this information in a bulleted list, as shown in the example above.

7.Find the table under the heading “Things drivers do that take their eyes and focus off the road:”.

a.Change the table as follows:

i.Change the table background colour to light blue.

ii.Set the space between the text and the cell borders to 3 pt.

iii.Set the width of the table to 80%.

b.Change the first row of the table as follows:

i.Set the height of the first row to 80 pt.

ii.Vertically align the content of the first row to appear at the bottom.

iii.Change the heading “Action” to italics.

c.Add the data 14% to the cell in the third column of the fourth row.

d.Merge the cells in the last row of the table. (9)

Save and close the web page.

TOTAL: [18]

AT THE END OF THE CHAPTER