book

Access 2007: The Missing Manual

by Matthew MacDonald

December 2006

Beginner

754 pages

26h 55m

English

O'Reilly Media, Inc.

Read now

Unlock full access

About the AuthorAbout the Creative TeamAcknowledgementsThe Missing Manual Series
What You Can Do with AccessThe Two Sides of AccessAccess vs. ExcelAccess vs. SQL ServerThe New Face of Access 2007The RibbonUsing the Ribbon with the KeyboardThe Office MenuThe Quick Access ToolbarThe New Features in Access 2007About This BookAbout the OutlineAbout → These → ArrowsContextual tabsDrop-down buttonsOrdinary menusAbout Shortcut KeysAbout ClickingExamplesAbout MissingManuals.comSafari® Enabled
1.1. Understanding Access Databases1.2. Getting Started1.2.1. Creating a New Database1.2.2. Understanding Tables1.2.3. Creating a Simple Table1.2.4. Editing a Table1.2.4.1. Edit mode1.2.4.2. Shortcut keys1.2.4.3. Cut, copy, and paste1.3. Saving and Opening Access Databases1.3.1. Making Backups1.3.2. Saving a Database with a Different Name or Format1.3.3. Opening a Database1.3.4. Opening More Than One Database at Once1.3.5. Opening a Database Created in an Older Version of Access1.3.6. Creating Another Database1.4. The Navigation Pane1.4.1. Browsing Tables with the Navigation Pane1.4.2. Managing Database Objects
2.1. Understanding Data Types2.2. Design View2.2.1. Organizing and Describing Your Fields2.2.2. How Updates Work in Design View2.3. Access Data Types2.3.1. Text2.3.1.1. Text length2.3.2. Memo2.3.2.1. Formatted text2.3.3. Number2.3.3.1. Number size2.3.3.2. Number formatting2.3.4. Currency2.3.5. Date/Time2.3.5.1. Custom date formats2.3.6. Yes/No2.3.7. Hyperlink2.3.8. Attachment2.3.9. AutoNumber2.3.9.1. Using AutoNumbers without revealing the size of your table2.3.9.2. Using replication IDs2.4. The Primary Key2.4.1. Creating Your Own Primary Key Field2.5. Six Principles of Database Design2.5.1. 1. Choose Good Field Names2.5.2. 2. Break Down Your Information2.5.3. 3. Include All the Details in One Place2.5.4. 4. Avoid Duplicating Information2.5.5. 5. Avoid Redundant Information2.5.6. 6. Include an ID Field
3.1. Datasheet Customization3.1.1. Formatting the Datasheet3.1.2. Rearranging Columns3.1.3. Resizing Rows and Columns3.1.4. Hiding Columns3.1.5. Freezing Columns3.2. Datasheet Navigation3.2.1. Sorting3.2.1.1. Sorting on multiple fields3.2.2. Filtering3.2.2.1. Quick filters3.2.2.2. Filter by selection3.2.2.3. Filter by condition3.2.3. Searching3.3. Advanced Editing3.3.1. The Spell Checker3.3.1.1. Spell checking options3.3.2. AutoCorrect3.3.3. Special Characters3.4. Printing the Datasheet3.4.1. Print Preview3.4.1.1. Moving around the print preview3.4.1.2. Changing the page layout3.4.2. Fine-Tuning a Printout
4.1. Data Integrity Basics4.1.1. Preventing Blank Fields4.1.1.1. Blank values and empty text4.1.2. Setting Default Values4.1.3. Preventing Duplicate Values with Indexes4.1.3.1. Multifield indexes4.2. Input Masks4.2.1. Using a Ready-Made Mask4.2.2. Creating Your Own Mask4.3. Validation Rules4.3.1. Applying a Field Validation Rule4.3.2. Writing a Field Validation Rule4.3.2.1. Validating numbers4.3.2.2. Validating dates4.3.2.3. Validating text4.3.2.4. Combining validation conditions4.3.3. Creating a Table Validation Rule4.4. Lookups4.4.1. Creating a Simple Lookup with Fixed Values4.4.2. Adding New Values to Your Lookup List
5.1. Relationship Basics5.1.1. Redundant Data vs. Related Data5.1.2. Matching Fields: The Relationship Link5.1.3. Linking with the ID Column5.1.4. The Parent-Child Relationship5.2. Using a Relationship5.2.1. Defining a Relationship5.2.2. Editing Relationships5.2.3. Referential Integrity5.2.3.1. Blank values for unlinked records5.2.3.2. Cascading deletes5.2.3.3. Cascading updates5.2.4. Navigating a Relationship5.2.5. Lookups with Related Tables5.3. More Exotic Relationships5.3.1. One-to-One Relationship5.3.2. Many-to-Many Relationship5.3.2.1. Junction tables5.3.2.2. Multi-value fields5.4. Relationship Practice5.4.1. The Music School5.4.1.1. Identifying the tables5.4.1.2. Identifying the relationships5.4.1.3. Getting more detailed5.4.2. The Chocolate Store5.4.2.1. The product catalog and customer list5.4.2.2. Ordering products

