C:\Core\Apache\Apache2\htdocs\uploadedFiles\uPnhdMDP_YF4lgxpFtC4I2itkHWLE9nTW\Database.pdf

Microsoft Works Suite 2001

Database

E WING S ENIOR N ET C OMPUTER L ITERACY C ENTER

F EBRUARY , 2002

Prepared by: J. Joel May

Table of Contents

Introduction to Databases..............................................................................................................I-1

What You’ll Learn....................................................................................................................I-1

The Opening Screen (Task Launcher)...................................................................................I-1

Open a Database File ............................................................................................................I-2

Learn About Files, Records, and Fields.................................................................................I-2

The Works Database Screen ................................................................................................I-3

The Dialog Boxes ...................................................................................................................I-4

Moving Around in the Database..............................................................................................I-5

Moving Around within a Record in Form View........................................................................I-5

Take a Look at Several Records at Once – List View ............................................................I-5

Moving Around in List View .....................................................................................................I-5

To Return to Form View..........................................................................................................I-5

Finding Selected Records......................................................................................................I-6

Sorting the Records ...............................................................................................................I-6

Customize the Toolbar ...........................................................................................................I-7

Saving the File and Ending the Session.................................................................................I-7

II.

Managing Records........................................................................................................................II-1

What You’ll learn....................................................................................................................II-1

Add a New Record to the Database......................................................................................II-1

Deleting a Record from the Database..................................................................................II-2

Edit the Contents of an Existing Field of a Record ...............................................................II-3

Changing the Contents of a Field in Multiple Records ..........................................................II-3

Adding a Field to Your Records .............................................................................................II-4

Create a Computed Field......................................................................................................II-5

Making Form View More Attractive........................................................................................II-5

Saving the File and Ending the Session................................................................................II-5

III.

Formatting the Database and Using Filters............................................................................. III-1

What You’ll Learn.................................................................................................................. III-1

Changing the Field Width..................................................................................................... III-1

Formatting Field Contents.................................................................................................... III-2

Formatting Field Alignment................................................................................................... III-2

Formatting Field Font and Style ........................................................................................... III-3

Formatting Field Borders and Shading................................................................................. III-3

Using Filters to Find Records that Match Exactly................................................................ III-4

Using Filters to Find Records that Exceed a Certain Value................................................. III-4

Using Filters to Find Records that Fall Below a Certain Value............................................. III-4

Using Filters to Find Records that Match in Multiple Fields.................................................. III-5

Using Filters to Find Records that Match Any of Several Conditions .................................. III-5

Using Filters to Find Records that Fall Within a Range....................................................... III-5

Saving the File and Ending the Session............................................................................... III-6

IV.

Advanced Filter Techniques and Printing the Database..........................................................IV-1

What You’ll Learn..................................................................................................................IV-1

Creating and Deleting Filters................................................................................................IV-1

Naming and Saving Filters ...................................................................................................IV-1

Filtering Using Formulas ......................................................................................................IV-1

Inverting Filters .....................................................................................................................IV-2

Hiding and Unhiding Fields and Records .............................................................................IV-2

Printing Database Records..................................................................................................IV-3

Printing a Blank Database Form..........................................................................................IV-3

Using the Print Preview screen.............................................................................................IV-3

Printing the Entire Contents of a Database..........................................................................IV-3

Printing Selected Records ...................................................................................................IV-4

Adjusting the Printed Output.................................................................................................IV-4

Saving the File and Ending the Session...............................................................................IV-5

Creating and Formatting Database Reports............................................................................ V-1

What You’ll Learn.................................................................................................................. V-1

Introduction........................................................................................................................... V-1

Creating a Standard Database Report................................................................................. V-1

Modifying the Database Report............................................................................................ V-4

Saving the File and Ending the Session............................................................................... V-6

VI.

Creating a Database and Using Mail Merge.............................................................................VI-1

What You’ll Learn..................................................................................................................VI-1

Create Fields for the New Database....................................................................................VI-1

Adding Some Finishing Touches..........................................................................................VI-1

Entering Data Into the Database - Part I..............................................................................VI-2

