Microsoft Office Access 2007 Forms, Reports, and Queries

“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

Table of Contents:
Contents Introduction      1 What’s in the Book      2 This Bookís 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 Controlís 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. Donít 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 Whatís 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

About the Author :
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).