Access 2007: The Missing Manual

Inhaltsverzeichnis
The Missing Credits
Introduction
Part One: Storing Information in Tables
Chapter 1. Creating Your First Database
Understanding Access Databases
Getting Started
Creating a New Database
Understanding Tables
Creating a Simple Table
Editing a Table
Saving and Opening Access Databases
Making Backups
Saving a Database with a Different Name or Format
Opening a Database
Opening More Than One Database at Once
Opening a Database Created in an Older Version of Access
Creating Another Database
The Navigation Pane
Browsing Tables with the Navigation Pane
Managing Database Objects
Chapter 2. Building Smarter Tables
Understanding Data Types
Design View
Organizing and Describing Your Fields
How Updates Work in Design View
Access Data Types
Text
Memo
Number
Currency
Date/Time
Yes/No
Hyperlink
Attachment
AutoNumber
The Primary Key
Creating Your Own Primary Key Field
Six Principles of Database Design
1. Choose Good Field Names
2. Break Down Your Information
3. Include All the Details in One Place
4. Avoid Duplicating Information
5. Avoid Redundant Information
6. Include an ID Field
Chapter 3. Mastering the Datasheet: Sorting, Searching, Filtering, and More
Datasheet Customization
Formatting the Datasheet
Rearranging Columns
Resizing Rows and Columns
Hiding Columns
Freezing Columns
Datasheet Navigation
Sorting
Filtering
Searching
Advanced Editing
The Spell Checker
AutoCorrect
Special Characters
Printing the Datasheet
Print Preview
Fine-Tuning a Printout
Chapter 4. Blocking Bad Data
Data Integrity Basics
Preventing Blank Fields
Setting Default Values
Preventing Duplicate Values with Indexes
Input Masks
Using a Ready-Made Mask
Creating Your Own Mask
Validation Rules
Applying a Field Validation Rule
Writing a Field Validation Rule
Creating a Table Validation Rule
Lookups
Creating a Simple Lookup with Fixed Values
Adding New Values to Your Lookup List
Chapter 5. Linking Tables with Relationships
Relationship Basics
Redundant Data vs. Related Data
Matching Fields: The Relationship Link
Linking with the ID Column
The Parent-Child Relationship
Using a Relationship
Defining a Relationship
Editing Relationships
Referential Integrity
Navigating a Relationship
Lookups with Related Tables
More Exotic Relationships
One-to-One Relationship
Many-to-Many Relationship
Relationship Practice
The Music School
The Chocolate Store
Part Two: Manipulating Data with Queries
Chapter 6. Queries That Select Records
Query Basics
Creating Queries
Creating a Query in Design View
Creating a Simple Query with the Query Wizard
Understanding the SQL View
Queries and Related Tables
Joining Tables in a Query
Outer Joins
Multiple Joins
Chapter 7. Essential Query Tricks
Calculated Fields
Defining a Calculated Field
Simple Math with Numeric Fields
Expressions with Text
Query Functions
Using a Function
The Expression Builder
Formatting Numbers
More Mathematical Functions
Text Functions
Date Functions
Dealing with Blank Values (Nulls)
Summarizing Data
Grouping a Totals Query
Joins in a Totals Query
Query Parameters
Chapter 8. Queries That Update Records
Understanding Action Queries
Testing Action Queries (Carefully)
The Action Query Family
Update Queries
Append Queries
Creating an Append (or Make-Table) Query
Getting AutoNumbers to Start at Values Other Than 1
Delete Queries
Tutorial: Flagging Out-of-Stock Orders
Finding Out-of-Stock Items
Putting the Orders on Hold
Chapter 9. Analyzing Data with Crosstab Queries and Pivot Tables
Understanding Crosstab Queries
Creating Crosstab Queries
Creating a Crosstab Query with the Wizard
Creating a Crosstab Query from Scratch
Pivot Tables
Building a Pivot Table
Manipulating a Pivot Table
Creating a Calculated Field
Hiding and Showing Details
Filtering Pivot Tables
Pivot Charts
Choosing a Chart Type
Printing a Pivot Chart
Part Three: Printing Reports
Chapter 10. Creating Reports
Report Basics
Creating a Simple Report
Arranging a Report
Adding and Removing Fields
The Many Views of a Report
Creating a Report from Scratch
Printing, Previewing, and Exporting a Report
Previewing a Report
Exporting a Report
Getting the "Save As PDF" Add-in
Formatting a Report
Formatting Columns and Column Headers
Conditional Formatting
Filtering and Sorting a Report
Filtering a Report
Sorting a Report
Chapter 11. Designing Advanced Reports
Improving Reports in Design View
The Design View Sections
Understanding Controls
Moving Fields out of a Layout
Adding More Controls
Creating a Report from Scratch (in Design View)
The Report Wizard
The Label Wizard
Fine-Tuning Reports with Properties
Modifying Common Properties
Expressions
Grouping
Report Grouping
Fine-Tuning with the "Group, Sort, and Total" Pane
Multiple Groups
Part Four: Building a User Interface with Forms
Chapter 12. Creating Simple Forms
Form Basics
Creating a Simple Form
Using a Form
Sorting and Filtering in a Form
Sorting a Form
Filtering a Form
Using the Filter by Form Feature
Saving Filters for the Future
Creating Better Layouts
Liberating Controls from Layouts
Using More Than One Layout
Using Tabular Layouts
Showing Multiple Records in any Form
Split Forms
Even More Useful Form Properties
The Form Wizard
Chapter 13. Designing Advanced Forms
Customizing Forms in Design View
Form Sections: The Different Parts of Your Form
Adding Controls to Your Form
The Control Gallery: A Quick Tour
Arranging Controls on Your Form
Anchoring: Automatically Resizing Controls
Tab Order: Making Keyboard Navigation Easier
Taking Control of Controls
Locking Down Fields
Prevent Errors with Validation
Performing Calculations with Expressions
Organizing with Tab Controls
Going Places with Links
Navigating with Lists
Performing Actions with Command Buttons
Forms and Linked Tables
Table Relationships and Simple Forms
The Subform Control
Creating Customized Subforms
Chapter 14. Building a Navigation System
Mastering the Navigation Pane
Configuring the Navigation List
Better Filtering
Hiding Objects
Using Custom Groups
Searching the Navigation List
Building Forms with Navigation Smarts
Building a Switchboard
Designating a Startup Form
Switchboard Alternatives
Showing All Your Forms in a List
Linking to Related Data
Showing Linked Records in Separate Forms
Showing More Detailed Reports with Links
Part Five: Programming Access
Chapter 15. Automating Tasks with Macros
Macro Essentials
Creating a Macro
Running a Macro
Debugging a Macro
Macros and Security
Unsafe Macro Actions
How Access Handles Unsafe Macros
The Trust Center
Setting Up a Trusted Location
Three Macro Recipes
Find a Record
Print a Report
Email Your Data
Managing Macros
Macro Groups
Assigning a Macro to a Keystroke
Configuring a Startup Macro
Connecting Macros to Forms
Understanding Events
Attaching a Macro to an Event
Reading Arguments from a Form
Changing Form Properties
Conditional Macros
Building a Condition
Validating Data with Conditions
More Complex Conditional Macros
Chapter 16. Automating Tasks with Visual Basic
The Visual Basic Editor
Adding a New Module
Writing the Simplest Possible Code Routine
Putting Code in a Form
Responding to a Form Event
Calling the Code in a Module
Reading and Writing the Fields on a Form
Understanding Objects
Properties
Methods
Events
Using Objects
Indicating That a Record Has Changed
Creating a Mouseover Effect
Chapter 17. Writing Smarter Code
Exploring the VB Language
Storing Information in Variables
Making Decisions
Repeating Actions with a Loop
Creating Custom Functions
Putting It All Together: A Function for Testing Credit Cards
Dealing with Trouble
Debugging
Error Handling
Deeper into Objects
The DoCmd Object
Converting a Macro to VB Code
Using VB to Run a Better Business
Keeping a Running Total
Getting Price Information
Adding a New Product During an Order
Managing Order Fulfillment
Updating Stock Numbers
Part Six: Sharing Access with the Rest of the World
Chapter 18. Sharing a Database with Multiple Users
Opening Up Your Database to the World
How Access Sharing Works
Preparing Your Database
Understanding Split Databases
Splitting a Database with the Wizard
How Linked Tables Work
Manually Splitting a Database
Locking Down Your Front End
Sharing a Database with People Who Don't Own Access
Playing Well with Others
Seeing Changes As They Happen
Dealing with Editing Conflicts
Using Locks to Stop Overlapping Edits
Opening a Database in Exclusive Mode
Data Corruption
Diagnosing (and Fixing) Corrupt Databases
Preventing Corruption
Securing Your Database
Password-Protecting Your Database
Passwords and Split Databases
Using Windows File Security
Chapter 19. Importing and Exporting Data
Case for Importing and Exporting
Understanding Exports
Understanding Imports
Using the Clipboard
Copying a Table from Access to Somewhere Else
Copying Cells from Excel into Access
Import and Export Operations
Importable File Types
Importing Data
Importing from an Excel File
Importing from a Text File
Exportable File Types
Exporting Data
Reusing Import and Export Settings
Access and XML
What Is XML, Really?
Three Rules of XML
XML Files and Schemas
The Access XML Story
Exporting to an XML File
Importing from an XML File
Collecting Info by Email
Creating an Email Message
Processing Replies Manually
Processing Replies Automatically
Managing Your Email Collection Settings
Chapter 20. Connecting Access to SQL Server
Should You Switch to SQL Server?
How SQL Server Works
A Cheaper SQL Server
Getting Started: SQL Server 2005 Express
Installing SQL Server Express
Putting SQL Server on the Network
Creating a SQL Server Database
Upsizing a Database
Managing Your Database
Creating a SQL Server Database from Scratch
Adding Objects to a SQL Server Database
Creating a Table
Understanding Queries
Creating a View
Chapter 21. Connecting Access to SharePoint
Understanding SharePoint
What You Can Do in SharePoint
Setting Up SharePoint
Creating a Team Site
Customizing Your Site
SharePoint and Access
Building a List
Exporting a Table to SharePoint
Importing Data in Access
Move a Whole Database to SharePoint
Editing Your SharePoint Data in Access
Making Offline Changes
Part Seven: Appendix
Appendix. Customizing the Quick Access Toolbar
Index