You are previewing Access® 2007 For Dummies®.
O'Reilly logo
Access® 2007 For Dummies®

Book Description

Reduce stress with timesaving database shortcuts


Explore database basics and build tables and reports that corral your data

Access has undergone an extreme makeover! Whether you've used one of the older versions or this is your first exposure to Access, here's where you'll find the essentials you need to make this database system work for you. Cruise around the new interface, team up Access with other Office applications, use wizards to automate your work, and much more.

Discover how to
* Create a new Access database
* Import and export data
* Build forms for efficient data entry
* Search tables for specific data
* Construct custom reports
* Customize your database navigation

Table of Contents

  1. Copyright
  2. About the Authors
  3. Introduction
    1. About This Book
    2. Conventions Used in This Book
    3. What You Don't Have to Read
    4. Foolish Assumptions
    5. How This Book Is Organized
      1. Part I: Basic Training
      2. Part II: Getting It All on the Table
      3. Part III: Data Mania and Management
      4. Part IV: Ask Your Data, and Ye Shall Receive Answers
      5. Part V: Plain and Fancy Reports
      6. Part VI: More Power to You
      7. Part VII: The Part of Tens
      8. Appendix: Getting Help
    6. Icons Used in This Book
    7. Where to Go from Here
  4. I. Basic Training
    1. In this part . . .
    2. 1. Getting to Know Access 2007
      1. What Is Access Good For, Anyway?
        1. Building big databases
        2. Creating databases with multiple tables
          1. The Big Customer database
          2. Failure to plan? Plan to fail
        3. Databases with user forms
        4. Databases requiring special reporting
      2. How Access Works and How You Work with It
        1. Opening Access
        2. Selecting a starting point
          1. Opening an existing database
          2. Starting a new database from scratch
          3. Starting with a template
        3. Now what?
    3. 2. Finding Your Way Around Access
      1. The Getting Started Window
      2. Working with Access's On‐screen Tools
        1. Clicking tabs
        2. Using buttons
        3. The Quick Access menu and toolbar
        4. Accessing panes, panels, and context‐sensitive tools
      3. Customizing the Access Workspace
        1. Repositioning the Quick Access toolbar
        2. Adding buttons from the tabs to the Quick Access toolbar
        3. Removing buttons from the Quick Access toolbar
        4. Minimizing the Ribbon
        5. Working with ScreenTips
        6. Correcting your screen resolution for maximum visibility
      4. Mousing Around
      5. Navigating Access with the Alt Key
    4. 3. Database Basics
      1. Database Lingo
        1. Data, no matter how you pronounce it
        2. Fields of dreams (or data)
        3. Records
        4. Tables
        5. The database
      2. Field Types and Uses
      3. Choosing between Flat and Relational Databases
        1. Isolationist tables
        2. Tables that mix and mingle
          1. The key to relational databases
          2. But do your tables need to relate?
      4. Building a Database
      5. Adding and Removing Tables
        1. One more, please
        2. Oops, I didn't mean to do that
  5. II. Getting It All on the Table
    1. In this part . . .
    2. 4. Keys, Relationships, and Indexes
      1. The Primary Key to Success
        1. The lowdown on primary keys
          1. Uses
          2. Rules
            1. Location
            2. Defaults
            3. Restrictions
        2. Creating a primary key
      2. Making Tables Get Along
        1. Rules of relationships
        2. Relationship types
          1. One‐to‐many
          2. One‐to‐one
          3. Many‐to‐many
            1. Multivalue fields
            2. Junction tables
      3. Building Table Relationships
        1. The Relationships window
        2. Table relationships
          1. Selecting tables
          2. Managing relationships
            1. Creating relationships
            2. Modifying relationships
      4. Indexing for Faster Queries
        1. Create your own index
        2. Adding and removing indexes
    3. 5. Remodeling Your Data
      1. Opening a Table for Editing
      2. Inserting Records and Fields
        1. Adding a record
        2. Inserting a field
        3. Deleting a field
      3. Modifying Field Content
      4. Name Calling
        1. Renaming fields
          1. Table view
          2. Datasheet tab
        2. Renaming a table
      5. Turn Uh Oh! into Yi Hah!
    4. 6. What's Happening under the Table?
      1. Access Table Settings
      2. Field Data Formats
        1. Text and memo fields
          1. Capitalization
            1. Uppercase
            2. Lowercase
          2. Spacing and punctuation
            1. Show filler spaces
            2. Don't show filler spaces
        2. Number and currency fields
          1. General Number
          2. Currency formats
          3. Scientific, Percent, and Decimal formats
        3. Date/time fields
        4. Yes/No fields
          1. Allowable Yes/No field entries
          2. Create your own Yes/No format
      3. Gaining Control of Data Entry
        1. You really need to put a mask on those fields
          1. Using the Input Mask Wizard
          2. Making a mask by hand
            1. Designing an input mask
            2. Putting on your input mask
        2. To require or not to require
        3. Making your data toe the line with validation
  6. III. Data Mania and Management
    1. In this part . . .
    2. 7. Creating Data Forms
      1. Generating Forms
        1. Keeping it simple: AutoForm
        2. Granting most wishes: Form Wizard
      2. Customizing Form Parts
        1. Taking the Layout view
        2. Managing form controls
          1. Control Types
            1. Moving controls
            2. Sizing controls
            3. Editing labels
          2. Deleting controls
    3. 8. Importing and Exporting Data
      1. Retrieving Data from Other Sources
        1. Translating file formats
          1. Spreadsheets
          2. Text files
        2. Importing and linking
          1. Steps for importing
          2. Troubleshooting
            1. Slow imports and links
            2. Bad data
      2. Get This Data Out of Here
        1. Export formats
        2. Exporting table or query data
    4. 9. Automatically Editing Data
      1. Please Read This First!
      2. Creating Consistent Corrections
      3. Using Queries to Automate the Editing Process
        1. Looking for duplicate records
        2. Running the Find Duplicates Query Wizard
    5. 10. Gather Locally, Share Globally
      1. Access and the Web
      2. Click! Using Hyperlinks in Your Access Database
        1. Adding a hyperlink field to your table
        2. Typing your hyperlinks
        3. Fine tuning your hyperlinks
        4. Testing links
      3. Publishing Your Data to the Web
        1. Publishing your Access tables
  7. IV. Ask Your Data, and Ye Shall Receive Answers
    1. In this part . . .
    2. 11. Fast Finding, Filtering, and Sorting Data
      1. Using the Find Command
        1. Finding anything fast
        2. Shifting Find into high gear
          1. Look In
          2. Match
          3. Search
          4. Match Case
          5. Search Fields As Formatted
      2. Sorting from A to Z or Z to A
        1. Sorting by a single field
        2. Sorting on more than one field
      3. Fast and Furious Filtering
        1. Filtering by a field's content
        2. Filter by selection
        3. Filter by Form
        4. Unfiltering in a form
        5. Filter by excluding selection
    3. 12. I Was Just Asking . . . For Answers
      1. Simple (Yet Potent) Filter and Sort Tools
        1. Filter things first
          1. How filters work
          2. What about queries?
          3. Advanced Filter/Sort
        2. Fact‐finding with fun, fast filtering
        3. Here's the “advanced” part
          1. Starting the process
          2. Selecting fields and criteria
          3. Running the filter
          4. Saving your work
      2. Select Queries
        1. Solid relationships are the key to getting it all (from your tables)
        2. Running the Query Wizard
      3. Getting Your Feet Wet with Ad Hoc Queries
        1. Adding the finishing touches
        2. Saving the query
        3. Running your query
    4. 13. I'll Take These AND Those OR Them
      1. Working with AND and/or OR
        1. Data from here to there
        2. Using multiple levels of AND
        3. Establishing criteria with OR
      2. Combining AND with OR and OR with AND
    5. 14. Queries That Think Faster than You
      1. Kissing That Calculator Goodbye via the Total Row
      2. Adding the Total Row to Your Queries
      3. Giving the Total Row a Workout
        1. Organizing things with Group By
        2. Performing sums
        3. Counting, the easy way
        4. Narrowing the results with Where
      4. Creating Your Own Top‐Ten List
      5. Choosing the Right Field for the Summary Instruction
    6. 15. Calculating with Your Data
      1. A Simple Calculation
      2. Complex Calculations
        1. Calculate until you need to calculate no more!
        2. Using one calculation in another
        3. Using parameter queries to ask for help
        4. Daisy chaining your words with text formulas
      3. Expression Builder (Somewhat) to the Rescue
  8. V. Plain and Fancy Reporting
    1. In this part . . .
    2. 16. Quick and Not‐So‐Dirty Automatic Reporting
      1. Fast and Furious Automatic Reporting
        1. Creating a quick, one‐table report
          1. Your instant report formatting options
          2. Rearranging columns
          3. Sizing columns to fit the data
        2. Starting the Report Wizard
      2. Previewing Your Report
        1. Zooming in and out and all around
        2. Pop goes the menu
      3. Beauty Is Only Skin (Report) Deep
        1. The Print Options tab
        2. The Page tab
        3. The Columns tab
    3. 17. Dazzling Report Design
      1. Taking Your Report In for Service
      2. Report Organization
        1. Structural devices
          1. Controls
          2. Sections
            1. Headers
            2. Detail
            3. Footers
        2. Page breaks
          1. Inserting a page break
          2. Removing a page break
      3. Formatting This, That, and the Other
        1. Adding color
        2. Relocation, relocation, relocation
          1. Moving a single control
          2. Moving a group of controls
        3. One size does not fit all
        4. Spaced out controls
        5. Borderline beauty
          1. Coloring
          2. Thickening
          3. Changing the type
        6. Tweaking your text
          1. Fonts
          2. Alignment
      4. Sneaking a Peek
      5. Getting an AutoFormat Makeover
      6. Adding Additional Design Elements
        1. Drawing lines
          1. Straight lines
          2. Rectangular boxes
        2. Pretty as a picture
          1. Removing the Auto_Logo icon
          2. Adding a logo
    4. 18. Headers and Footers and Groups, Oh My!
      1. A Place for Everything and Everything in Its Place
        1. Layout basics
        2. Sections
        3. Grouping your records
        4. So you want more?
      2. Customizing Properties
        1. Controlling report and page headings
        2. Adjusting individual sections
        3. Itemized adjustments
          1. Customizing headers and footers
          2. A good header on your report's shoulders
          3. Solid footing with page numbers and dates
            1. And this is page what?
            2. Time‐stamping your reports
    5. 19. Magical Mass Mailings
      1. Massive Mailings with the Label Wizard
  9. VI. More Power to You
    1. In this part . . .
    2. 20. Making It All Better with the Analyzer Tools
      1. Convert Your Flat Files to Relational Tables with Analyzer
      2. Record Database Object Details with the Documenter
      3. Improve Database Performance without Steroids
    3. 21. Hello! Creating an Interface to Welcome Data Users
      1. The Comings and Goings of a Switchboard
        1. Creating a switchboard
        2. Adding switchboard items
      2. Am I in the Right Place? Switchboard Testing
      3. Maintaining the Switchboard
        1. Edit switchboard items
        2. Delete a switchboard or switchboard item
        3. Move a switchboard item
      4. Displaying the Switchboard at Startup
  10. VII. The Part of Tens
    1. In this part . . .
    2. 22. Ten Common Problems
      1. You Type 73.725, but it Changes to 74
      2. The Words they are A‐Changing
      3. The Record was There and Now it's Gone
        1. Undo
        2. Search for the missing record
        3. Backup recovery
      4. You Run a Query, but the Results Aren't What You Expect
      5. The Validation That Never Was
      6. The Slowest Database in Town
      7. Your Database File Is as Big as a Whale
      8. You Get a Mess When Importing Your Spreadsheet
      9. We're Sorry; Your Database File Is Corrupt
      10. The Program Won't Start
    3. 23. Ten Uncommon Tips
      1. Document Everything as Though You'll be Questioned by the FBI
      2. Keep Your Fields as Small as Possible
      3. Use Number Fields for Real Numbers
      4. Validate Your Data
      5. Use Understandable Names to Keep Things Simple
      6. Delete with Great Caution
      7. Backup, Backup, Backup
      8. Think, Think, and Think Again
      9. Get Organized and Stay Organized
      10. There's No Shame in Asking for Help
    4. Getting Help
      1. Asking Access for Help
        1. Installed help files
        2. Asking the right questions
      2. Online Help
        1. Microsoft.com
        2. Search engines and other sites
      3. Who's Our Next Caller?
        1. Voice calls
          1. Basic installation
          2. Non‐technical questions
          3. Technical questions
        2. TDD/TT calls