Entering Data Into the Database - Part II..............................................................................VI-2

Saving the Database for Future Use....................................................................................VI-3

Opening the Template File....................................................................................................VI-3

Turn the Template Into a Form Letter...................................................................................VI-3

Printing the Results ..............................................................................................................VI-4

Saving the Results and Ending the Session........................................................................VI-4

Microsoft Works Suite 2001 - Database

I. Introduction to Databases

A. What You’ll Learn

1. How to open a database and view it in two different ways

2. How to identify the database records and fields and navigate among them

3. How to find and sort information

4. How to save a copy of the database

B. The Opening Screen (Task Launcher)

1. When Works starts, the first screen displayed is the Task Launcher Screen

2. At the top of the window is the Task Launcher title bar. Underneath are six words:

a) Home

b) Tasks

c) Programs

d) History

e) Customize

f) Help

Works Suite 2001 Database

Page I-1

Prepared for: Ewing SeniorNet Computer Literacy Center

Prepared by: J. Joel May

2/2002

C. Open a Database File

1. Let’s start by working with a database that has already been created for you.

a) Click on History

b) The document we want to use is not (yet) listed here (take my word for it), so click on

Find Files or Folders

c) Since the file you want to use is on the floppy disk in your Drive A: (if the disk is not in

place, insert it now), you must change the Look In choice to 3 ½ inch Floppy (A:)

d) In the Named text box, type in Videos1. Now click on the Find Now button

e) Double click on Vidoes1 in the text box at the bottom of this window

D. Learn About Files, Records, and Fields

1. You just opened a database file called VIDEOS1.WDB (a database file is a collection of

records like an address book, a recipe file, or an inventory of household goods. It this

case it is a listing of video tapes).

2. What appears on your screen is a Record (like a name, address and telephone number; a

list of ingredients and instructions for combining and cooking them; or information about a

single piece of household furniture). The particular record you are looking at is that of a

video tape called “Smokey and the Sleeper.” Works databases can have as many

records as your computer hard disk can hold.

3. The individual items of information in the Record are called Fields. In this case,

there are eight fields: Stock #, Title, Star, Distributor, Year, Genre, On Hand, and Price.

Works databases can have up to 256 fields.

Works Suite 2001 Database

Page I-2

Prepared for: Ewing SeniorNet Computer Literacy Center

Prepared by: J. Joel May

2/2002

E. The Works Database Screen

Minimize, Resize

Title

Tool

Entry

File

and Close

Bar

Name

Buttons

Bar

Help

Work

Area

Field

Name

Value

Cursor

View

Control

Task

Status

Bar

Record

Control

Horizontal

Vertical

Help Menu

Scroll Bar

Controls

Works Suite 2001 Database

Page I-3

Prepared for: Ewing SeniorNet Computer Literacy Center

Prepared by: J. Joel May

2/2002

F. The Dialog Boxes

1. As you use Works (in Windows 98 or other Windows programs), you will

encounter dialog boxes

2. Dialog boxes are used to provide information to the program regarding the

appearance of your document, the options you want to have in effect, etc.

3. They can contain one or more of the following components:

Text Box

Scrolling List Box

Drop-Down List Box

Action Buttons

Help

Check Boxes

Radio Buttons

Preview Box

Works Suite 2001 Database

Page I-4

Prepared for: Ewing SeniorNet Computer Literacy Center

Prepared by: J. Joel May

2/2002

G. Moving Around in the Database

1. Press CTRL-Page Down to move to the next record

2. Press CTRL-Page Up to move to the previous record

3. Press CTRL-End to move to the last record (a blank record)

4. Press CTRL-Home to move to the first record, or …

5. Use the Record control buttons (they work almost like those of a VCR)

6. As you move from record to record, watch the right end of the Status Bar

H. Moving Around within a Record in Form View

1. The database cursor (in Form View) is a black highlight visible on the current field value or

field name.

2. Press TAB to move to the next field value (NOTE: If you are at the last field value in a

record, pressing TAB will take you to the first field value of the next record)

