Access 2013 Absolute Beginner's Guide: (Absolute Beginner's Guide)

Make the most of Access 2013—without becoming a technical expert! This book is the fastest way to master Access and use it to build powerful, useful databases of all kinds—even web application databases! Even if you’ve never used Access before, you’ll learn how to do what you want, one incredibly clear and easy step at a time. Access has never, ever been this simple!   Who knew how simple Access® 2013 could be? This is the easiest, most practical beginner’s guide to using Microsoft’s incredibly powerful new Access 2013 database program… simple, reliable instructions for doing everything you really want to do! Here’s a small sample of what you’ll learn:   •           Create tables to efficiently store and navigate your data •           Build queries that retrieve exactly the information you want •           Design intuitive forms that help your users work more efficiently •           Build reports that answer key questions intuitively and visually •           Learn easy techniques for designing more reliable databases •           Work faster with AutoForms, AutoReports, and other shortcuts •           Automate repetitive tasks and build more polished databases with macros •           Share Access data with Excel, SQL Server, and other applications •           Solve complex problems with advanced query, form, and reporting techniques •           Build modern web databases that serve users through browsers •           Run your database on the cloud through Microsoft Office 365 •           Construct a complete database application from start to finish •           And much more…   Alison Balter, President of InfoTech Services Group, Inc., has spent 25 years training and consulting on Microsoft Access and related applications with top organizations such as Cisco, Shell, Accenture, Northrop, the U.S. Drug Enforcement Administration, Prudential, Transamerica, Fox Broadcasting, and the U.S. Navy. She travels throughout North America delivering seminars on Access and has authored 14 books and videos for Pearson, including Microsoft Access 2010 LiveLessons and Alison Balter’s Mastering Access 2007 Development. She is past president of the Independent Computer Consultants Association of Los Angeles.     Category:             Databases Covers:             Microsoft® Access® 2013 User Level:            Beginning    

Table of Contents:
Introduction.....................................................1 Who Should Read This Book.................................................................................... 1 How This Book Is Organized..................................................................................... 1     Requirements, Editions, and Features................................................................ 2 Using This Book.......................................................................................................... 5   1 Why Use Microsoft Access?...................7 What Is a Relational Database?................................................................................ 8 What Types of Things Can I Do with Microsoft Access?....................................... 8     Access as a Development Platform for Small-Business Applications.............. 9     Access as a Development Platform for Departmental Applications................ 9     Access as a Development Platform for Corporation-Wide Applications................10     Access as a Front End for Enterprisewide Client/Server Applications..................11     Access as a Tool to Develop Web Applications..............................................11 A Preview of the Database Components..............................................................11     Tables: A Repository for Data............................................................................11     Relationships: Tying the Tables Together........................................................14     Queries: Stored Questions or Actions You Apply to Data.............................16     Forms: A Means to Display, Modify, and Add Data.......................................17     Reports: Turning Data into Information............................................................18     Macros: A Means of Automating a System......................................................20     Modules: The Foundation of the Application Development Process...........21   2 Getting Started with Microsoft Access........................25 Creating a Database from a Template..................................................................26 Viewing Database Objects......................................................................................27     Viewing Database Tables...................................................................................27     Viewing Queries..................................................................................................30     Viewing Forms.....................................................................................................30     Viewing Reports...................................................................................................31 Creating a Database from Scratch.........................................................................31 Opening an Existing Database...............................................................................32 Closing a Database..................................................................................................33 Exiting Access...........................................................................................................33   3 Tables: The Repository for Your Data............................35 Working with Table Data.........................................................................................36     Opening an Access Table..................................................................................36     Navigating Around a Table................................................................................36     Closing a Table....................................................................................................38 Editing Table Data...................................................................................................39     Edit Existing Records..........................................................................................39     Undoing Changes...............................................................................................39 Adding Records to a Table.....................................................................................40 Add Records to a Table...........................................................................................40 Deleting Records......................................................................................................41     Selecting One or More Records........................................................................41     Deleting Records.................................................................................................42     Finding and Replacing Records.........................................................................44 Filtering Table Data..................................................................................................47     Filtering by Selection..........................................................................................47     Removing Filters..................................................................................................48   4 Using Queries to Retrieve the Data You Need......................51 What Is a Query and When Should You Use One?..............................................51 Creating a Simple Query.........................................................................................52     Designing the Query...........................................................................................52     Adding Tables to Queries..................................................................................53     Adding Fields to 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

About the Author :
Alison Balter is the president of InfoTech Services Group, Inc., a computer consulting firm based in Newbury Park, California. Alison is a highly experienced independent trainer and consultant specializing in Windows applications training and development. During her 28 years in the computer industry, she has trained and consulted with many corporations and government agencies. Since Alison founded InfoTech Services Group, Inc. (formerly Marina Consulting Group) in 1990, its client base has expanded to include major corporations and government agencies such as Cisco, Shell Oil, Accenture, Northrop, the U.S. Drug Enforcement Administration, Prudential Insurance, Transamerica Insurance, Fox Broadcasting, the U.S. Navy, the University of Southern California , Massachusetts Institute of Technology, and others.   Alison is the author of more than 300 internationally marketed computer training videos and CD-ROMs, including 18 Access 2000 videos, 35 Access 2002 videos, 15 Access 2003 videos, 14 Access 2007 User Videos, and 18 Access 2007 Developer Videos. Alison travels throughout North America giving training seminars on Microsoft Access, Microsoft SQL Server, and Visual Basic for Applications. Alison is also the author of 13 books published by Sams Publishing: Alison Balter’s Mastering Access 95 Development, Alison Balter’s Mastering Access 97 Development, Alison Balter’s Mastering Access 2000 Development, Alison Balter’s Mastering Access 2002 Desktop Development, Alison Balter’s Mastering Access 2002 Enterprise Development, Alison Balter’s Mastering Microsoft Access Office 2003, Teach Yourself Microsoft Office Access 2003 in 24 Hours, Access Office 2003 in a Snap, Alison Balter’s Mastering Access 2007 Development, three e-books on Microsoft Access 2007, and Teach Yourself SQL Express 2005 in 24 Hours. Alison is a co-author of three Access books published by Sams Publishing: Essential Access 95, Access 95 Unleashed, Access 97 Unleashed, and 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.