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

The Navigation Pane

It's time to step back and take a look at what you've accomplished so far. You've created the Bobblehead database, and added a single database object: a table named Dolls. You've filled the Dolls table with several records. You don't have the fancy windows, reports, and search routines that make a database work really smoothly, but you do have the most important ingredient—organized data.

One issue you haven't tackled yet is how you manage the objects in your database. For example, if you have more than one table, you need a way to move back and forth between the two. That tool's the navigation pane, shown in Figure 1-19.

Browsing Tables with the Navigation Pane

The navigation pane shows the objects (Section 1.1) that are part of your database, and it lets you manipulate them. However, you don't necessarily see all your database objects at all times. The navigation pane has several different viewing modes, so you can home in on exactly what interests you.

When you first create a database, the navigation pane shows only the tables in your database. That's good enough for now—after all, your database doesn't contain anything but the tables you've created.

Unhappy with the space consumed by the navigation pane? Click the Open/Close but-ton in the top-right corner (top), and the navigation bar slides out of the way to give more room for the datasheet (bottom). Click the button again to expand it back into view.

Figure 1-19. Unhappy with the space consumed by the navigation pane? Click the Open/Close but-ton in the top-right corner (top), and the navigation bar slides out of the way to give more room for the datasheet (bottom). ...

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

The Navigation Pane

Browsing Tables with the Navigation Pane

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.