3. Press Shift-TAB to move to the previous field value (NOTE: If you are at the first field

value in a record, pressing Shift-TAB will take you to the last field value of the previous

record)

4. As you move from field value to field value, watch the Entry Bar at the top of the

work area.

I. Take a Look at Several Records at Once – List View

1. You are currently looking at the database in a Form View (You are viewing one record at a

time as a “Form”)

2. On the View menu, choose List

a) or click the List View button on the toolbar. (To remind yourself of what each button on

the toolbar does, simply let your mouse pointer hover over it for a second)

b) or press Shift-F9

3. You are now in List View (You are viewing several records at a time in a “List”)

J. Moving Around in List View

1. The database cursor (in List View) is a box around the current field value.

2. To move the cursor in List View

a) Use the Up-Arrow or Down-Arrow keys to move from record to record and the Left-

Arrow or Right-Arrow keys to move from field to field.

b) Press the End key to move to the last field of a record and the Home key to move to

the first field of a record.

c) As in Form View, CTRL-End will take you to the end of the last record and CTRL-

Home will take you to the beginning of the first record

d) You can also simply click your mouse pointer on particular field of a particular record,

and the cursor will move directly there

3. Again, keep an eye on the Status Bar at the bottom of the screen to remember where you

are in the database and on the Entry Bar to remember where you are in the record

K. To Return to Form View

1. On the View menu, choose Form

2. Click on the Form View button on the toolbar

3. Press F-9

4. When is it preferable to use List View? When is it preferable to use Form View?

Works Suite 2001 Database

Page I-5

Prepared for: Ewing SeniorNet Computer Literacy Center

Prepared by: J. Joel May

2/2002

L. Finding Selected Records

1. Let’s find out how many Family-oriented videos we have on hand and what their titles are

a) Go to List View

b) On the Edit menu, choose Find

c) In the Find What text box, type “Family” (do not use quotation marks)

d) In the Match box, click on the All Records radio button (since we want to find all of the

Family-oriented videos, not just the “next” one in the database)

e) Click the OK button (or press ENTER)

f) The four videotapes (Record numbers 5, 18, 20, and 32) in your collection which are

classified as “Family” are displayed.

g) Notice that the Status Bar shows that you are currently displaying 4 of a total of 48

videos in the database

h) Finally, go to the Record menu, choose Show and All Records, and the entire

collection of video tapes (all 48 of them) will again be displayed.

2. Which Tapes are Distributed by Neilson (Neilsen?)

a) We would like to know the answer to this, but we can’t remember if the distributor’s

name is spelled with an “o” (the Norwegian spelling) or an “e” (the Swedish spelling).

b) Go to List View (if you’re not already there)

c) On the Edit menu, choose Find

d) In the Find What text box, type “Neils” (do not use quotation marks). Note that we

have simply ignored the part we aren’t sure of. Any portion of a word (or, indeed, any

combination of letters you specify) will be identified by the Works “Find” command.

e) In the Match box, click on the All Records radio button

f) Click on the OK button (or press ENTER)

g) The eight videotapes in your collection which were distributed by Neilson are

displayed. NOTE that the distributor’s name of “Lord of the Bugs” is spelled with a “e”

while all the others are spelled with an “o.” This is very likely a misspelling and

probably should be corrected.

h) Finally, go to the Record menu, choose Show and All Records, and the entire contents

of the database will be displayed.

3. Find a single Videotape

a) How much did our copy of “Lovely Story” cost?

b) Since in this case we are looking for only one specific video tape, we might as well be

in Form View, switch to Form View

c) On the Edit menu, choose Find

d) In the Find What text box, type as much of the title as you feel is necessary to uniquely

identify it. Is “Lovely” enough?

e) In the Match box, click on the Next Record radio button (or simply notice that it is the

default choice and is already chosen so you can simply leave it alone)

f) Click on the OK button (or press ENTER)

g) The record for Lovely Story appears and we see that the price was $20.95

h) Re-display the entire contents of the database. (Show/All Records)

M. Sorting the Records

1. Suppose we want to sort the contents of the database into alphabetical order by title

a) Go to List View so you can see the results of your sorting.

