Microsoft Office Access 2007 Forms, Reports, and Queries

by McFedries, Paul

• ISBN13: 9780789736697
• ISBN10: 0789736691
• Edition: 1st
• Format: Paperback
• Copyright: 2007-05-01
• Publisher: Que Publishing

Summary

Shows the reader how to create user-friendly forms, striking reports, and powerful queries in Microsoft Access 2007.

Author Biography

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

Table of Contents

Introduction	p. 1
What's in the Book	p. 2
This Book's Special Features	p. 2
The Examples Used in the Book	p. 3
Creating Forms
Creating and Using a Form	p. 7
Building a Basic Form	p. 9
Building a Standard Form	p. 9
Building a Split Form	p. 11
Building a Multiple Items Form	p. 11
Creating Simple Forms with the Form Wizard	p. 12
Navigating a Form	p. 14
Creating a Form in Design View	p. 15
Displaying the Design View	p. 15
Changing the Record Source	p. 15
Understanding Form Controls	p. 16
Adding Fields to the Form	p. 17
Changing the Size of the Form	p. 18
Viewing the Form	p. 18
Assigning an AutoFormat in Design View	p. 19
Working with Form Properties	p. 20
Working with the Form Header and Footer	p. 20
Adding a Logo	p. 22
Adding a Title	p. 23
Formatting the Background	p. 23
Creating a Form Interactively in Layout View	p. 25
Case Study: Protecting the Form and Data from Other Users	p. 26
From Here	p. 27
Working with Form Controls	p. 29
Manipulating Form Controls	p. 29
Inserting Controls on a Form	p. 30
Selecting Controls	p. 30
Formatting Controls	p. 31
Adding Conditional Formatting	p. 31
Sizing Controls	p. 33
Moving Controls	p. 34
Creating a Control Layout	p. 36
Working with Control Margins	p. 38
Grouping Controls	p. 38
Ordering Overlapped Controls	p. 39
Converting an Unbound Control to a Bound Control	p. 39
Changing a Control's Type	p. 40
Setting the Tab Order	p. 40
Adding Labels to the Form	p. 41
Inserting a Label	p. 41
Editing the Label Caption	p. 42
Using Labels to Create Keyboard Shortcuts for Controls	p. 42
Adding Text Boxes to the Form	p. 43
Inserting a Text Box	p. 43
Using Text Boxes as Calculated Controls	p. 44
Case Study: Creating a Mortgage Calculator	p. 46
From Here	p. 48
Designing Forms for Efficient and Accurate Data Entry	p. 49
Preventing Errors by Validating Data	p. 50
Helping Users with Text Prompts	p. 50
Preventing Errors with Data Validation Expressions	p. 51
Using Input Masks for Consistent and Accurate Data Entry	p. 52
Using Controls to Limit Data Entry Choices	p. 55
Working with Yes/No Fields	p. 56
Using Option Buttons to Present a Limited Number of Choices	p. 59
Case Study: Using an Option Group to Select the Shipper	p. 61
Using Lists to Present a Large Number of Choices	p. 62
Entering Data with ActiveX Controls	p. 67
Entering Numbers Using a Spin Button	p. 68
Entering Numbers Using a Scrollbar	p. 69
Entering Dates Using a Calendar	p. 71
Collecting Form Data via Email	p. 72
Sending the Access Data Collection Email Message	p. 72
Replying to an Access Data Collection Email Message	p. 74
Managing the Access Data Collection Replies	p. 75
From Here	p. 75
Designing Forms for Business Use	p. 77
Using Forms in a Business Context	p. 77
Why Collect the Data?	p. 78
What Is the Data?	p. 78
Who Are Your Users?	p. 78
Ten Design Guidelines for Business Forms	p. 79
Make Forms Fast	p. 79
Make Forms Foolproof	p. 79
Mimic Paper Forms When Practical	p. 79
Give Users What They Need and Then Stop	p. 79
Don't Neglect the Keyboard	p. 80
Watch the Field Order (and the Tab Order, Too)	p. 80
Watch Your Screen Resolution	p. 80
Make Form Text Readable	p. 80
Go Easy on the Extras	p. 81
Organize Your Form Controls	p. 81
Organizing Controls on the Form	p. 81
Making Good Use of Lines and Rectangles	p. 82
Organizing with Option Groups	p. 83
Organizing with a Tab Control	p. 84
Enhancing Form Text	p. 86
Formatting Text	p. 87
Text Formatting Tips and Guidelines	p. 87
Applying Fancier Form Formatting	p. 88
Working with Colors	p. 88
Adding Images to Your Forms	p. 91
Creating a Shadow Effect for Text	p. 92
From Here	p. 93
Creating Specialized Forms	p. 95
Creating a Multiple-Table Form	p. 95
Understanding Subforms	p. 95
Creating a Form and Subform with the Form Wizard	p. 95
Creating a Subform in the Form Design View	p. 98
Working with Form Command Buttons	p. 99
Case Study: Creating a Switchboard Form	p. 102
Creating a Form Pop-Up Box or Dialog Box	p. 103
Creating a Pop-Up Form	p. 103
Creating a Modal Form	p. 104
Using a Custom Form with a Parameter Query	p. 105
Creating the Custom Form	p. 105
Adjusting the Parameter Query	p. 106
Using the Custom Form and Parameter Query	p. 107
Creating a Startup Form	p. 107
Creating a PivotChart Form	p. 108
From Here	p. 110
Designing and Customizing Reports
Creating and Publishing a Report	p. 113
Creating a Basic Report	p. 113
Creating Simple Reports with the Report Wizard	p. 114
Creating a Report in Design View	p. 116
Displaying the Design View	p. 116
Changing the Record Source	p. 117
Understanding the Architecture of Access Reports	p. 118
Understanding Report Controls	p. 119
Adding Fields to the Report	p. 120
Adding Labels to the Report	p. 120
Adding a Logo	p. 121
Adding a Title	p. 122
Adding Page Numbers to the Report	p. 122
Adding the Date and Time to the Report	p. 123
Changing the Size of a Report Section	p. 124
Previewing the Report	p. 124
Assigning an AutoFormat in Design View	p. 125
Working with Report Properties	p. 126
Formatting the Background	p. 126
Manipulating Report Controls	p. 128
Creating a Report Interactively in Layout View	p. 136
Publishing a Report	p. 137
Publishing on Paper	p. 137
Publishing to Email	p. 138
Exporting to Word	p. 138
Exporting to PDF or XPS	p. 139
From Here	p. 140
Designing Effective Business Reports	p. 141
Using Reports in Business	p. 141
What's in the Report?	p. 142
What Is the Goal of the Report?	p. 142
Who Are Your Readers?	p. 144
Ten Design Guidelines for Business Reports	p. 145
Copy Legacy Reports When Practical	p. 145
Give Users What They Need, Then Stop: Part 1	p. 145
Give Users What They Need, Then Stop: Part 2	p. 145
Use Page Numbers	p. 146
Use Dates and Times	p. 146
Watch the Field Order	p. 146
Watch Your Screen Resolution	p. 146
Make Report Text Readable	p. 147
Always Sort and/or Group Data	p. 147
Organize the Report Layout	p. 147
Organizing Controls on the Report	p. 147
Making Good Use of Lines and Rectangles	p. 148
Creating Page Breaks	p. 149
Enhancing Report Text	p. 150
Formatting Text	p. 150
Text Formatting Tips and Guidelines	p. 151
Applying Fancier Report Formatting	p. 152
Working with Colors	p. 152
Adding Images to Your Reports	p. 153
Adding Special Effects	p. 154
Creating a Shadow Effect for Text	p. 154
From Here	p. 155
Designing Advanced Reports	p. 157
Sorting and Grouping a Report	p. 157
Setting Up Sorting Options	p. 158
Setting Up Grouping Options	p. 158
Sorting and Grouping Using an Expression	p. 161
Adding Calculations to a Report	p. 161
Using the Totals List	p. 162
Inserting a Text Box	p. 162
Using Text Boxes as Calculated Controls	p. 163
Case Study: Creating an Invoice Report	p. 165
Using Advanced Methods to Launching a Report	p. 167
Launching a Report with a Command Button	p. 167
Launching a Report with a Macro	p. 169
Controlling Report Output	p. 174
Adding Page Breaks After Sections	p. 174
Starting Sections at the Top of a Row or Column	p. 174
Avoiding Widowed Records	p. 175
From Here	p. 175
Creating Specialized Reports	p. 177
Creating a Multiple-Column Report	p. 177
Setting Up the Report	p. 178
Tweaking the Page Setup	p. 178
Troubleshooting Multiple Columns	p. 180
Case Study: Using Multiple Columns to Reduce Report Page Count	p. 181
Creating Mailing Labels	p. 184
Running the Label Wizard	p. 185
Creating a Custom Label	p. 187
Creating a Mail Merge Report	p. 187
Creating a Multiple-Table Report	p. 189
Understanding Subreports	p. 190
Creating a Report and Subreport with the Report Wizard	p. 191
Creating a Subreport in the Report Design View	p. 192
Creating a PivotChart Report	p. 194
From Here	p. 195
Creating Powerful Queries
Creating a Basic Query	p. 199
Sorting Records	p. 199
Sorting on a Single Field	p. 200
Sorting on Multiple Fields	p. 200
Filtering Table Data	p. 201
Filtering by Selection	p. 203
Filtering Excluding Selection	p. 203
Filtering in Place	p. 204
Applying Text, Numeric, and Date Filters	p. 204
Filtering by Form	p. 206
Learning About Filter Criteria	p. 207
Creating a Filter	p. 208
Working with Queries	p. 210
Creating a Query	p. 210
Creating a New Query Object	p. 211
Selecting the Fields to Include in the Query	p. 212
Entering the Query Criteria	p. 213
Excluding a Field from the Query Results	p. 213
Returning Only the Top N Values	p. 213
Setting Field Properties	p. 214
Running the Query	p. 215
Querying Notes for Business Users	p. 216
Case Study: Querying for a Mail Merge	p. 217
Querying the Customers Table	p. 218
Running the Mail Merge	p. 219
Creating Queries with the Query Wizards	p. 220
Creating Crosstab Queries	p. 220
Creating Find Duplicates Queries	p. 222
Setting Up a Find Unmatched Query	p. 222
Working with a Query Dynaset	p. 223
Understanding the Datasheet View	p. 223
Navigating Fields	p. 224
Entering Data	p. 224
Adding More Records	p. 225
Navigating Records	p. 225
Selecting a Record	p. 226
Copying a Record	p. 226
Deleting a Record	p. 227
Formatting the Datasheet	p. 227
Working with Query Properties	p. 228
From Here	p. 229
Building Criteria Expressions	p. 231
Using Operands in Criteria Expressions	p. 232
Literals	p. 232
Identifiers	p. 232
Functions	p. 233
Using Operators in Criteria Expressions	p. 233
Comparison Operators	p. 233
Arithmetic Operators	p. 234
The Like Operator	p. 235
The Between...And Operator	p. 235
The In Operator	p. 235
The Is Null Operator	p. 235
Compound Criteria and the Logical Operators	p. 236
Using the Logical Operators	p. 237
Understanding Operator Precedence	p. 238
Setting Up a Calculated Column	p. 239
Calculating Inventory Value	p. 240
Calculating Discounted Product Totals	p. 241
Using the Built-In Functions	p. 241
Using Text Functions	p. 243
Using Date and Time Functions	p. 246
Using Math Functions	p. 251
Using Financial Functions	p. 253
Working with the Expression Builder	p. 256
From Here	p. 257
Working with Multiple-Table Queries	p. 259
Relational Database Fundamentals	p. 259
The Pitfalls of a Nonrelational Design	p. 259
How a Relational Design Can Help	p. 262
Types of Relational Models	p. 264
The One-to-Many Model	p. 264
The One-to-One Model	p. 265
The Many-to-Many Model	p. 265
Enforcing Referential Integrity	p. 266
Establishing Table Relationships	p. 267
Understanding Join Lines	p. 267
Identifying Join Types	p. 268
Adding Tables to the Relationships Window	p. 269
Joining Tables	p. 269
Editing a Relationship	p. 271
Removing a Join	p. 271
Working with Multiple Tables in a Query	p. 271
Adding Multiple Tables to a Query	p. 271
Adding Fields from Multiple Tables	p. 272
Nesting Queries Within Queries	p. 273
Joining Tables Within the Query Design Window	p. 274
Creating Other Types of Joins	p. 275
Creating Outer Joins	p. 275
Creating Self-Joins	p. 278
Creating Theta Joins	p. 279
Creating a Unique Values Query	p. 280
Case Study: Drilling Down to the Order Details	p. 282
Adding a Subdatasheet to a Query	p. 284
Working with Query Subdatasheets	p. 284
From Here	p. 286
Creating Advanced Queries	p. 287
Creating a Totals Query	p. 287
Displaying the Total Row in the Design Grid	p. 288
Setting Up a Totals Query on a Single Field	p. 289
Setting Up a Totals Query on Multiple Fields	p. 289
Filtering the Records Before Calculating Totals	p. 290
Creating a Totals Query for Groups of Records	p. 291
Grouping on Multiple Fields	p. 291
Creating a Totals Query Using a Calculated Field	p. 293
Creating a Totals Query Using Aggregate Functions	p. 294
Combining Aggregate Functions and Totals	p. 295
Creating Queries That Make Decisions	p. 296
Making Decisions with the IIf Function	p. 297
Making Decisions with the Switch Function	p. 299
Case Study: Calculating a Customer Discount Rate	p. 300
Calculating a Simple Discount Rate	p. 300
Calculating a Complex Discount Rate	p. 301
Running Parameter Queries	p. 302
Creating a Simple Query Parameter	p. 302
Specifying the Parameter Data Type	p. 304
Running Action Queries	p. 304
Modifying Table Data with an Update Query	p. 304
Removing Records from a Table with a Delete Query	p. 306
Creating New Tables with Make-Table Queries	p. 307
Adding Records to a Table with an Append Query	p. 309
From Here	p. 309
Creating PivotTable Queries	p. 311
What Is a PivotTable?	p. 311
How PivotTables Work	p. 312
Some PivotTable Terms	p. 313
Creating a One-Dimensional PivotTable	p. 314
Display Data Field Details	p. 314
Displaying the Sum of the Data Field Values	p. 316
Hiding and Showing the Data Details	p. 318
Inserting an AutoCalc Data Field Summary Calculation	p. 318
Changing the AutoCalc Calculation Type	p. 320
Creating a Calculated Field	p. 321
Removing a PivotTable Field	p. 322
Creating a Multiple-Field One-Dimensional PivotTable	p. 322
Creating a Two-Dimensional PivotTable	p. 323
Analyzing Customer Orders by Product Category	p. 324
Adding a Temporal Dimension to the PivotTable	p. 326
Filtering a PivotTable	p. 332
Using the PivotTable AutoFilters	p. 332
Displaying Only the Top or Bottom Items	p. 333
Grouping Field Items	p. 335
Adding a Filter Field	p. 335
Pivoting a PivotTable	p. 336
Moving a Field to a Different Area	p. 337
Changing the Field Order	p. 338
Formatting a PivotTable	p. 339
From Here	p. 339
Querying with SQL Statements	p. 341
Viewing the SQL Statement	p. 341
Using SQL to Perform a Select Query	p. 342
Understanding the SELECT Statement	p. 343
Using SQL with Multiple-Table Queries	p. 346
Adding a Calculated Column to the SELECT Statement	p. 349
Using SQL to Total and Group Records	p. 350
Using SQL to Set Up a Parameter Query	p. 350
The Full SQL SELECT Syntax	p. 351
Using SQL to Perform Action Queries	p. 351
Using SQL to Perform an Update Query	p. 352
Using SQL to Perform a Delete Query	p. 352
Using SQL to Perform a Make-Table Query	p. 353
Using SQL to Perform an Append Query	p. 353
Using SQL to Create Subqueries	p. 354
Using a Subquery to Define a Field	p. 355
Determining Whether a Unit Price Is Greater Than the Average	p. 355
Using a Subquery to Define Criteria for a Field	p. 356
Using Subqueries That Return Dynasets	p. 356
In Predicate: Customers Who Have Placed Orders	p. 357
All Predicate: Products Cheaper Than All the Condiments	p. 358
Using SQL to Create Union Queries	p. 359
From Here	p. 360
Index	p. 361
Table of Contents provided by Ingram. All Rights Reserved.