book

Access 2007 for Starters: The Missing Manual

Name: Access 2007 for Starters: The Missing Manual
Author: Matthew MacDonald
ISBN: 9780596553715

by Matthew MacDonald

January 2007

Beginner

396 pages

9h 31m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Access 2007 for Starters: The Missing Manual
SPECIAL OFFER: Upgrade this ebook with O’Reilly
The Missing Credits
About the Author
About the Creative Team
Acknowledgements
The Missing Manual Series
Introduction
What You Can Do with AccessThe Two Sides of AccessAccess vs. Excel
The New Face of Access 2007
The RibbonUsing the Ribbon with the KeyboardThe Office MenuThe Quick Access Toolbar
About This Book
About the OutlineAbout → These → ArrowsContextual tabsDrop-down buttonsAbout Shortcut KeysAbout ClickingExamplesAbout MissingManuals.comSafari® 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 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 paste
1.3. Saving and Opening Access Databases
1.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 Database
1.4. The Navigation Pane
1.4.1. Browsing Tables with the Navigation Pane1.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 Fields2.2.2. How Updates Work in Design View
2.3. Access Data Types
2.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.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 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 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. Mastering the Datasheet: Sorting, Searching, Filtering, and More
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 Columns
3.2. Datasheet Navigation
3.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. Searching
3.3. Printing the Datasheet
3.3.1. Print Preview3.3.1.1. Moving around the print preview3.3.1.2. Changing the page layout3.3.2. Fine-Tuning a Printout
4. Blocking Bad Data
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 indexes
4.2. Input Masks
4.2.1. Using a Ready-Made Mask
4.3. Validation Rules
4.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 Rule
4.4. Lookups
4.4.1. Creating a Simple Lookup with Fixed Values4.4.2. Adding New Values to Your Lookup List
5. Linking Tables with Relationships
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 Relationship
5.2. Using a Relationship
5.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 Tables
5.3. More Exotic Relationships
5.3.1. One-to-One Relationship5.3.2. Many-to-Many Relationship5.3.2.1. Junction tables
5.4. Relationship Practice
5.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
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 View6.2.1.1. Building filter expressions6.2.1.2. Getting the top records6.2.2. Creating a Simple Query with the Query Wizard
6.3. Queries and Related Tables
6.3.1. Joining Tables in a Query6.3.2. Outer Joins6.3.2.1. Finding unmatched records6.3.3. Multiple Joins
6.4. Query Power: Calculated Fields and Text Expressions
6.5. Calculated Fields
6.5.1. Defining a Calculated Field6.5.2. Simple Math with Numeric Fields6.5.2.1. Date fields6.5.2.2. Order of operations6.5.3. Expressions with Text
7. Queries that Update Records
7.1. Understanding Action Queries7.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 Basics8.1.1. Creating a Simple Report8.1.2. Arranging a Report8.1.3. Adding and Removing Fields8.1.4. The Many Views of a Report8.1.5. Creating a Report from Scratch
8.2. Printing, Previewing, and Exporting a Report
8.2.1. Previewing a Report8.2.2. Exporting a Report8.2.3. Getting the "Save As PDF" Add-in
8.3. Formatting a Report
8.3.1. Formatting Columns and Column Headers8.3.1.1. Formatting numeric fields8.3.1.2. Alternating row formatting8.3.1.3. Gridlines8.3.1.4. Borders
8.4. Filtering and Sorting a Report
8.4.1. Filtering a Report8.4.2. Sorting a Report
9. Creating Simple Forms
9.1. Form Basics9.1.1. Creating a Simple Form9.1.2. Using a Form9.1.2.1. Finding and editing a record9.1.2.2. Adding a record9.1.2.3. Deleting a record9.1.2.4. Printing records
9.2. Sorting and Filtering in a Form
9.2.1. Sorting a Form9.2.2. Filtering a Form9.2.3. Using the Filter by Form Feature9.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 Exporting10.1.1. Understanding Exports10.1.2. Understanding Imports
10.2. Using the Clipboard
10.2.1. Copying a Table from Access to Somewhere Else10.2.2. Copying Cells from Excel into Access
10.3. Import and Export Operations
10.3.1. Importable File Types10.3.2. Importing Data10.3.3. Importing from an Excel File10.3.4. Importing from a Text File10.3.5. Exportable File Types10.3.6. Exporting Data
Index
About the Author
Colophon
SPECIAL OFFER: Upgrade this ebook with O’Reilly

Content preview from Access 2007 for Starters: The Missing Manual

Query Power: Calculated Fields and Text Expressions

Every Access expert stocks his or her database with a few (or a few dozen) useful queries that simplify day-to-day tasks. Earlier in this chapter, you learned how to create queries that chew through avalanches of information and present exactly what you need to see. But as Access masters know, there's much more power lurking just beneath the surface of the query design window.

Here's how you define a list of classes that includes the name of the assigned teacher next to each class. The top query shows the design you need, and the bottom figure shows the result.

Figure 6-21. Here's how you define a list of classes that includes the name of the assigned teacher next to each class. The top query shows the design you need, and the bottom figure shows the result.

In this section, you'll delve into some query magic that's sure to impress your boss, co-workers, and romantic partners. You'll learn how to carry out calculations in a query and perform some basic text manipulation (joining together first and last names, for example).

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596528337Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Access 2007 for Starters: The Missing Manual

by Matthew MacDonald

Query Power: Calculated Fields and Text Expressions

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.