Applications Challenges

The following challenges are designed to flex your advanced application feature skills for your favorite word processor, spreadsheet or database.

Challenge 1: Identifying Advanced Features
Identify at least two features of each program that you would classify as 'advanced'. Write a short description of each one and briefly comment on when or where it would be useful.

Challenge 2: The Import/Export Business
So you've just set up an art gallery and you want to create a database to keep track of your artists. To kick off your used-art supplier emails you this list of artists names. Your task is to import it into a table in your database application...

ArtistID,First_Name,Last_Name 3,Adolph,Gottlieb 6,Robert,Motherwell 8,Frank,Stella 13,Rembrandt,Van Rijn 16,Pablo,Picasso 17,Robert,Delaunay 24,Franz,Marc 25,Edvard,Munch 26,Wassily,Kandinsky 29,Amedeo,Modigliani 30,August,Macke 31,Albrecht,Durer 35,Henri,Matisse 37,Georges,Braque 39,Mary,Cassatt 40,Georges,Seurat 44,Edgar,Degas 45,Edmund,Tarbell 46,Pierre  ,Bonnard 56,Grant,Wood 57,,Christo 58,Frank Lloyd,Wright 61,Wolf,Kahn 64,David,Hockney 65,William,Dyce 78,Claes,Oldenburg 79,Roy,Lichtenstein 82,Keith,Haring 84,Vincent,Van Gogh

You will need to select the text, copy it into a text editor, and save it with a sensible file name and extension (.csv might work)

Some things to think about before you start:
 * What field delimiter is used?
 * Is a text qualifier used? If so, what is it?
 * Does the first row contain the field (column) names?

Your supplier has also sent through a small crate of paintings (some of them still have a bit of wet paint on them, so you know they're fresh), and emailed a list (with some handy links to pictures he took of the stock). You need to import these too:

"ProductID","Title","ArtistID","ImageURL" 10023927,"J`Taime, No. III with Loaf of Bread",6,"http://images.art.com/images/products/large/10023000/10023927.jpg" 10083728,"Cambridge, Collage, 1963",6,"http://images.art.com/images/products/large/10083000/10083728.jpg" 10090457,"Mallarme's Swan, 1961",6,"http://images.art.com/images/products/large/10090000/10090457.jpg" 10099526,"Beside the Sea with Bulkhead",6,"http://images.art.com/images/products/large/10099000/10099526.jpg" 10123849,"Dialogue I",3,"http://images.art.com/images/products/large/10123000/10123849.jpg" 10202317,"Peterson IV, DNC 1980",8,"http://images.art.com/images/products/large/10202000/10202317.jpg" 10202318,"Lincoln Center Festival, 1967",8,"http://images.art.com/images/products/large/10202000/10202318.jpg" 10202319,"At Castellis, 1982",8,"http://images.art.com/images/products/large/10202000/10202319.jpg" 10225934,"West Broadway, 1958",8,"http://images.art.com/images/products/large/10225000/10225934.jpg" 10225935,"Seward Park, 1958",8,"http://images.art.com/images/products/large/10225000/10225935.jpg" 10225936,"Sinjirli Variations Squared",8,"http://images.art.com/images/products/large/10225000/10225936.jpg"

Once they're imported create a query by linking the artistID in both tables and export the results into a spreadsheet. If you're using Microsoft office this stuff should be easy to figure out. For the Open Office student searchopensource.com have some tutorials

Challenges 3: Linking and Embedding
Now you've got a spreadsheet with some data from your tables, have a play with it. What happens if you change the data in the Artist table? Does it update the spreadsheet data? Copy your spreadsheet to a table in a word processing document - firstly as a cut and paste exercise; and secondly as an embedded object. Now change the original spreadsheet data and check if the embedded object shows the changes. Or does the copied data remain unchanged? What happens to the original database record once you change your spreadsheet?

Challenge 4: Make a Template
Create a template for a letter that creates new documents pre-filled with the date and your name and address in the top right part of the page.

Challenge 5: Making a Macro
Record a macro that adds your signature at the current cursor position. Add this macro to the keyboard shortcut SHIFT-ALT-S. Add it to a button on the toolbar too.

Challenge 6: Using buttons, checkboxes, option buttons, text boxes and drop down lists
TBD

Challenge 7: Mail Merge
For this challenge you will be doing a mail merge for a store with a customer loyalty system. Each customer builds 'loyalty points' for their purchases over the year. At the end of the year they're sent a letter with a gift certificate. To complete the challenge you will need to create a spreadsheet with the following customer data (click on the image for an enlarged version): Once you have the spreadsheet saved, use it to create this mail merged letter. You will need to assign the spreadsheet columns to field names for the merge, insert an address block and put the points field into the mail merge template. If you're feeling creative you could go on to create the gift certificate with a fancy border, embedded image and word art to match.