Access 2013 · the Missing Manual by MacDonald, Matthew -- Read -- Imperial Library of Trantor

Index

Access 2013: 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 Access vs. SQL Server

The Access Ribbon

Using the Ribbon with the Keyboard The Quick Access Toolbar Backstage View

The Changes in Access 2013 About This Book

About the Outline About→These→Arrows

Contextual Tabs Drop-Down Buttons Backstage View Ordinary Menus

About Shortcut Keys

About the Online Resources

Missing CD Registration Feedback Errata

Safari® Books Online

1. Storing Information in Tables

1. Creating Your First Database

Understanding Access Databases Starting a Database Building Your First Table

Creating a Simple Table Editing a Table

Edit Mode Datasheet Shortcut Keys Cut, Copy, and Paste

Saving Databases

Making Backups Saving a Database with a Different Name Saving a Database in a Different Format Shrinking a Database

Opening Databases

Designating a Database as Trusted Opening More Than One Database at Once Opening a Database Created in an Older Version of Access

The Navigation Pane

Browsing Tables with the Navigation Pane Managing Database Objects

2. Building Smarter Tables

Understanding Data Types Design View

Organizing and Describing Your Fields How Updates Work in Design View

Access Data Types

Short Text

Text Length

Long Text

Formatted Text

Number

Number Size Number Formatting

Currency Date/Time

Custom Date Formats

Yes/No Hyperlink Attachment AutoNumber

Using AutoNumbers without Revealing the Size of Your Table Using Replication IDs

Calculated

The Primary Key

Creating Your Own Primary Key Field

Six Principles of Database Design

1. Choose Good Field Names 2. Break Down Your Information 3. Include All the Details in One Place 4. Avoid Duplicating Information 5. Avoid Redundant Information 6. Include an ID Field

3. Mastering the Datasheet: Sorting, Searching, and Filtering

Datasheet Customization

Formatting the Datasheet Rearranging Columns Resizing Rows and Columns Hiding Columns Freezing Columns

Datasheet Navigation

Sorting

Sorting on Multiple Fields

Filtering

Quick Filters Filter by Selection Filter by Condition

Searching

Advanced Editing

The Spell Checker

Spell-Checking Options

AutoCorrect Special Characters

Printing the Datasheet

Print Preview

Moving Around the Print Preview Changing the Page Layout

Fine-Tuning a Printout

4. Blocking Bad Data

Data Integrity Basics

Preventing Blank Fields

Blank Values and Empty Text

Setting Default Values Preventing Duplicate Values with Indexes

Multifield Indexes

Input Masks

Using a Readymade Mask Creating Your Own Mask

Validation Rules

Applying a Field Validation Rule Writing a Field Validation Rule

Validating Numbers Validating Dates Validating Text Combining Validation Conditions

Creating a Table Validation Rule

Lookups

Creating a Simple Lookup with Fixed Values Adding New Values to Your Lookup List

5. Linking Tables with Relationships

Relationship Basics

Redundant Data vs. Related Data Matching Fields: The Relationship Link Linking with the ID Column The Parent-Child Relationship

Using a Relationship

Defining a Relationship Editing Relationships Referential Integrity

Blank Values for Unlinked Records Cascading Deletes Cascading Updates

Navigating a Relationship Lookups with Related Tables

More Exotic Relationships

One-to-One Relationship Many-to-Many Relationship

Junction Tables Multi-Value Fields

Relationship Practice

The Music School

Identifying the Tables Identifying the Relationships Getting More Detailed

The Chocolate Store

The Product Catalog and Customer List Ordering Products

2. Manipulating Data with Queries

6. Queries That Select Records

Query Basics Creating Queries

Creating a Query in Design View

Building Filter Expressions Getting the Top Records

Creating a Simple Query with the Query Wizard Understanding the SQL View

Analyzing a Query Creating a Union Query

Queries and Related Tables

Joining Tables in a Query Outer Joins

Finding Unmatched Records

Multiple Joins

7. Essential Query Tricks

Calculated Fields

Defining a Calculated Field Simple Math with Numeric Fields

Date Fields Order of Operations

Expressions with Text

Query Functions

Using a Function

Nested Functions

The Expression Builder Formatting Numbers More Mathematical Functions Text Functions Date Functions Dealing with Blank Values (Nulls)

Query Parameters

8. Queries That Summarize Data

Totals Queries

Grouping in a Totals Query Joins in a Totals Query

