Access 2007: The Missing Manual

Access 2007: The Missing Manual

by Matthew MacDonald
Released December 2006
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596554545

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

Compared to industrial-strength database products such as Microsoft's SQL Server, Access is a breeze to use. It runs on PCs rather than servers and is ideal for small- to mid-sized businesses and households. But Access is still intimidating to learn. It doesn't help that each new version crammed in yet another set of features; so many, in fact, that even the pros don't know where to find them all. Access 2007 breaks this pattern with some of the most dramatic changes users have seen since Office 95. Most obvious is the thoroughly redesigned user interface, with its tabbed toolbar (or "Ribbon") that makes features easy to locate and use. The features list also includes several long-awaited changes. One thing that hasn't improved is Microsoft's documentation. To learn the ins and outs of all the features in Access 2007, Microsoft merely offers online help.

Access 2007: The Missing Manual was written from the ground up for this redesigned application. You will learn how to design complete databases, maintain them, search for valuable nuggets of information, and build attractive forms for quick-and-easy data entry. You'll even delve into the black art of Access programming (including macros and Visual Basic), and pick up valuable tricks and techniques to automate common tasks -- even if you've never touched a line of code before. You will also learn all about the new prebuilt databases you can customize to fit your needs, and how the new complex data feature will simplify your life. With plenty of downloadable examples, this objective and witty book will turn an Access neophyte into a true master.