6.1. Query Basics6.2. Creating Queries6.2.1. Creating a Query in Design View6.2.1.1. Building filter expressions6.2.1.2. Getting the top records6.2.2. Creating a Simple Query with the Query Wizard6.2.3. Understanding the SQL View6.2.3.1. Analyzing a query6.2.3.2. Creating a union query6.3. Queries and Related Tables6.3.1. Joining Tables in a Query6.3.2. Outer Joins6.3.2.1. Finding unmatched records6.3.3. Multiple Joins
7.1. Calculated Fields7.1.1. Defining a Calculated Field7.1.2. Simple Math with Numeric Fields7.1.2.1. Date fields7.1.2.2. Order of operations7.1.3. Expressions with Text7.2. Query Functions7.2.1. Using a Function7.2.1.1. Nested functions7.2.2. The Expression Builder7.2.3. Formatting Numbers7.2.4. More Mathematical Functions7.2.5. Text Functions7.2.6. Date Functions7.2.7. Dealing with Blank Values (Nulls)7.3. Summarizing Data7.3.1. Grouping a Totals Query7.3.2. Joins in a Totals Query7.4. Query Parameters
8.1. Understanding Action Queries8.1.1. Testing Action Queries (Carefully)8.1.2. The Action Query Family8.2. Update Queries8.3. Append Queries8.3.1. Creating an Append (or Make-Table) Query8.3.2. Getting AutoNumbers to Start at Values Other Than 18.4. Delete Queries8.5. Tutorial: Flagging Out-of-Stock Orders8.5.1. Finding Out-of-Stock Items8.5.2. Putting the Orders on Hold
9.1. Understanding Crosstab Queries9.2. Creating Crosstab Queries9.2.1. Creating a Crosstab Query with the Wizard9.2.2. Creating a Crosstab Query from Scratch9.3. Pivot Tables9.3.1. Building a Pivot Table9.3.2. Manipulating a Pivot Table9.3.3. Creating a Calculated Field9.3.4. Hiding and Showing Details9.3.5. Filtering Pivot Tables9.4. Pivot Charts9.4.1. Choosing a Chart Type9.4.2. Printing a Pivot Chart
10.1. Report Basics10.1.1. Creating a Simple Report10.1.2. Arranging a Report10.1.3. Adding and Removing Fields10.1.4. The Many Views of a Report10.1.5. Creating a Report from Scratch10.2. Printing, Previewing, and Exporting a Report10.2.1. Previewing a Report10.2.2. Exporting a Report10.2.3. Getting the “Save As PDF” Add-in10.3. Formatting a Report10.3.1. Formatting Columns and Column Headers10.3.1.1. Formatting numeric fields10.3.1.2. Alternating row formatting10.3.1.3. Gridlines10.3.1.4. Borders10.3.2. Conditional Formatting10.4. Filtering and Sorting a Report10.4.1. Filtering a Report10.4.2. Sorting a Report
11.1. Improving Reports in Design View11.1.1. The Design View Sections11.1.2. Understanding Controls11.1.3. Moving Fields out of a Layout11.1.4. Adding More Controls11.1.5. Creating a Report from Scratch (in Design View)11.2. The Report Wizard11.3. The Label Wizard11.4. Fine-Tuning Reports with Properties11.4.1. Modifying Common Properties11.5. Expressions11.6. Grouping11.6.1. Report Grouping11.6.2. Fine-Tuning with the “Group, Sort, and Total” Pane11.6.2.1. Sort by …11.6.2.2. Group on …11.6.2.3. From A to Z/from smallest to largest11.6.2.4. By entire value11.6.2.5. With … totaled11.6.2.6. With title …11.6.2.7. With a header section / with a footer section11.6.2.8. Keep group together on one page11.6.3. Multiple Groups
12.1. Form Basics12.1.1. Creating a Simple Form12.1.2. Using a Form12.1.2.1. Finding and editing a record12.1.2.2. Adding a record12.1.2.3. Deleting a record12.1.2.4. Printing records12.2. Sorting and Filtering in a Form12.2.1. Sorting a Form12.2.2. Filtering a Form12.2.3. Using the Filter by Form Feature12.2.4. Saving Filters for the Future12.3. Creating Better Layouts12.3.1. Liberating Controls from Layouts12.3.2. Using More Than One Layout12.3.3. Using Tabular Layouts12.3.4. Showing Multiple Records in any Form12.3.5. Split Forms12.3.6. Even More Useful Form Properties12.4. The Form Wizard
13.1. Customizing Forms in Design View13.1.1. Form Sections: The Different Parts of Your Form13.1.2. Adding Controls to Your Form13.1.3. The Control Gallery: A Quick Tour13.1.4. Arranging Controls on Your Form13.1.4.1. Aligning controls13.1.4.2. Sizing controls13.1.4.3. Spacing controls13.1.4.4. Controls that overlap13.1.5. Anchoring: Automatically Resizing Controls13.1.5.1. Making controls as wide as the form13.1.5.2. Making a single control as large as possible13.1.6. Tab Order: Making Keyboard Navigation Easier13.2. Taking Control of Controls13.2.1. Locking Down Fields13.2.2. Prevent Errors with Validation13.2.3. Performing Calculations with Expressions13.2.4. Organizing with Tab Controls13.2.5. Going Places with Links13.2.6. Navigating with Lists13.2.7. Performing Actions with Command Buttons13.3. Forms and Linked Tables13.3.1. Table Relationships and Simple Forms13.3.2. The Subform Control13.3.3. Creating Customized Subforms
14.1. Mastering the Navigation Pane14.1.1. Configuring the Navigation List14.1.2. Better Filtering14.1.3. Hiding Objects14.1.4. Using Custom Groups14.1.5. Searching the Navigation List14.2. Building Forms with Navigation Smarts14.2.1. Building a Switchboard14.2.2. Designating a Startup Form14.2.3. Switchboard Alternatives14.2.3.1. Custom switchboard forms14.2.3.2. Compound forms14.2.4. Showing All Your Forms in a List14.3. Linking to Related Data14.3.1. Showing Linked Records in Separate Forms14.3.2. Showing More Detailed Reports with Links
15.1. Macro Essentials15.1.1. Creating a Macro15.1.2. Running a Macro15.1.3. Debugging a Macro15.2. Macros and Security15.2.1. Unsafe Macro Actions15.2.2. How Access Handles Unsafe Macros15.2.3. The Trust Center15.2.4. Setting Up a Trusted Location15.3. Three Macro Recipes15.3.1. Find a Record15.3.2. Print a Report15.3.3. Email Your Data15.4. Managing Macros15.4.1. Macro Groups15.4.2. Assigning a Macro to a Keystroke15.4.3. Configuring a Startup Macro15.5. Connecting Macros to Forms15.5.1. Understanding Events15.5.2. Attaching a Macro to an Event15.5.3. Reading Arguments from a Form15.5.4. Changing Form Properties15.6. Conditional Macros15.6.1. Building a Condition15.6.2. Validating Data with Conditions15.6.3. More Complex Conditional Macros
16.1. The Visual Basic Editor16.1.1. Adding a New Module16.1.2. Writing the Simplest Possible Code Routine16.2. Putting Code in a Form16.2.1. Responding to a Form Event16.2.2. Calling the Code in a Module16.2.3. Reading and Writing the Fields on a Form16.3. Understanding Objects16.3.1. Properties16.3.2. Methods16.3.3. Events16.4. Using Objects16.4.1. Indicating That a Record Has Changed16.4.2. Creating a Mouseover Effect
17.1. Exploring the VB Language17.1.1. Storing Information in Variables17.1.2. Making Decisions17.1.3. Repeating Actions with a Loop17.1.4. Creating Custom Functions17.1.5. Putting It All Together: A Function for Testing Credit Cards17.2. Dealing with Trouble17.2.1. Debugging17.2.2. Error Handling17.3. Deeper into Objects17.3.1. The DoCmd Object17.3.2. Converting a Macro to VB Code17.4. Using VB to Run a Better Business17.4.1. Keeping a Running Total17.4.2. Getting Price Information17.4.3. Adding a New Product During an Order17.4.4. Managing Order Fulfillment17.4.5. Updating Stock Numbers
18.1. Opening Up Your Database to the World18.1.1. How Access Sharing Works18.2. Preparing Your Database18.2.1. Understanding Split Databases18.2.2. Splitting a Database with the Wizard18.2.3. How Linked Tables Work18.2.4. Manually Splitting a Database18.2.5. Locking Down Your Front End18.2.6. Sharing a Database with People Who Don’t Own Access18.3. Playing Well with Others18.3.1. Seeing Changes As They Happen18.3.2. Dealing with Editing Conflicts18.3.3. Using Locks to Stop Overlapping Edits18.3.4. Opening a Database in Exclusive Mode18.4. Data Corruption18.4.1. Diagnosing (and Fixing) Corrupt Databases18.4.2. Preventing Corruption18.5. Securing Your Database18.5.1. Password-Protecting Your Database18.5.2. Passwords and Split Databases18.5.3. Using Windows File Security
19.1. Case for Importing and Exporting19.1.1. Understanding Exports19.1.2. Understanding Imports19.2. Using the Clipboard19.2.1. Copying a Table from Access to Somewhere Else19.2.2. Copying Cells from Excel into Access19.3. Import and Export Operations19.3.1. Importable File Types19.3.2. Importing Data19.3.3. Importing from an Excel File19.3.4. Importing from a Text File19.3.5. Exportable File Types19.3.6. Exporting Data19.3.7. Reusing Import and Export Settings19.4. Access and XML19.4.1. What Is XML, Really?19.4.2. Three Rules of XML19.4.2.1. The prolog19.4.2.2. Elements19.4.2.3. Nesting19.4.3. XML Files and Schemas19.4.4. The Access XML Story19.4.5. Exporting to an XML File19.4.6. Importing from an XML File19.5. Collecting Info by Email19.5.1. Creating an Email Message19.5.2. Processing Replies Manually19.5.3. Processing Replies Automatically19.5.4. Managing Your Email Collection Settings
20.1. Should You Switch to SQL Server?20.1.1. How SQL Server Works20.1.2. A Cheaper SQL Server20.2. Getting Started: SQL Server 2005 Express20.2.1. Installing SQL Server Express20.2.2. Putting SQL Server on the Network20.3. Creating a SQL Server Database20.3.1. Upsizing a Database20.3.2. Managing Your Database20.3.3. Creating a SQL Server Database from Scratch20.4. Adding Objects to a SQL Server Database20.4.1. Creating a Table20.4.1.1. SQL Server data types20.4.1.2. AutoNumber fields20.4.1.3. Lookups20.4.1.4. Relationships20.4.2. Understanding Queries20.4.3. Creating a View
21.1. Understanding SharePoint21.1.1. What You Can Do in SharePoint21.2. Setting Up SharePoint21.2.1. Creating a Team Site21.2.2. Customizing Your Site21.3. SharePoint and Access21.3.1. Building a List21.3.2. Exporting a Table to SharePoint21.3.3. Importing Data in Access21.3.4. Move a Whole Database to SharePoint21.3.5. Editing Your SharePoint Data in Access21.3.6. Making Offline Changes
A.1. The Quick Access ToolbarA.1.1. Adding ButtonsA.1.2. Customizing Specific Databases

Content preview from Access 2007: The Missing Manual

Colophon

Sanders Kleinfeld was the production editor for Access 2007:The Missing Manual. Mary Brady and Marlowe Shaeffer provided quality control. Lucie Haskins wrote the index.

The cover of this book is based on a series design originally created by David Freedman and modified by Mike Kohnke, Karen Montgomery, and Fitch (http://www.fitch.com). Back cover design, dog illustration, and color selection by Fitch.

David Futato designed the interior layout, based on a series design by Phil Simpson. This book was converted by Abby Fox to FrameMaker 5.5.6. The text font is Adobe Minion; the heading font is Adobe Formata Condensed; and the code font is LucasFont’s TheSans Mono Condensed. The illustrations that appear in the book were produced by Robert Romano and Jessamyn Read using Macromedia FreeHand MX and Adobe Photoshop CS.