Crosstab Queries

Creating a Crosstab Query with the Wizard Creating a Crosstab Query from Scratch

9. Queries That Change Tables

Understanding Action Queries

Testing Action Queries (Carefully) The Action Query Family

Update Queries

Creating an Update Query

Append Queries

Creating an Append (or Make-Table) Query Getting AutoNumbers to Start at Values Other Than 1

Delete Queries

Creating a Delete Query Deleting Linked Records

Tutorial: Flagging Out-of-Stock Orders

Finding Out-of-Stock Items Putting the Orders on Hold

3. Printing Reports

10. Creating Reports

Report Basics

Creating a One-Click Report Building a Report in Layout View The Many Views of a Report Rearranging a Report

Printing, Previewing, and Exporting a Report

Previewing a Report Export Formats Exporting a Report to a PDF Exporting a Report to a Different Format

Formatting a Report

Applying a Theme Formatting Columns and Column Headers

Formatting Numeric Fields Alternating Row Formatting Gridlines Borders Margin and Padding Background Image

Conditional Formatting Data Bars

Filtering and Sorting a Report

Filtering a Report Sorting a Report

11. Designing Advanced Reports

Improving Reports in Design View

The Design View Sections Understanding Controls Moving Fields Out of a Layout Adding More Controls Creating a Report from Scratch (in Design View)

The Report Wizard The Label Wizard Fine-Tuning Reports with Properties

Modifying Common Properties

Expressions Grouping

Report Grouping Fine-Tuning with the “Group, Sort, and Total” Pane

Sort by Group on With a on Top / From Smallest to Largest By Entire Value With … Totaled With Title With a Header Section / With a Footer Section Keep Group Together on One Page

Multiple Groups

4. Building a User Interface with Forms

12. Creating Simple Forms

Creating Forms

Building a Simple Form Using AutoNumber Fields in Forms Showing Pictures from a Table

Using Forms

Finding and Editing a Record Adding a Record Deleting a Record Printing Records

Sorting and Filtering in a Form

Sorting a Form Filtering a Form Using the “Filter by Form” Feature Saving Filters for the Future

Creating Fancy Forms

Manipulating Layouts

Inserting Columns and Rows Splitting and Merging Cells

Liberating Controls from Layouts Using Tabular Layouts Showing Multiple Records in any Form Split Forms More Useful Form Properties

The Form Wizard

13. Designing Advanced Forms

Customizing Forms in Design View

Form Sections: The Different Parts of Your Form Adding Controls to Your Form The Control Gallery: A Quick Tour Arranging Controls on Your Form

Aligning Controls Sizing Controls Spacing Controls Controls that Overlap

Anchoring: Automatically Resizing Controls

Making Controls as Wide as the Form Making a Single Control as Large as Possible

Tab Order: Making Keyboard Navigation Easier

Taking Control of Controls

Locking Down Fields Prevent Errors with Validation Performing Calculations with Expressions Organizing with Tab Controls Going Places with Links Showing Web Pages with the Web Browser Navigating with Lists Performing Actions with Command Buttons

Forms and Linked Tables

Table Relationships and Simple Forms The Subform Control Creating Customized Subforms

14. Building a Navigation System

Mastering the Navigation Pane

Configuring the Navigation List Better Filtering Hiding Objects Using Custom Groups Searching the Navigation List

Building Forms with Navigation Smarts

Custom Menu Forms Designating a Startup Form Compound Forms Showing All Your Forms in a List

Navigation Forms

Creating a Single-Level Navigation Form Creating a Two-Level Navigation Form Fine-Tuning Your Navigation Buttons

Linking to Related Data

Showing Linked Records in Separate Forms Showing More Detailed Reports with Links

5. Programming Access

15. Automating Tasks with Macros

Macro Essentials

Creating a Macro Running a Macro Editing a Macro Finding Actions in the Action Catalog Collapsing, Expanding, and Grouping Macro Actions Solving Macro Problems

Macros and Security

Unsafe Macro Actions Trusted Databases Temporary Trust The Trust Center Setting Up a Trusted Location

Three Macro Recipes

Find a Record Print a Report Email Your Data

Managing Macros

Submacros Assigning a Macro to a Keystroke Configuring a Startup Macro

16. Connecting Macros to Forms and Tables

Macro-Powered Forms

Understanding Events Attaching a Macro to an Event Reading Arguments from a Form Changing Form Properties

