Home Page Icon
Home Page
Table of Contents for
II. MANIPULATING DATA WITH QUERIES
Close
II. MANIPULATING DATA WITH QUERIES
by Matthew MacDonald
Access 2007 for Starters: The Missing Manual
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
The New Face of Access 2007
The Ribbon
Using the Ribbon with the Keyboard
The Office Menu
The Quick Access Toolbar
About This Book
About the Outline
About → These → Arrows
Contextual tabs
Drop-down buttons
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.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. Printing the Datasheet
3.3.1. Print Preview
3.3.1.1. Moving around the print preview
3.3.1.2. Changing the page layout
3.3.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.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.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.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
6.4. Query Power: Calculated Fields and Text Expressions
6.5. Calculated Fields
6.5.1. Defining a Calculated Field
6.5.2. Simple Math with Numeric Fields
6.5.2.1. Date fields
6.5.2.2. Order of operations
6.5.3. Expressions with Text
7. Queries that Update Records
7.1. Understanding Action Queries
7.1.1. Testing Action Queries (Carefully)
7.1.2. The Action Query Family
7.2. Update Queries
7.3. Append Queries
7.3.1. Creating an Append (or Make-Table) Query
7.4. Delete Queries
III. PRINTING REPORTS AND USING FORMS
8. Creating Reports
8.1. Report Basics
8.1.1. Creating a Simple Report
8.1.2. Arranging a Report
8.1.3. Adding and Removing Fields
8.1.4. The Many Views of a Report
8.1.5. Creating a Report from Scratch
8.2. Printing, Previewing, and Exporting a Report
8.2.1. Previewing a Report
8.2.2. Exporting a Report
8.2.3. Getting the “Save As PDF” Add-in
8.3. Formatting a Report
8.3.1. Formatting Columns and Column Headers
8.3.1.1. Formatting numeric fields
8.3.1.2. Alternating row formatting
8.3.1.3. Gridlines
8.3.1.4. Borders
8.4. Filtering and Sorting a Report
8.4.1. Filtering a Report
8.4.2. Sorting a Report
9. Creating Simple Forms
9.1. Form Basics
9.1.1. Creating a Simple Form
9.1.2. Using a Form
9.1.2.1. Finding and editing a record
9.1.2.2. Adding a record
9.1.2.3. Deleting a record
9.1.2.4. Printing records
9.2. Sorting and Filtering in a Form
9.2.1. Sorting a Form
9.2.2. Filtering a Form
9.2.3. Using the Filter by Form Feature
9.2.4. Saving Filters for the Future
9.3. The Form Wizard
IV. SHARING ACCESS WITH THE REST OF THE WORLD
10. Importing and Exporting Data
10.1. Case for Importing and Exporting
10.1.1. Understanding Exports
10.1.2. Understanding Imports
10.2. Using the Clipboard
10.2.1. Copying a Table from Access to Somewhere Else
10.2.2. Copying Cells from Excel into Access
10.3. Import and Export Operations
10.3.1. Importable File Types
10.3.2. Importing Data
10.3.3. Importing from an Excel File
10.3.4. Importing from a Text File
10.3.5. Exportable File Types
10.3.6. Exporting Data
Index
About the Author
Colophon
Copyright
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
5. Linking Tables with Relationships
Next
Next Chapter
6. Queries that Select Records
Part II. MANIPULATING DATA WITH QUERIES
Chapter 6 Queries That Select Records
Chapter 7 Queries That Update Records
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset