Access 2007: The Missing Manual. The Missing Manual (e-book)

Access 2007: The Missing Manual. The Missing Manual (e-book) - Opis

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