Grover Park George on Access

1. What This Book Will Do for You

• Why You Need this Book

  • A Professional Approach
• What’s in this Book
• Get Normal
• How to Use this Book

2. Why Do You Want to Use Access Anyway?

• Do I Really Need a Database?

  • Understanding the Problem
  • What Do My Answers Mean?
  • The Right Tool for the Job

• Why do you want to use Access?

  • Who Will Use the Database
  • How Much Data Will the Database Hold
  • What Hardware and Software is Available
  • Networked Environments
• Access It Is, Then!

3. Data Modeling 101

• The Cardboard Box Metaphor
• It’s All About the Data Model

• Okay, Cardboard Box Guy, Model This

  • What Do I Want to Keep Track of—the Entities
  • Tech Talk—Formal Entity Definition
  • Hierarchies and Entity Groups
  • Choosing a Model—Workflow and Business Rules
  • Entities Have Attributes
  • Enough Attributes

• Tech Talk—Entities, Attributes and Relationships

  • Entity
  • Attributes
  • Domain
  • Attribute/Data Value Pairs
  • Relationships
  • Relationship Set

• Relationship Types

  • One-to-One Relationships
  • One-to-Many Relationships
  • Many-to-Many Relationships
  • Key Attributes
  • Further Study

• Creating an Informal Data Model

  • Your Data Model

• My Data Model

  • Entities, Definitions, and Attributes
  • Managing Data—Delete vs. Inactive
  • Status
  • Who’s Related to Whom
  • Gray Areas, Trade-Offs, and Lessons Learned
  • Lessons Learned
  • Trade-Offs
• Summary

4. Let’s Get Physical

• Naming Conventions

• It’s Finally Time to Open Access

  • Create a New Database
  • Setting Options
  • Create a New Table
  • Your First Table
  • Naming Conventions
  • Data Type
  • Primary and Foreign Keys
  • Key Attributes and Natural Keys
  • Primary Keys
  • Summary—What are Primary Keys
  • AutoNumbers for Primary Keys
  • Primary Key Notation
  • Indexes, With and Without Duplicates
  • Ignore or Allow Nulls
  • Foreign Keys
  • Views
  • Add a Related Table
  • Can You Relate to This?

• Creating Relationships in the Relationship Window

  • Taking Stock

5. Try it Yourself—Create Tables

• Data and Look Up Tables

  • Additional Look Up Tables
  • Additional Data Tables
  • Person Table
  • Many-to-Many Relationships and Junction Tables
  • Household Head

• Relationships

  • Wrapping Up The Table Design

6. Normalize Your Data

• Why Normalization Matters

• The Goals of Normalization

  • Eliminating Redundant Data
  • Selecting the Smallest Meaningful Values
  • Storing Only Related Data

• The Rules of Normalization

  • Cutting You Some Slack
  • First Normal Form
  • First Normal Form Defined
  • Second Normal Form
  • Third Normal Form
• Summary—The Normalization Process

7. Try It Yourself—Normalization

• • Steps to Normalization
  • Calculated Fields
  • Taking Stock
  • Creating the Relationships
  • Address Table Relationships
  • We’re All Normal Here
• Backup, Backup, Backup

8. Getting Data Into Your Database—Simple Forms

• Forms, the Primary Data Input Tool

  • Bound and Unbound Forms
  • Main Forms and Sub Forms
  • Unbound Forms
  • Learning Strategy for Forms
  • Controls
  • Moving and Aligning Controls
  • Create a Form from Scratch
  • Other Form Properties
  • Add a Command Button
  • How the Button Works
  • The Builder Button

• Shortcut to Creating New Forms

  • Change the Record Source and Control Source

• Navigation on Forms

  • Triggering Events on a Form
  • From Simple to Intermediate
  • Backup, Backup, Backup

9. Getting Data Into Your Database—Intermediate Forms

• • Drag and Drop Fields
  • Navigating with a Searching Combo Box
  • Combo Boxes and Visual Basic for Applications Code
  • Conditional Instructions in VBA
  • What this Conditional Statement Does
  • Why I Prefer Combo Boxes to Scroll Bars and Navigation Buttons
  • VBA Behind the Combo Box—Further Study
  • Creating SQL Statements
  • Housekeeping Chores—Control Names
