What Topics Does (and Doesn t) This Book Cover?

What Topics Does (and Doesn't) This Book Cover?

This book emphasizes understanding how Access works. It does not include many topics (some of them important) that the more encyclopedic Access texts contain. I have mostly ignored subjects such as customized toolbars and changing fonts, not because these are not useful, but because they don't pose any conceptual or theoretical issues. There is little on topics that are mostly of use to specialized audiences, such as data access pages. And recognizing the admonishment that "a little knowledge is a dangerous thing," I have almost nothing to say about security issues, such as protecting your data from malevolent forces.

More questionably, the book also does not touch upon the computer language Visual Basic for Applications (VBA), despite its centrality to doing advanced work in Access. VBA gets very complicated very quickly. I wanted to avoid content that, in effect, says, "You don't have to understand thisjust type this code here, and it will work." In every topic discussed, I want you to understand fully what you're doing and why you're doing it.

This commitment is particularly applicable to Access wizards. Creating objects in Access often involves many steps, and the program provides numerous wizards to produce them. The wizards are effective and efficient, and usually (although not always) easy to use; however, they only intermittently enhance the user's understanding of relational databases and Access. They can become a crutch, and users who rely too heavily on them will often be at a loss when they need to make even minor modifications to the wizard's output. I want you to use the wizards, but I also want you to know what the wizards are actually doing.

Here is a chapter-by-chapter breakdown of the book's contents:

Chapter 1, "Getting Started," introduces basic database terminology, such as table, field, and value. It describes the various data types and tackles the issue of null values and zero-length strings.

Chapter 2, "Database Design," is devoted to database theory. It explains the shortcomings of nonrelational database models for storing data of some size and consequence. It discusses basic relational database design principles. It takes you from the start of the database creation process (developing a mission statement for the database) to the initial distribution of fields into tables. The essential topic of primary keys and their use in resolving multivalue fields is discussed at length. To illustrate the issues posed in the chapter, I introduce the Classic TV database, which contains data about hit TV shows from the past. I also utilize a traditional order-entry model, like Northwind, to help make some of the points.

I continue to develop the Classic TV database in Chapter 3, "Understanding Relationships," where the focus is on relationships. One-to-many, many-to-many, and one-to-one relationships are analyzed, and numerous examples of each relationship type are offered. You learn how to use a linking table to resolve many-to-many relationships. At the end of the chapter, I describe and simplify the first three rules of the normalization process for relational databases.

Chapter 4, "Establishing Relationships," teaches you how to formally establish in Access the relationships you learned in Chapter 3. I discuss the system of relationship rules known as referential integrity, including two options that mitigate these restrictions: Cascade Update Related Fields and Cascade Delete Related Records.

Tables are the subject of Chapter 5, "Building Tables." It begins with a discussion of lookup fields, a confusing and minor topic that nonetheless serves as an excellent vehicle for reviewing the database design principles covered in Chapters 14 and gives you insight into the true nature of tables. The chapter then looks at the various methods of table creation. Most of the discussion, however, focuses on field propertiesField Size, Format, Validation Rule, and so onwhich have an extraordinary impact on your database. Table properties are also considered.

Data entry is the focus of Chapter 6, "Entering, Editing, and Displaying Data." Forms are introduced and distinguished from tables. The tricky topic of how controls in forms inherit (and disinherit) field properties assigned in tables is considered. Data-entry tools and features such as input masks and the Undo command are also described.

Chapter 7, "Find and Filter," begins a section of three chapters devoted to finding and retrieving data from your database. In this initial chapter, the Find command is dissected, and the varying filter methods are discussed at length. AND and OR criteria, and related topics such as expressions and operators, are described and examples are offered.

Chapter 8, "Queries," introduces queries, perhaps the most interesting and enjoyable topic in relational databases. You'll learn about calculated fields and the Top Values property. The topic of multitable queries is particularly important because these queries reveal the true power and elegance of a relational database. Joins are introduced, and inner joins are distinguished from outer joins. Structured Query Language (SQL) is discussed, if only briefly; it's sufficient, however, to demystify the topic.

Chapter 9 has the unimaginative title of "Queries, Part II." It builds on the knowledge you acquired in Chapter 8, but the queries are not all that advanced, nor are they particularly difficult to use. These include parameter, total, crosstab, and action queries; you'll find them extremely useful.

Chapter 10, "Reports," is dedicated to giving you a more in-depth understanding of reports. Although you can create a fairly sophisticated report using a wizard in a few seconds, understanding what you created or how it was accomplished is another matter entirely. To fill the gap, I take you through the entire process of creating a report in Design viewmanually, step by excruciating step, with no help from a wizard. At the end of the chapter, you create a report with the same specifications using a wizard and then compare the results, to demonstrate how each element of the wizard's output would be accomplished manually. Armed with this knowledge, you will be able to edit and add to wizard-created reports, which inevitably you will need to do.

What Chapter 10 did for reports, Chapter 11, "Forms/Subforms," accomplishes for forms. The various elements of formscombo boxes, subforms, option groups, and so onare discussed in detail, and you have the opportunity to add them to an existing form. At chapter's end, you'll use a wizard to create a form with the same specifications. As in Chapter 10, I compare the wizard's output with the object created by hand to note differences. Again, the aim is to help you understand what the wizard accomplishes automatically and how you can modify the results.

Chapter 12, "Form/Report Design Elements," covers a multitude of topics on report and form creation that were not covered in Chapters 10 and 11. The approach these chapters took was useful for deconstructing wizards, but there was a major drawback: Many useful report-/form-creation features were glossed over or simply were not covered. This chapter seeks to make amends and fill in the gaps. The result is a discussion that ranges from bound and unbound controls to linking and embedding, from conditional formatting to special effects, from macros to snaked column reports. It is a hodge-podge, but not an unruly one.

Chapter 13, "Importing and Exporting," is all about getting data into Access from external sources, and its opposite, putting Access data into external environments. I show various techniques that can accomplish these transfers: export and import wizards, but also cut/copy/paste as well as drag-and-drop. Because this is an Access book, the focus is on using Access features to move data. But I haven't been draconian in excluding non-Access features, especially when they accomplish the data transfers more successfully than Access tools. For example, I describe Microsoft Word's mail-merge feature at length because you are far more likely to begin and execute mail merges in Word.

Pivot tables are the topic of Chapter 14, "Pivot Tables and Pivot Charts." Although crosstab queries have their uses for slicing and splicing data, pivot tables are a far more powerful and effective tool for data analysis. In earlier versions of Access, pivot tables relied heavily on Microsoft Excel for their execution. But pivot tables are now fully and powerfully integrated in Access. You will have to endure some learning curve to use them effectively, but the investment should yield a high return.