b) On the Records menu, choose Sort Records

Works Suite 2001 Database

Page I-6

Prepared for: Ewing SeniorNet Computer Literacy Center

Prepared by: J. Joel May

2/2002

c) In the Sort By drop-down list box, type “Title” (no quotes), the name of the field on

which we want to sort (or click on the arrow to the right of the box and click on Title to

choose it from the list of field names displayed).

d) In this case we want an Ascending sort (lowest to highest or, in our example, first

letter to last letter [“A” to “Z”]), so be sure that the Ascending radio button is chosen.

e) Notice that we could, if we wished, sort on two additional fields at the same time. For

example, we could sort first by Genre, then by Distributor, and then alphabetize the

videos within each category. However, we’ll keep it simple for the moment.

f) Click on the OK button (or press ENTER)

g) The result is a list of your video tapes sorted alphabetically by title.

2. Now let’s sort our video tape collection from the newest (most recent) to the oldest.

a) On the Records menu, choose Sort Records

b) In the Sort By drop-down list box, choose (or type) “Year” (no quotes)

c) Since we want to sort from newest to oldest, we need to do a Descending sort. Click

on the Descending radio button

d) Clock on the OK button (or press ENTER)

e) The result is a list of your video tapes sorted by Year from newest to oldest. Note

also that, within each year, they are still sorted alphabetically. This is the result of the

previous sorting process followed by the current one. There is a lesson here!! What

is it?

N. Customize the Toolbar

1. Let’s put a Show All Records icon in the toolbar

a) Open Tools menu, choose Customize Toolbar

b) Under Categories, highlight Record

c) In the Toolbar Buttons box, click on each icon until you find one that has Display All

Records as its description

d) Drag that icon to the Toolbar and drop it.

e) Now with one click, you can Show All Records

O. Saving the File and Ending the Session

1. First, let’s be sure to save the changes we’ve made to the database

a) From the File menu, choose Save As. (Do you know why we choose Save As rather

than just Save?

(1) Using Save As allows us to save whatever changes we have made into a NEW file

with a name we will assign rather than saving it with its previous name and thus

totally destroying the original version of the file

b) In the Save As dialog box in the File Name text box, type a new name for the file (for

example VIDEOS2.WDB) and be sure that the directory selected in the Save In drop

down list box is correct. In this case, since we are going to save this to our floppy

disk, the choice in the Save In box should be 3 ½ Floppy (A:)

c) When everything is correct, click on the OK Button (or press ENTER). The file is

saved with a new name.

d) Now you can safely end your Works database session.

Works Suite 2001 Database

Page I-7

Prepared for: Ewing SeniorNet Computer Literacy Center

Prepared by: J. Joel May

2/2002

Works Suite 2001 Database

Page I-8

Prepared for: Ewing SeniorNet Computer Literacy Center

Prepared by: J. Joel May

2/2002

II. Managing Records

A. What You’ll learn

1. How to add new records to a database

2. How to delete unwanted or out-of-date records

3. How to change information in a record

B. Add a New Record to the Database

1. Open the original database

a) Click on History

b) The document we want to use is now listed here, so click VIDEOS1.WDB

c) NOTE that Works will remember files previously opened, including their locations, and

will present them on this list.

d) The VIDEOS1.WDB file is located on the 3 ½ Floppy (A:) drive

2. Add a New Record to the database in Form View

a) If you are not already in Form View, change to it. (Do you remember how? If not,

review Lesson #1, Sections I and K)

b) We will add a new record to the end of the database so move there. (Do you

remember how? If not, review Lesson #1, Section G)

c) Your screen should now display a blank record (Record #49 - check this out on the

Status Bar) and your cursor (the black area) should be in the Field Value area of the

Stock # field. If it is not, move it there (Do you remember how? If not, review Lesson

#1, Section H)

d) Type “439120” (no quotes” as the stock number, then press TAB to move to the next

field. NOTE: It may seem natural to press ENTER here. DO NOT DO THAT!!

Always use the TAB key to move from Field Value to Field Value.

e) Type “Father of the Groom” (no quotes) as the Title of the video and press TAB

