Cover image for Access 2007: The Missing Manual

Book description

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

Access 2007: The Missing Manual was written from theground up for this redesigned application. You will learn how todesign complete databases, maintain them, search for valuablenuggets of information, and build attractive forms forquick-and-easy data entry. You'll even delve into the black art ofAccess programming (including macros and Visual Basic), and pick upvaluable tricks and techniques to automate common tasks -- even ifyou've never touched a line of code before. You will also learn allabout the new prebuilt databases you can customize to fit yourneeds, and how the new complex data feature will simplify yourlife. With plenty of downloadable examples, this objective andwitty book will turn an Access neophyte into a true master.

Table of Contents

  1. Access 2007: The Missing Manual
    1. A Note Regarding Supplemental Files
    2. The Missing Credits
      1. About the Author
      2. About the Creative Team
      3. Acknowledgements
      4. The Missing Manual Series
    3. 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
    4. 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
    5. 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
    6. 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
    7. 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
    8. 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
    9. 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
    10. 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
    11. Index
    12. About the Author
    13. Colophon