book

Access 2007: The Missing Manual

Name: Access 2007: The Missing Manual
Author: Matthew MacDonald
ISBN: 9780596554545

by Matthew MacDonald

December 2006

Beginner

754 pages

26h 55m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Access 2007: The Missing Manual
A Note Regarding Supplemental Files
The Missing Credits
About the AuthorAbout the Creative TeamAcknowledgementsThe Missing Manual Series
Introduction
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
I. Storing Information in Tables
1. Creating Your First Database
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. Building Smarter Tables
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. 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 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. 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 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. 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 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

II. Manipulating Data with Queries
6. Queries That Select Records
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. Essential Query Tricks
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. Queries That Update Records
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. Analyzing Data with Crosstab Queries and Pivot Tables
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
III. Printing Reports
10. Creating Reports
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. Designing Advanced Reports
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
IV. Building a User Interface with Forms
12. Creating Simple Forms
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. Designing Advanced Forms
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. Building a Navigation System
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
V. Programming Access
15. Automating Tasks with Macros
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. Automating Tasks with Visual Basic
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. Writing Smarter Code
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
VI. Sharing Access with the Rest of the World
18. Sharing a Database with Multiple Users
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. Importing and Exporting Data
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. Connecting Access to SQL Server
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. Connecting Access to SharePoint
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
VII. Appendix
A. Customizing the Quick Access Toolbar
A.1. The Quick Access ToolbarA.1.1. Adding ButtonsA.1.2. Customizing Specific Databases
Index
About the Author
Colophon
Copyright

Content preview from Access 2007: The Missing Manual

Chapter 9. Analyzing Data with Crosstab Queries and Pivot Tables

Access is ready and willing to store all the details in your database. But sometimes you don’t need to know everything—instead you just want the big picture. You need a way to take your raw data, which may include hundreds or thousands of records, and summarize it in some meaningful way.

You’ve already learned one way to analyze large volumes of information: with a totals query (Section 7.3). Using a totals query, you can take a huge swatch of rows and reduce it to a few neatly grouped subtotals. In this chapter, you’ll learn about two more specialized options for crunching the numbers: crosstab queries and pivot tables.

Crosstab queries and pivot tables play the same role as the totals queries that you’ve already mastered. However, they present the data in a slightly different way. Crosstab queries use extra columns to pack summary information into an extremely tight table. Pivot tables use a drag-and-drop interface that lets you rearrange your summary on the fly to search for different trends and relationships. Both these features get plenty of usage in the toolkit of every Access expert.

Note

To try out crosstab queries and pivot tables, you need data—and lots of it. The sample databases used in earlier chapters just don’t have enough raw data. Instead, the examples in this chapter use some of the tables from Microsoft’s huge AdventureWorks sample database, which has the product catalog and sales data for a fictional ...

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

Access 2007 for Starters: The Missing Manual

Publisher Resources

ISBN: 0596527608Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Access 2007: The Missing Manual

by Matthew MacDonald

Chapter 9. Analyzing Data with Crosstab Queries and Pivot Tables

Note

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.