f) Type “Sanda” (no quotes) as the start, press TAB; then type “Borgas” as the

distributor, press TAB; then “1994” as the Year’ “Horror” as the Genre; “2” as the On-

Hand quantity; and 16.95 as the price, always pressing TAB between each entry.

g) Note that when you entered the price (16.95), Works automatically supplied a dollar

sign and justified the number to the right of the field value entry area. This is because

the field is already formatted as CURRENCY for you. We’ll learn how to do this in a

later lesson.

h) When you press TAB a final time (after entering the price), you will move to the first

blank field (Stock #) of the next blank record (Record #50). Check your record

number on the Status Bar at the bottom of your screen. We won’t add any more

records at this time.

3. Add a New Record to the database in List View

a) You can also add records to the database in List View. Go to that view now (If you

can’t remember how, check Lesson #1, Section I)

b) Use the CTRL-End key or the Down-Arrow key to look at the record you just added. It

should be Record #49 in the database.

c) Whereas in Form View you MUST add records to the end of a database, in List View

you can add them anywhere you like

Works Suite 2001 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page II-1

Prepared by: J. Joel May

2/2002

d) Position your cursor in the Stock # Field of Record #11, the video with the title “Night of

Mischief” (If you can’t remember how to move the cursor in List View, check Lesson

#1, Section J)

e) On the Record menu, choose Insert Record. A blank row (a new blank record)

appears allowing you to add (insert) your new record at the chosen location

f) Using the TAB key to move from field value to field value (REMEMBER: DON’T USE

ENTER), enter the following information:

(1) Stock #:

432645

(2) Title:

The Pussycat King

(3) Star:

Curtis

(4) Distributor:

United

(5) Year:

1995

(6) Genre:

Family

(7) On Hand:

(8) Price:

19.95

g) You have now created a new Record #11 and, if you press CTRL-End, you’ll see that

there are now 50 records in the database.

C. Deleting a Record from the Database

1. We have in our collection a 1959 video called “They Came to Silverado” which we have

watched literally hundreds of times. It stars the Duke, a great favorite of our 11-year-old

grandson. We have decided to give it to him for his birthday. Wrapping and mailing it

present no problems, but how do we get it our of out database.

2. Find the appropriate Record

a) Since this is easier to see in List View, change to that view first

b) From the Edit menu, choose Find

c) In the Find What dialog box, type “Silverado” (or “Duke,” or any text that is unique to

the item you want to find). Choose Next Record. NOTE: If you choose to use “Duke”

as the search text, you’ll find that you may not get “They Came to Silverado” on the

first Find command – you may get another movie in which he starred instead. Simply

repeat the process until you get the record you want.

3. Delete the Record

a) When your cursor is located anywhere in the appropriate record, from the Record

menu, choose Delete Record.

b) The selected record (the video having been given to your grandson) is removed from

the database, never to be seen again.

4. Recovering Deleted Records

a) But, suppose that just after performing the delete record function, you pick up the

current issue of Video Connoisseur Magazine and read that the 1959 print of “They

Came to Silverado” is a collectors’ item worth over $1,100.

b) Wisely (or selfishly, if you wish), you decide NOT to give it to your grandson, but

instead to hold on to it as it further increases in value. Now the problem is getting it

back into your database.

c) Fortunately, the solution is simple. From the Edit menu, choose Undo Delete Record

and, magically, the record is reinstated

d) NOTE: The Undo Command must be used IMMEDIATELY in order for it to work. If

you have done anything else to your database in the meantime, it WILL NOT WORK.

So if you’re going to change your mind, do it quickly

Works Suite 2001 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page II-2

Prepared by: J. Joel May

2/2002

D. Edit the Contents of an Existing Field of a Record

1. Record #10, “The Little Match Boy,” contains two mistakes: the word “Family” in the Genre

field is misspelled, and the price should be $19.95. Let’s fix these mistakes

a) Move your cursor to the Genre field of Record #10. Note on the Status Line you are

given the reminder “F2 to Edit.” Press F2 to enter the Edit Mode.

