Here's part of "The Secret Guide to Computers," copyright by Russ Walter, 29th edition. For newer info, read the 32nd edition at www.SecretFun.com.

Databases

A database program is a program that manipulates lists of facts. It can store info about your friends & enemies, customers & suppliers, employees & stockholders, students & teachers, hobbies & libraries. It puts all that data about your life and business onto a disk, which acts as an electronic filing cabinet. Then it lets you retrieve the data easily. It can generate mailing lists, phone directories, sales reports, and any other analyses you wish.

It’s called a database program or database management system (DBMS) or information retrieval system. The terms are synonymous.

 

Database jargon

In an old-fashioned office that lacks a computer, you’ll see a filing cabinet containing several drawers:

One drawer’s labeled CUSTOMERS; another drawer’s labeled EMPLOYEES; another drawer’s labeled SUPPLIERS. Each drawer contains alphabetized index cards.

For example, the drawer labeled CUSTOMERS contains a card about each customer; the first card might be labeled “ADAMS, JOAN”; the last card might be labeled “ZABRONSKI, JILL”. The first card contains all known information about Joan Adams: it contains her name, address, phone number, everything she bought, how much she paid, how much she still owes, and other personal information about her. That card is called her record. Each item of information on that card is called a field.

If the card is a pre-printed form, it allows a certain amount of space for each item: for example, it might allow only 30 characters for the person’s name. The number of characters allowed for a field is called the field’s width. In that example, the width of the NAME field is 30 characters.

Each drawer is called a file. For example, the drawer that contains information about customers is called the customer file; another drawer is the employee file; another drawer is the supplier file.

The entire filing cabinet — which contains all the information about your company — is called the database.

A sample file

Here’s a file about amazing students in the School of Life:

Last name: Smith                        First name: Suzy

Age: 4              Class: 12

Comments: Though just 4 years old, she finished high school because she's fast.

 

Last name: Bell                         First name: Clara

Age: 21             Class: 10

Comments: The class clown, she never graduated but had fun trying. Super-slow!

 

Last name: Smith                        First name: Buffalo Bob

Age: 7              Class: 2

Comments: Boringly normal, he's jealous of his sister Suzy. Always says "Howdy!"

 

Last name: Kosinski                     First name: Stanislaw

Age: 16             Class: 11

Comments: He dislikes Polish jokes.

 

Last name: Ketchopf                     First name: Heinz

Age: 57             Class: 1

Comments: His pour grades make him the slowest Ketchopf in the West.

 

Last name: Nixon                        First name: Tricky Dick

Age: 78             Class: 13

Comments: The unlucky President, he disappointed our country.

 

Last name: Walter                       First name: Russy-poo

Age: 53             Class: 0

Comments: This guy has no class.

That file consists of seven records: Suzy Smith’s record, Clara Bell’s record, Buffalo Bob Smith’s record, Stanislaw Kosinski’s record, Heinz Ketchopf’s record, Tricky Dick Nixon’s record, and Russy-poo Walter’s record.

Each record consists of five fields: last name, first name, age, class, and comments. The age and class fields are narrow; the comments field is very wide.

Database programs versus word processing

Like a word processing program, a database program lets you type info, put it onto a disk, edit it, and copy it onto paper.

In a word processing system, the info’s called a document, consisting of paragraphs which in turn consist of sentences. In a database system, the info’s called a file (instead of a document); it consists of records, which in turn consist of fields.

Since a database program resembles a word processor, a word processor can act as a crummy database program. A good database program offers these extras, which the typical word processor lacks:

A good database program can alphabetize, put info into numerical order, and check for criteria. For example, you can tell it to check which customers are women under 18 who have light red hair and live in a red-light district, make it print their names and addresses on mailing labels in ZIP-code order, and make it print a phone book containing their names and numbers. Database programs are very potent and can be nasty tools for invading people’s privacy!

 

Famous programs

Many database programs have been invented.

PFS

Most database programs are hard to use. In 1980, John Page invented the first easy database program. He called it the Personal Filing System (PFS).

It ran on Apple 2 computers. He developed it while sitting in his garage.

He showed the program to two friends: Fred Gibbons and Janelle Bedke. The three of them tried to find a company willing to market his program, but no company was interested, so they decided to market the program themselves by forming a company, Software Publishing Corporation.

The program became very popular. Software Publishing Corporation became a multi-million-dollar corporation. It developed improved versions of PFS for the Apple 2 family, Radio Shack models 3 & 4, Commodore 64, Mac, and IBM PC. Now the fanciest version of PFS is Professional File, which runs on the IBM PC.


The company also invented a word processor, whose IBM version is called Professional Write. It works well with Professional File. When you buy Professional Write, you get Professional File free!

You can write a memo by using Professional Write and build a mailing list by using Professional File. Then use those programs together to print personalized copies of your memo to everybody on your mailing list.

Software Publishing Corporation has invented an even easier program, called PFS First Choice. It includes the easiest parts of both Professional File and Professional Write. It also includes spreadsheets, graphics, and telecommunication.

In 1988, John Page and Janelle Bedke got bored and quit the company. Fred Gibbons and the rest of his staff hung on but sold PFS First Choice to Spinnaker, which later became part of Softkey, which later became part of The Learning Company, which later became part of the Mattel toy company.

Those products (PFS, Professional Write, Professional File, and PFS First Choice) are no longer marketed. Exciting new competitors have taken their place. Let’s look at those competitors.…

Q&A

Inspired by the PFS series, a new company called Symantec developed a similar program, called Q&A.

At first glance, Q&A seems to just imitate the PFS series, since Q&A uses almost the same commands and keystrokes as the first IBM version of PFS. But Q&A understands many extra commands, making Q&A much more powerful than the PFS series. Q&A handles just two topics — databases and word processing — but very well! It’s fairly easy (almost as easy as the PFS series) and powerful enough to handle the computing needs of most businesses. Q&A is the database program I use to run my own business.

The best versions of Q&A is called Q&A version 5 for DOS. You can get it for $199 (plus $15 shipping) from Professional Computer Technology Associates in Pennsylvania at 215-598-8440.

Reflex

Reflex was the first database program to let you view your data in five ways: it lets you see a form view (a filled-in form showing a record), a list view (a large spreadsheet showing the entire file), a graph view (a graph of all the data), a report view (a report on the entire file, with subtotals), and a crosstab view (a table of totals for statisticians).

Reflex can show you many views simultaneously, by dividing your screen into windows. As you edit the view in one window, the views in other windows change simultaneously. For example, if one window shows numbers and another window shows a graph, the graph changes automatically as you edit the numbers.

Reflex is partly a database program and partly a spreadsheet. Many of Reflex’s features were copied by Microsoft’s spreadsheet, Excel.

Reflex is published by Borland; but Borland given up trying to market it anymore, because the competition from Q&A and other database programs is too fierce.

Relational databases

Reflex is a simple flat-file system, which means it manipulates just one file at a time. Q&A goes a step further: while you’re editing a file, Q&A lets you insert data from a second file.

Software that goes even further than Q&A and lets you edit two files simultaneously is called a relational database program (or relational database management system or relational DBMS).


The most popular relational database programs for DOS are DBASE, FOXPRO, and Paradox. You can customize them to meet any need, because they include complete programming languages.

Another relational database program for DOS is Alpha 4. It lets you accomplish some tasks more easily than DBASE, FOXPRO, and Paradox but lacks a programming language.

Windows wars

Programmers have been trying to invent database programs for Windows. Going beyond DOS programs such as Q&A, Windows database programs let the screen display pretty fonts and photographs.

The first popular Windows database program was Approach, which is now published by the Lotus division of IBM.

Borland has invented Windows versions of DBASE and Paradox and a new Windows database program called Delphi. Microsoft has invented a Windows version of FOXPRO and a new Windows database program called Microsoft Access. Alpha Software has invented Alpha 5, which resembles Alpha 4 but handles Windows and is also programmable.

The most popular database program for the Mac is FileMaker Pro. It’s as easy as Q&A! It’s published by the FileMaker company, which is owned by Apple but has had the good sense to also invent a Windows version of FileMaker Pro.

Microsoft Works includes a database program that’s very limited. For example, it can’t handle big mailing lists, since it’s limited to 32,000 records.

Symantec invented a Windows version of Q&A, but Q&A’s Windows version is hated by everybody.

It’s worse than the DOS version and worse than all other major Windows databases. If you use Q&A, stick with Q&A’s DOS version.

Though Q&A for Windows is terrible, the other Windows database programs are fine. Here’s the hierarchy:

The simplest Windows database program is the database part of Microsoft Works, but it comes with no instruction manual and you’ll outgrow the program’s abilities.

The next step up is FileMaker Pro. It’s wonderful! People who buy it love it. It’s more powerful than the Microsoft Works database — it performs more tricks and handles a wider variety of problems. It comes with a decent instruction manual.

The next step up is Approach. By a “step up”, I mean it’s more powerful than the Microsoft Works database and Filemaker Pro — it performs more tricks and handles a wider variety of problems — but it’s also more complex (harder to learn & use). Unlike Microsoft Works and Filemaker Pro, it’s relational. But it’s still not programmable.

The next step up (in power and complexity) is Alpha 5. It’s relational and also programmable! But its programming language is small.

The next step up is Microsoft Access. Its programming language is bigger.

