Access 2013: The Missing Manual

Access 2013: The Missing Manual

by Matthew MacDonald
Released April 2013
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781449357412

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.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

Unlock the secrets of Access 2013 and discover how to use your data in creative ways. With this book’s easy step-by-step instructions, you’ll learn how to build and maintain a full-featured database and even turn it into a web app. You also get tips and practices from the pros for good database design—ideal whether you’re using Access for business, school, or at home.

The important stuff you need to know

  • Build a database with ease. Organize and update lists, documents, catalogs, and other types of information.
  • Create your own web app. Let your whole team work on a database in the cloud.
  • Share your database on a network. Link your Access database to SQL Server or SharePoint.
  • Customize the interface. Make data entry a breeze by building your own templates
  • Find what you need fast. Search, sort, and summarize huge amounts of data in minutes.
  • Put your info to use. Turn raw info into well-formatted printed reports.
  • Dive into Access programming. Automate complex tasks and solve common challenges.

Table of contents

  1. The Missing Credits
    1. About the Author
    2. About the Creative Team
    3. Acknowledgements
    4. The Missing Manual Series
  2. 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 Access Ribbon
      1. Using the Ribbon with the Keyboard
      2. The Quick Access Toolbar
      3. Backstage View
    3. The Changes in Access 2013
    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
    5. About the Online Resources
      1. Missing CD
      2. Registration
      3. Feedback
      4. Errata
    6. Safari® Books Online
  3. 1. Storing Information in Tables
    1. 1. Creating Your First Database
      1. Understanding Access Databases
      2. Starting a Database
      3. Building Your First Table
        1. Creating a Simple Table
        2. Editing a Table
          1. Edit Mode
          2. Datasheet Shortcut Keys
          3. Cut, Copy, and Paste
      4. Saving Databases
        1. Making Backups
        2. Saving a Database with a Different Name
        3. Saving a Database in a Different Format
        4. Shrinking a Database
      5. Opening Databases
        1. Designating a Database as Trusted
        2. Opening More Than One Database at Once
        3. Opening a Database Created in an Older Version of Access
      6. 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. Short Text
          1. Text Length
        2. Long Text
          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, and Filtering
      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
  4. 2. 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. Query Parameters
    3. 8. Queries That Summarize Data
      1. Totals Queries
        1. Grouping in a Totals Query
        2. Joins in a Totals Query
      2. Crosstab Queries
        1. Creating a Crosstab Query with the Wizard
        2. Creating a Crosstab Query from Scratch
    4. 9. Queries That Change Tables
      1. Understanding Action Queries
        1. Testing Action Queries (Carefully)
        2. The Action Query Family
      2. Update Queries
        1. Creating an Update Query
      3. Append Queries
        1. Creating an Append (or Make-Table) Query
        2. Getting AutoNumbers to Start at Values Other Than 1
      4. Delete Queries
        1. Creating a Delete Query
        2. Deleting Linked Records
      5. Tutorial: Flagging Out-of-Stock Orders
        1. Finding Out-of-Stock Items
        2. Putting the Orders on Hold
  5. 3. 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
  6. 4. 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
  7. 5. 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
  8. 6. Going Large: Access Databases for Many Users
    1. 19. Sharing a Database on Your Network
      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. Building an Access Web App
      1. Assessing Web Apps
      2. Preparing for Web Apps
      3. Building a Simple Web App
        1. Creating a Web App in Access
        2. Creating a Web App in SharePoint
        3. Adding a Table
        4. Entering Data in a Table
        5. Back to the Home Tab
      4. Running Your Web App
        1. Editing Data in a Web App
        2. Understanding the Web App Navigation System
      5. Customizing Views
        1. Modifying a View
        2. Rearranging Controls
        3. Configuring Controls
        4. Changing the List
        5. Adding and Removing Controls
      6. Creating More Advanced Views
        1. Adding a New View
        2. Using a View to Create a Welcome Page
        3. Views That Use Queries
        4. Views That Use Related Tables
          1. Adding Child Records
          2. Adding Parent Records
      7. Tutorial: Creating a Web App from an Access Database
        1. Creating a Web App Based on a Desktop Database
        2. Dealing with a Many-to-Many Relationship
          1. Creating a Query to Get Related Data
          2. Creating a View for Your Query
          3. Showing Your View in a Popup
    3. 21. Linking Access to SQL Server
      1. How Access and SQL Server Can Work Together
        1. Should You Switch to SQL Server?
        2. How Access and SQL Server Work Together
        3. A Cheaper SQL Server
      2. Getting Started with 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. Building a SQL Server Database
        1. Creating a New Database
        2. Adding a Table
        3. Creating Relationships Between Tables
      4. Editing SQL Server Tables in Access
        1. Setting Up a Connection to SQL Server
        2. Managing Linked Tables
      5. Migrating an Access Database to SQL Server
        1. Installing SSMA
        2. Transferring an Access Database to SQL Server
    4. 22. Linking Access to SharePoint
      1. Understanding SharePoint
        1. What You Can Do in SharePoint
        2. Adding an App to a SharePoint Site
      2. SharePoint and Access
        1. Creating a List in SharePoint
        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
    5. 23. 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
  9. 7. 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
  10. Index
  11. About the Author
  12. Copyright

Product information

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