Table of contents

  1. Access 2007: The Missing Manual
  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 New Face of Access 2007
      1. The Ribbon
      2. Using the Ribbon with the Keyboard
      3. The Office Menu
      4. The Quick Access Toolbar
    3. The New Features in Access 2007
    4. About This Book
      1. About the Outline
      2. About → These → Arrows
        1. Contextual tabs
        2. Drop-down buttons
        3. Ordinary menus
      3. About Shortcut Keys
      4. About Clicking
      5. Examples
      6. About MissingManuals.com
      7. Safari® Enabled
  5. I. Storing Information in Tables
    1. 1. Creating Your First Database
      1. 1.1. Understanding Access Databases
      2. 1.2. Getting Started
        1. 1.2.1. Creating a New Database
        2. 1.2.2. Understanding Tables
        3. 1.2.3. Creating a Simple Table
        4. 1.2.4. Editing a Table
          1. 1.2.4.1. Edit mode
          2. 1.2.4.2. Shortcut keys
          3. 1.2.4.3. Cut, copy, and paste
      3. 1.3. Saving and Opening Access Databases
        1. 1.3.1. Making Backups
        2. 1.3.2. Saving a Database with a Different Name or Format
        3. 1.3.3. Opening a Database
        4. 1.3.4. Opening More Than One Database at Once
        5. 1.3.5. Opening a Database Created in an Older Version of Access
        6. 1.3.6. Creating Another Database
      4. 1.4. The Navigation Pane
        1. 1.4.1. Browsing Tables with the Navigation Pane
        2. 1.4.2. Managing Database Objects
    2. 2. Building Smarter Tables
      1. 2.1. Understanding Data Types
      2. 2.2. Design View
        1. 2.2.1. Organizing and Describing Your Fields
        2. 2.2.2. How Updates Work in Design View
      3. 2.3. Access Data Types
        1. 2.3.1. Text
          1. 2.3.1.1. Text length
        2. 2.3.2. Memo
          1. 2.3.2.1. Formatted text
        3. 2.3.3. Number
          1. 2.3.3.1. Number size
          2. 2.3.3.2. Number formatting
        4. 2.3.4. Currency
        5. 2.3.5. Date/Time
          1. 2.3.5.1. Custom date formats
        6. 2.3.6. Yes/No
        7. 2.3.7. Hyperlink
        8. 2.3.8. Attachment
        9. 2.3.9. AutoNumber
          1. 2.3.9.1. Using AutoNumbers without revealing the size of your table
          2. 2.3.9.2. Using replication IDs
      4. 2.4. The Primary Key
        1. 2.4.1. Creating Your Own Primary Key Field
      5. 2.5. Six Principles of Database Design
        1. 2.5.1. 1. Choose Good Field Names
        2. 2.5.2. 2. Break Down Your Information
        3. 2.5.3. 3. Include All the Details in One Place
        4. 2.5.4. 4. Avoid Duplicating Information
        5. 2.5.5. 5. Avoid Redundant Information
        6. 2.5.6. 6. Include an ID Field
    3. 3. Mastering the Datasheet: Sorting, Searching, Filtering, and More
      1. 3.1. Datasheet Customization
        1. 3.1.1. Formatting the Datasheet
        2. 3.1.2. Rearranging Columns
        3. 3.1.3. Resizing Rows and Columns
        4. 3.1.4. Hiding Columns
        5. 3.1.5. Freezing Columns
      2. 3.2. Datasheet Navigation
        1. 3.2.1. Sorting
          1. 3.2.1.1. Sorting on multiple fields
        2. 3.2.2. Filtering
          1. 3.2.2.1. Quick filters
          2. 3.2.2.2. Filter by selection
          3. 3.2.2.3. Filter by condition
        3. 3.2.3. Searching
      3. 3.3. Advanced Editing
        1. 3.3.1. The Spell Checker
          1. 3.3.1.1. Spell checking options
        2. 3.3.2. AutoCorrect
        3. 3.3.3. Special Characters
      4. 3.4. Printing the Datasheet
        1. 3.4.1. Print Preview
          1. 3.4.1.1. Moving around the print preview
          2. 3.4.1.2. Changing the page layout
        2. 3.4.2. Fine-Tuning a Printout
    4. 4. Blocking Bad Data
      1. 4.1. Data Integrity Basics
        1. 4.1.1. Preventing Blank Fields
          1. 4.1.1.1. Blank values and empty text
        2. 4.1.2. Setting Default Values
        3. 4.1.3. Preventing Duplicate Values with Indexes
          1. 4.1.3.1. Multifield indexes
      2. 4.2. Input Masks
        1. 4.2.1. Using a Ready-Made Mask
        2. 4.2.2. Creating Your Own Mask
      3. 4.3. Validation Rules
        1. 4.3.1. Applying a Field Validation Rule
        2. 4.3.2. Writing a Field Validation Rule
          1. 4.3.2.1. Validating numbers
          2. 4.3.2.2. Validating dates
          3. 4.3.2.3. Validating text
          4. 4.3.2.4. Combining validation conditions
        3. 4.3.3. Creating a Table Validation Rule
      4. 4.4. Lookups
        1. 4.4.1. Creating a Simple Lookup with Fixed Values
        2. 4.4.2. Adding New Values to Your Lookup List
    5. 5. Linking Tables with Relationships
      1. 5.1. Relationship Basics
        1. 5.1.1. Redundant Data vs. Related Data
        2. 5.1.2. Matching Fields: The Relationship Link
        3. 5.1.3. Linking with the ID Column
        4. 5.1.4. The Parent-Child Relationship
      2. 5.2. Using a Relationship
        1. 5.2.1. Defining a Relationship
        2. 5.2.2. Editing Relationships
        3. 5.2.3. Referential Integrity
          1. 5.2.3.1. Blank values for unlinked records
          2. 5.2.3.2. Cascading deletes
          3. 5.2.3.3. Cascading updates
        4. 5.2.4. Navigating a Relationship
        5. 5.2.5. Lookups with Related Tables
      3. 5.3. More Exotic Relationships
        1. 5.3.1. One-to-One Relationship
        2. 5.3.2. Many-to-Many Relationship
          1. 5.3.2.1. Junction tables
          2. 5.3.2.2. Multi-value fields
      4. 5.4. Relationship Practice
        1. 5.4.1. The Music School
          1. 5.4.1.1. Identifying the tables
          2. 5.4.1.2. Identifying the relationships
          3. 5.4.1.3. Getting more detailed
        2. 5.4.2. The Chocolate Store
          1. 5.4.2.1. The product catalog and customer list
          2. 5.4.2.2. Ordering products
  6. II. Manipulating Data with Queries
    1. 6. Queries That Select Records
      1. 6.1. Query Basics
      2. 6.2. Creating Queries
        1. 6.2.1. Creating a Query in Design View
          1. 6.2.1.1. Building filter expressions
          2. 6.2.1.2. Getting the top records
        2. 6.2.2. Creating a Simple Query with the Query Wizard
        3. 6.2.3. Understanding the SQL View
          1. 6.2.3.1. Analyzing a query
          2. 6.2.3.2. Creating a union query
      3. 6.3. Queries and Related Tables
        1. 6.3.1. Joining Tables in a Query
        2. 6.3.2. Outer Joins
          1. 6.3.2.1. Finding unmatched records
        3. 6.3.3. Multiple Joins
    2. 7. Essential Query Tricks
      1. 7.1. Calculated Fields
        1. 7.1.1. Defining a Calculated Field
        2. 7.1.2. Simple Math with Numeric Fields
          1. 7.1.2.1. Date fields
          2. 7.1.2.2. Order of operations
        3. 7.1.3. Expressions with Text
      2. 7.2. Query Functions
        1. 7.2.1. Using a Function
          1. 7.2.1.1. Nested functions
        2. 7.2.2. The Expression Builder
        3. 7.2.3. Formatting Numbers
        4. 7.2.4. More Mathematical Functions
        5. 7.2.5. Text Functions
        6. 7.2.6. Date Functions
        7. 7.2.7. Dealing with Blank Values (Nulls)
      3. 7.3. Summarizing Data
        1. 7.3.1. Grouping a Totals Query
        2. 7.3.2. Joins in a Totals Query
      4. 7.4. Query Parameters
    3. 8. Queries That Update Records
      1. 8.1. Understanding Action Queries
        1. 8.1.1. Testing Action Queries (Carefully)
        2. 8.1.2. The Action Query Family
      2. 8.2. Update Queries
      3. 8.3. Append Queries
        1. 8.3.1. Creating an Append (or Make-Table) Query
        2. 8.3.2. Getting AutoNumbers to Start at Values Other Than 1
      4. 8.4. Delete Queries
      5. 8.5. Tutorial: Flagging Out-of-Stock Orders
        1. 8.5.1. Finding Out-of-Stock Items
        2. 8.5.2. Putting the Orders on Hold
    4. 9. Analyzing Data with Crosstab Queries and Pivot Tables
      1. 9.1. Understanding Crosstab Queries
      2. 9.2. Creating Crosstab Queries
        1. 9.2.1. Creating a Crosstab Query with the Wizard
        2. 9.2.2. Creating a Crosstab Query from Scratch
      3. 9.3. Pivot Tables
        1. 9.3.1. Building a Pivot Table
        2. 9.3.2. Manipulating a Pivot Table
        3. 9.3.3. Creating a Calculated Field
        4. 9.3.4. Hiding and Showing Details
        5. 9.3.5. Filtering Pivot Tables
      4. 9.4. Pivot Charts
        1. 9.4.1. Choosing a Chart Type
        2. 9.4.2. Printing a Pivot Chart
  7. III. Printing Reports
    1. 10. Creating Reports
      1. 10.1. Report Basics
        1. 10.1.1. Creating a Simple Report
        2. 10.1.2. Arranging a Report
        3. 10.1.3. Adding and Removing Fields
        4. 10.1.4. The Many Views of a Report
        5. 10.1.5. Creating a Report from Scratch
      2. 10.2. Printing, Previewing, and Exporting a Report
        1. 10.2.1. Previewing a Report
        2. 10.2.2. Exporting a Report
        3. 10.2.3. Getting the “Save As PDF” Add-in
      3. 10.3. Formatting a Report
        1. 10.3.1. Formatting Columns and Column Headers
          1. 10.3.1.1. Formatting numeric fields
          2. 10.3.1.2. Alternating row formatting
          3. 10.3.1.3. Gridlines
          4. 10.3.1.4. Borders
        2. 10.3.2. Conditional Formatting
      4. 10.4. Filtering and Sorting a Report
        1. 10.4.1. Filtering a Report
        2. 10.4.2. Sorting a Report
    2. 11. Designing Advanced Reports
      1. 11.1. Improving Reports in Design View
        1. 11.1.1. The Design View Sections
        2. 11.1.2. Understanding Controls
        3. 11.1.3. Moving Fields out of a Layout
        4. 11.1.4. Adding More Controls
        5. 11.1.5. Creating a Report from Scratch (in Design View)
      2. 11.2. The Report Wizard
      3. 11.3. The Label Wizard
      4. 11.4. Fine-Tuning Reports with Properties
        1. 11.4.1. Modifying Common Properties
      5. 11.5. Expressions
      6. 11.6. Grouping
        1. 11.6.1. Report Grouping
        2. 11.6.2. Fine-Tuning with the “Group, Sort, and Total” Pane
          1. 11.6.2.1. Sort by …
          2. 11.6.2.2. Group on …
          3. 11.6.2.3. From A to Z/from smallest to largest
          4. 11.6.2.4. By entire value
          5. 11.6.2.5. With … totaled
          6. 11.6.2.6. With title …
          7. 11.6.2.7. With a header section / with a footer section
          8. 11.6.2.8. Keep group together on one page
        3. 11.6.3. Multiple Groups
  8. IV. Building a User Interface with Forms
    1. 12. Creating Simple Forms
      1. 12.1. Form Basics
        1. 12.1.1. Creating a Simple Form
        2. 12.1.2. Using a Form
          1. 12.1.2.1. Finding and editing a record
          2. 12.1.2.2. Adding a record
          3. 12.1.2.3. Deleting a record
          4. 12.1.2.4. Printing records
      2. 12.2. Sorting and Filtering in a Form
        1. 12.2.1. Sorting a Form
        2. 12.2.2. Filtering a Form
        3. 12.2.3. Using the Filter by Form Feature
        4. 12.2.4. Saving Filters for the Future
      3. 12.3. Creating Better Layouts
        1. 12.3.1. Liberating Controls from Layouts
        2. 12.3.2. Using More Than One Layout
        3. 12.3.3. Using Tabular Layouts
        4. 12.3.4. Showing Multiple Records in any Form
        5. 12.3.5. Split Forms
        6. 12.3.6. Even More Useful Form Properties
      4. 12.4. The Form Wizard
    2. 13. Designing Advanced Forms
      1. 13.1. Customizing Forms in Design View
        1. 13.1.1. Form Sections: The Different Parts of Your Form
        2. 13.1.2. Adding Controls to Your Form
        3. 13.1.3. The Control Gallery: A Quick Tour
        4. 13.1.4. Arranging Controls on Your Form
          1. 13.1.4.1. Aligning controls
          2. 13.1.4.2. Sizing controls
          3. 13.1.4.3. Spacing controls
          4. 13.1.4.4. Controls that overlap
        5. 13.1.5. Anchoring: Automatically Resizing Controls
          1. 13.1.5.1. Making controls as wide as the form
          2. 13.1.5.2. Making a single control as large as possible
        6. 13.1.6. Tab Order: Making Keyboard Navigation Easier
      2. 13.2. Taking Control of Controls
        1. 13.2.1. Locking Down Fields
        2. 13.2.2. Prevent Errors with Validation
        3. 13.2.3. Performing Calculations with Expressions
        4. 13.2.4. Organizing with Tab Controls
        5. 13.2.5. Going Places with Links
        6. 13.2.6. Navigating with Lists
        7. 13.2.7. Performing Actions with Command Buttons
      3. 13.3. Forms and Linked Tables
        1. 13.3.1. Table Relationships and Simple Forms
        2. 13.3.2. The Subform Control
        3. 13.3.3. Creating Customized Subforms
    3. 14. Building a Navigation System
      1. 14.1. Mastering the Navigation Pane
        1. 14.1.1. Configuring the Navigation List
        2. 14.1.2. Better Filtering
        3. 14.1.3. Hiding Objects
        4. 14.1.4. Using Custom Groups
        5. 14.1.5. Searching the Navigation List
      2. 14.2. Building Forms with Navigation Smarts
        1. 14.2.1. Building a Switchboard
        2. 14.2.2. Designating a Startup Form
        3. 14.2.3. Switchboard Alternatives
          1. 14.2.3.1. Custom switchboard forms
          2. 14.2.3.2. Compound forms
        4. 14.2.4. Showing All Your Forms in a List
      3. 14.3. Linking to Related Data
        1. 14.3.1. Showing Linked Records in Separate Forms
        2. 14.3.2. Showing More Detailed Reports with Links
  9. V. Programming Access
    1. 15. Automating Tasks with Macros
      1. 15.1. Macro Essentials
        1. 15.1.1. Creating a Macro
        2. 15.1.2. Running a Macro
        3. 15.1.3. Debugging a Macro
      2. 15.2. Macros and Security
        1. 15.2.1. Unsafe Macro Actions
        2. 15.2.2. How Access Handles Unsafe Macros
        3. 15.2.3. The Trust Center
        4. 15.2.4. Setting Up a Trusted Location
      3. 15.3. Three Macro Recipes
        1. 15.3.1. Find a Record
        2. 15.3.2. Print a Report
        3. 15.3.3. Email Your Data
      4. 15.4. Managing Macros
        1. 15.4.1. Macro Groups
        2. 15.4.2. Assigning a Macro to a Keystroke
        3. 15.4.3. Configuring a Startup Macro
      5. 15.5. Connecting Macros to Forms
        1. 15.5.1. Understanding Events
        2. 15.5.2. Attaching a Macro to an Event
        3. 15.5.3. Reading Arguments from a Form
        4. 15.5.4. Changing Form Properties
      6. 15.6. Conditional Macros
        1. 15.6.1. Building a Condition
        2. 15.6.2. Validating Data with Conditions
        3. 15.6.3. More Complex Conditional Macros
    2. 16. Automating Tasks with Visual Basic
      1. 16.1. The Visual Basic Editor
        1. 16.1.1. Adding a New Module
        2. 16.1.2. Writing the Simplest Possible Code Routine
      2. 16.2. Putting Code in a Form
        1. 16.2.1. Responding to a Form Event
        2. 16.2.2. Calling the Code in a Module
        3. 16.2.3. Reading and Writing the Fields on a Form
      3. 16.3. Understanding Objects
        1. 16.3.1. Properties
        2. 16.3.2. Methods
        3. 16.3.3. Events
      4. 16.4. Using Objects
        1. 16.4.1. Indicating That a Record Has Changed
        2. 16.4.2. Creating a Mouseover Effect
    3. 17. Writing Smarter Code
      1. 17.1. Exploring the VB Language
        1. 17.1.1. Storing Information in Variables
        2. 17.1.2. Making Decisions
        3. 17.1.3. Repeating Actions with a Loop
        4. 17.1.4. Creating Custom Functions
        5. 17.1.5. Putting It All Together: A Function for Testing Credit Cards
      2. 17.2. Dealing with Trouble
        1. 17.2.1. Debugging
        2. 17.2.2. Error Handling
      3. 17.3. Deeper into Objects
        1. 17.3.1. The DoCmd Object
        2. 17.3.2. Converting a Macro to VB Code
      4. 17.4. Using VB to Run a Better Business
        1. 17.4.1. Keeping a Running Total
        2. 17.4.2. Getting Price Information
        3. 17.4.3. Adding a New Product During an Order
        4. 17.4.4. Managing Order Fulfillment
        5. 17.4.5. Updating Stock Numbers
  10. VI. Sharing Access with the Rest of the World
    1. 18. Sharing a Database with Multiple Users
      1. 18.1. Opening Up Your Database to the World
        1. 18.1.1. How Access Sharing Works
      2. 18.2. Preparing Your Database
        1. 18.2.1. Understanding Split Databases
        2. 18.2.2. Splitting a Database with the Wizard
        3. 18.2.3. How Linked Tables Work
        4. 18.2.4. Manually Splitting a Database
        5. 18.2.5. Locking Down Your Front End
        6. 18.2.6. Sharing a Database with People Who Don’t Own Access
      3. 18.3. Playing Well with Others
        1. 18.3.1. Seeing Changes As They Happen
        2. 18.3.2. Dealing with Editing Conflicts
        3. 18.3.3. Using Locks to Stop Overlapping Edits
        4. 18.3.4. Opening a Database in Exclusive Mode
      4. 18.4. Data Corruption
        1. 18.4.1. Diagnosing (and Fixing) Corrupt Databases
        2. 18.4.2. Preventing Corruption
      5. 18.5. Securing Your Database
        1. 18.5.1. Password-Protecting Your Database
        2. 18.5.2. Passwords and Split Databases
        3. 18.5.3. Using Windows File Security
    2. 19. Importing and Exporting Data
      1. 19.1. Case for Importing and Exporting
        1. 19.1.1. Understanding Exports
        2. 19.1.2. Understanding Imports
      2. 19.2. Using the Clipboard
        1. 19.2.1. Copying a Table from Access to Somewhere Else
        2. 19.2.2. Copying Cells from Excel into Access
      3. 19.3. Import and Export Operations
        1. 19.3.1. Importable File Types
        2. 19.3.2. Importing Data
        3. 19.3.3. Importing from an Excel File
        4. 19.3.4. Importing from a Text File
        5. 19.3.5. Exportable File Types
        6. 19.3.6. Exporting Data
        7. 19.3.7. Reusing Import and Export Settings
      4. 19.4. Access and XML
        1. 19.4.1. What Is XML, Really?
        2. 19.4.2. Three Rules of XML
          1. 19.4.2.1. The prolog
          2. 19.4.2.2. Elements
          3. 19.4.2.3. Nesting
        3. 19.4.3. XML Files and Schemas
        4. 19.4.4. The Access XML Story
        5. 19.4.5. Exporting to an XML File
        6. 19.4.6. Importing from an XML File
      5. 19.5. Collecting Info by Email
        1. 19.5.1. Creating an Email Message
        2. 19.5.2. Processing Replies Manually
        3. 19.5.3. Processing Replies Automatically
        4. 19.5.4. Managing Your Email Collection Settings
    3. 20. Connecting Access to SQL Server
      1. 20.1. Should You Switch to SQL Server?
        1. 20.1.1. How SQL Server Works
        2. 20.1.2. A Cheaper SQL Server
      2. 20.2. Getting Started: SQL Server 2005 Express
        1. 20.2.1. Installing SQL Server Express
        2. 20.2.2. Putting SQL Server on the Network
      3. 20.3. Creating a SQL Server Database
        1. 20.3.1. Upsizing a Database
        2. 20.3.2. Managing Your Database
        3. 20.3.3. Creating a SQL Server Database from Scratch
      4. 20.4. Adding Objects to a SQL Server Database
        1. 20.4.1. Creating a Table
          1. 20.4.1.1. SQL Server data types
          2. 20.4.1.2. AutoNumber fields
          3. 20.4.1.3. Lookups
          4. 20.4.1.4. Relationships
        2. 20.4.2. Understanding Queries
        3. 20.4.3. Creating a View
    4. 21. Connecting Access to SharePoint
      1. 21.1. Understanding SharePoint
        1. 21.1.1. What You Can Do in SharePoint
      2. 21.2. Setting Up SharePoint
        1. 21.2.1. Creating a Team Site
        2. 21.2.2. Customizing Your Site
      3. 21.3. SharePoint and Access
        1. 21.3.1. Building a List
        2. 21.3.2. Exporting a Table to SharePoint
        3. 21.3.3. Importing Data in Access
        4. 21.3.4. Move a Whole Database to SharePoint
        5. 21.3.5. Editing Your SharePoint Data in Access
        6. 21.3.6. Making Offline Changes
  11. VII. Appendix
    1. A. Customizing the Quick Access Toolbar
      1. A.1. The Quick Access Toolbar
        1. A.1.1. Adding Buttons
        2. A.1.2. Customizing Specific Databases
  12. Index
  13. About the Author
  14. Colophon
  15. Copyright

Product information

  • Title: Access 2007: The Missing Manual
  • Author(s): Matthew MacDonald
  • Release date: December 2006
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596554545