• Try it Yourself

• My Design for frmParent

  • Command Buttons
  • Further VBA Study
  • Tab Order and Tab Stop
• Intermediate Form Complete
• Backup, Backup, Backup

10. Getting Data Into Your Database - Complex Forms

• Add Sample Data

• Main Form—Sub Form Strategy

  • Unrelated Sub Forms
  • Add a Subform
  • Pre-defined Relationships
  • M
  • Subform Control versus Subform
  • Bound Column, Column Count, and Column Width
  • Assigning New Persons to a Household
  • Handling Errors in a Combo Box Not In List Event
  • Help with Visual Basic for Applications
  • What the Custom Message Box Does
  • Try It Out

• Adding New Records through the Form

  • Timing Issues with Record Sets
  • Modify the VBA for the Add Button
  • Designating Heads of Households

• User Interaction Strategy

  • Independent Data Entry Forms
  • Interrelated Data Entry Forms
  • Designing Forms to Support Workflow
  • Generalizing the Lesson

• Where Were We Just Now?

  • Move a Command Button from One Form to Another
  • Remaining Issues

• Try it Yourself

  • Additional Data Entry Forms

• My Suggested Subforms

  • Phone Number Subform

• Forms Wrap Up

  • Additional Enhancements and a Self Study Opportunity
• Backup, Backup, Backup

11. Getting Data out of Your Database - Reports

• Useful Reports and Forms

• Mailing Labels Report Wizard

  • String Concatenation in Reports
  • Data Display Versus Data Input
  • Hey, What Was that “Trim” Thing You Skipped Over?
  • Adding a Second Table to the Report’s Record Source

• The Expression Builder

  • Creating Expressions
  • Click to Paste Expressions
  • Functions in the Expression Builder
  • Using the Expression Builder in our Labels
  • Adding Text to an Expression
  • Using The Expression Builder
  • Field Names
  • Sorting Records
  • Page Set Up
  • Wrap up the Label Report

• Birth dates by Household Report

  • Create a Query
  • Report Requirement—Query Contents
  • Inner Joins
  • Report Wizard—Birthday List
  • Report Wizard, Field Layout on the Report
  • Report Wizard, Report Style
  • Headers and Footers
  • Creating a Calculated Field on a Report

• Debugging an Existing Report

  • The Root of the Problem
  • Fixing the Bug
  • Report Filters
• Wrap Up Report
• Backup, Backup, Backup

12. Getting Data out of Your Database—Display-only Forms

• • Display-only Form
  • Creating an Event to Requery the Birthday List
• Other Filtered List Forms

• Filtering Records for a Report

  • Design View
• Backup, Backup, Backup

13. Create a User Interface

• Hands Off the DB Objects, Please
• Ah, It All Makes Sense Now

• A User Interface for Our Contacts Database

  • Keep it Simple, Stupid
  • Navigation by Menus
  • A Menu-Based Switchboard
  • The Grover Park Menu Switchboard
  • Try it Yourself
  • Using the Menu to Close Forms
  • Coding Efficiency
  • Assign the Function to the On_Click Event

• Forms Consistency

  • Maintenance Forms
  • Control Form for Printing Reports
  • Filtering List box for the Printed Report
• Let’s Get Outta Here

• Consistency and Workflow

  • Background and Foreground Colors
  • Background Images and Icons
  • Screen Resolution
  • Form Height and Width
  • Form Open Event Procedures
  • Menu and Tool Bars

• A Custom Menu Bar

  • Open the Toolbar Customization Dialog
  • Create a New Toolbar
  • Assign the Custom Menu Bar as the Default Menu
  • Hide and Lock the Menu Bar
• Bypassing Startup

• An Application Ready to Test

  • Identify Entities and Attributes
  • Normalization
  • Identify Relationships, Primary and Foreign Keys
  • Get Data in, Creating Input Forms
  • Get Data Out, Creating Reports and Display Forms
  • Manage Records and Forms
  • Control the Application
• Where Do You Go From Here
• Download Personal Contacts