Conditional Macros

Building a Condition Validating Data with Conditions More Complex Conditional Macros

Data Macros

Table Events Validation with Before Events Keeping a Change Log Debugging Data Macros An Advanced Data Macro: Synchronizing Data

17. Automating Tasks with Visual Basic

The Visual Basic Editor

Adding a New Module Writing the Simplest Possible Code Routine

Putting Code in a Form

Responding to a Form Event Calling the Code in a Module Reading and Writing the Fields on a Form

Understanding Objects

Properties Methods Events

Using Objects

Indicating That a Record Has Changed Creating a Mouseover Effect

18. Writing Smarter Code

Exploring the VB Language

Storing Information in Variables Making Decisions Repeating Actions with a Loop Creating Custom Functions Putting It All Together: A Function for Testing Credit Cards

Dealing with Trouble

Debugging Error Handling

Deeper into Objects

The DoCmd Object Converting a Macro to VB Code

Using VB to Run a Better Business

Keeping a Running Total Getting Price Information Adding a New Product During an Order Managing Order Fulfillment Updating Stock Numbers

6. Going Large: Access Databases for Many Users

19. Sharing a Database on Your Network

Opening Up Your Database to the World

How Access Sharing Works

Preparing Your Database

Understanding Split Databases Splitting a Database with the Wizard How Linked Tables Work Manually Splitting a Database Locking Down Your Front End Sharing a Database with People Who Don’t Own Access

Playing Well with Others

Seeing Changes as They Happen Dealing with Editing Conflicts Using Locks to Stop Overlapping Edits Opening a Database in Exclusive Mode

Data Corruption

Diagnosing (and Fixing) Corrupt Databases Preventing Corruption

Securing Your Database

Password-Protecting Your Database Passwords and Split Databases Using Windows File Security

20. Building an Access Web App

Assessing Web Apps Preparing for Web Apps Building a Simple Web App

Creating a Web App in Access Creating a Web App in SharePoint Adding a Table Entering Data in a Table Back to the Home Tab

Running Your Web App

Editing Data in a Web App Understanding the Web App Navigation System

Customizing Views

Modifying a View Rearranging Controls Configuring Controls Changing the List Adding and Removing Controls

Creating More Advanced Views

Adding a New View Using a View to Create a Welcome Page Views That Use Queries Views That Use Related Tables

Adding Child Records Adding Parent Records

Tutorial: Creating a Web App from an Access Database

Creating a Web App Based on a Desktop Database Dealing with a Many-to-Many Relationship

Creating a Query to Get Related Data Creating a View for Your Query Showing Your View in a Popup

21. Linking Access to SQL Server

How Access and SQL Server Can Work Together

Should You Switch to SQL Server? How Access and SQL Server Work Together A Cheaper SQL Server

Getting Started with SQL Server Express

Preparing for SQL Server Express Downloading SQL Server Express Installing SQL Server Express Putting SQL Server on the Network

Building a SQL Server Database

Creating a New Database Adding a Table Creating Relationships Between Tables

Editing SQL Server Tables in Access

Setting Up a Connection to SQL Server Managing Linked Tables

Migrating an Access Database to SQL Server

Installing SSMA Transferring an Access Database to SQL Server

22. Linking Access to SharePoint

Understanding SharePoint

What You Can Do in SharePoint Adding an App to a SharePoint Site

SharePoint and Access

Creating a List in SharePoint Exporting a Table to SharePoint Importing a List in Access Moving a Whole Database to SharePoint Editing Your SharePoint Data in Access

23. Importing and Exporting Data

Case for Importing and Exporting

Understanding Exports Understanding Imports

Using the Clipboard

Copying a Table from Access to Somewhere Else Copying Cells from Excel into Access

Import and Export Operations

Importable File Types Importing Data Importing from an Excel File Importing from a Text File Exportable File Types Exporting Data Reusing Import and Export Settings

Access and XML

What Is XML, Really? Three Rules of XML

The Prolog Elements Nesting

XML Files and Schemas The Access XML Story Exporting to an XML File Importing from an XML File

7. Appendix

A. Customizing Access

Adding Your Favorites to the QAT

Adding Buttons Customizing Specific Databases

Personalizing the Ribbon

Changing Existing Tabs Creating Your Own Tab Saving and Reusing Your Custom Ribbon

Index About the Author Copyright

← Prev
Back
Next →

← Prev
Back
Next →