 |
Table of ContentsICAU2013B Integrate commercial computing packagesThis unit defines the competency required to manipulate, convert and integrate data between different two or more commercial software applications. The following unit is a prerequisite for this competency: • ICAU1128B Operate a personal computer. There may be benefit in concurrent learning of the following unit: • ICAU2006B Operate computing packages. These units are linked and form an appropriate cluster: • ICAD2012B Design organisational documents using computing packages • ICAU2006B Operate computing packages. Unit OutlineWelcome to ICAU2013B Integrate commercial computing packages. In this unit you’ll investigate and practise the skills for working across software applications for word processing, spreadsheet and database. You’ll learn how to work with software features to convert, import, export and merge data between different types of documents. You’ll do five tasks to help a small business called Lifetime Australia Pty Ltd. set up some company documents. To do these tasks you’ll need to be able to: 1. Determine the requirements of a job task. 2. Produce data/documents to a new format. 3. Save and retrieve data with the aid of help functions. Assessment informationTo successfully complete ICAU2013B Integrate commercial computing packages, you will need to be able to: manipulate, convert and integrate data between commercial application software. Your assessment tasks will require you to demonstrate your competence by:
There are five tasks given in this resource. You will be required to complete these for your assessment tasks. This unit could be assessed in a holistic manner with the following Units of competency:
You may have already completed some of the task needed for assessment in this unit! Simulated Workplace ScenarioWelcome to Lifetime Australia Pty Ltd., a small company in the business of manufacturing pens. This company needs your help to set up computerised systems for creating and maintaining data and documents on an ongoing basis. You have been employed on contract as an IT Support person to help Lifetime Australia with their computing needs. Meet your coach from Lifetime Australia to find out more about the tasks you’ll do in this job.
Hi, and welcome to Lifetime Australia! I'm Tim, the Office Manager, and I'll be taking you through your jobs, to get this business more computerised. Task 1 Recommend suitable software to service the business needs We don't have all the software applications we're going to need - I think I know what we'll need, but I'll get you to do a recommendation on the packages we should purchase - based on our business needs. Task 2 Create two reports by integrating software packages Then you'll put the new software to the test, and create two integrated documents: an inventory report and a report on outstanding invoices. Task 3 Create a mailing list for mailing letters, brochures and invoices If you're still around after that last task, we'd like you to create a mailing list to be used for mailing letters, brochures and invoices. Task 4 Create a brochure that incorporates a spreadsheet and a database A more challenging task awaits you next: to create a brochure for our sales people. The brochure will be made up of documents from different software packages: a spreadsheet and a database. Task 5 Investigate help resources to find out about software features. Before your contract is up, we need you to look into software tools and features - to produce graphics in-house. I've got some issues for you to investigate that will help us learn more about our software and get the most out of it. Section 1Select appropriate software to meet specific business requirementsPreview More job roles than ever before require workers to produce computerised documentation in order to disseminate information, give instruction or perform and store calculations. In order to efficiently produce appropriate and effective documents there are three broad skills and areas of knowledge required and they, in effect, form three steps to create documents and files: 1. Identify the requirements — of both the document to be produced and the tool to produce it. 2. Select the most appropriate software tool for the task. 3. Follow the organisation’s guidelines for the production of that type of document. Learning outcomes After completing this topic you will be able to:
We have already covered the necessary material for this element in Section 2 of Operate Computing Packages Section 2Link and embed objectsPreview The process of placing data or a whole document into an active document is called importing and is an efficient use of objects and/or data previously generated without the need to re-create content. The process of importing data from one document to another can be done by either a static paste or a dynamic linking or embedding of the data/file. Linking and embedding achieve and maintain a dynamic connection between the imported data and its file of origin, and this enables the object to be modified in its new location. Unlike this, a static paste into a new document retains no ties with the original file. That is, the pasted object is isolated from the original file and the application that created it, so it cannot be easily modified. Learning outcomes After completing this topic you will be able to:
Before you start You should have a basic understanding of the functions and operations of software applications for word processing, creating spreadsheets and databases before you do this topic. ReadingThe readings here will help you develop your knowledge and skills for linking and embedding objects. This will involve an understanding of:
Get reading:  Link and Embed_reading.docxPracticeContents Activity 1: Investigate importing functions Activity 2: Use spreadsheet data in a word processor file Activity 3: Embed a bitmap image Activity 4: Embed a spreadsheet worksheet Activity 4: Link a spreadsheet file Download the worksheet here: Link and Embed Practice Tasks.docxAssessment Task 2 : Link and embed objectsOur new software has been purchased and installed — well done on the recommendations! Now you’ll put the software to the test and create two sample integrated documents: an inventory report and a document showing our outstanding invoices. We’ll circulate the samples to show staff and the General Manager how these reports will look in the future. The inventory report should be in a word document, as it will be widely used. First you’ll need to create a database table for the inventory, then insert this table into a word document as a static database table. Also create the outstanding invoices report in a word document, but you’ll need to insert and create a new embedded invoice spreadsheet. Can you try creating the spreadsheet and entering the first lot of data from inside the word processor? Then check that you have the spreadsheet properly embedded by entering the second lot of data into the spreadsheet using the spreadsheet application. When you next open the word document you should see that the new spreadsheet data appears in the word document.  Tim McNiell, Office Manager, Lifetime Australia To Do List 1. Create a word processor document with a static database table inserted. 2. Create a word processor document with an embedded spreadsheet (inserted and) created within it. Tools Use this sample data to set up the database table : Include the following data in the database table you create for the inventory report.
Data to enter within the word document: This is the first data to include in the invoice spreadsheet from the word document:
Data to enter from the spreadsheet software: This is the second data to include in the invoice spreadsheet:
Tips A great source for assistance is the online Help facility in your word processing software. ResearchYou should investigate the online Help facility within any computing package if there is any difficulty or uncertainty with any of the processes covered in this topic. TermsEmbedding: inserting an object that retains the attributes from the application with which it was created; can only be modified by double clicking on it since there is no link to the source Integrated document: integrated documents (sometimes called compound documents) containing not only text but embedded and/or linked objects as well Integrated package: a software package that contains a suite of several applications which create data that is compatible between the applications Linking: importing a copy of an object created with a different application and maintaining a link to that original file so any changes made in the original file will automatically be up-dated in the linked copy within your document and vice versa OLE: object linking and embedding Open software: software that is available free of charge, ie you don’t pay for it and you have the ability to make your own changes to the source code Paste special: pastes an item with options to create and preserve a link between the item and the source file from which it was copied Section 3Merge data to create a new documentPreview In business there is often the need to send one document to many people, for example, an advertising brochure. The potentially long and tedious process of individually addressing or otherwise customising a document for each recipient can be avoided with the use of the merge features within software applications. Merging allows data from one (or part of one) source document to be integrated into one or several other documents. For example, merging individual names and addresses stored in a database file into a form letter, so that each letter is customised with a different name and address, but the body of the letter is the same for everyone. Learning outcomes After completing this topic you will be able to:
Before you start You should have a basic understanding of the functions and operations of software applications for word processing, creating spreadsheets and databases before you do this topic. ReadingThe readings here will help you develop your knowledge and skills for merging data created from different software applications. This will involve an understanding of:
Get reading: Merge data_reading.docxPracticeContents Activity 1: Merge spreadsheet data with a word template Activity 2: Set up a mail merge Activity 3: Case study Activity 1: Merge spreadsheet data with a word templateThe Busy Bee Book Shop is holding a sale next month, with all books reduced by at least 10%. Mandy wants to let her customers know about the sale and has decided the most efficient way to do this is to send a form letter. However the customer information is in a spreadsheet. Your task is to create a ‘special offer’ letter by merging a letter template with customer data:
Activity 2: Set up a mail mergeA student who did this course got a part-time job in a real estate agency. One of her first tasks was to send some letters to tenants informing them of a rent increase. To do this she: (1) created a template form letter (2) created a second document with the names and addresses in a table (3) created a template that could merge the names and addresses into the form letter. Your task is to create a template that merges data from a table with a form letter. Base the form letter template design on this example.  Base your source table on this example.  Use the following as a guide for where to insert the merge fields in the main document (the form letter). Dear <> After discussions with <> <> it has been decided to increase your rent from <> to <>. This rental increase will take effect in four weeks. If you have any questions regarding this please contact me. Here are the steps for this task using Microsoft Word mailmergesteps.docx. It may be helpful to look at this process even if you are using another word processing package.Activity 3: Case studyThe IT department had been working on an invoice payment database for the company. The data for invoicing was received in a comma-delimited text file. The file was received on a weekly basis and contained 600-700 invoice lines. The format of the file was: Date,Invoice,Amount 01-01-00,123456,79.56 01-01-00,234567,7.95 01-01-00,345678,24.56 All was going well and the development team was ready to sign off this project and move on to another one. Suddenly the invoice data total didn’t match that of the paper invoice total. A thorough checking of the data against the paper invoice revealed that some invoice amounts were now over the $1,000 mark. This confused the importing routines and turned $1,234.56 into $1.00. Can you determine the cause of the import problem described in the case study? Discuss in class the steps the developer could have taken to avoid the problem, and suggest how she might now rectify it. Assessment Task 3Lifetime Australia is so pleased with the work you’ve completed that we’ve renewed your contract. Now we’d like you to create a mailing list to be used for mailing letters, brochures and invoices. We’ve got a database for you to use, to create a mailing list table of Lifetime Australia’s clients. Then, can you create a ‘special offer’ letter, which will be sent to all our clients? In the letter, tell the clients that Lifetime Australia is having a huge stock take sale and all products are 50% off. This offer will end on 30 June of this year. Use the mailing list database table to include each client’s name and address at the top of a special offer letter. Tim McNiell, Office Manager, Lifetime Australia To Do: Create ‘special offer’ letters, with merged names and addresses from a database mailing list. Tools: Use this mailing list data to create a database table. Client data
ResearchAt http://office.microsoft.com/training/training.aspx?AssetID=RC102778121033 you can find out more details about merging within Microsoft applications. TermsData source: a document that contains the variable information to be inserted into a standard document Embedding: inserting an object that retains the attributes from the application with which it was created; can only be modified by double clicking on it since there is no link to the source Field: discrete piece of information given for a record Form letters: letters to be reproduced in multiple quantities at places where variable information is to be inserted for printing Import: bringing previously processed data into the current document, for example, from another data file or even the Help file into a completely different application Linking: importing a copy of an object created with a different application and maintaining a link to that original file; allows any changes made in the original file to be automatically updated in the linked copy within your document and vice versa Main document: contains the standard text and merge codes in those locations where the document is to be personalised Merged document: the document that is created when the main document and data file are joined together (merged) Merging: combining information from two documents into one document Records: collection of data about an individual entry in a database (data source) Templates: pre-formatted documents Section 4Convert data for use in another software packagePreview Regardless of the software package used, when files are created they can usually be saved in one of a variety of file formats. The range of options for file formats varies amongst applications. For example, a graphics program will save data in different types of file formats compared to a database or a word processor. Saving or converting a file to an appropriate file format can be a critical step in being able to perform tasks such as merging data, or exporting and importing data from one file type to another. Learning outcomes After completing this topic you will be able to:
Before you start You should have a basic understanding of the functions and operations of software applications for word processing, creating spreadsheets and databases before you do this topic. ReadingThe readings here will help you develop your knowledge and skills for converting data. This will involve an understanding of:
Get reading: Convert data_reading.docxPracticeDownload and complete the four activities found in the worksheet here: ConvertData_exercises.docxAssessment Task 4Convert data for use in another software package Lifetime Australia now needs a brochure for our sales people. The brochure will be made up of documents from different software packages. Begin by creating a spreadsheet pricelist. I’ve got the pricelist data to give you. You also need to create a word processor sales brochure. You only need to type appropriate headings into the brochure. Rather than typing price information into the brochure, insert the spreadsheet pricelist into the brochure. Finally, create and save a product list database table. I also have the product list data for you. When the database table is finished, copy and paste it to the word processor sales brochure. Tim McNiell, Office Manager, Lifetime Australia To Do List 1. Create the spreadsheet pricelist. 2. Create the product list database table. 3. Create the word processor sales brochure with the spreadsheet pricelist inserted and the product list database table pasted in. Tools 1. Data for the spreadsheet: Enter this pricelist data into a spreadsheet:
2. Data for the database table: Enter this product data into the product list database table:
Mailmerge PracticeMail mergeUsing Mail Merge in Microsoft WordWhat is a Mail Merge? In business it is very common to send the same letter to many people or businesses. This letter may be promotional or some kind of an announcement. For example, you may want to send all of the hotel managers in Wagga an invitation to attend some kind of presentation that you are giving. If you have the names and addresses of 50 general managers, you can: 1) Type 50 letters. 2) Type 1 letter and use mail merge to combine it with your 50 addresses to create 50 letters. The more addresses that you have the more reason you have to use mail merge.  A Mail Merge (or merge) takes one Main Document, combines it with a Data Document and produces a 3rd multiple document set. For example you could have a Form Letter as the Main Document, a list of 500 Addresses as the Data Document and merge them to create 500 personalised letters to the people or companies in your list. The picture above shows a visual interpretation of mail merge. Or you could also be creating a "Form Fax" that you can fax out to people you wish to contact. You set "form fields" in the main document that correspond to the headings in your data document. When merged the info in that column goes into that place in resulting file.  Image 1  Image 2 If you were to merge the Main Doc from Image 1, with the Data Doc from Image 2, you would get a new file with 7 pages. There would be a fax to each of the seven travel agents on each page.  Example of the 1st page of the Merged Document Simple mail mergeAs we said before creating a merged document is the result of merging a Main Document with a Data Source.The data source can be a Word table an Access database or an Excel spreadsheet. Follow the example steps below to create a simple mail merge. You will do a simple mail merge with a simple table as the data source, and a quick note as the main document. Step 1. Make the Data Source in MS Word. a) Create the following table in Word - 5 columns, 6 rows.
c) Save the table as "friendat.docx" - on your USB drive: or in your folder in my documents . d) Close the document. Step 2. Make the Main Document in MS Word The Main Document should look something like this:
3. Begin the Mail Merge a) Open "mastnote.docx". Select: "mailings" from the ribbon menu at the top of the Word 2007 window. b) Choose step by step mailing wizard in start mail merge  c) Choose " Letters" in Step 1 and "Use current document" in Step 2 to make "mastnote.doc" into the main document. d) Go next >select recipients>Using existing list and browse to and open your file "friendat.docx". e) Use the list from frienddat.docx > click Ok >and then go next>write your letter f) Use "Insert Merge Field" to put the merge fields in the correct places in your main document. Eg. Dear <<Name>> (put the Name merge field after "Dear")  g) Preview your letters h) Complete the merge i)Edit the individual letters and select all j) You should get a new document with 5 pages with letters to your friends on each page. k) print pages 1&2 of the merged document only! write your name on it if you are submitting it to your instructor! Extra Mail Merge PracticeDownload these two files below to your folder in my documents or to your USB thumb drive (make sure you can find them later!) Merge document.docx Recipient List MM II_RT10277162.xlsxUsing the Ribbon to create a mail merge documentIn this practice you will start the merge by using a letter, connect to a recipient list, insert fields, and complete the merge by using the Mailings tab on the Ribbon. You will also sort and filter the recipient list, and match the list's columns to the Address Block field elements. Start by opening the word document called: Merge document.docx (you should have downloaded it to your folder already) EXERCISE 1: DISPLAY THE MAILINGS TAB AND START THE MERGE 1. Click the Mailings tab on the Ribbon and then click Start Mail Merge. 2. Click Letters. Nothing will appear to have happened because we already have a document open. EXERCISE 2: CONNECT TO YOUR RECIPIENT LIST AND REFINE THE LIST This exercise takes you through connecting to the recipient list and then sorting and filtering the list to send the right mailing to the right customers. First you need to make sure you have downloaded the recipient list above. It is called Recipient List MM II.xlsx. Make sure you right click on it and save target as to your folder in my documents so you can find it in the next step. Connect to the recipient list 1. On the Mailings tab, in the Start Mail Merge group, click Select Recipient and then click Use Existing List. 2. In the Select Data Source dialog box, locate the "Recipient List MM II.xlsx" file. Here's how: In the folders section navigate to your folder and select the file Recipient List MM II.xlsx and then click Open. 3. In the Select Table dialog box, click Customers$$. 4. Click OK. Now you are connected to the list. Notice that more commands, such as Edit Recipient List and Address Block, have become available. You'll use these in the next steps. Sort and filter the recipient list 1. In the Start Mail Merge group, click Edit Recipient List. 2. In the Mail Merge Recipients dialog box, click Sort. 3. In the Filter and Sort dialog box, click the arrow next to Sort by, and then click City. 4. Click OK. Notice that the recipients are now grouped into cities. 5. Click Filter 6. In the Filter and Sort dialog box, click the arrow next to Field and click Postal Code. 7. In the Comparison list choose Equal to and then type 14457 in the Compare to text box. 8. Click OK. Notice that only the recipients that are located in the 14457 postal code appear in the recipient list. Don't worry, the other recipients are not deleted. To see all the recipients again simply click the arrow in the heading labeled Postal Code and click (All). 9. Close the Mail Merge Recipients dialog box by clicking OK. EXERCISE 3: ADD AND MATCH FIELDS As you can see, the text that will be the same in all copies of the letter is already in the main document. Now you need to add fields for the content that will be unique in each copy, including: • The recipient's address. • The recipient's name in the greeting line. • A personal note. The commands to insert these fields are located in the Write & Insert Fields group. Then after you have inserted the fields, you will view all of them by using another command in the group. Add the recipient's address and match fields 1. In the main document, click after "2009" in the date. Press ENTER twice. 2. In the Write & Insert Fields group, click Address Block. 3. If the Select Table dialog box appears, click Customers$$ and then click OK. 4. In the Insert Address Block dialog box, under the Correct Problems section in the lower right, click Match Fields. 5. In the Match Fields dialog box, match the Name column from the recipient list with the Address Block elements called First Name. Match the Number column with the Address 1 element. 6. Click OK, and then click OK again. An «AddressBlock» field is inserted into your main document. Note : If your fields look like this: {MERGEFIELD "Address Block"} instead of like this: «Address Block», when you insert them then the field codes are turned on. This does not affect the merge, but you can turn the field codes off by pressing ALT + F9. Add a greeting 1. The cursor should be at the end of the «AddressBlock» field. Press ENTER twice. 2. In the Write & Insert Fields group, click Greeting Line. 3. In the Greeting Line dialog box, choose a greeting format that looks good to you. 4. Click OK. Add a personal note 1. In the main document, click after the period following the word "coffee" at the end of the second paragraph, and then press ENTER. 2. In the Write & Insert Fields group, click the arrow next to Insert Merge Fields. 3. In the list that appears, click Personal_Note, and then click Insert. The <<Personal_Note>> field appears in the letter. You will get a chance to format this note in the next lesson. View all fields in the main document To see all the fields that have been added to the main document: In the Write & Insert Fields group, click Highlight Merge Fields. All the fields will now have a gray background so they are easy to spot in the document. To clear the gray, click Highlight Merge Fields again. Did you notice the text, "[Instructions for]"? You'll change this in the practice in the next lesson, so just leave it for now. EXERCISE 4: PREVIEW AND FINISH THE MERGE Now that you've added all the fields to the main document, you're ready to see how the records will merge.
If you were actually preparing these letters to send, you could either:
In either case, you have a chance to choose the recipients you want to include in the merge.
Close Word by clicking the Microsoft Office Button and then in the lower right, click Exit Word. Perform a complex mail mergeResume the mail merge Open the Word document that you saved in the previous lesson. Here's how:
Complete the letter Your letter has a coupon included in it and now you need to add numbers to the coupons and tell customers who live in town to bring the coupon into the store. You want to tell out-of-towners to mail in the coupon. To do this you need to use conditional text. You also want the personal note to be more visible. You'll format the note to be red and italics. Exercise 1: Insert fields In this exercise you'll number the coupon sequentially and add instructions for the customer about how to redeem the coupon. Add a number to the coupon Scroll down the document until you see all of the coupon. Click in the blank line in the coupon under "Expires February 1, 2010." Click the Mailings tab, and in the Write & Insert Fields group, click Rules and then Merge Record #. A «Merge Record #» field appears in the coupon. Each coupon will have a sequential number starting on the first coupon with 1. Note: If you see a number instead of «Merge Record #», click Preview Results in the Preview Results group. Add coupon instructions In the main document, select the text, "[Instructions for]." In the Write & Insert Fields group, click Rules. Click If...Then...Else.... In the Insert Word Field: IF dialog box, for Field name, click City. For Comparison, click Equal to. For Compare to, type Bellingham. In the Insert this text text box, type Bring in. In the Otherwise insert this text text box, type Mail in. Click OK. Even though the text "Bring in" replaces "[Instructions for]" in the main document, when you complete the merge, "Bring in" or "Mail in" will appear in the merged documents, depending upon whether the recipient lives in Bellingham or somewhere else. Exercise 2: Format a field You want to make sure the personal note stands out, so you decide to make it red and italics. In the main document, select the «Personal_Note» field, including the chevrons. Note: If you see a note text instead of «Personal_Note», click Preview Results in the Preview Results group. Click the Home tab, and in the Font group, click the arrow next to Font Color, and then click a red color that looks good to you. Click the italics command in the Font group to make the note italics. Exercise 3: Preview the merged letters and complete the merge Ready to see the results? You'll want to make sure that the coupons are numbered sequentially and the instructions are correct for the people who live in town versus the out-of-towners. Look at the merged documents Click the Mailings tab, and in the Preview Results group, click Preview Results. Scroll down the merged document to see how information from one of the recipients in your list replaced the fields. Click Next Record to page through the other merged documents. Make sure that when the recipient's address is not Bellingham, the coupon instructions say "Mail in." Also check to see that, in the letters for recipients who have personal notes, the note is in red. Finish the merge If you were actually preparing these letters to send, you could either create a comprehensive document containing all the letters, or print the them immediately. In the Finish group, when you click Finish & Merge: Click Edit Individual Documents to create a new comprehensive document containing all the letters, one letter per page. You can change all or some of the letters before you print them. Click Print Documents to print the letters. In either case, you have a chance to choose the recipients you want to include in the merge. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
and then click Save as. In the Save As dialog box, choose a location, type a file name, and in the Save as type box, select Word Document. Then click Save.
