"Everything you need to master Access 2007 forms, reports, and queries."
-Charles Carr, Reviews Editor, ComputorEdge Magazine
Create Forms for Business
Ensure Data Entry Accuracy
Build Elegant Form Interfaces
Collect Data Via Email
Design Effective Business Reports
Make an Invoice Report
Create Mailing Labels
Extract Data
Work with Multiple Tables
Calculate Discounts
Analyze Data
Develop your Microsoft Access expertise instantly with proven techniques
Let's face it: Microsoft Access is a large, intimidating program. Most people never progress beyond creating simple tables and using wizards to build basic forms and reports. At the same time, you need information and you know that what you seek is embedded somewhere in your Access database. Without a more sophisticated knowledge of how to extract and present that data, you're forced to rely on office gurus and overworked IT people to provide canned reports or one-size-fits-all solutions.
This book changes all that by giving you the skills to build efficient front-ends for data (forms), publish the results in an attractive and easy-to-read format (reports), and extract the data you need (queries). This book shuns the big Access picture and instead focuses intently on forms, reports, and queries. This in-depth approach will give you the knowledge and understanding you need to get at the data and prove the old saw that knowledge is power.
? Focuses on the three technologies that you must master to get the most out of Access: forms, reports, and queries.
? Avoids database theory in favor of practical know-how that you can put to use right away.
? Packed full of real-world examples and techniques to help you learn and understand the importance of each section.
? Covers what's new and changed in Microsoft Access 2007.
Introduction
Part I: Creating Forms
Chapter 1 Creating and Using a Form
Chapter 2 Working with Form Controls
Chapter 3 Designing Forms for Efficient and Accurate Data Entry
Chapter 4 Designing Forms for Business Use
Chapter 5 Creating Specialized Forms
Part II: Designing and Customizing Reports
Chapter 6 Creating and Publishing a Report
Chapter 7 Designing Effective Business Reports
Chapter 8 Designing Advanced Reports
Chapter 9 Creating Specialized Reports
Part III: Creating Powerful Queries
Chapter 10 Creating a Basic Query
Chapter 11 Building Criteria Expressions
Chapter 12 Working with Multiple-Table Queries
Chapter 13 Creating Advanced Queries
Chapter 14 Creating PivotTable Queries
Chapter 15 Querying with SQL Statements
Index
Sprache
Verlagsort
Verlagsgruppe
Zielgruppe
Für höhere Schule und Studium
Maße
Höhe: 239 mm
Breite: 166 mm
Dicke: 23 mm
Gewicht
ISBN-13
978-0-7897-3669-7 (9780789736697)
Schweitzer Klassifikation
Paul McFedries is the president of Logophilia Limited, a technical writing company. Now primarily a writer, Paul is well known as a teacher of Microsoft Office and Microsoft Windows, and has worked as a programmer, consultant, database developer, and website developer. He has written more than 50 books that have sold more than three million copies worldwide. These books include Tricks of the Microsoft Office 2007 Gurus (Que, 2007), Formulas and Functions with Microsoft Excel 2007 (Que, 2007), VBA for the 2007 Microsoft Office System (Que, 2007), and Windows Vista Unleashed (Sams, 2006).
Contents
Introduction 1
What's in the Book 2
This Bookis Special Features 2
The Examples Used in the Book 3
I CREATING FORMS
1 Creating and Using a Form 7
Building a Basic Form 9
Building a Standard Form 9
Building a Split Form 11
Building a Multiple Items Form 11
Creating Simple Forms with the Form Wizard 12
Navigating a Form 14
Creating a Form in Design View 15
Displaying the Design View 15
Changing the Record Source 15
Understanding Form Controls 16
Adding Fields to the Form 17
Changing the Size of the Form 18
Viewing the Form 18
Assigning an AutoFormat in Design View 19
Working with Form Properties 20
Working with the Form Header and Footer 20
Adding a Logo 22
Adding a Title 23
Formatting the Background 23
Creating a Form Interactively in Layout View 25
Case Study: Protecting the Form and Data from Other Users 26
From Here 27
2 Working with Form Controls 29
Manipulating Form Controls 29
Inserting Controls on a Form 30
Selecting Controls 30
Formatting Controls 31
Adding Conditional Formatting 31
Sizing Controls 33
Moving Controls 34
Creating a Control Layout 36
Working with Control Margins 38
Grouping Controls 38
Ordering Overlapped Controls 39
Converting an Unbound Control to a Bound Control 39
Changing a Controlis Type 40
Setting the Tab Order 40
Adding Labels to the Form 41
Inserting a Label 41
Editing the Label Caption 42
Using Labels to Create Keyboard Shortcuts for Controls 42
Adding Text Boxes to the Form 43
Inserting a Text Box 43
Using Text Boxes as Calculated Controls 44
Case Study: Creating a Mortgage Calculator 46
From Here 48
3 Designing Forms for Efficient and Accurate Data Entry 49
Preventing Errors by Validating Data 50
Helping Users with Text Prompts 50
Preventing Errors with Data Validation Expressions 51
Using Input Masks for Consistent and Accurate Data Entry 52
Using Controls to Limit Data Entry Choices 55
Working with Yes/No Fields 56
Using Option Buttons to Present a Limited Number of Choices 59
Case Study: Using an Option Group to Select the Shipper 61
Using Lists to Present a Large Number of Choices 62
Entering Data with ActiveX Controls 67
Entering Numbers Using a Spin Button 68
Entering Numbers Using a Scrollbar 69
Entering Dates Using a Calendar 71
Collecting Form Data via Email 72
Sending the Access Data Collection Email Message 72
Replying to an Access Data Collection Email Message 74
Managing the Access Data Collection Replies 75
From Here 75
4 Designing Forms for Business Use 77
Using Forms in a Business Context 77
Why Collect the Data? 78
What Is the Data? 78
Who Are Your Users? 78
Ten Design Guidelines for Business Forms 79
1. Make Forms Fast 79
2. Make Forms Foolproof 79
3. Mimic Paper Forms When Practical 79
4. Give Users What They Need and Then Stop 79
5. Donit Neglect the Keyboard 80
6. Watch the Field Order (and the Tab Order, Too) 80
7. Watch Your Screen Resolution 80
8. Make Form Text Readable 80
9. Go Easy on the Extras 81
10. Organize Your Form Controls 81
Organizing Controls on the Form 81
Making Good Use of Lines and Rectangles 82
Organizing with Option Groups 83
Organizing with a Tab Control 84
Enhancing Form Text 86
Formatting Text 87
Text Formatting Tips and Guidelines 87
Applying Fancier Form Formatting 88
Working with Colors 88
Adding Images to Your Forms 91
Creating a Shadow Effect for Text 92
From Here 93
5 Creating Specialized Forms 95
Creating a Multiple-Table Form 95
Understanding Subforms 95
Creating a Form and Subform with the Form Wizard 96
Creating a Subform in the Form Design View 98
Working with Form Command Buttons 99
Case Study: Creating a Switchboard Form 102
Creating a Form Pop-Up Box or Dialog Box 103
Creating a Pop-Up Form 103
Creating a Modal Form 104
Using a Custom Form with a Parameter Query 105
Creating the Custom Form 105
Adjusting the Parameter Query 106
Using the Custom Form and Parameter Query 107
Creating a Startup Form 107
Creating a PivotChart Form 108
From Here 110
II DESIGNING AND CUSTOMIZING REPORTS
6 Creating and Publishing a Report 113
Creating a Basic Report 113
Creating Simple Reports with the Report Wizard 114
Creating a Report in Design View 116
Displaying the Design View 116
Changing the Record Source 117
Understanding the Architecture of Access Reports 118
Understanding Report Controls 119
Adding Fields to the Report 120
Adding Labels to the Report 120
Adding a Logo 121
Adding a Title 122
Adding Page Numbers to the Report 122
Adding the Date and Time to the Report 123
Changing the Size of a Report Section 124
Previewing the Report 124
Assigning an AutoFormat in Design View 125
Working with Report Properties 126
Formatting the Background 126
Manipulating Report Controls 128
Creating a Report Interactively in Layout View 136
Publishing a Report 137
Publishing on Paper 137
Publishing to Email 138
Exporting to Word 138
Exporting to PDF or XPS 139
From Here 140
7 Designing Effective Business Reports 141
Using Reports in Business 141
Whatis in the Report? 142
What Is the Goal of the Report? 142
Who Are Your Readers? 144
Ten Design Guidelines for Business Reports 145
1. Copy Legacy Reports When Practical 145
2. Give Users What They Need, Then Stop: Part 1 145
3. Give Users What They Need, Then Stop: Part 2 145
4. Use Page Numbers 146
5. Use Dates and Times 146
6. Watch the Field Order 146
7. Watch Your Screen Resolution 146
8. Make Report Text Readable 147
9. Always Sort and/or Group Data 147
10. Organize the Report Layout 147
Organizing Controls on the Report 147
Making Good Use of Lines and Rectangles 148
Creating Page Breaks 149
Enhancing Report Text 150
Formatting Text 150
Text Formatting Tips and Guidelines 151
Applying Fancier Report Formatting 152
Working with Colors 152
Adding Images to Your Reports 153
Adding Special Effects 154
Creating a Shadow Effect for Text 154
From Here 155
8 Designing Advanced Reports 157
Sorting and Grouping a Report 157
Setting Up Sorting Options 158
Setting Up Grouping Options 158
Sorting and Grouping Using an Expression 161
Adding Calculations to a Report 161
Using the Totals List 162
Inserting a Text Box 162
Using Text Boxes as Calculated Controls 163
Case Study: Creating an Invoice Report 165
Using Advanced Methods to Launching a Report 167
Launching a Report with a Command Button 167
Launching a Report with a Macro 169
Controlling Report Output 174
Adding Page Breaks After Sections 174
Starting Sections at the Top of a Row or Column 174
Avoiding Widowed Records 175
From Here 175
9 Creating Specialized Reports 177
Creating a Multiple-Column Report 177
Setting Up the Report 178
Tweaking the Page Setup 178
Troubleshooting Multiple Columns 180
Case Study: Using Multiple Columns to Reduce Report Page Count 181
Creating Mailing Labels 184
Running the Label Wizard 185
Creating a Custom Label 187
Creating a Mail Merge Report 187
Creating a Multiple-Table Report 189
Understanding Subreports 190
Creating a Report and Subreport with the Report Wizard 191
Creating a Subreport in the Report Design View 192
Creating a PivotChart Report 194
From Here 195
III CREATING POWERFUL QUERIES
10 Creating a Basic Query 199
Sorting Records 199
Sorting on a Single Field 200
Sorting on Multiple Fields 200
Filtering Table Data 201
Filtering by Selection 203
Filtering Excluding Selection 203
Filtering in Place 204
Applying Text, Numeric, and Date Filters 204
Filtering by Form 206
Learning About Filter Criteria 207
Creating a Filter 208
Working with Queries 210
Creating a Query 210
Creating a New Query Object 211
Selecting the Fields to Include in the Query 212
Entering the Query Criteria 213
Excluding a Field from the Query Results 213
Returning Only the Top N Values 213
Setting Field Properties 214
Running the Query 215
Querying Notes for Business Users 216
Case Study: Querying for a Mail Merge 217
Querying the Customers Table 218
Running the Mail Merge 219
Creating Queries with the Query Wizards 220
Creating Crosstab Queries 220
Creating Find Duplicates Queries 222
Setting Up a Find Unmatched Query 222
Working with a Query Dynaset 223
Understanding the Datasheet View 223
Navigating Fields 224
Entering Data 224
Adding More Records 225
Navigating Records 225
Selecting a Record 226
Copying a Record 226
Deleting a Record 227
Formatting the Datasheet 227
Working with Query Properties 228
From Here 229
11 Building Criteria Expressions 231
Using Operands in Criteria Expressions 232
Literals 232
Identifiers 232
Functions 233
Using Operators in Criteria Expressions 233
Comparison Operators 233
Arithmetic Operators 234
The Like Operator 235
The Between...And Operator 235
The In Operator 235
The Is Null Operator 235
Compound Criteria and the Logical Operators 236
Using the Logical Operators 237
Understanding Operator Precedence 238
Setting Up a Calculated Column 239
Calculating Inventory Value 240
Calculating Discounted Product Totals 241
Using the Built-In Functions 241
Using Text Functions 243
Using Date and Time Functions 246
Using Math Functions 251
Using Financial Functions 253
Working with the Expression Builder 256
From Here 257
12 Working with Multiple-Table Queries 259
Relational Database Fundamentals 259
The Pitfalls of a Nonrelational Design 259
How a Relational Design Can Help 262
Types of Relational Models 264
The One-to-Many Model 264
The One-to-One Model 265
The Many-to-Many Model 265
Enforcing Referential Integrity 266
Establishing Table Relationships 267
Understanding Join Lines 267
Identifying Join Types 268
Adding Tables to the Relationships Window 269
Joining Tables 269
Editing a Relationship 271
Removing a Join 271
Working with Multiple Tables in a Query 271
Adding Multiple Tables to a Query 271
Adding Fields from Multiple Tables 272
Nesting Queries Within Queries 273
Joining Tables Within the Query Design Window 274
Creating Other Types of Joins 275
Creating Outer Joins 275
Creating Self-Joins 278
Creating Theta Joins 279
Creating a Unique Values Query 280
Case Study: Drilling Down to the Order Details 282
Adding a Subdatasheet to a Query 284
Working with Query Subdatasheets 284
From Here 286
13 Creating Advanced Queries 287
Creating a Totals Query 287
Displaying the Total Row in the Design Grid 288
Setting Up a Totals Query on a Single Field 289
Setting Up a Totals Query on Multiple Fields 289
Filtering the Records Before Calculating Totals 290
Creating a Totals Query for Groups of Records 291
Grouping on Multiple Fields 291
Creating a Totals Query Using a Calculated Field 293
Creating a Totals Query Using Aggregate Functions 294
Combining Aggregate Functions and Totals 295
Creating Queries That Make Decisions 296
Making Decisions with the IIf Function 297
Making Decisions with the Switch Function 299
Case Study: Calculating a Customer Discount Rate 300
Calculating a Simple Discount Rate 300
Calculating a Complex Discount Rate 301
Running Parameter Queries 302
Creating a Simple Query Parameter 302
Specifying the Parameter Data Type 304
Running Action Queries 304
Modifying Table Data with an Update Query 304
Removing Records from a Table with a Delete Query 306
Creating New Tables with Make-Table Queries 307
Adding Records to a Table with an Append Query 309
From Here 309
14 Creating PivotTable Queries 311
What Is a PivotTable? 311
How PivotTables Work 312
Some PivotTable Terms 313
Creating a One-Dimensional PivotTable 314
Display Data Field Details 314
Displaying the Sum of the Data Field Values 316
Hiding and Showing the Data Details 318
Inserting an AutoCalc Data Field Summary Calculation 318
Changing the AutoCalc Calculation Type 320
Creating a Calculated Field 321
Removing a PivotTable Field 322
Creating a Multiple-Field One-Dimensional PivotTable 322
Creating a Two-Dimensional PivotTable 323
Analyzing Customer Orders by Product Category 324
Adding a Temporal Dimension to the PivotTable 326
Filtering a PivotTable 332
Using the PivotTable AutoFilters 332
Displaying Only the Top or Bottom Items 333
Grouping Field Items 335
Adding a Filter Field 335
Pivoting a PivotTable 336
Moving a Field to a Different Area 337
Changing the Field Order 338
Formatting a PivotTable 339
From Here 339
15 Querying with SQL Statements 341
Viewing the SQL Statement 341
Using SQL to Perform a Select Query 342
Understanding the SELECT Statement 343
Using SQL with Multiple-Table Queries 346
Adding a Calculated Column to the SELECT Statement 349
Using SQL to Total and Group Records 350
Using SQL to Set Up a Parameter Query 350
The Full SQL SELECT Syntax 351
Using SQL to Perform Action Queries 351
Using SQL to Perform an Update Query 352
Using SQL to Perform a Delete Query 352
Using SQL to Perform a Make-Table Query 353
Using SQL to Perform an Append Query 353
Using SQL to Create Subqueries 354
Using a Subquery to Define a Field 355
Determining Whether a Unit Price Is Greater Than the Average 355
Using a Subquery to Define Criteria for a Field 356
Using Subqueries That Return Dynasets 356
In Predicate: Customers Who Have Placed Orders 357
All Predicate: Products Cheaper Than All the Condiments 358
Using SQL to Create Union Queries 359
From Here 360
Index 361
