Microsoft Office Access 2007 Forms, Reports, and Queries

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