The next step up is the triumvirate: the Windows versions of DBASE, FOXPRO, and Paradox. They’re powerful, fancy, and more than most folks can understand. If you buy one of them, you’ll probably admire the big box it comes in, put it on the shelf, and invite friends to visit you and admire your big box, but you’ll never figure out how to use it.

What to buy

To make your life easy, get one of the easy database programs: Q&A for DOS, Microsoft Works, or FileMaker Pro. Go beyond them just if your database needs are too complex for them to handle.

Even if your database needs are complex, begin by practicing with an easy database program first, so you master database fundamentals easily and quickly without getting distracted by needlessly complex details.

Complex database programs are like sneakers with untied shoelaces: though their overall design can let you perform amazing feats, you’ll probably trip, get bloodied, and have to call in a computer “first-aid squad”, which is a team of high-priced computer consultants.

To avoid the need for consultants, use Microsoft Works, FileMaker Pro, or Q&A.

 

FileMaker Pro

Many database programs have been invented. In general, the best one to use is FileMaker Pro. It’s published by the FileMaker company, which is owned by Apple. It’s the most popular database program for Macintosh computers, and a Windows version is also available.

Like Q&A, it’s easy to learn how to use. It has two main advantages over Q&A: it can handle databases that are more advanced, and its Windows version is excellent. (Q&A’s Windows version is terrible.) FileMaker Pro has been nicknamed “Q&A for Windows, done right.” It’s also been nicknamed “Microsoft Access, made reasonable” (because Microsoft Access is unreasonably hard).

The newest version of FileMaker Pro is FileMaker Pro 7. It lists for $299. Pay just $149 if you’re upgrading from an earlier version. You can download a 30-day trial version free from www.filemaker.com.

This chapter explains how to use FileMaker Pro 5. (FileMaker Pro 6 and FileMaker Pro 7 are similar.) I’ll explain the Mac version and also the Windows version.

Copy FileMaker to the hard disk

FileMaker Pro 5 comes on a CD-ROM disk, which you must copy to your computer’s hard disk.

For the Mac version, do this:

Turn on the computer without any floppy or CD-ROM disks in the drives.

Put the FileMaker Pro 5 CD-ROM disk into the CD-ROM drive. Double-click “Start Here”. Press the RETURN key twice.

The computer says “Personalization”. Type your name, press the TAB key, type your company’s name (if any), press the TAB key. Type your 17-digit Installation Code Number (which is on a white sticker; that sticker came on a big sheet of paper with the CD-ROM disk and should be transferred to the back of the CD-ROM disk’s white envelope). Press the RETURN key.

The computer says “Installation was successful”. Choose Eject from the Special menu. Remove the CD-ROM disk from the drive, then close the drive’s door.

For the Windows version, do this instead:

Turn on the computer without any floppy or CD-ROM disks in the drives, so the computer runs Windows 95 (or 98) and the computer’s bottom left corner says Start.

Put the FileMaker Pro 5 CD-ROM disk into the CD-ROM drive. In the phrase “è Install FileMaker Pro 5”, click the right-arrow that begins the phrase.

The computer says “FileMaker Pro 5 Installation”. Press ENTER four times.

The computer says “Personalization”. While holding down the Ctrl key, tap the DELETE key (so you erase the word “User”). Type your name, press the TAB key, type your company’s name (if any), press the TAB key. Type your 17-digit Installation Code Number (which is on a white sticker; that sticker came on a big sheet of paper with the CD-ROM disk and should be transferred to the back of the CD-ROM disk’s white envelope). Press ENTER twice.

The computer says “Installation Completed”. Press ENTER twice.

Launch FileMaker Pro

For the Mac version, do this:

Double-click “Macintosh HD” then “FileMaker Pro 5 Folder” then “FileMaker Pro”.

For the Windows version, do this instead:

Click “Start” then “Programs” then “FileMaker Pro 5” then “FileMaker Pro”.


Mac versus Windows

Now I’m going to explain how to use the Mac version. The Windows version works the same way, except for minor headaches (which I’ll explain later) and these keyboard differences:

The Mac has a clearly marked RETURN key.

In Windows, the RETURN key says “Enter” on it.

The Mac has a clearly marked CONTROL key.

In Windows, the CONTROL key says “Ctrl” on it.

The Mac has a COMMAND key (which shows a squiggly cloverleaf and an Apple). Windows doesn’t have COMMAND key, so use the Windows “Ctrl” key instead.

Create a database

Click “Create a new empty file” then “OK”.

For our first experiment, let’s create this database:

 

 

                       ┌───────────────────────────────────┐

           First name  │Sue                                │

                       └───────────────────────────────────┘

                       ┌───────────────────────────────────┐

            Last name  │Smith                              │

                       └───────────────────────────────────┘

                       ┌───────────────────────────────────┐

             Comments  │wiggles her toes                   │

                       └───────────────────────────────────┘

 

 

 

 

                       ┌───────────────────────────────────┐

           First name  │Sam                                │

                       └───────────────────────────────────┘

                       ┌───────────────────────────────────┐

            Last name  │Smith                              │

                       └───────────────────────────────────┘

                       ┌───────────────────────────────────┐

             Comments  │picks his nose                     │

                       └───────────────────────────────────┘

 

 

 

 

                       ┌───────────────────────────────────┐

           First name  │Tina                               │

                       └───────────────────────────────────┘

                       ┌───────────────────────────────────┐

            Last name  │Ash                                │

                       └───────────────────────────────────┘

                       ┌───────────────────────────────────┐

             Comments  │the class clown                    │

                       └───────────────────────────────────┘

 

 

 

 

                       ┌───────────────────────────────────┐

           First name  │Tina                               │

                       └───────────────────────────────────┘

                       ┌───────────────────────────────────┐

            Last name  │Smith                              │

                       └───────────────────────────────────┘

                       ┌───────────────────────────────────┐

             Comments  │incredible                         │

                       └───────────────────────────────────┘

 

 

Here’s how to create it.…

Invent a name for your database (such as “Friends”). Type the name. At the end of the name, press the RETURN key (which in Windows is marked “Enter”).

If the computer says the file “already exists”, do this procedure:

Press RETURN. Type a different name instead (such as “Friends2” or “Buddies” or “Pals” or “Enemies”) and then press RETURN. If the computer says the file “already exists” again, do this whole procedure again.


Define fields The computer says “Define Fields”.

We’re trying to create a database that has three fields, called “First name”, “Last name”, and “Comments”. To accomplish that, type the words “First name”, then press RETURN, then type the words “Last name”, then press RETURN again, then type the word “Comments”, then press RETURN again.

Above your typing, you see this list of fields you created:

┌────────────────┬───────────┬─────────────────────────────┐

│Field name      │Type       │Options                      │

├────────────────┼───────────┼─────────────────────────────┤

│First name      │Text       │                             │

│Last name       │Text       │                             │

│Comments        │Text       │                             │

│                │           │                             │

│                │           │                             │

│                │           │                             │

│                │           │                             │

└────────────────┴───────────┴─────────────────────────────┘

Click “Done”.

Enter data You see this blank form:

                       ┌───────────────────────────────────┐

           First name  │                                   │

                       └───────────────────────────────────┘

                       ┌───────────────────────────────────┐

            Last name  │                                   │

                       └───────────────────────────────────┘

                       ┌───────────────────────────────────┐

             Comments  │                                   │

                       └───────────────────────────────────┘

Into that form, type a person’s record. Here’s how: type the person’s first name (“Sue”), press the TAB key, type the person’s last name (“Smith”), press the TAB key, and type the comment (“wiggles her toes”), so the form looks like this:

                       ┌───────────────────────────────────┐

           First name  │Sue                                │

                       └───────────────────────────────────┘

                       ┌───────────────────────────────────┐

            Last name  │Smith                              │

                       └───────────────────────────────────┘

                       ┌───────────────────────────────────┐

             Comments  │wiggles her toes                   │

                       └───────────────────────────────────┘

That’s Sue Smith’s record! When you’ve finished typing it, and want to start typing the next person’s record, do this: tap the N key while holding down the COMMAND key (which on a Mac shows a squiggly cloverleaf and Apple, and in Windows says “Ctrl”.) That makes the computer start a new record, so the computer shows you a new blank form, where you can fill in the details for the next person (Sam Smith). The screen shows just one record at a time: while you’re typing Sam Smith’s record, you don’t see Sue Smith’s record.

When you’ve finished typing Sam Smith’s record, press COMMAND with N again, so you see another blank form, so you can type the next person’s record (Tina Ash’s). Then press COMMAND with N again, then type Tina Smith’s record.

While you’re typing those records, the computer automatically copies them to the hard disk. (You don’t have to click any “Save button”.)

When you’ve finished typing all the records, congratulations! You’ve created a database!

View your data

To view the data you typed, you can use several tricks.

CONTROL key To go back and view the previous record, press CONTROL with up-arrow (which means do this: while pressing the CONTROL key, tap the up-arrow key). To view the next record, press CONTROL with down-arrow. (In Windows, the CONTROL key says “Ctrl” on it.)

So in Windows, while pressing the Ctrl key you can do this:

tap the up-arrow key to view the previous record

tap the down-arrow key to view the next record

tap the N key to create a new record

Rolodex At the screen’s right edge, under the word “Layout”, you see a picture of a Rolodex (which is a device that displays business cards). The Rolodex shows two business cards (except that the words on the cards are too small to read).

To go back and view the previous record, click the top card.

To view the next record, click the bottom card.

Below the Rolodex, you see two numbers:

