Access 2010: The Missing Manual

Access 2010: The Missing Manual

by Matthew MacDonald
Released June 2010
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781449394172

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Unlock the secrets of this powerful database program and discover how to use your data in creative ways. With this book’s easy step-by-step process, you’ll quickly learn how to build and maintain a complete Access database, using Access 2013’s new, simpler user interface and templates. You also get practices and tips from the pros for good database design—ideal whether you’re using Access for school, business, or at home.

The important stuff you need to know:

  • Build a database with ease. Store information to track numbers, products, documents, and more.
  • Customize the interface. Build your own forms to make data entry a snap.
  • Find what you need fast. Search, sort, and summarize huge amounts of information.
  • Put your data to use. Turn raw info into printed reports with attractive formatting.
  • Share your data. Collaborate online with SharePoint and the Access web database.
  • Dive into Access programming. Get tricks and techniques to automate common tasks.
  • Create rich data connections. Build dynamic links with SQL Server, SharePoint, and other systems.

Publisher resources

View/Submit Errata

Table of contents

  1. Access 2010: The Missing Manual
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    2. A Note Regarding Supplemental Files
    3. The Missing Credits
      1. About the Author
      2. About the Creative Team
      3. Acknowledgements
      4. The Missing Manual Series
    4. Introduction
      1. What You Can Do with Access
        1. The Two Sides of Access
        2. Access vs. Excel
        3. Access vs. SQL Server
      2. The Modern Face of Access
        1. Backstage View
        2. The Ribbon
        3. Using the Ribbon with the Keyboard
        4. The Quick Access Toolbar
      3. The New Features in Access 2010
      4. About This Book
        1. About the Outline
        2. About→These→Arrows
          1. Contextual tabs
          2. Drop-down buttons
          3. Backstage view
          4. Ordinary menus
        3. About Shortcut Keys
        4. Examples
        5. About MissingManuals.com
        6. Safari® Books Online
    5. One. Storing Information in Tables
      1. 1. Creating Your First Database
        1. Understanding Access Databases
        2. Getting Started with Your First Database
          1. Understanding Tables
          2. Creating a Simple Table
          3. Editing a Table
            1. Edit mode
            2. Datasheet shortcut keys
            3. Cut, copy, and paste
        3. Saving Databases
          1. Making Backups
          2. Saving a Database with a Different Name or Format
          3. Shrinking a Database
        4. Opening Databases
          1. Designating a Database as Trusted
          2. Opening Recent Databases
          3. Opening More Than One Database at Once
          4. Opening a Database Created in an Older Version of Access
        5. The Navigation Pane
          1. Browsing Tables with the Navigation Pane
          2. Managing Database Objects
      2. 2. Building Smarter Tables
        1. Understanding Data Types
        2. Design View
          1. Organizing and Describing Your Fields
          2. How Updates Work in Design View
        3. Access Data Types
          1. Text
            1. Text length
          2. Memo
            1. Formatted text
          3. Number
            1. Number size
            2. Number formatting
          4. Currency
          5. Date/Time
            1. Custom date formats
          6. Yes/No
          7. Hyperlink
          8. Attachment
          9. AutoNumber
            1. Using AutoNumbers without revealing the size of your table
            2. Using replication IDs
          10. Calculated
        4. The Primary Key
          1. Creating Your Own Primary Key Field
        5. Six Principles of Database Design
          1. 1. Choose Good Field Names
          2. 2. Break Down Your Information
          3. 3. Include All the Details in One Place
          4. 4. Avoid Duplicating Information
          5. 5. Avoid Redundant Information
          6. 6. Include an ID Field
      3. 3. Mastering the Datasheet: Sorting, Searching, Filtering, and More
        1. Datasheet Customization
          1. Formatting the Datasheet
          2. Rearranging Columns
          3. Resizing Rows and Columns
          4. Hiding Columns
          5. Freezing Columns
        2. Datasheet Navigation
          1. Sorting
            1. Sorting on multiple fields
          2. Filtering
            1. Quick filters
            2. Filter by selection
            3. Filter by condition
          3. Searching
        3. Advanced Editing
          1. The Spell Checker
            1. Spell-checking options
          2. AutoCorrect
          3. Special Characters
        4. Printing the Datasheet
          1. Print Preview
            1. Moving around the print preview
            2. Changing the page layout
          2. Fine-Tuning a Printout
      4. 4. Blocking Bad Data
        1. Data Integrity Basics
          1. Preventing Blank Fields
            1. Blank values and empty text
          2. Setting Default Values
          3. Preventing Duplicate Values with Indexes
            1. Multifield indexes
        2. Input Masks
          1. Using a Readymade Mask
          2. Creating Your Own Mask
        3. Validation Rules
          1. Applying a Field Validation Rule
          2. Writing a Field Validation Rule
            1. Validating numbers
            2. Validating dates
            3. Validating text
            4. Combining validation conditions
          3. Creating a Table Validation Rule
        4. Lookups
          1. Creating a Simple Lookup with Fixed Values
          2. Adding New Values to Your Lookup List
      5. 5. Linking Tables with Relationships
        1. Relationship Basics
          1. Redundant Data vs. Related Data
          2. Matching Fields: The Relationship Link
          3. Linking with the ID Column
          4. The Parent-Child Relationship
        2. Using a Relationship
          1. Defining a Relationship
          2. Editing Relationships
          3. Referential Integrity
            1. Blank values for unlinked records
            2. Cascading deletes
            3. Cascading updates
          4. Navigating a Relationship
          5. Lookups with Related Tables
        3. More Exotic Relationships
          1. One-to-One Relationship
          2. Many-to-Many Relationship
            1. Junction tables
            2. Multi-value fields
        4. Relationship Practice
          1. The Music School
            1. Identifying the tables
            2. Identifying the relationships
            3. Getting more detailed
          2. The Chocolate Store
            1. The product catalog and customer list
            2. Ordering products
    6. Two. Manipulating Data with Queries
      1. 6. Queries That Select Records
        1. Query Basics
        2. Creating Queries
          1. Creating a Query in Design View
            1. Building filter expressions
            2. Getting the top records
          2. Creating a Simple Query with the Query Wizard
          3. Understanding the SQL View
            1. Analyzing a query
            2. Creating a union query
        3. Queries and Related Tables
          1. Joining Tables in a Query
          2. Outer Joins
            1. Finding unmatched records
          3. Multiple Joins
      2. 7. Essential Query Tricks
        1. Calculated Fields
          1. Defining a Calculated Field
          2. Simple Math with Numeric Fields
            1. Date fields
            2. Order of operations
          3. Expressions with Text
        2. Query Functions
          1. Using a Function
            1. Nested functions
          2. The Expression Builder
          3. Formatting Numbers
          4. More Mathematical Functions
          5. Text Functions
          6. Date Functions
          7. Dealing with Blank Values (Nulls)
        3. Summarizing Data
          1. Grouping a Totals Query
          2. Joins in a Totals Query
        4. Query Parameters
      3. 8. Queries That Update Records
        1. Understanding Action Queries
          1. Testing Action Queries (Carefully)
          2. The Action Query Family
        2. Update Queries
        3. Append Queries
          1. Creating an Append (or Make-Table) Query
          2. Getting AutoNumbers to Start at Values Other Than 1
        4. Delete Queries
        5. Tutorial: Flagging Out-of-Stock Orders
          1. Finding Out-of-Stock Items
          2. Putting the Orders on Hold
      4. 9. Analyzing Data with Crosstab Queries and Pivot Tables
        1. Understanding Crosstab Queries
        2. Creating Crosstab Queries
          1. Creating a Crosstab Query with the Wizard
          2. Creating a Crosstab Query from Scratch
        3. Pivot Tables
          1. Building a Pivot Table
          2. Manipulating a Pivot Table
          3. Creating a Calculated Field
          4. Hiding and Showing Details
          5. Filtering Pivot Tables
        4. Pivot Charts
          1. Choosing a Chart Type
          2. Printing a Pivot Chart
    7. Three. Printing Reports
      1. 10. Creating Reports
        1. Report Basics
          1. Creating a One-Click Report
          2. Building a Report in Layout View
          3. The Many Views of a Report
          4. Rearranging a Report
        2. Printing, Previewing, and Exporting a Report
          1. Previewing a Report
          2. Export Formats
          3. Exporting a Report to a PDF
          4. Exporting a Report to a Different Format
        3. Formatting a Report
          1. Applying a Theme
          2. Formatting Columns and Column Headers
            1. Formatting numeric fields
            2. Alternating row formatting
            3. Gridlines
            4. Borders
            5. Margin and padding
            6. Background image
          3. Conditional Formatting
          4. Data Bars
        4. Filtering and Sorting a Report
          1. Filtering a Report
          2. Sorting a Report
      2. 11. Designing Advanced Reports
        1. Improving Reports in Design View
          1. The Design View Sections
          2. Understanding Controls
          3. Moving Fields Out of a Layout
          4. Adding More Controls
          5. Creating a Report from Scratch (in Design View)
        2. The Report Wizard
        3. The Label Wizard
        4. Fine-Tuning Reports with Properties
          1. Modifying Common Properties
        5. Expressions
        6. Grouping
          1. Report Grouping
          2. Fine-Tuning with the “Group, Sort, and Total” Pane
            1. Sort by …
            2. Group on …
            3. With A on top / from smallest to largest
            4. By entire value
            5. With … totaled
            6. With title …
            7. With a header section / with a footer section
            8. Keep group together on one page
          3. Multiple Groups
    8. Four. Building a User Interface with Forms
      1. 12. Creating Simple Forms
        1. Creating Forms
          1. Building a Simple Form
          2. Using AutoNumber Fields in Forms
          3. Showing Pictures from a Table
        2. Using Forms
          1. Finding and Editing a Record
          2. Adding a Record
          3. Deleting a Record
          4. Printing Records
        3. Sorting and Filtering in a Form
          1. Sorting a Form
          2. Filtering a Form
          3. Using the Filter by Form Feature
          4. Saving Filters for the Future
        4. Creating Fancy Forms
          1. Manipulating Layouts
            1. Inserting columns and rows
            2. Splitting and merging cells
          2. Liberating Controls from Layouts
          3. Using Tabular Layouts
          4. Showing Multiple Records in any Form
          5. Split Forms
          6. More Useful Form Properties
        5. The Form Wizard
      2. 13. Designing Advanced Forms
        1. Customizing Forms in Design View
          1. Form Sections: The Different Parts of Your Form
          2. Adding Controls to Your Form
          3. The Control Gallery: A Quick Tour
          4. Arranging Controls on Your Form
            1. Aligning controls
            2. Sizing controls
            3. Spacing controls
            4. Controls that overlap
          5. Anchoring: Automatically Resizing Controls
            1. Making controls as wide as the form
            2. Making a single control as large as possible
          6. Tab Order: Making Keyboard Navigation Easier
        2. Taking Control of Controls
          1. Locking Down Fields
          2. Prevent Errors with Validation
          3. Performing Calculations with Expressions
          4. Organizing with Tab Controls
          5. Going Places with Links
          6. Showing Web Pages with the Web Browser
          7. Navigating with Lists
          8. Performing Actions with Command Buttons
        3. Forms and Linked Tables
          1. Table Relationships and Simple Forms
          2. The Subform Control
          3. Creating Customized Subforms
      3. 14. Building a Navigation System
        1. Mastering the Navigation Pane
          1. Configuring the Navigation List
          2. Better Filtering
          3. Hiding Objects
          4. Using Custom Groups
          5. Searching the Navigation List
        2. Building Forms with Navigation Smarts
          1. Custom Menu Forms
          2. Designating a Startup Form
          3. Compound Forms
          4. Showing All Your Forms in a List
        3. Navigation Forms
          1. Creating a Single-Level Navigation Form
          2. Creating a Two-Level Navigation Form
          3. Fine-Tuning Your Navigation Buttons
        4. Linking to Related Data
          1. Showing Linked Records in Separate Forms
          2. Showing More Detailed Reports with Links
    9. Five. Programming Access
      1. 15. Automating Tasks with Macros
        1. Macro Essentials
          1. Creating a Macro
          2. Running a Macro
          3. Editing a Macro
          4. Finding Actions in the Action Catalog
          5. Collapsing, Expanding, and Grouping Macro Actions
          6. Solving Macro Problems
        2. Macros and Security
          1. Unsafe Macro Actions
          2. Trusted Databases
          3. Temporary Trust
          4. The Trust Center
          5. Setting Up a Trusted Location
        3. Three Macro Recipes
          1. Find a Record
          2. Print a Report
          3. Email Your Data
        4. Managing Macros
          1. Submacros
          2. Assigning a Macro to a Keystroke
          3. Configuring a Startup Macro
      2. 16. Connecting Macros to Forms and Tables
        1. Macro-Powered Forms
          1. Understanding Events
          2. Attaching a Macro to an Event
          3. Reading Arguments from a Form
          4. Changing Form Properties
        2. Conditional Macros
          1. Building a Condition
          2. Validating Data with Conditions
          3. More Complex Conditional Macros
        3. Data Macros
          1. Table Events
          2. Validation with Before Events
          3. Keeping a Change Log
          4. Debugging Data Macros
          5. An Advanced Data Macro: Synchronizing Data
      3. 17. Automating Tasks with Visual Basic
        1. The Visual Basic Editor
          1. Adding a New Module
          2. Writing the Simplest Possible Code Routine
        2. Putting Code in a Form
          1. Responding to a Form Event
          2. Calling the Code in a Module
          3. Reading and Writing the Fields on a Form
        3. Understanding Objects
          1. Properties
          2. Methods
          3. Events
        4. Using Objects
          1. Indicating That a Record Has Changed
          2. Creating a Mouseover Effect
      4. 18. Writing Smarter Code
        1. Exploring the VB Language
          1. Storing Information in Variables
          2. Making Decisions
          3. Repeating Actions with a Loop
          4. Creating Custom Functions
          5. Putting It All Together: A Function for Testing Credit Cards
        2. Dealing with Trouble
          1. Debugging
          2. Error Handling
        3. Deeper into Objects
          1. The DoCmd Object
          2. Converting a Macro to VB Code
        4. Using VB to Run a Better Business
          1. Keeping a Running Total
          2. Getting Price Information
          3. Adding a New Product During an Order
          4. Managing Order Fulfillment
          5. Updating Stock Numbers
    10. Six. Sharing Access with the Rest of the World
      1. 19. Sharing a Database with Multiple Users
        1. Opening Up Your Database to the World
          1. How Access Sharing Works
        2. Preparing Your Database
          1. Understanding Split Databases
          2. Splitting a Database with the Wizard
          3. How Linked Tables Work
          4. Manually Splitting a Database
          5. Locking Down Your Front End
          6. Sharing a Database with People Who Don’t Own Access
        3. Playing Well with Others
          1. Seeing Changes As They Happen
          2. Dealing with Editing Conflicts
          3. Using Locks to Stop Overlapping Edits
          4. Opening a Database in Exclusive Mode
        4. Data Corruption
          1. Diagnosing (and Fixing) Corrupt Databases
          2. Preventing Corruption
        5. Securing Your Database
          1. Password-Protecting Your Database
          2. Passwords and Split Databases
          3. Using Windows File Security
      2. 20. Importing and Exporting Data
        1. Case for Importing and Exporting
          1. Understanding Exports
          2. Understanding Imports
        2. Using the Clipboard
          1. Copying a Table from Access to Somewhere Else
          2. Copying Cells from Excel into Access
        3. Import and Export Operations
          1. Importable File Types
          2. Importing Data
          3. Importing from an Excel File
          4. Importing from a Text File
          5. Exportable File Types
          6. Exporting Data
          7. Reusing Import and Export Settings
        4. Access and XML
          1. What Is XML, Really?
          2. Three Rules of XML
            1. The prolog
            2. Elements
            3. Nesting
          3. XML Files and Schemas
          4. The Access XML Story
          5. Exporting to an XML File
          6. Importing from an XML File
        5. Collecting Info by Email
          1. Creating an Email Message
          2. Processing Replies Manually
          3. Processing Replies Automatically
          4. Managing Your Email Collection Settings
      3. 21. Connecting Access to SQL Server
        1. Should You Switch to SQL Server?
          1. How SQL Server Works
          2. A Cheaper SQL Server
        2. Getting Started: SQL Server Express
          1. Preparing for SQL Server Express
          2. Downloading SQL Server Express
          3. Installing SQL Server Express
          4. Putting SQL Server on the Network
        3. Creating a SQL Server Database
          1. Upsizing a Database
          2. Managing Your Database
          3. Creating a SQL Server Database from Scratch
        4. Adding Objects to a SQL Server Database
          1. Creating a Table
            1. SQL Server data types
            2. AutoNumber fields
            3. Lookups
            4. Relationships
          2. Understanding Queries
          3. Creating a View
      4. 22. Connecting Access to SharePoint
        1. Understanding SharePoint
          1. What You Can Do in SharePoint
        2. Setting Up SharePoint
          1. Creating a Team Site
          2. Customizing Your Site
        3. SharePoint and Access
          1. Building a List
          2. Exporting a Table to SharePoint
          3. Importing a List in Access
          4. Moving a Whole Database to SharePoint
          5. Editing Your SharePoint Data in Access
          6. Making Offline Changes
      5. 23. Building a Web Database
        1. Understanding Web Databases
        2. Your First Web Database
          1. Creating a New Web Database
          2. Client Objects and Web Objects
        3. Publishing a Web Database
          1. The SharePoint Web Database Page
          2. Web Forms
          3. Distributing Your Database
          4. Synchronizing Your Database
          5. Working Offline
        4. Delving Deeper into Web Objects
          1. Web Forms
          2. Web Reports
          3. Web Queries
          4. Web Macros
        5. Converting an Existing Database to a Web Database
          1. Checking Web Compatibility
          2. Migration Strategies
    11. Seven. Appendix
      1. A. Customizing Access
        1. Adding Your Favorites to the QAT
          1. Adding Buttons
          2. Customizing Specific Databases
        2. Personalizing the Ribbon
          1. Changing Existing Tabs
          2. Creating Your Own Tab
          3. Saving and Reusing Your Custom Ribbon
    12. Index
    13. About the Author
    14. SPECIAL OFFER: Upgrade this ebook with O’Reilly

Product information

  • Title: Access 2010: The Missing Manual
  • Author(s): Matthew MacDonald
  • Release date: June 2010
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781449394172