b) Your full attention should now be directed to the Entry Bar (the fourth from the top of

your screen). It is here that ALL changes and corrections to your database are made.

Watching the field value entry on the record screen will simply confuse you.

c) To correct any mistakes (or change any text for any reason) you will proceed just as if

you were using the Works Word Processor. In this case:

(1) Use the Backspace key to erase the text you don’t want (“oy”) and then type in the

“y” to make the spelling correct. Press ENTER to verify this and to leave Edit

Mode

d) Use the TAB key (or the arrow keys) to move to the Price field and again press F2 to

enter Edit Mode

e) Use Backspace to get rid of the (incorrect) 21.95 price and type in the correct price

(19.95). Then press ENTER to verify that the changes have been made correctly and

to return to Edit Mode.

f) You can modify the contents of any field at any time using this technique.

2. You have four copies of a video called “Murmur of the Heart” and plan to give one to your

next door neighbor. Let’s adjust the database accordingly

a) First we must find the record with which we want to work. Use the methods we

practiced in Lesson #1 Section L to find a record containing “murmur.” (Remember

from last time that typing any part of the text we want will find it. Here you should also

note that capital letters are not important in the Find command.

b) Be sure the Next Record radio button is chosen and click OK (or press ENTER).

Record #37, Murmur of the Heart is displayed

c) Use the TAB key or the arrow keys to move the cursor to the On Hand field, press F2

to enter Edit Mode, change the 4 to a 3 (to account for the one copy we are giving

away), and press ENTER to verify the changes and leave Edit Mode.

3. You have learned how to keep you database current. This procedure will also work for

updating addresses or telephone numbers in an address database, making a note of the

most recent date you used a particular recipe in a recipe database, or any other kind of

update.

E. Changing the Contents of a Field in Multiple Records

1. Sometimes we want to change the contents of several records at once (for example

when the 732 area code was created in New Jersey)

2. We can handle this with the Replace function

3. Two of the distributors of video tape in our database have merged. One of them, MOM,

has become part of another, Universe. So, to keep our database current, we have to

change all the entries for MOM to Universe.

4. To do this:

a) Go to List View (if you are not there already) and click on the Distributor field name

(the gray box at the top of the column). This will highlight all the values in the field

b) From the Edit menu, choose Replace. The Replace dialog box appears

c) In the Find What text box, type “mom” (no quotes, and capitals are not important – as

we learned earlier)

Works Suite 2001 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page II-3

Prepared by: J. Joel May

2/2002

d) In the Replace With text box, type “Universe” (no quote, but capitals are important if

you want them to appear in the new entries in your database)

e) Click on the Replace All button. Works replaces all occurrences of MOM in the

Distributor field with Universe.

f) NOTE: Sometimes the Replace or Replace All command will do what appear to be

strange things, but which are really quite logical, just unexpected. For example, if you

replace “is” with “are,” you will not only get what you expect, but in addition “island” will

become “areland,” “this” will become “thare,” and “history” will become “harestory.”

(1) If you think anything like this might happen, use the Find Next and Replace buttons

to make the replacements one at a time rather than the Replace All button

(2) If it happens to you and you want to correct it, Open the Edit menu and choose

Undo before doing anything else.

F. Adding a Field to Your Records

1. Ordinarily you should try to plan your database carefully enough so that all the field you will

end up needing are included when you first create it. However, needs can change and

Works gives you a way to add fields to an existing database.

2. There is a potential problem, however. When you add a field to the database, it is added

in every record in the database and, obviously, needs to be filled in with real data in order

to be useful. For example, suppose you are using an address database and want to add

a field for fax numbers. In order to make this a useful addition to your database you must

go back and enter a fax number for each person in the database who has one.

3. For this exercise we will add a field to record the Total Retail Value of each video in our

collection. This is obviously equal to the price multiplied by the number of copies of the

video that we own. Hence, in this section we will learn two new things: (1) how to add an

additional field to a database and (2) how to use a computed field (one that is the

calculated result of combining the entries already present in other field in the record)

4. Adding a field is most easily done in Form Design View, so choose that (this is a view