Applications Challenges

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

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.

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

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?

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.

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.

TBD

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.