Access 2003 for Starters: The Missing Manual

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

A Tour of an Access Database
Creating a New Database
Starting with a Wizard
Modifying the Wizard's Database
Starting from a Blank Database

In this chapter, you'll create your first database, from the initial concept to a full-fledged Access file. Using Access's Order Entry template as an example, you'll learn how to set up all the types of database fields to give you the information you want, when you want it, and in exactly the way you want it. You'll also learn how to put information into your database and print a simple report.

As you read in the introduction to this book, a database typically refers to any organized collection of information. In Access, however, a database is much more than that: it's a kind of wrapper to hold other database objects like tables, forms, and reports. An Access database is also relational, which means (among other things) that you can link tables together to combine their information.

A computerized database must let you add, store, find, display, and print information. Access does all these things and more. For each task, Access has different database objects.

When you start Access, a large window opens on your desktop. When you open a database, its window appears inside this outer one. You can have any number of individual database windows open in Access, so in effect, the Access window is like a desktop-within-a-desktop. As shown in Figure 1-1, all of Access's toolbars and the task pane also reside in the same window, so it can get pretty busy in there. You can make more room by closing the task pane (click the close button in the upper-right corner) and minimizing any windows you're not currently using.

Access tables hold the information you store in the database (see Figure 1-2), and you can also use them for data entry. In a table, each row contains one

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As you read in the introduction to this book, a database typically refers to any organized collection of information. In Access, however, a database is much more than that: it's a kind of wrapper to hold other database objects like tables, forms, and reports. An Access database is also relational, which means (among other things) that you can link tables together to combine their information.

A computerized database must let you add, store, find, display, and print information. Access does all these things and more. For each task, Access has different database objects.

When you start Access, a large window opens on your desktop. When you open a database, its window appears inside this outer one. You can have any number of individual database windows open in Access, so in effect, the Access window is like a desktop-within-a-desktop. As shown in Figure 1-1, all of Access's toolbars and the task pane also reside in the same window, so it can get pretty busy in there. You can make more room by closing the task pane (click the close button in the upper-right corner) and minimizing any windows you're not currently using.

Access tables hold the information you store in the database (see Figure 1-2), and you can also use them for data entry. In a table, each row contains one record with information about a specific item, like a customer, sales order, or inventory item. Each column contains a field with information about a specific feature of the record in that row, like a customer's first name, the date of a sales order, or the part number of an inventory item.

When you read about Access on the Web or visit a user group, you'll hear much talk about database objects. Database objects are somewhat similar to objects in the real world, like dogs, trees, ice cream cones, and books.

