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.

 

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:

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:

 

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

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.

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

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

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.

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

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

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.

 

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.

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:

For the Windows version, do this instead:

Launch FileMaker Pro

For the Mac version, do this:

For the Windows version, do this instead:

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:

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:

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:

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

Below the Rolodex, you see two numbers:

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:

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

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

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

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:

Find

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

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

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:

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:

Here’s the rule:

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:

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

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

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:

Here’s how to say that:

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:

Say which view you want to print:

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

For the Mac, proceed as follows:

For Windows, proceed as follows instead:

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:

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:

You can use these symbols:

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

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

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:

You can use these symbols:

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

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:

You can use these symbols:

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

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.

 

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

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:

For Works 4.5, do this instead:

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

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

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.

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 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.…

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:

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:

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.

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:

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:

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:

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:

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”.

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:

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:

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

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:

To insert an extra field, do this:

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

To move a field, do this:

Data types

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

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.

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

The newest versions are:

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:

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:

Finally, you see these choices:

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:

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

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:

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:

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

Delete Here’s how to delete:

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

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.

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

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:

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:

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

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

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

or do this:

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

Alphabetize

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

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:

Improve the fields

You can view your database table in two ways:

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.

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

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

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

Move a field You can move a field easily:

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:

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:

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 31^st, 1920, at 45 seconds after 11:59PM.

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:

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 ¹/₈ 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:

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

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

You can use these symbols:

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

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:

Each of those styles is a color scheme.

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:

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

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.