The first number says which record you’re viewing. (For example, while you’re viewing record #3, which is Tina Ash’s record, that number is 3.)

The bottom number says how many records there are in the whole database.

Try this experiment: look at the first number (which is the number of the record you’re viewing). If you change that number to a different number (such as 2), the computer will hop to record 2. Here’s how to change the number:

Method 1: click the number, then type the number you want instead (and press RETURN).

Method 2: drag the slider (which is above the number).

TAB key While you’re viewing a record, you can edit the data by retyping it.

To move to the next field, press the TAB key.

To move back to the previous field, press SHIFT with TAB.

Delete While you’re typing, here’s how to delete:

To delete a character, click after it (on a Mac) or before it (in Windows),

then press the DELETE key.

To delete an entire word, double-click it then press the DELETE key.

To delete an entire line, triple-click it then press the DELETE key.

To eliminate (delete) the entire record, press COMMAND with E, then press D.

Three views If you click “View” (which is near the screen’s top), you see these choices:

View as Form

View as List

View as Table

The normal choice is “View as Form”, which shows you just one person’s record at a time.

If you click “View as List” instead, you see the first person’s record, and down from it you see the second person’s record, and down from it you see the third person’s record, etc., so you see many records on the screen at once. If there are too many records to fit on your screen, you can see the other records by using the window’s scroll arrows (or the PAGE DOWN and PAGE UP keys or your mouse’s wheel).

If you click “View as Table” instead, you see the database as a table looking like this:

┌────────────────┬────────────────┬────────────────┐

│   First name   │   Last name    │    Comments    │

├────────────────┼────────────────┼────────────────┤

│Sue             │Smith           │wiggles her toes│

├────────────────┼────────────────┼────────────────┤

│Sam             │Smith           │picks his nose  │

├────────────────┼────────────────┼────────────────┤

│Tina            │Ash             │the class clown │

├────────────────┼────────────────┼────────────────┤

│Tina            │Smith           │incredible      │

└────────────────┴────────────────┴────────────────┘

At the top of each column, you see the column’s heading (field name). To the right of the column’s heading, you see a vertical gridline separating that heading from the next column’s heading. To widen the column (so you can see longer words), drag that gridline toward the right (by using your mouse). To narrow the column (so the column consumes less space and you can fit more columns onto the screen), drag that gridline back toward the left. Changing the column’s width does not change the data; it changes just the table’s view of it; the other views show that the data is unchanged.

Mouse wheel If the computer is trying to display more records than can fit on the screen, and your mouse has a wheel between its buttons, do this:

Rotate the wheel away from you to view earlier records.

Rotate the wheel toward you to view later records.

Find

Here’s how to find everybody whose last name is Smith:

Say “find” by pressing COMMAND with F.

Click in the “Last name” box, type “smith”, and press RETURN.

That makes the computer show you the records of just the people whose last name is Smith.

You can view them however you wish: from the View menu, choose “View as Form” or “View as List” or “View as Table”.

I recommend choosing “View as Table”, since it shows you all relevant records at once. (If you choose “View as Form”, you see just one Smith at a time; to see the next Smith, press CONTROL with down-arrow.)

You see the Smiths but not Tina Ash, since she’s not a Smith. You see a filtered database, where the Smiths are still visible but Tina Ash has been filtered out and is invisible.

Here’s how to find everybody whose first name is Tina:

Say “find” by pressing COMMAND with F.

Click in the “First name” box, type “tina”, and press RETURN.

That makes the computer show you the records of just the people whose first name is Tina. You see Tina Ash’s record and Tina Smith’s record, but not Sue Smith, not Sam Smith.

When in doubt about which records to show you, the computer is generous and shows you many:

If you tell the computer to find everybody whose last name is “smith”, the computer will show you every “Smith” and also everybody whose last name begins with “Smith”, such as “Smithson” and “Smithers” and “Smithers Jr., MD”. If you tell the computer to find everybody whose last name is “sm”, the computer will show you everybody whose last name begins with “sm”, such as “Smith” and “Smythe” and “Smyers” and “smells so bad I forgot his last name”. If you tell the computer to find everybody whose comment is “clown”, the computer will show you everybody whose comment includes the word “clown”, such as Tina Ash (whose comment is “the class clown”) and anybody whose comment mentions “clown” or “clowns” or “clowning” or “clowned”. If you tell the computer to find everybody whose first name is “ti”, the computer will show you everybody whose first name is “Tina” or “Tim” or “Timothy” or “the amazing Timothy” or “His Esteemed Majesty Timothy”.

Here’s the rule:

If you tell the computer to search in a field for a word, the computer will show every record where that field contains the word (or contains a longer word beginning with the same letters).

If you tell the computer to find certain records, the computer will filter the database and keep showing you just those records, until you tell the computer to find different records instead, or until you say “jumbo” (by pressing COMMAND with J), which makes the computer show you the entire jumbo database again, unfiltered.

Two fields at once (how to say “and”) You can search two fields at once. For example, to search for Tina Smith’s record, do this:

Say “find” by pressing COMMAND with F.

Type “tina” (in the “First name” box), press TAB (to go to the “Last name” box), type “smith”, and press RETURN.

How to say “or” Here’s how to search for people whose first name is “Sue” or “Tina”:

Say “find” by pressing COMMAND with F.

Type “sue” (in the “First name” box).

Say “new search” by pressing COMMAND with N.

Type “tina” (in the “First name” box) and press RETURN.

That makes the computer show the records of all people named “Sue” or “Tina” (not “Sam”).

How to say “not” Here’s how to search for people whose first name is not “Sam”:

Say “find” by pressing COMMAND with F.

Type “sam” (in the “First name” box).

Say “not” (by clicking the Omit box, which is at the screen’s left edge).

Press RETURN.

That makes the computer omit Sam, so the computer will show the records of all people not named “Sam”. (You’ll see the records for “Sue” and “Tina”.) Sam is still in the database, but he’s not shown (until you do a different “find” instead).

Alphabetize

Here’s how to alphabetize (sort) the records.

First, get a good view of how the records are currently organized (by clicking “View” then “View as Table”).

Decide which records you want to include. (If you want to include just some of the records, filter them, by pressing COMMAND with F and then saying which records you want. If you want to include all the records, making sure they’re unfiltered, by pressing COMMAND with J.)

Now you see a table of the records you want to alphabetize.

Say “Sort” by pressing COMMAND with S.

You see two big white boxes.

Make sure the right-hand “big white box” is empty. (If it’s not, click “Clear All”.)

The left “big white box” contains a list of field names.

To alphabetize the records, let’s make the computer look at each person’s last name and put the last names in alphabetical order. If two people have the same last name, let’s make the computer look at their first names and put their first names in alphabetical order. So here’s the rule:

Sort by last name. If two people have the same last name, sort them by first name.

Here’s how to say that:

Click “Last name” then “Move”.

Click “First name” then “Move”.

Press RETURN. The computer alphabetizes the table by Last name, then First name, so the table becomes this:

┌────────────────┬────────────────┬────────────────┐

│   First name   │   Last name    │    Comments    │

├────────────────┼────────────────┼────────────────┤

│Tina            │Ash             │the class clown │

├────────────────┼────────────────┼────────────────┤

│Sam             │Smith           │picks his nose  │

├────────────────┼────────────────┼────────────────┤

│Sue             │Smith           │wiggles her toes│

├────────────────┼────────────────┼────────────────┤

│Tina            │Smith           │incredible      │

└────────────────┴────────────────┴────────────────┘

Those records will remain sorted until you give a different sort command or you unsort them (by pressing COMMAND with S and then clicking “Unsort”). Warning: if you edit those records or add extra records, they won’t be accurately sorted until you give the sort command again.

Print

Here's how to print records onto paper.

Say which records to print:

If you want to include just some of the records, filter them (by pressing COMMAND with F and then saying which records you want). If you want to print all the records, making sure they’re unfiltered (by pressing COMMAND with J).

If you want the records to be sorted, sort them (by pressing COMMAND with S and then saying which fields to sort on).


Say which view you want to print:

Click “View”, then click either “View as List” or “View as Table”.

(If you click “View as Form”, the computer will print a “View as List” instead.)

Say “Print” (by pressing COMMAND with P or clicking the Print button).

For the Mac, proceed as follows:

You see your printer’s window. Click “Records being browsed” — unless you change your mind and want to print something else.

For Windows, proceed as follows instead:

You see the Print window. In it, the first box is labeled “Print”. In that Print box, make sure you see “Records being browsed”. If you see something else, click that box’s down-arrow, then click “Records being browsed” — unless you change your mind and want to print something else.

The typical choices are “Records being browsed”, “Current record”, “Blank record, showing fields”, and “Field definitions”.

Make sure the printer is turned on and contains paper. Press RETURN. Then the printer will print on paper.

Final steps

When you finish using the Friends database, close the Friends window. (In Windows, do that by clicking the Friends window’s X button.) Then you have three choices:

To start creating a different database, click the New button (which is near the screen’s top left corner, below the word “File”) then follow my instructions on page 278 for how to “Create a database”.

To use a database you previously created, click the Open button (which is near the screen’s top left corner and looks like an opening manila file folder). You see a list of databases you created (and some folders, too). (If the list is too long to see it all, scroll to see the rest of it.) Double-click the database you want (such as “Friends”). Then that database will appear on the screen, with the same view and filtering and sorting as when you last used it.

To stop using FileMaker Pro, do this: for a Mac, choose Quit from the File menu; for Windows, click FileMaker Pro’s X button.

Improve the fields

While you’re using a database, try clicking “File” then “Define Fields”. Then you see the Define Fields window, which shows the list of fields again, like this:

┌────────────────┬───────────┬─────────────────────────────┐

│Field name      │Type       │Options                      │

├────────────────┼───────────┼─────────────────────────────┤

│First name      │Text       │                             │

│Last name       │Text       │                             │

│Comments        │Text       │                             │

│                │           │                             │

│                │           │                             │

│                │           │                             │

│                │           │                             │

└────────────────┴───────────┴─────────────────────────────┘

Extra fields To create an extra field, just type the field’s name and press RETURN.

Done I’m going to reveal extra tricks for improving your fields, by using the Define Fields window. Whenever you finish using the Define Fields window, click “Done”.

Numeric fields To create an extra field that contains just numbers (such as a field about “Age” or “Test score” or “Population” or “Number of children” or “Temperature” or “Amount paid” or “Balance due” or “Profit” or “Debt” or “Income” or “Cost” or “Sales” or “Discount percentage”), type the field’s name then click “Number” then press RETURN. That lets the computer do filtering and sorting better.

For example, suppose you create a numeric field about “Test score”. Later, you can find everybody who scored below 60 by doing this:

Say “find” by pressing COMMAND with F.

Click in the “Test score” box, type “<60”, and press RETURN.


You can use these symbols:

Symbol   Meaning

<               less than

<=             less than or equal to

>               greater than

>=             greater than or equal to

You can display the students from lowest score to highest score by doing this:

Say you want to see all students (jumbo), by pressing COMMAND with J.

Say “sort”, by pressing COMMAND with S.

Click “Clear All”.

Click “Test score” then “Ascending order” then “Move”.

Press RETURN.

You can display the students from highest score to lowest score by doing this:

Say you want to see all students (jumbo), by pressing COMMAND with J.

Say “sort”, by pressing COMMAND with S.

Click “Clear All”.

Click “Test score” then “Descending order” then “Move”.

Press RETURN.

If a field is numeric, when you type your data you can include a decimal point and a negative sign.

If you type extra characters (such as commas or dollar signs or units of measure such as “miles”), the computer will include them in your database, but beware: since the computer doesn’t know their meaning, the computer will ignore them while doing finds or sorts. The computer doesn’t know that “2 miles” is more than “3 feet”.

Dates To create an extra field that contains just dates (such as a field about “Date of birth” or “Date the loan began” or “Date due” or “Date processed”), type the field’s name then click “Date” then press RETURN. That lets the computer filter and sort the dates better.

For example, suppose you create a date field about “Date of birth”. Later, you can later find everybody born before 1980 by doing this:

Say “find” by pressing COMMAND with F.

Click in the “Date of birth” box, type “<1/1/1980”, and press RETURN.

You can use these symbols:

Symbol   Meaning

<               before

<=             before or on

>               after

>=             after or on

You can display the people in order of birth by doing this:

Say you want to see all people (jumbo), by pressing COMMAND with J.

Say “sort”, by pressing COMMAND with S.

Click “Clear All”.

Click “Date of birth” then “Ascending order” then “Move”.

Press RETURN.

When typing a date field, type the month’s number, then a slash, then the date number, then a slash, then a four-digit year. For example, December 31, 1980 should be typed as “12/31/1980”. You can type any year from 0001 (which was near Jesus’s birth) to 3000.

If you omit the year (and type just “12/31”), the computer automatically types today’s year for you. If you type a 2-digit year (such as 98 or 01), the computer automatically changes it to a 4-digit year. (To change a 2-digit year to a 4-digit year, the computer usually puts “20” before the year; but if the 2-digit year is 90, 91, 92, 93, 94, 95, 96, 97, 98, or 99, the computer puts “19” before the year instead.) The computer does that automatic typing when you move to the next field or record.

Times To create an extra field that contains just the time of day (such as a field about “Appointment time”) or a time duration (such as “Time to finish race”), type the field’s name then click “Time” then press ENTER. That lets the computer do filter and sort the times better.

For example, suppose you create a time field about “Appointment time”. Later, you can find all appointments before 2PM doing this:

Say “find” by pressing COMMAND with F.

Click in the “Date of birth” box, type “<2PM”, and press RETURN.

You can use these symbols:

Symbol   Meaning

<               before

<=             before or at

>               after

>=             after or at

You can display the people in order of appointment times by doing this:

Say you want to see all people (jumbo), by pressing COMMAND with J.

Say “sort”, by pressing COMMAND with S.

Click “Clear All”.

Click “Appointment time” then “Ascending order” then “Move”.

Press RETURN.

For 9AM, you can type “9AM” or “9:00” or just “9”. For 2PM, you can type “2PM” or “2:00PM” or “14:00” (which is military style) or just “14”. You can include seconds: for 15 seconds after 9AM, type “9:00:15”. If a person ran a marathon race and took “4 hours, 12 minutes, and 7 seconds”, you can express that by typing “4:12:07”.

Rearrange fields To delete a field, click its name (in the Define Fields window) then click “Delete” then click “Delete” again.

To change a field’s type (such as from “Text” to “Number”), click the field’s name then click the type you want then press RETURN. If the computer asks “Proceed anyway?”, click “OK”.

In front of each field’s name, you see a double-headed arrow. To move a field, drag its double-headed arrow up or down.

 

Works Database

Microsoft Works is a program that handles word processing, spreadsheets, and databases.

I explained the word-processing part of Microsoft Works on pages 205-216.

I explained the spreadsheet part of Microsoft Works on pages 258-275.

Here’s how to use the database part of Microsoft Works 4.5&5&6&7.…

Create a database

Turn the computer on, so you see the Start button.

For Works 5&6&7, do this:

Double-click the Microsoft Works icon (which Works 6 might call the Microsoft Works Suite icon). Click Programs (which is near the screen’s top left corner) then Works Database (which is near the screen’s left edge) then Start a blank Database.

For Works 4.5, do this instead:

Double-click the icon that says Shortcut to Microsoft Works. Click Works Tools then the Database button.

If the computer says “First-time Help”, press ENTER.


For our first experiment, let’s create this database:

First name      Last name       Comments

Sue                      Smith                  wiggles

Sam                    Smith                  tickles

Tina                   Ash                     clown

Tina                   Smith                  wow

Here’s how to create it.…

In that database, the top of each column has a field name. The field names are “First name”, “Last name”, and “Comments”. Type them and press ENTER after each one.

So type the words “First name”, then press ENTER, then type the words “Last name”, then press ENTER, then type the word “Comments”, then press ENTER again.

Click “Done”.

You see the List view, which looks like a spreadsheet table and begins like this:

      ┌────────────┬───────────┬──────────┐

      │ First name │ Last name │ Comments │

      ├────────────┼───────────┼──────────┤

   1  │            │           │          │

      ├────────────┼───────────┼──────────┤

   2  │            │           │          │

      ├────────────┼───────────┼──────────┤

   3  │            │           │          │

      ├────────────┼───────────┼──────────┤

   4  │            │           │          │

      ├────────────┼───────────┼──────────┤

   5  │            │           │          │

      ├────────────┼───────────┼──────────┤

   6  │            │           │          │

      └────────────┴───────────┴──────────┘

Type the data that you want in the table, moving from cell to cell by pressing the TAB key. So type “Sue”, press TAB, type “Smith”, press TAB, type “wiggles”, press TAB, type “Sam”, press TAB, etc., until you’ve finally typed the last entry (“wow”), so the spreadsheet table looks like this:

      ┌────────────┬───────────┬──────────┐

      │ First name │ Last name │ Comments │

      ├────────────┼───────────┼──────────┤

   1  │Sue         │Smith      │wiggles   │

      ├────────────┼───────────┼──────────┤

   2  │Sam         │Smith      │tickles   │

      ├────────────┼───────────┼──────────┤

   3  │Tina        │Ash        │clown     │

      ├────────────┼───────────┼──────────┤

   4  │Tina        │Smith      │wow       │

      ├────────────┼───────────┼──────────┤

   5  │            │           │          │

      ├────────────┼───────────┼──────────┤

   6  │            │           │          │

      └────────────┴───────────┴──────────┘

Congratulations! You’ve created a database table.

In a database table, each row of data is called a record; each column of data is called a field. In the database table you created, here are the records and fields:

The first record (row of data) is Sue Smith’s record.

The second record is Sam Smith’s.

The third record is Tina Ash’s.

The fourth record is Tina Smith’s.

So altogether, there are 4 records (plus blanks underneath).

The first field (column) is called “First name”.

The next field is “Last name”.

The next field is “Comments”.

So altogether, there are 3 fields (columns), whose field names (column headings) are “First name”, “Last name”, and “Comments”.

The Works database program can handle 32,000 records, 256 fields. If you want to create a database bigger than that, use a different database program instead.

Edit the table

I’m going to explain how to edit your table’s data. Before you edit, finish what you’ve been typing, by pressing the TAB key. Then edit as follows.…


Click the cell you want to edit, then choose one of these editing methods.…

Method 1 Press the DELETE key. That makes the cell become totally blank.

Method 2 Retype the entire text that you want to put into the cell.

Method 3 At the top of the screen, you see a wide white box, which contains a copy of what’s in the cell you clicked; click in that wide white box, then edit your typing as if you were using a word processing: you can use the left-arrow key, right-arrow key, BACKSPACE key, DELETE key, and mouse. When you finish editing, press the ENTER key.

To make an entire row become blank, click the row’s number then press the DELETE key.

To make an entire row’s data disappear and make the data that was underneath move up to fill the gap, do this: right-click anywhere in the row, then click Delete Record.

Move around To move to different cells in the table, you can use the mouse or keyboard:

To move right, to the next cell,    press the right-arrow key (or TAB key).

To move left, to the previous cell,    press left-arrow key (or SHIFT with TAB).

To move down, to the cell below,  press the down-arrow key (or ENTER key).

To move up, to the cell above,      press the up-arrow key.

To move far right, to last column, press the END key.

To move far left, to first column,  press the HOME key.

To move far right & down, to last filled cell (bottom right), press Ctrl with END.

To move far left & up, to the first cell (top left), press Ctrl with HOME.

Add extra records To add an extra record, just type the extra record in the blank row below the other records.

Undo If you make a mistake while using the Works database, you can typically undo the mistake by pressing Ctrl with Z. If you change your mind and wish you hadn’t pressed Ctrl with Z, you can “undo the undo” by pressing Ctrl with Z again.

Make a column look wider Here’s how to make a column look wider, so it can show longer words:

Look at the column’s heading (the field name, such as “First name”), and look at the vertical gridline that’s to the right of that column heading. Drag that  gridline to the right (by using your mouse).

To make the column become narrower again, drag the gridline back toward the left. To make the column look just wide enough to hold everything in it, double-click the column’s heading.

Fiddling with the gridlines affects just what you see on the screen, not the data itself. If you make a column narrow by dragging its gridline, you’ll see just part of the column’s contents on the screen, but the contents are still stored, invisibly: afterward, if you drag the gridline to make the column look wider again, you’ll see the full contents again.

Filter

Here’s how to find everybody whose last name is “Smith”.

Click the Filters button.

In Works 5&6&7, it’s the rightmost big button near the screen’s top and shows a funnel. In Works 4.5, it’s the next-to-rightmost big button near the screen’s top and shows a question mark.

If the computer says “First-time Help”, press ENTER.

The computer says “Filter Name”. Press ENTER. Then you see this table:

     Field name      Comparison       Compare To

    ┌───────────────┬────────────────┬─────────────────────┐

    │(None)         │is equal to     │                     │

┌───┼───────────────┼────────────────┼─────────────────────┤

│and│(None)         │is equal to     │                     │

├───┼───────────────┼────────────────┼─────────────────────┤

│and│(None)         │is equal to     │                     │

├───┼───────────────┼────────────────┼─────────────────────┤

│and│(None)         │is equal to     │                     │

├───┼───────────────┼────────────────┼─────────────────────┤

│and│(None)         │is equal to     │                     │

└───┴───────────────┴────────────────┴─────────────────────┘

To find everybody whose last name is “Smith”, change the table so it begins like this:

     Field name      Comparison       Compare To

    ┌───────────────┬────────────────┬─────────────────────┐

    │Last name      │is equal to     │smith                │

    └───────────────┴────────────────┴─────────────────────┘

Here’s how:

The table’s first box temporarily says “(None)”. Next to that “(None)”, your screen has a down-arrow. Click that down-arrow. You see a list of field names. Click the field name you want (“Last name”). Now the table’s first box says “Last name”.

Move to the table’s top right box (by pressing the TAB key twice). In that top right box, type the name “smith”. (You don’t have to capitalize it, since the computer ignores capitalization.)

Press ENTER. Then you see an abridged database, whose records show just people whose last name is Smith:

      ┌────────────┬───────────┬──────────┐

      │ First name │ Last name │ Comments │

      ├────────────┼───────────┼──────────┤

   1  │Sue         │Smith      │wiggles   │

      ├────────────┼───────────┼──────────┤

   2  │Sam         │Smith      │tickles   │

      ├────────────┼───────────┼──────────┤

   4  │Tina        │Smith      │wow       │

      ├────────────┼───────────┼──────────┤

   5  │            │           │          │

      ├────────────┼───────────┼──────────┤

   6  │            │           │          │

      ├────────────┼───────────┼──────────┤

   7  │            │           │          │

      └────────────┴───────────┴──────────┘

You don’t see record #3 (Tina Ash’s record), since she’s not a Smith. You see a filtered database, where the Smiths are still visible but Tina Ash has been filtered out and is invisible.

When you finish admiring that abridged database and want to see all the records again (including even Tina Ash’s), do this:

Click “Record” then “Show” then “All Records”.

Then (to make sure you see even the first record), press Ctrl with HOME.

Different filters To see everybody whose first name begins with “S”, make the filter table begin like this:

     Field name      Comparison       Compare To

    ┌───────────────┬────────────────┬─────────────────────┐

    │First name     │begins with     │s                    │

    └───────────────┴────────────────┴─────────────────────┘

Then you’ll see Sue’s record and Sam’s record (but not Tina’s).

To see everybody except Sam, make the filter table begin like this:

     Field name      Comparison       Compare To

    ┌───────────────┬────────────────┬─────────────────────┐

    │First name     │is not equal to │sam                  │

    └───────────────┴────────────────┴─────────────────────┘

To see just Tina Smith’s record, make the filter table begin like this:

     Field name      Comparison       Compare To

    ┌───────────────┬────────────────┬─────────────────────┐

    │First name     │is equal to     │tina                 │

┌───┼───────────────┼────────────────┼─────────────────────┤

│and│Last name      │is equal to     │smith                │

└───┴───────────────┴────────────────┴─────────────────────┘

Then you’ll see Tina Smith’s record (without seeing the other Smiths and without seeing Tina Ash).

To see everybody whose first name is Sue or Tina, make the filter table begin like this:

     Field name      Comparison       Compare To

    ┌───────────────┬────────────────┬─────────────────────┐

    │First name     │is equal to     │sue                  │

┌───┼───────────────┼────────────────┼─────────────────────┤

│or │First name     │is equal to     │tina                 │

└───┴───────────────┴────────────────┴─────────────────────┘

Then you’ll see everybody whose first name is Sue or Tina (without seeing Sam).

Alphabetize

To make the computer look at each person’s last name and put the last names in alphabetical order, do this:

Click “Record” then “Sort records”. (If the computer says “First-time Help”, press ENTER.) Type “Last name” (or choose “Last name” from the “Sort by” box down-arrow’s menu). Press ENTER.

That makes the computer alphabetize the table by Last name, so Tina Ash’s record is at the top of the table and the Smiths are under her.

If you change your mind and want to undo the alphabetizing, just say “undo” (by pressing Ctrl with Z).

You’ve learned how to put the last names in alphabetical order. But what if two people have the same last name? Let’s make the computer look at each person’s last name and put the last names in alphabetical order — but if two people have the same last name, make the computer look at those people’s first names and put their first names in alphabetical order:

Click “Record” then “Sort records”. (If the computer says “First-time Help”, press ENTER.) In the “Sort by” box, put “Last type” (by typing it or by choosing from the down-arrow’s menu). In the box below, put “First name”. Press ENTER.

Save

To save the database (copy it onto the hard disk), click the Save button (which is under the word “Tools”).

If you haven’t saved the database before, the computer will say “File name”. Invent a name for your database. Type the name and press ENTER.

That makes the computer copy the database onto the hard disk.

For example, if you named the database “mary”, the computer will make that database become a file called mary.wdb, which means “Mary’s Works database”.

Works 5&6&7 will put that file into the My Documents folder. Works 4.5 will put that file into the Documents folder that’s in the MSWorks folder (which is in the Program Files folder).

Afterwards, if you change your mind and want to do more editing, go ahead! When you finish that extra editing, save it by clicking the Save button again.

Save often If you’re typing a long database, click the Save button about every 10 minutes. Click it whenever you get to a good resting place and think, “What I’ve typed so far looks good!”

Then if an accident happens, you’ll lose at most 10 minutes of work.

Print

To print records onto paper, you can click the Print button (which is under the world “Help”). That makes the computer print the table.

If you want to print just some of the records, filter them before printing. If you want the records to be alphabetized, alphabetize them before printing.

When printing, the computer tends to be lazy: it doesn’t bother printing the column headings (field names), doesn’t bother printing the record numbers, and doesn’t bother printing the gridlines. To force the computer to print them, do this before clicking the Print button:

Click “File” then “Page Setup” then “Other Options”. Put a check mark in the “Print gridlines” box and in the “Print record and field labels” box (by clicking those boxes). Press ENTER.

Leave the database

When you finish working on a database, do this:

Works 5&6&7 Click the X at the screen’s top right corner. Then you have three choices:

If you click the X at the screen’s top right corner again, the computer stops using Microsoft Works.

If you click Programs then Start a blank Database, the computer lets you start typing a new database.

If you click History, you see a list of old databases (and other Works creations). If you want to use one of those databases, click the database’s name.

Works 4.5 Choose Exit Works or Close from the File menu.

If you choose Exit Works, the computer stops using Microsoft Works.

If you choose Close instead of Exit Works, the computer says “Works Task Launcher”. Then click the Database button or Existing Documents.

If you click the Database button, the computer lets you start typing a new database.

If you click Existing Documents and then double-click the name of an old database, the computer puts that database onto the screen and lets you edit it.

Didn’t save? If you didn’t save your database before doing those procedures, the computer asks, “Save changes?” If you click “Yes”, the computer copies your database’s most recent version to the hard disk; if you click “No” instead, the computer ignores and forgets your most recent editing.

Improve the fields

While you’re viewing at the database as a table, the top of each column shows the name of a field. Here’s how to improve the fields.

To change a field’s name, do this:

Click the field’s name. From the Format menu, choose Field. Type what you want the field to be named (and press ENTER).

To insert an extra field, do this:

Right-click the name of a nearby field (a field next to where you want the extra field to be). Click “Insert Field”.

If you want the extra field to be to the left of the nearby field, click “Before”; if you want the extra field to be to the right of the nearby field, click “After”.

Type what you want the extra field to be named (and press ENTER). If you want to insert another extra field, type the name you want for it (and press ENTER).

Click “Done”.

To delete a field (and all the data in that field), do this:

Right-click the field’s name. Click “Delete Field”. Press ENTER.

To move a field, do this:

Click the field’s name. Take your finger off the mouse’s button. While pressing the mouse’s left button again, drag the field’s name across to where you want it (between other fields, but not farther right than the rightmost field).

Data types

While you’re using Works, you can see this menu of 7 choices:

General

Number

Date

Time

Text

Fraction

Serialized

You see that menu while you’re inventing a new field (and typing the field’s name). Another way to see that menu is to click a field’s name, then click “Format” then “Field”.

On that menu, the 7 choices are called formats or data types. The computer assumes you want the first choice (which is “General”), but you can choose a different data type instead. The more accurately you choose, the more accurate the computer will be at filtering, sorting, and displaying data.

Choice    What data should be in the field

General  strange data that doesn’t fit the categories below

Number a number (such as 6237.90) having two digits after the decimal point

Date    a date (such as 12/31/00) that’s between 1930 and 2029

Time    a time (such as 4:48 PM)

Text   words or ID number (such as social security #, phone #, or ZIP code)

Fraction    whole number followed by fraction (such as 7 3/4, which means 7¾)

Serialized  an ID number (from 00001 to 99999) that the computer generates

Microsoft Access

Many companies use a database program called Microsoft Access because it comes free as part of Microsoft Office Professional.

The newest versions are:

Microsoft Access 2000, which is part of Microsoft Office 2000   Professional.

Microsoft Access 2002, which is part of Microsoft Office XP      Professional.

Microsoft Access 2003, which is part of Microsoft Office 2003   Professional.

I’ll explain all three.

If you have an older version, such as Microsoft Access 97, follow my instructions for Microsoft Access 2000, which is similar.

Launch Microsoft Access

Here’s how to start using Microsoft Access.…

Versions 2000&2002 Click “Start” then “Programs” then “Microsoft Access”.

Version 2003 Click “start”.

If you see “Microsoft Office Access 2003”, click it. (Otherwise, click “All Programs” then “Microsoft Office” then “Microsoft Office Access 2003”.)

Create a database

For version 2003, click “Create a new file” (which is at the screen’s right edge) then “Blank Database”. For version 2002, click “Blank Database”. For version 2000, click “Blank Access database” then “OK”.

For our first experiment, let’s create this database:

First name       Last name       Comments

Sue                      Smith                  wiggles toes

Sam                    Smith                  picks nose

Tina                   Ash                     class clown

Tina                   Smith                  incredible

Here’s how to create it.…

Invent a name for your database (such as “Friends”). Type the name. At the end of the name, press ENTER.

If the computer says “The file already exists”, do this procedure:

Press ENTER. Type a different name instead (such as “Friends2” or “Buddies” or “Pals” or “Enemies”) and then press ENTER. If the computer says “The file already exists” again, do this whole procedure again.

Finally, you see these choices:

Create table in Design view

Create table by using wizard

Create table by entering data

Press ENTER again (which selects “Create table in Design view”).

Design view You see the Design view, which begins like this:

┌───────────────────────┬───────────────┬──────────────────┐

│      Field name       │   Data Type   │   Description    │

├───────────────────────┼───────────────┼──────────────────┤

│                       │               │                  │

├───────────────────────┼───────────────┼──────────────────┤

│                       │               │                  │

├───────────────────────┼───────────────┼──────────────────┤

│                       │               │                  │

├───────────────────────┼───────────────┼──────────────────┤

│                       │               │                  │

└───────────────────────┴───────────────┴──────────────────┘


We’re trying to create a database who column headings are these:

First name       Last name       Comments

The column headings are called field names. Here’s a list of those field names:

First name

Last name

Comments

Type that list of field names. To do that, type the words “First name”, then press the keyboard’s down-arrow key, then type the words “Last name”, then press the down-arrow key again, then type the word “Comments”, then press the down-arrow key again.

The computer automatically puts the word “Text” next to each field name you type, so your screen looks like this:

┌───────────────────────┬───────────────┬──────────────────┐

│      Field name       │   Data Type   │   Description    │

├───────────────────────┼───────────────┼──────────────────┤

│First name             │Text           │                  │

├───────────────────────┼───────────────┼──────────────────┤

│Last name              │Text           │                  │

├───────────────────────┼───────────────┼──────────────────┤

│Comments               │Text           │                  │

├───────────────────────┼───────────────┼──────────────────┤

│                       │               │                  │

└───────────────────────┴───────────────┴──────────────────┘

Near the screen’s top left corner, you see the word “File”. Below “File”, you see the View button (which temporarily looks like a tiny spreadsheet table of numbers). Click that button. Press the ENTER key twice (which makes the computer save the table and call it “Table1”).

The computer will ask, “Do you want to create a primary key now?” Click “No”.

Datasheet view You see the Datasheet view, which looks like a spreadsheet table and begins like this:

┌─────────────┬─────────────┬─────────────┐

│ First name  │  Last name  │  Comments   │

├─────────────┼─────────────┼─────────────┤

│             │             │             │

└─────────────┴─────────────┴─────────────┘

Type the data that you want in the table, moving from cell to cell by pressing the ENTER key. So type “Sue”, press ENTER, type “Smith”, press ENTER, type “wiggles toes”, press ENTER, type Sam”, press ENTER, etc., until you’ve finally typed the last entry (“incredible”), so the spreadsheet table looks like this:

┌─────────────┬─────────────┬─────────────┐

│ First name  │  Last name  │  Comments   │

├─────────────┼─────────────┼─────────────┤

│Sue          │Smith        │wiggles toes │

├─────────────┼─────────────┼─────────────┤

│Sam          │Smith        │picks nose   │

├─────────────┼─────────────┼─────────────┤

│Tina         │Ash          │class clown  │

├─────────────┼─────────────┼─────────────┤

│Tina         │Smith        │incredible   │

├─────────────┼─────────────┼─────────────┤

│             │             │             │

└─────────────┴─────────────┴─────────────┘

Congratulations! You’ve created a database table.

While you’re typing that data, the computer automatically copies it to the hard disk. (You don’t have to click the Save button.)

In a database table, each row of data is called a record; each column of data is called a field. In the database table you created, here are the records and fields:

The first record (row of data) is Sue Smith’s record.

The second record is Sam Smith’s.

The third record is Tina Ash’s.

The fourth record is Tina Smith’s.

So altogether, there are 4 records.

The first field (column) is called “First name”.

The next field is “Last name”.

The next field is “Comments”.

So altogether, there are 3 fields (columns), whose field names (column headings) are “First name”, “Last name”, and “Comments”.

Edit the table

While you’re viewing the database table, you can edit it by retyping it. To move to different places in the table, you can use the mouse or keyboard:

To move down, to the cell below,         press the keyboard’s down-arrow key.

To move up, to the cell above,             press the keyboard’s up-arrow key.

To move right, to the next character,  press the keyboard’s right-arrow key.

To move left, to the previous character, press the keyboard’s left-arrow key.

To move far right, to the next cell, press the ENTER key

(or TAB key or repeatedly press the right-arrow key).

To move far left, to the previous cell, press SHIFT with TAB

(or repeatedly press the left-arrow key).

Add extra records To add an extra record, just type the extra record in the blank row below the other records. (The computer won’t let you type an extra record between other records.)

If your table contains many records, the fastest way to hop down to the blank row is to click the New Record button (which is near the screen’s top and shows a red triangle pointing at an asterisk).

Delete Here’s how to delete:

To delete a character, click before it then press the DELETE key.

To delete an entire word, double-click it then press the DELETE key.

To delete an entire cell, put the mouse pointer before the cell’s first word, so the pointer turns into a white cross, then click (so the whole cell is highlighted), then press the DELETE key.

Here’s how to delete an entire row (record):

Method 1: to the left of the row, you see a small gray square; click it, then press the DELETE key then ENTER.

Method 2: click anywhere in the row, then click the Delete Record button (which is near the screen’s top and shows a red curved X), then press ENTER.

Undo If you make a mistake while using Microsoft Access, you can typically undo the mistake by clicking the Undo button (which is near the screen’s top and shows an arrow bending toward the left). Clicking the Undo button makes the computer undo your last action.

Be cautious! Sometimes the Undo button’s curved arrow is gray instead of black: the gray means the Undo button is refusing to work.

Though you can sometimes undo your last action, you cannot undo your last two actions; you cannot click the Undo button twice in succession.

So if you make a mistake, you can undo it just if you click the Undo button immediately, before performing other actions, and just if the Undo button is in a good mood, so its arrow is black instead of gray.

Make a column look wider Here’s how to make a column look wider, so it can show longer words:

Look at the column’s heading (the field name, such as “First name”), and look at the vertical black gridline that’s to the right of that column heading. Drag that black gridline to the right (by using your mouse).

To make the column become narrower again, drag the black gridline back toward the left. To make the column look just wide enough to hold everything in it, double-click the black gridline.

Fiddling with the gridlines affects just what you see on the screen, not what the computer stores in the RAM memory chips or on disk. If you make a column narrow by dragging its gridline, you’ll see just part of the column’s contents on the screen, but the contents are still stored, invisibly: afterward, if you drag the gridline to make the column look wider again, you’ll see the full contents again.


Find

To find everybody whose last name is “Smith”, you can use three methods.

Method 1: “find” In Datasheet view, click in the first record’s “Last name” field. Say “find” (by pressing Ctrl with F). You’ll see the Find and Replace window.

Type “smith” and press ENTER. The computer will look down the “Last name” column, find the next “Smith”, and highlight that “Smith”.

If you press ENTER again, the computer will continue looking for Smiths, find another “Smith”, and highlight that Smith. Keep pressing ENTER, to keep finding Smiths.

After the computer has found all the Smiths, if you press ENTER again the computer will try to find another Smith but fail, so the computer will say “The search item was not found”. Press ENTER.

Whenever you get tired of having the computer look for Smiths, close the Find and Replace window (by clicking its X box).

Method 2: “filter by selection” In Datasheet view, look down the “Last name” column, until you notice a “Smith”. Click that “Smith”. Click the Filter By Selection button (which is at the screen’s top center and shows a funnel over a lightning bolt).

You see an abridged datasheet, whose records show just people whose last name is Smith:

┌─────────────┬─────────────┬─────────────┐

│ First name  │  Last name  │  Comments   │

├─────────────┼─────────────┼─────────────┤

│Sue          │Smith        │wiggles toes │

├─────────────┼─────────────┼─────────────┤

│Sam          │Smith        │picks nose   │

├─────────────┼─────────────┼─────────────┤

│Tina         │Smith        │incredible   │

├─────────────┼─────────────┼─────────────┤

│             │             │             │

└─────────────┴─────────────┴─────────────┘

You don’t see Tina Ash’s record, since she’s not a Smith. You see a filtered datasheet, where the Smiths are still visible but Tina Ash has been filtered out and is invisible.

When you finish admiring that abridged datasheet, see the full datasheet again by clicking the Remove Filter button (which is at the screen’s top center and shows just a funnel).

Method 3: “filter by form” In Datasheet view, click the Filter By Form button (which is at the screen’s top center and shows a funnel over a form).

You should see this form:

┌─────────────┬─────────────┬─────────────┐

│ First name  │  Last name  │  Comments   │

├─────────────┼─────────────┼─────────────┤

│             │             │             │

└─────────────┴─────────────┴─────────────┘

Make sure its bottom row is clear (contains no words, no numbers). If it’s not clear yet, clear it by clicking the Clear Grid button. (In versions 2000&2002, that button is a red curved X. In version 2003, that button is a black ball.)

To find everybody whose last name is Smith, click the box below “Last name”, then put “Smith” into the box using one of these methods:

Method A: start typing “smith”; after you’ve typed the beginning of “smith”, the computer will type the rest of it for you automatically.

Method B: click that box’s down-arrow; you’ll see an alphabetical list of all the last names; from that list, choose “Smith” by clicking it.

Click the Apply Filter button (which shows just a funnel).


You see an abridged datasheet, whose records show just people whose last name is Smith:

┌─────────────┬─────────────┬─────────────┐

│ First name  │  Last name  │  Comments   │

├─────────────┼─────────────┼─────────────┤

│Sue          │Smith        │wiggles toes │

├─────────────┼─────────────┼─────────────┤

│Sam          │Smith        │picks nose   │

├─────────────┼─────────────┼─────────────┤

│Tina         │Smith        │incredible   │

├─────────────┼─────────────┼─────────────┤

│             │             │             │

└─────────────┴─────────────┴─────────────┘

You don’t see Tina Ash’s record, since she’s not a Smith.

When you finish admiring that abridged datasheet, see the full datasheet again by clicking the Remove Filter button (which shows just a funnel).

Here’s a summary of the “filter by form” method:

Click the Filter By Form button (and the Clear Grid button). To see just the Smiths, put “smith” in the “Last name” field then click the Apply Filter button.

To see just Tina Smith’s record (without seeing the other Smiths and without seeing Tina Ash), do this:

Click the Filter By Form button (and the Clear Grid button). Put “tina” in the “First name” field, put “smith” in the “Last name” field, then click the Apply Filter button.

To see everybody whose first name is not Sam, do this:

Click the Filter By Form button (and the Clear Grid button). Put “not sam” in the “First name” field, then click the Apply Filter button.

To see everybody whose first name is Sue or Tina (without seeing Sam), do this —

Click the Filter By Form button (and the Clear Grid button). Put “sue or tina” in the “First name” field, then click the Apply Filter button.

or do this:

Click the Filter By Form button (and the Clear Grid button). Put “sue” in the “First name” field, click “Or” (which is at the screen’s bottom), put “tina” in the “First name” field, then click the Apply Filter button.

To see everybody whose first name begins with “S”, do this:

Click the Filter By Form button (and the Clear Grid button). Put “s*” in the “First name” field, then click the Apply Filter button.

Alphabetize

To make the computer look at each person’s last name and put the last names in alphabetical order, do this:

In Datasheet view, click “Last name”.

Click the Sort Ascending button

(which is at the screen’s top center and shows an A above a Z).

That makes the computer alphabetize the table by Last name, so Tina Ash’s record is at the top of the table and the Smiths are under her.

If you change your mind and want to undo the alphabetizing, I have bad news for you: the “Undo” button is too stupid to know how to unalphabetize! To unalphabetize, you must click “Records” then “Remove Filter/Sort”.

Print

To print records onto paper, click the Print button (which is near the screen’s top left corner). That makes the computer print the table.

(If you want to print just some of the records, filter them before printing. If you want the records to be alphabetized, alphabetize them before printing.)

Final steps

When you finish using the table you created, close the Table window (by clicking its X button). If you alphabetized (or made any other changes to your table’s structure), the computer asks “Do you want to save changes to the design of table?”; reply by pressing ENTER.

You see the Friends Database window. Close it (by clicking its X button). Then you have three choices:

To start creating a different database, click the New button (which is near the screen’s top left corner, below the word “File”), then do this: for version 2003, click “Blank database” then continue the “Create a database” process explained on page 285; for version 2002, do the “Create a database” process explained on page 285; for version 2000, double-click the Database icon then invent a name for your database then continue the “Create a database” process explained on page 285.

To use a database you previously created, click the Open button (which is near the screen’s top left corner and looks like an opening manila file folder). You see a list of databases you created (and some folders, too). (If the list is too long to see it all, scroll to see the rest of it.) Double-click the database you want (such as “Friends”). You see the Friends Database window. To be safe, click “Tables” (which is at the screen’s left edge, under “Objects”) then the “Table1” icon then press ENTER (which opens Table1). The database’s table will appear on the screen, with the same view and filtering and sorting as when you last used it.

To stop using Microsoft Access, click its X button.

Improve the fields

You can view your database table in two ways:

The Design view shows you the field names (next to a word such as “Text”).

The Datasheet view shows you the data itself.

To switch from one view to the other, click the View button (which near the screen’s top right corner, below the word “File”).

Add an extra field In Design view, you can add an extra field easily.

If you want the extra field to be the last field, type the extra field’s name below the other field names.

If you want the extra field to be where another field is now, do this: click that other field’s name, click the Insert Rows button (which is near the screen’s top and shows a blue row that’s out but moving in), then type the extra field’s name.

Finally, press the down-arrow key (so the computer says “Text”).

Delete a field In Design view, you can delete a field easily:

Method 1: to the left of the field’s name, you see a small gray square; click it, then press the DELETE key.

Method 2: click the field’s name, then click the Delete Rows button (which is near the screen’s top and shows a blue row that’s in but moving out).

If the computer asks “Do you want to permanently delete?”, press ENTER.

Move a field You can move a field easily:

Click the object you want to move (the field’s name in Datasheet view, or “the square before the field’s name” in Design view). Take your finger off the mouse’s button. While pressing the mouse’s left button again, drag that object to where you want it (between other fields).


Data types

In Design view, each field normally says “Text”. If you click “Text”, you see a down-arrow next to it. If you click that down-arrow, you see these choices:

Text

Memo

Number

Date/Time

Currency

AutoNumber

Yes/No

OLE Object

Hyperlink

Lookup Wizard

Click whichever choice you want; then the screen’s bottom might show boxes, such as “Field Size” and “Decimal Places”. The more accurately you choose, the more accurate the computer will be at finding, filtering, and sorting.

The bottom 3 choices (“OLE Object”, “Hyperlink”, and “Lookup Wizard”) are unpopular; I won’t bother to discuss them. Here’s what the popular choices and boxes mean.…

Currency Choose “Currency” if the field’s data will be an amount of money, written as dollars and cents (such as $7,893.20). For example, choose “Currency” if the field is “Amount paid” or “Balance due” or “Profit” or “Debt” or “Income” or “Cost” or “Sales”).

Then when you type the data in Datasheet view, here’s what will happen:

The computer will automatically put a dollar sign before the number, put two digits after the decimal point, and insert a comma if the number is big. If the number is negative, the computer will imitate an accountant: it will put the number and dollar sign all in parentheses (instead of writing a minus sign).

The amount of money can be very big (up to $922,337,203,685,477.58), so you can say you’re even richer than Bill Gates! To see such a big number, widen the Datasheet’s column by dragging its vertical gridline.

If you try to go higher than $922,337,203,685,477.58, the computer will say “The value you entered isn’t valid for this field”.

When you choose “Currency” in Design view, the screen’s bottom usually shows that the Decimal Places box contains the word “Auto”, which makes the computer automatically put 2 digits after the decimal point. You can force the computer to display 4 digits after the decimal point by putting 4 in the Decimal Places box (which will make the biggest number be $922,337,203,685,477.5807). If you want the computer to round to the nearest dollar (and show no decimal point and no pennies), put 0 in the Decimal Places box. You can put 0, 1, 2, 3, or 4 in the Decimal Places box; the computer can’t handle more than 4 accurately.

Each currency amount is stored by using a special code that consumes just 8 bytes of your hard disk, even if the number contains many digits.

Date/Time Choose “Date/Time” if the field’s data will be a date or time. For example, choose “Date/Time” if the field is “Date of birth” or “Date the loan began” or “Date due” or “Date processed” or “Appointment time”.

In Datasheet view, you can type a date/time like this: “12/31/1920 11:59:45 PM”, which means December 31st, 1920, at 45 seconds after 11:59PM.

If you don’t want to be so detailed, type just part of that: type just the date or just the time.

When typing the time, you can omit the number of seconds.

You can write AM or PM or use 24-hour military time.

The computer can handle any 4-digit year from 0100 (which was near Jesus’s birth) to 9999.

When typing a year from 1930 through 2029, you can omit the first two digits.

When typing a year from 0100 through 0999, you can omit the first zero.

Each date/time consumes 8 bytes of your hard disk.

AutoNumber Choose “AutoNumber” if you want the field’s data to be a simple counting number (such as 1, 2, 3, 4,…) that the computer will generate automatically.

In Datasheet view, the computer will automatically type a “1” in that field for the first record, “2” in that field for the second record, etc. That counting number will act as an ID. Each such counting number consumes 4 bytes on your hard disk.

You can name the field “Record number” or “ID number”.

Number Choose “Number” if the field’s data will be a number that’s not an amount of money, not a date or time, and not an identification number. For example, choose “Number” if the field is “Age” or Test score” or “Population” or “Number of children” or Temperature” or “Discount percentage”.

(For an amount of money, choose “Currency” instead. For a date or time, choose “Date/Time” instead. For a simple identification number generated by the computer, choose “AutoNumber” instead. For other kinds of identification number, such as a social-security number or phone number or ZIP code, choose “Text” instead.)

When you choose “Number” in Design view, the screen’s bottom usually shows that the Field Size box contains “Long Integer”. Instead of “Long Integer”, choose a different size instead, if it fits your data better. Here are your choices:

Choice        Meaning                                                                                                        Memory

Byte             a whole number from 0 to 255, no decimals, no negatives                                   1 byte

Integer         an integer from -32768 to 32767, no decimals                                                       2 bytes

Long Integer   an integer from -2147483648 to 2147483647, no decimals                                   4 bytes

Single        a number that can contain decimals, minus sign, exponents, 7 significant digits   4 bytes

Double         a number that can contain decimals, minus sign, exponents, 15 significant digits 8 bytes

In that chart, the “Memory” column shows how many bytes of your hard disk each number consumes. The fewer the number of bytes, the shorter your data file will be and the faster your Access will run. Choose the shortest choice that’s still big enough to hold your data.

Yes/No Choose “Yes/No” if you want the field’s data to be a box, in which a check mark means “yes”; an empty box means “no”. For example, choose “Yes/No” if the field is “Was contacted?” or “Was sold?” or “Has diabetes?” or “Has retired?” or “Is a member now?” or “Is female?” or “Is an adult yet?”.

Then when you create data in Datasheet view, you’ll see an empty box in that field. Put a check mark into that box (by clicking the box) if you want to say “yes”; leave the box blank if you want to say “no”.

Each yes/no answer consumes just 1 bit of your hard disk. (1 bit is very little: it’s 1/8 of a byte.)

Memo “Memo” is the only popular choice that lets the field’s data be longer than 255 characters. “Memo” lets you write an entire long essay about the person and make that essay become part of the person’s record. The essay can be up to 65535 characters long. It consumes as many bytes as there are characters in the memo.

For example, choose “Memo” if the field is “Psychoanalytical comments about the patient” or “What the employee should do to improve” or “What I really think about this person”.


Text Stay with “Text” just if none of the other choices is better. Choose “Text” if the field either includes words (up to 255 characters) or is an ID number (such as a social-security number or phone number or ZIP code) that’s not an AutoNumber. For example, stay with “Text” if the field is “First name” or “Last name” or “Street address” or “City” or “State” or “ZIP code” or “Phone number” or “Social Security number” or “Product name”.

If you decide to stay with “Text”, beware: the computer limits you to 50 characters (because the number in the Field Size box is 50) unless you change that number. The biggest number allowed in the Field Size box is 255. Your text consumes as many bytes as the Field Size box says. To avoid wasting bytes, make the number in the Field Size box be as small as possible, but still big enough to hold your longest data.

Why use data types? Suppose you create a numeric field about “Test score”.

To display the students from lowest score to highest score, do this:

In Datasheet view, click “Test score”. Then click the Sort Ascending button, which is at the screen’s top center and shows an up-arrow (with an A above a Z).

To display the students from highest score to lowest score, do this:

In Datasheet view, click “Test score”. Then click the Sort Descending button, which is at the screen’s top center and shows a down-arrow (with an A below a Z).

To display just the students who scored below 60, do this:

In Datasheet view, click the Filter By Form button (which is at the screen’s top center and shows a funnel over a form).

You see a form. If its bottom row is not clear yet, clear it (by clicking the Clear Grid button, which is a red curved X).

Click the box below “Test score”. Type “<60”.

Click the Apply Filter button (which shows just a funnel).

You can use these symbols:

Symbol                      Meaning

<60                              less than 60 (below 60)

<=60                        less than or equal to 60 (at most 60)

>60                              greater than 60 (over 60)

>=                               greater than or equal to 60 (at least 60)

not 60                          not equal to 60 (not 60)

between 60 and 70  at least 60 but not over 70

You can use apply those techniques to the other data types also! For a date/time field:

The Sort Ascending button puts the records in order from oldest (earliest) to newest (latest).

The Sort Descending button puts the records in order from newest (latest) to oldest (earliest).

“<1/1/1920” gets you all records before 1920.

Forms

In Datasheet view, the computer shows you many records on the screen simultaneously, but each record is restricted to being just one line of the table. If you want a person’s record to include more info than can fit on a single line of your screen, Datasheet view is inconvenient.

Invent a Form view instead. In Form view, a record can consume your entire screen, instead of just one line.


Invent a Form view Here’s how to invent a Form view.…

While you’re looking at the table’s Datasheet view, close that table’s window by clicking its X box. (If the computer asks “Do you want to save changes to the design of table?”, press ENTER.)

You see the Friends Database window. Click “Forms” (which is near the window’s left edge). Double-click “Create form by using wizard”. Click the “>>” button. Press ENTER twice.

You see this list of styles:

Blends

Blueprint

Exedition

Industrial

International

Ricepaper

SandStone

Standard

Stone

Sumi Painting

Each of those styles is a color scheme.

To see how each style looks, press the down-arrow key or up-arrow key several times. Which style do you like best? I recommend “Standard” (which is the simplest, black-gray-white, resembling most other Microsoft products) or “Blends” (which is the most colorful and cheeriest, black-blue-yellow-white). The other choices are compromises between those two extremes.

When you decide which style you want, click it, then press ENTER.

Invent a name for your form. For example, the name can be “Table1 form” or “A wild look at my friends”. Invent any name you wish! Type that name and press ENTER.

View a form You see a form displaying the first person’s record, like this:

                    ┌─────────────────────────┐

First name          │Sue                      │

                    └─────────────────────────┘

                    ┌─────────────────────────┐

Last name           │Smith                    │

                    └─────────────────────────┘

                    ┌─────────────────────────┐

Comments            │wiggles toes             │

                    └─────────────────────────┘

While you’re admiring that record, you can edit it by using the mouse or these keyboard shortcuts:

To move to the next box (field), press ENTER (or TAB).

To move back to the previous box (field), press SHIFT with TAB.

To move to the bottom box, press the END key.

To move back to the top box, press the HOME key.

See other records Here’s how to see other records:

To see the next person’s record, click the button

or press the PAGE DOWN key (or rotate the mouse’s wheel toward you).

To see the previous person’s record again, click the ƒ button

or press the PAGE UP key (or rotate the mouse’s wheel away from you).

To hop back to the first record, click the |ƒ button or press Ctrl with HOME.

To hop ahead to the final record, click the | button or press Ctrl with END.

To create an extra record, click the * button or press Ctrl with +.

To hop back to record #2, press the F5 key, then type 2 (and press ENTER).

Close The form is in a window (called “Table1 form” or “A wild look at my friends” or whatever name you invented). When you finish looking at forms, close that window (by clicking its X button). Then click “Tables” (which is at the screen’s left edge, under “Objects”), to put the screen back to normal.

Getting back to your form To see the Form view again, click “Forms” then double-click your form’s name.