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

[edit | edit source]

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

[edit | edit source]

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

[edit | edit source]

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

[edit | edit source]

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

[edit | edit source]

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

[edit | edit source]

TBD

## Challenge 7: Mail Merge

[edit | edit source]

The customer details spreadsheet

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):

An example of the mail merged letter

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.