All objects have properties. A dog, for example, has the properties of color, breed, height, weight, and age. An Access report has the properties of a certain number of columns, a certain content in each column, a certain placement of page numbers, and so forth.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Now that you've got some lists or diagrams of the information you need to track and how you're going to organize it (you did read the previous section, didn't you?), you can launch Access and start building your database. You can start Access two ways:

Click the Windows Start button, and then choose Program Files → Microsoft Office → Microsoft Office Access 2003.
In Windows Explorer, double-click an Access database you want to open.

Just like a text document or a spreadsheet, you can close an Access file and pick up where you left off when you return. To close Access, choose File → Exit, or click the close button at the top-right corner of the Access window.

A database is a collection of individual records organized in a way that makes it easy to add, edit, or update records as well as search for and retrieve information.

Using a form is the easiest way to add records or modify existing ones. Forms—either on paper or computerized—offer a way to collect information and often become an actual record. You probably already have a fair amount of experience with forms because you've had to fill out medical questionnaires, tax and insurance documents, and credit and employment applications.

Each part of a form—called a form field—asks for very specific details, like your last name, your Social Security number, your phone number, your street address, your city, and so on. These form fields usually correspond to individual fields found within an individual record in a database. As you begin to work with databases, you'll see that forms play a role in Access as well: You often use them to enter information into a database. Thinking about something you know fairly well—those pesky forms you have to fill out—helps you appreciate how to design a database so you can obtain and enter all the required details.

Access gives you two main ways to create a database. The method you choose depends on your specific database needs. You can create a database:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When you use a template to create a database, the Access Database Wizard asks you questions about what you need. The wizard gives you more choices with some templates than it does with others, but you always use the same process:

The wizard asks questions—and you answer them—in a series of windows.
The wizard builds a database based on your answers.
You either use the database as the wizard created it, or you modify it further.

Templates are predesigned database files that let you get started with ease because someone's already done part of the work for you. Templates include common elements, like name and address, so you won't have to spend time creating them. (See the box on Section 1.3.1 for a tour of Access's templates.)

To start the Access Database Wizard:

Choose File → New.

Access displays the New File task pane at the right side of your screen (Figure 1-6).

Figure 1-6: The New File task pane has three sections: the New section, Templates section, and Recently Used Templates section. In the Templates section, you can choose a template on your computer or from Microsoft's Web site.
In the Templates section of the New File task pane, click "On my computer."

Access displays the Templates dialog box.
Near the top of the Templates dialog box, click the Databases tab.

The dialog box moves the Databases tab to the front and shows the database templates available on your computer (Figure 1-7).

Access includes 10 ready-to-use templates, each designed for a specific database need.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Congratulations! You've created your first database in Access. You made all the big decisions, but the Database Wizard handled the details. You can learn a lot about Access databases by exploring and modifying a database similar to what you and the wizard have just designed. (If you haven't downloaded the example database as discussed on Section 3.5, do so now and then read on.)

Before you can explore a database, you have to open it. To open a database:

Choose File → Open.

The Open dialog box appears. Just as if you were opening any document in Windows, you can use this box to locate and open Access databases anywhere on your PC.
Browse to and select TypewriterOrders_example.mdb. Then click Open.

Before opening the database, Access displays a warning box. This message merely means that Access databases may contain programming code that could harbor computer viruses, not necessarily that it does contain them. The box has three buttons: Cancel, Open, and More Info. If you created a database yourself or obtained it from a trusted source, the database is probably safe.

To defend yourself against computer viruses, use up-to-date virus detection software and scan any database files you receive.
Click Open one more time.

Access opens the database. The TypewriterOrders database reveals a menu panel called a switchboard. By clicking buttons on the panel, you can do things in your database: view tables, enter data, print reports, and so forth. See Figure 1-14.

Figure 1-14: Switchboards, like the one shown here, let people who don't know Access do a number of common database tasks simply by clicking buttons. Notice the button at the bottom labeled "Exit this database." It closes the current database but leaves Access open so you can keep working.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If your database needs are unusual—or if you're an incorrigible do-it-your-selfer—you may not want to use one of Access's database templates. As an alternative you can create a blank database. You can then manually design all your tables, forms, reports, and other database objects. That's how the developers of Access created the templates you use with the Database Wizard: they started with a blank database and added one piece at a time.

On the plus side, creating a blank database gives you freedom: you can design everything just as you want it from the get-go. On the minus side, it's more work, but as database guru Kahlil Gibran said, "Work is love made visible." If you love what you're doing—or even just like it a bit—then it's never a burden to put in a little extra work.

It's easy to create a blank database. Here are the steps:

Start Access, and then choose File → New.

At the right side of your screen, Access displays the New File task pane. It's just like the one in Figure 1-6 that you used to open a template. This time, however, you're going to use the New section to create a fresh, empty database document.
Near the top of the Getting Started dialog box, click Blank Database.

Access displays the File → New Database dialog box.
In the File → New Database dialog box, choose a folder to save the database in, and then type a filename of your choice.

If you're just practicing, you can keep all your work for this chapter in one folder, as described on Section 1.3.1.
Click Create.

Access creates a blank database (see Figure 1-21). You can now add tables (as you'll do in Chapter 2), forms (covered in Chapter 3), and other database objects.

Figure 1-21: A database is a wrapper for tables and other database objects. When you create a blank database, you start with just the wrapper. Notice that the title bar of the Database window reads "Access 2000 file format." Access 2003 automatically uses the older Access 2000 file format so that other people can use your database even if they don't have an up-to-date version of Access.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Planning Your Tables
Creating Tables with the Wizard
Creating Tables in the Design Window
Adding and Editing Table Data
Changing How a Table Displays Data

Tables do the most important job in a database: They actually store your information. They're the foundation for all of Access's other features. Accordingly, when you start a new, blank database, the first thing you do is create tables to hold your data. This chapter walks you through the two ways you can build tables in Access—the Table Wizard and Design view. At the end, you'll learn how to enter records into your newly created tables the quick and dirty way, without the help of a form—in Datasheet view.

In the previous chapter, you started your database with the Order Entry template, which came equipped with a number of tables. A database designer somewhere, sometime, figured out which tables the database would need and then created them. When you create your own tables, you're starting to design the structure of your own database. So dig out any notes you jotted down when you first started planning your database and read the box on the next page for more information on good database design to get a clearer idea of the tables you want to create.

As your database grows in size and function, you can add new tables to any existing database at any time, including those you've created with a wizard. For example, if you start by keeping only customer information in a database, you can add a new table for order information a year or two down the road.

Start by thinking about the kind of information your database will contain and what you need to do with the information. For example, will your database contain customer, order, and supplier information? Will you sometimes need to combine different types of information, such as connecting customers to their orders or inventory items to their suppliers? Each major type of information should get its own table, with the smaller bits of information going into fields. Some points to consider are listed on Section 2.2.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Start by thinking about the kind of information your database will contain and what you need to do with the information. For example, will your database contain customer, order, and supplier information? Will you sometimes need to combine different types of information, such as connecting customers to their orders or inventory items to their suppliers? Each major type of information should get its own table, with the smaller bits of information going into fields. Some points to consider are listed on Section 2.2.

The difference between good and bad database design isn't aesthetic. Instead, where a bad database design hits home is in use: A poorly planned database may make it tougher to enter or update new records or it may allow many mistakes to get in. You also see this difference when you're trying to pull information from the database, which, of course, is the whole reason you're going to the trouble of creating and managing that database in the first place.

Utilize the kind of planning discussed both in this chapter and the next one, and you're probably going to do fine. But start the database without such advance work, and you're going to expend a great deal of time later on trying to troubleshoot problems you could've avoided.

• Think ahead of your database needs for the next six months to one year. All too often, people in charge of databases discover after the fact that they're not collecting all the information they'll ultimately need for reports and analyses weeks or months later. While Access lets you go back and modify a database later, it's more work to go back to the information source to obtain the additional details.
• Don't create a database only you can understand.
• Store individual bits of information (like a name or a phone number) in the smallest logical units that make sense, such as dividing a customer's name into last name, first name, and middle initial, or keeping the area code separate from the rest of a phone number.
• Create a logical flow to a record entry. For example, you wouldn't normally put the entry for the customer's last name in one place and the entry for the first name several spots away.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Just like the Access Database Wizard, Access provides a Table Wizard to help you construct tables. The Table Wizard includes 25 predefined tables for business uses and 20 for personal uses. Chances are, one of these tables matches exactly (or closely) what you need.

Business. Tables that help you keep track of mailing lists, customers, employees, orders, invoices, reservations, billing time, and almost anything else you'd need to run your own firm, whether it be humble or globe-spanning.
Personal. Tables that help you keep track of fun stuff, such as books, wine lists, photographs, addresses, and exercise and diet logs (well, it can't all be fun).

Also like the Access Database Wizard, you can easily start with a predefined table similar to what you want, and then modify it. In the personal tables, for example, Microsoft forgot to include a predefined table for your monster-truck magazines. The easiest solution is to start with a similar predefined table, such as Books, create it with the wizard, and then modify the wizard's design.

Your first step is to start the Table Wizard. From there, you can choose and rename the fields to include in your new table, set a primary key (information unique to each individual record, such as a Social Security number or an account number) to identify each record, name your table, and even enter data in a datasheet or form. A primary key isn't anything too scary: It's just a table field with a value that uniquely identifies each record, such as a Customer ID number.

To see the Table Wizard in action, you'll create a table that holds contact information. There's a sample Access file all set for you to work in. Download CreatingTables.mdb from the "Missing CD" page (Section 3.5).

Open the CreatingTables.mdb database.

For example, choose File → Open, or double-click the file's icon. A window called CreatingTables: Database opens.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When you plan your database tables as discussed at the beginning of this chapter, you may discover that the Table Wizard, which allows for quick setup of standard table types, simply doesn't have a table type that is a good match for your specific needs.

Next you have two options: You can choose a type from the wizard and then modify its design using the Design window, as you learned earlier in this chapter, or you can create a highly customized table from scratch using the Design window (as shown in Figure 2-7). Use the Design window to create a table on those rare occasions when you're doing something so unusual it would take less time to build a custom table than to adapt one of the standard tables.

When you're working in the Design window, you're looking at Access's Design View. "Design view" and "the Design window" mean the same thing.

The Design window has two main sections:

The field entry area. Here in the top half of the window, you add fields, choose their data types, and write descriptions of fields.
The field properties area. In the bottom half of the window, you set the properties of each field (see Setting Field Properties on Section 2.3.3), such as size, format, and default values. This area displays different properties depending on the data type of the field you've selected in the field entry area.
You can switch from one section to the other by pressing the F6 function key or by clicking the other section.

The first step in creating a table in the Design window is to add a field. As you add each field, you set its properties before you add another field. If you change your mind about a field's properties, it's easy to go back and change them.

To start a table design and add fields:

Choose File → New → Blank Database. In the dialog box, name the database TableDesign and click Create

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

After you've created a table, you can use a table's Datasheet view to add new data, change data you've previously entered, and view any data in the table. Using Datasheet view is as quick as typing into a spreadsheet, and quite easy if you're already very familiar with the information you're entering. However, if your table has many fields, the information is complicated, or there are lots of values to remember, you could use a form, as you'll learn in the next chapter, to provide onscreen help and error-checking.

Adding new records to a table is a breeze since it's already so easy to move around in a datasheet (see the box on the next page). If you're starting with a new table that has no records, Access automatically puts the cursor in the first column of the first row in the datasheet. You simply add the first record to that row. When you add data to the last column and press Tab, Access moves the cursor down to the next line, where you can enter another record.

Any time you open a database, you can move around in a datasheet to view records or different columns of records either by using the keyboard or your mouse. If you can see the location in the datasheet on your screen, you can move there directly by clicking it with the mouse.

A group of buttons (shown here) appears at the lower-left corner of the Datasheet window. You can use them to move from record to record. From left to right:

• The first button, with an arrow pointing left to a vertical line, moves the cursor directly to the first record in the table.
• The second button, with an arrow pointing left, moves the cursor to the previous record.
• The box between the second and third buttons shows the current record number where the cursor is located. By pressing the F5 key, you can type a record number into this box, and then press Enter to go directly to that record.
• The third button, with an arrow pointing right, moves the cursor to the next record.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If you've followed along in this chapter and created a table or two, you have every reason to feel proud. But once you start adding information as described in the previous section, you may find that you can't see some information clearly or move around efficiently. Don't worry: Going back and making adjustments is what database design is all about. Datasheets are great for entering records and getting an overview of your information, and are very easy to modify. For example:

Columns in the datasheet are often too narrow to display all the information they contain. You can make them wider.
Access displays field columns in the same order as the fields appear in the table design. You can move columns left or right in the datasheet, so that it's easy to populate them in order.
You can freeze columns so that as you scroll horizontally, the frozen columns stay on the screen. That way, you can always see the first column as a point of reference.

For more options, choose View → Toolbars → Formatting (Datasheet) to display the Datasheet Formatting toolbar. This toolbar has buttons that let you change the datasheet's type fonts, colors, borders, and other display properties.

To try your hand at tweaking the Datasheet view, you can make some changes to a sample table whose columns need help with both size and positioning.

Open the TableDisplay database, and then open the DisplayData table.

It's the same example file used in the previous section (Section 2.4.1). If you have an Access table of your own that could stand improvement, you can use that instead.
In the DisplayData table, start by widening the Address column. Drag the column border to the right until it shows all the address data.

To adjust the width of any column, simply move the mouse pointer in the heading row over the border between the heading of the column to resize and the next column to the right. You can see the resize pointer in Figure 2-13.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Form Basics
Creating Autoforms
Creating Forms with the Form Wizard
Modifying the Form Design
Creating Forms in Design View

There's a reason all doctor's offices, schools, businesses, and governments use forms to collect information from you: Forms work. They ensure all the important information is obtained. And though you may resent having to fill them out, forms do make life easier.

In Access, forms don't just handle data collection. Onscreen forms can also provide help so people know what to type even if they're not familiar with your database. In addition, forms can error-check information as it's entered, preventing incorrect information from getting into the database. Furthermore, you can use Access forms to display information onscreen.

You can use forms to print data, too, but it's not always easy to make an onscreen form look good on paper. Reports (Chapter 8) are usually better used for printing.

Access gives you three ways to create forms:

Autoforms are the fastest and easiest way to create a form but give you very few design choices. You get what you get, though very often what you get is enough.
The Form Wizard takes you through a series of screens that ask you questions about your form design. At the end, the wizard creates a form based on your answers. The Form Wizard gives you more choices than the Autoforms method and is much easier to use than creating a form from scratch in the Design window.
The Design window requires the most work on your part but gives you the most control of all three methods. In the Design window, you manually lay out your form on a screen grid. You position the form's blanks, labels, buttons, and other features. The Design window method is fine if you need an unusual form layout (or you're just a die-hard do-it-yourselfer).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

No matter which of the three methods you use to create your forms, all forms have some features in common. These common features are:

Layout. The basic arrangement of information on the form.
Controls. The labels, text boxes, list boxes, buttons, and other items you put on the form.
Properties. The aspects of the form itself and of the controls on the form. Common properties include height, color, type font, size, and data source (such as a table).

If you understand these common features, you'll know how to apply them to any form you create. You'll be able to rearrange the organization of your forms, change how they work, and change how they look.

A form layout determines where fields, labels, and other items appear on the form. It also determines the general look of the form. No matter which layout you choose, your form will work the same way. The layout affects only how your form looks on the screen. (You'll find the following types of forms in the Form Wizard, but you can create most of them, or get the same effect, using the Autoform or Design view, too.)

Access, through the wizard, includes these predefined layouts:

Columnar forms place the table's fields in columns. To the left of each field, a text label shows the field name. Of all the types of forms, columnar forms most resemble paper forms. Use columnar forms for situations where it works well to show only one record on the screen at a time.
Tabularforms make each table record into one horizontal row of fields, the same as in a datasheet. At the top of each column, a text label gives the field name. Tabular forms are laid out a lot like datasheet forms, except that you only see one record at a time, each record can have multiple rows, and the fields look like blanks on a form instead of table cells (see Figure 3-1). Use a tabular form when what you'll enter requires relatively narrow fields and you want to be able to eyeball more than one record on the screen at once.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Easy is good. If easy does what you want, then there's no need to look any further. And Autoforms are easy. That's their main advantage.

Autoforms also have three disadvantages. First, they can only work with data from a single table, while forms you create with a wizard or in the Design window can work with data from multiple sources. Second, Autoforms typically include all the fields from your table or data source, while the wizard and Design window methods let you pick and choose the fields you want to include. Third, you can't use the justified layout with Autoforms, though you can use all the other layouts.

To get some practice, download the example file for this chapter from the "Missing CD" page (Section 3.5). Open the CreatingForms database, and then follow the steps below to create an Autoform.

The CreatingForms database is ideal for following the tutorials in this chapter, but it's unrealistic in one respect: The Customers table wouldn't normally have a Balance field showing each customer's account balance. In Chapter 4, you'll learn a better way to keep that kind of often-changing information in a separate (but linked) table.

In the Objects bar, click Forms, and then click New at the top of the Database window.

Access displays the New Form dialog box (Figure 3-3). You can also open the New Form dialog box by choosing Insert → Form.

Figure 3-3: The New Form dialog box lets you create forms by any of the methods Access offers: Autoforms, the Form Wizard, and the Design window. You can also create charts and pivot tables, but you'll probably do that less often.
In the list box, click Autoform: Columnar.

That unlabeled white box lists all the kinds of forms Access can create using either an Autoform or the wizard.
From the "Choose the table or query" drop-down menu, choose Customers, and then click OK

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Form Wizard is the happy medium of forms creation. Using it is almost as easy as creating an Autoform, but the wizard gives you a lot more flexibility. Unlike an Autoform, the wizard lets you do the following:

Use multiple tables or other data sources with your form. For example, you could put all the fields from a Customers table on your form, and then add one field from an Orders table.
Choose which fields you want to include on your form.
Specify the order in which fields appear on your form.
Select a visual style for your form.

There's really no downside to using the Form Wizard unless you need a form with unusual features or tabbed pages: The wizard can create any common type of form. Most of the time, you'll need to fine-tune the forms created by the wizard, but using it is still much quicker and much less work than doing all the form layout yourself. You go through five chief phases to design a form using the Form Wizard.

In phase 1 of the process, you start the Form Wizard and choose a record source. Your record source can be a table from any open Access database or a query.

Open the CreatingForms example database. It's the same one used in the preceding section. Next you'll do the following:

In the Database window's Objects bar, click Forms, and then double-click "Create form by using wizard."

Access displays the first Form Wizard screen, as shown in Figure 3-5.

An alternative method: In the Objects bar, click Forms, and then click New. Click Form Wizard, choose a table from the pop-up menu, and then click OK.

Figure 3-5: After you choose a table in the first Form Wizard screen, the fields from that table appear in the list box at the left. Move the fields you want to include on your form into the list on the right by using the arrow buttons. The whole routine is described on Section 3.3.2.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

After you've create a form using the Autoform or Form Wizard, you can open the form in Design view to tweak it a bit and make sure the results are exactly what you want. You may not get many design choices with an Autoform, and even the Form Wizard doesn't give you full control, but once you've saved a form you've made using either of those methods, you can open it up in Design view and modify it any way you like.

Open the frm_Customers form you created earlier in this chapter. If you don't have one, create a quick Autoform now, as described on Section 3.2. Take a look at the Columnar layout that Access designed for you. It's got a few flaws:

The field labels have no spaces in them.
The form has no title at the top.
The Special Offers checkbox is higher than its label. (Admit it: This is driving you crazy.)

To modify the form, choose View → Design View or click the Design button (with little pictures of a pencil and a triangle) at the left end of the Access toolbar. Access opens your form in the Design window (see Figure 3-10). Maximize the Design window.

Figure 3-10: The Design window shows your controls against a grid that helps you position them on the form. You can turn the grid on or off by choosing View → Grid.

The first change you want to make is to put spaces into the field labels. If the form's property sheet isn't visible, choose View → Properties or press Alt+Enter to display it. Then proceed as follows:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Normally, it's most efficient to create your forms with the Form Wizard and then use the Design window to modify them. However, there are times when you should start in the Design window, especially if you're brimming with creative ideas or have a very complex form in mind. For example, for a form with multiple tabbed pages, the Design window is often easier because you create the tabbed pages at the outset. If you'd started with the Form Wizard, you'd spend lots of extra time trying to rearrange the fields from the original nontabbed design.

In this section, you'll learn the basics of creating a form in the Design window. Along the way, you'll learn to use the Image control in the Toolbox.

For your first form, you'll start with a fairly simple one. Because it's already got some good tables to work with, open the Creating Forms database. If you haven't downloaded the example files for this chapter, do so now (Section 3.5), because there's an image file in there you'll need for this tutorial. Then follow these steps:

In the Database window, click Forms in the Objects bar, and then double-click "Create form in Design view."

Access displays the Design window as shown in Figure 3-12. To set up your workspace, double-click the Design window's title bar (it says Form1:Form) to maximize the window. If the property sheet doesn't appear, choose View → Properties to display it. In the next step, you'll define the boundaries of the form itself.
Drag the bottom border of the work area down to three inches on the vertical ruler. Then drag the right border of the work area out to six inches on the horizontal ruler.

To this blank canvas, you need to add the individual parts that make up a form. In this example, you'll add a header and footer.
Choose View → Form Header/Footer to add header and footer sections to the form.

Notice that your form now has three sections: Form Header, Detail, and Form Footer. The Detail section is where you put most of the form content, like fields and labels. Use the Form Header section for logos, form titles, and other material you want at the top of your form. Use the Form Footer section for material you want at the bottom of your form.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Why Relate?
A Relational Database Tour
Linking Tables in the Relationships Window
Using Subdatasheets to View Related Items
Creating Subforms

It's time to learn the most powerful technology of all: the very heart of Access and most other modern database managers. So far, this book has dealt only with flat databases—those with only one table, or with several tables that aren't linked in any way. Each record in a flat database can hold bits of information (fields) about an individual item, and your forms, searches, and sorts are limited to shifting that information around in various ways. Flat databases are perfectly good for some purposes, as explained in the box on Section 4.1.

Ultimately, however, flat databases have a severe limitation: They can't understand how different masses of information are somehow related to each other the way you can. For example, a Customer database, if it has just that one table, can store information on thousands and thousands of customers and perform sorts and reports on it in seconds. Same thing with tables for Orders and Products. But say you have a nationwide business where you want to increase your sales by targeted marketing for each region of the country. You need the answer to a broader question like, "What do my customers on the West Coast purchase differently from my customers on the East Coast?" You can see the connection between your Customers' state of residence, the products they buy, and the total amounts ordered in each state, but you need your database to go through all those records and come up with the answer for you.

The problem is, you know the information is in there somewhere, but you have no idea how to tell your database to total up the orders (from the Orders table) for each product (in the Products table) and sort them by state (from the Customers table). Well, that's exactly what you'll learn how to do in this chapter. In Access, it's not hard at all to create the connections—known as

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Relational databases have all the advantages of nonlinked databases and none of their disadvantages. Just as in a flat database, you can keep all your information in one file. But unlike a flat database, you can add more records without changing your table designs, you don't risk errors or inconsistencies, and you don't waste disk space with lots of empty columns.

In relational databases, you split up different kinds of information into separate tables. Next, you relate the tables, two at a time, by telling Access about a field that's the same in both tables. The shared field lets Access pick out which records in one table go with which records in the other table. For example, the CustomerID field you've seen in the Customers table (Section 2.2.3) serves as a link between each customer and the orders he or she places. Since they're unique and unchanging, primary key fields are often also shared fields.

•You need to store only one kind of data. For example, if you want to keep only names and addresses, the titles of movies you have on DVD, a list of your family members' birthdays, or the dog's feeding schedule.
•Your information doesn't change much, or it changes at the same time. If Sally gets a new phone number or (in a momentary lapse of good taste) you buy a DVD of the movie Glitter, you can easily go into your table and make the change. In a flat database, nothing else depends on the data you change, so there's no danger that you'll make your data inconsistent.
•You don't need to combine different kinds of information. You'll never need a report that combines your family members' birthdays with the names of DVD movies you own and the dog's feeding schedule. Therefore, you can use nonlinked tables for all that data.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Download (Section 3.5) and open the SpiesAndStuff_End database to walk through a preview of how a relational database works. By the end of this chapter, you'll know how to create all the features you see in this database. After you've opened the SpiesAndStuff_End database, choose Tools → Relationships to inspect how it links tables together (see Figure 4-1).

Figure 4-1: The Relationships window shows you not only which tables are linked, but how they're linked. The link lines have "1" at one end and an infinity sign (a side-ways "8") at the other end. That means the link is a one-to-many relationship, as discussed in the box on Section 4.2.1.

This database contains three tables:

The Spies table lists each spy's secret ID number as well as his or her first and last names.
The EquipmentIssued table lists the date you issued the equipment, the secret ID number of the spy to whom you issued it, the secret inventory ID number of the equipment, and whether or not the spy returned it intact.
The SpyEquipment table lists the secret inventory ID number and the description of each piece of equipment.

The Relationships window shows that the Spies table is linked to the EquipmentIssued table in a one-to-many relationship, meaning that you can issue equipment to each spy as many times as needed. Likewise, the SpyEquipment table is linked to the EquipmentIssued table in a one-to-many relationship, meaning that you can issue each piece of equipment as many times as needed. For more information about the different types of relationship, see the box below.

Records in two tables can relate to each other in three possible ways:

•One-to-one. Each record in one table (A) corresponds to exactly one record in the other table (B). In Access, one-to-one relationships work fine, but they're pretty rare. Just about the only reason to have a one-to-one relationship is to save disk space: You can have a separate table holding large image files, with a one-to-one relationship to the table that has the image's filename, description, and thumbnail. That way, you can take just the thumbnail table on your laptop, and leave the big image file at home.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Your first step in creating a linked-table database is, unsurprisingly, to set up some links. Download (from the "Missing CD" page described on Section 3.5) and open the SpiesAndStuff_Start database.

The tutorial on the following pages shows you how to create relationships between existing tables in this database. You can work with any database of your own as long as:

Your database has more than one table, and you've thought through which records you wish to link and why. For example, you need to connect customers to orders so that you can show customer information on an order form. Review Chapter 2 for advice on organizing your tables.
You've sketched out (on paper or on screen) your one-to-many relationships. Review the box on Section 4.2.1 if you're not sure which records to link.
Each table that you want to link has a common field with the table to which you link it. The common field should be a unique value like SpyID or EquipmentID. (See Section 2.2.3 for a refresher on primary keys.)

After you've done your planning, your next step is to add the tables to the Relationships window where you can create the links. To add tables to the Relationships window:

Choose Tools → Relationships to open the Relationships window. Then choose Relationships → Show Table.

Access displays the Show Table dialog box (see Figure 4-5).
In the dialog box, click Spies, and then click the Add button.

Access adds the table to the Relationships window.
Using the same method, add the EquipmentIssued and SpyEquipment tables to the Relationships window.

You can also add a table to the Relationships window by double-clicking the table name in the Show Table dialog box.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

One of the reasons Access is so flexible is that it lets you create subdatasheets to link tables together in multiple ways. Subdatasheets are also what let you create forms within forms (see the box on Section 4.5). Are you ready for the really good news about subdatasheets? Here's what you have to do to create them: Nothing. Nada. N

Table of Contents