Access 2013 Absolute Beginner's Guide

Queries...53 Ordering the Query Result...56 Sorting on a Single Field...56 Sorting on More Than One Field...57 Working with Simple Criteria...57 Using an Exact Match Query...57 Creating Criteria Based on Multiple Conditions...58 Saving a Query...66 Closing a Query...66 5 Using Forms to Display and Modify Information...69 Moving from Record to Record in a Form...70 Undoing Changes Made Within a Form...71 Using a Form to Delete Records from a Table...73 Copying Records Within a Form...76 Finding a Record That Meets Specific Criteria...77 Sorting Records...79 Sort Records on a Single Field...80 Filtering the Data Underlying a Form...80 Use the Filter by Form Feature...80 Removing a Filter...81 Use Multiple Filter Criteria...81 Closing a Form...83 6 Creating Your Own Forms...85 The AutoForm Feature...86 Create a Form by Using the AutoForm Feature...86 Saving a Form...87 Using the Form Wizard to Build a Form...87 Creating Split Forms...88 Creating Multiple Item Forms...90 Creating Navigation Forms...91 Customizing a Navigation Form...93 Creating a Navigation Form with Horizontal and Vertical Tabs...95 7 Using Reports to Print Information...99 Opening and Viewing a Report...100 Preview a Report...100 Moving from Page to Page...100 Zooming In and Out ...101 Viewing Multiple Pages...102 Working in Layout View...103 Working in Report View...103 Printing a Report...104 Use Page Setup...104 Sending Reports to the Printer...105 Closing a Report...105 8 Building Your Own Reports... 109 Using the AutoReport Feature...110 Viewing the Design of a Report...113 Types of Reports Available...114 Detail Reports...114 Summary Reports...115 Reports with Graphics...116 Reports with Forms...116 Reports with Labels...117 Creating Mailing Labels...117 9 Creating Your Own Tables... 125 Building a New Table...126 Building a Table from a Datasheet...126 Designing a Table from Scratch...128 Selecting the Appropriate Field Type for Data...129 Short Text Fields: The Most Common Field Type...132 Long Text Fields: For Long Notes and Comments...132 Number Fields: For When You Need to Calculate...132 Date/Time Fields: For Tracking When Things Happened...133 Currency Fields: For Storing Money...133 AutoNumber Fields: For Unique Record Identifiers...133 Yes/No Fields: For When One of Two Answers Is Correct...134 OLE Object Fields: For Storing Just About Anything...134 Hyperlink Fields: For Linking to the Internet...134 Attachment Fields: For Storing File Attachments Associated with a Record...134 Calculated Fields: For Storing Your Database Calculations...135 Using Indexes to Improve Performance...135 Create an Index Based on a Single Field...136 Create an Index Based on Multiple Fields...137 The All-Important Primary Key...137 Working with Field Properties...138 The Field Size Property: Limiting What the User Enters into a Field...139 The Format Property: Determining How Access Displays Data...139 The Caption Property: Providing Alternatives to the Field Name...141 The Default Value Property: Saving Data-Entry Time...142 The Validation Rule and Validation Text Properties: Controlling What the User Enters in a Field...142 The Required Property: Making the User Enter a Value...144 The Allow Zero Length Property: Accommodating for Situations with Nonexistent Data...144 The Input Mask Property: Determining What Data Goes into a Field...145 The Lookup Wizard...147 10 Relating the Information in Your Database... 151 Introduction to Relational Database Design...152 The History of Relational Database Design...152 Goals of Relational Database Design...152 Rules of Relational Database Design...152 Normalization and Normal Forms...154 Denormalization: Purposely Violating the Rules...158 Integrity Rules...158 The Types of Relationships...159 One-to-Many Relationships...159 One-to-One Relationships...160 Many-to-Many Relationships...161 Establishing Relationships in Access...161 Following Guidelines for Establishing Relationships...162 Modifying an Existing Relationship...164 Establishing Referential Integrity...164 The Cascade Update Related Fields Option...166 The Cascade Delete Related Records Option...167 11 Enhancing the Queries That You Build... 171 Everything You Ever Needed to Know About Query Basics...172 Removing a Field from the Query Design Grid...172 Inserting a Field After a Query Is Built...172 Moving a Field to a Different Location on the Query Grid...173 Move More Than One Column...173 Updating Query Results...173 Update Results of a Query...174 Building Queries Based on Multiple Tables...174 Pitfalls of Multitable Queries...176 AutoLookup in Multitable Queries...177 Modifying the Datasheet View of a Query...178 Printing Query Results...181 Refining a Query by Using Criteria...184 Working with Dates in Criteria...185 12 Advanced Query Techniques... 189 Creating Calculated Fields...190 Getting Help from the Expression Builder...191 Creating and Running Parameter Queries...192 Creating and Running Action Queries...194 Creating and Running Update Queries...195 Creating and Running Delete Queries...197 Creating and Running Append Queries...198 Creating and Running Make Table Queries...201 Using Aggregate Functions to Summarize Numeric Data...202 Creating Totals Queries...203 Working with Outer Joins...206 Establish an Outer Join...206 13 Building Powerful Forms... 209 Power Control Techniques...210 Add Fields to a Form...210 Selecting, Moving, Aligning, and Sizing Form Objects...211 Modifying Object Tab Order...219 Conditional Formatting...219 Conditionally Format Data...220 Form Properties and Why Should You Use Them...221 Working with the Properties Window...221 Working with the Important Form Properties...222 Control Properties and Why to Use Them...227 The Format Properties of a Control...227 The Data Properties of a Control...230 The Other Properties of a Control...233 14 Advanced Form Techniques... 237 Working with Combo Boxes...238 List Boxes...241 The Command Button Wizard: Programming Without Typing...241 Building Forms Based on More Than One Table...244 Creating One-to-Many Forms...244 Working with Subforms...247 15 Building Powerful Reports... 251 The Anatomy of a Report...252 Control Properties and Why to Use Them...253 The Format Properties of a Control...253 The Data Properties of a Control...258 The Other Properties of a Control...258 Building Reports Based on More Than One Table...260 Creating One-to-Many Reports...260 Working with Subreports...266 16 Advanced Report Techniques... 269 Working with Sorting and Grouping...270 Add Sorting and Grouping to a Report...270 Sorting and Grouping Settings...271 Group Header and Footer Properties and Why to Use Them...273 Report Properties and Why to Use Them...274 Working with the Properties Window...275 The Format Properties of a Report...275 The Report's Data Properties...276 Other Properties of a Report...277 Basing Reports on Stored Queries or Embedded SQL Statements...278 17 Automating Your Database with Macros... 281 Learning the Basics of Creating and Running a Macro...282 Working with Macro Actions...282 Working with Action Arguments...284 Submacros...286 Program Flow...287 Running an Access Macro...289 Running a Macro from the Macro Design Window...290 Running a Macro from the Macros Group of the Navigation Pane...290 Triggering a Macro from a Form or Report Event...291 Modifying an Existing Macro...292 Inserting New Macro Actions...292 Deleting Macro Actions...292 Moving Macro Actions...293 Copying Macro Actions...293 Creating an Embedded Macro...294 18 Advanced Macro Techniques... 299 Creating Data Macros...300 Respond to Events...300 Creating a Drillthrough Macro...301 Other New Features Available in Macros...304 Testing a Macro...305 Determining When You Should Use Macros and When You Shouldn't...306 Converting a Macro to VBA Code...307 Creating an AutoExec Macro...308 19 Sharing Data with Other Applications... 311 What Is External Data?...312 Importing, Linking, and Opening Files: When and Why...312 Determining Whether to Import or Link...313 Looking at Supported File Formats...314 Exporting to Another Access Database...314 Exporting to an Excel Spreadsheet...316 Exporting to an Excel Spreadsheet Using the Context-Sensitive Menu...316 Exporting to an Excel Spreadsheet Using Drag and Drop...317 Exporting to an Excel Spreadsheet by Using the External Data Tab on the Ribbon...318 Exporting to ASCII...318 Export Tables and Queries to the ASCII File Format...318 Importing from Another Access Database...321 Import an Access Table...322 Importing Spreadsheet Data...323 Importing ASCII Data...326 Import ASCII Data into Access...326 Linking to Tables in Another Access Database...329 Linking to Another Type of Database...331 Link to Excel Spreadsheets...331 Linking to SQL Server Databases...333 The Linked Table Manager...338 Move and Update Table Links...338 20 Working with Web Databases... 343 Working with Web Databases...344 Creating a Blank Web Database...344 Creating a Query...346 Creating and Working with Forms...349 Creating a New Form...350 Customizing an Existing Form...354 Using a Template to Create a Website...357 Viewing Your Website in a Browser...358 21 Putting It All Together... 363 Designing the Tables to Store Your Data...364 Building the Forms to Edit Your Data...369 Designing the Clients Form...369 Designing the Projects Form...372 Adding a Command Button That Links the Clients and Projects Forms...374 Building the Queries to Extract the Data You Need...375 Designing the Reports to Display Your Data...377 Designing the rptClientListing Report...377 Building the Macros Necessary to Automate Your Application...380 Index ... 383

Using Microsoft Access 2010. An active participant in many user groups and other organizations, Alison is a past president of the Independent Computer Consultants Association of Los Angeles and of the Los Angeles Clipper Users' Group. She served as president of the Ventura County Professional Women's Network for 2 years. Alison's firm, InfoTech Services Group, Inc., is available for consulting work and onsite training in Microsoft Access, Visual Studio .NET, and SQL Server, as well as for Windows Server 2008, Windows Vista, Windows XP, Windows 7, Windows 8, PC networking, and Microsoft Exchange Server. You can contact Alison by email at Alison@TechIsMyThing.com, or visit the InfoTech Services Group website at http://www.TechIsMyThing.com.