Microsoft® Access® Small Business Solutions: State-of-the-Art Database Models for Sales, Marketing, Customer Management, and More Key Business Activities

Book description

Database models developed by a team of leading Microsoft Access MVPs provide read-to-use solutions for sales, marketing, customer management and other key business activities for most small businesses

As the most popular relational database in the world, Microsoft Access is widely used by small business owners. This book responds to the growing need for resources that help business managers and end users design and build effective Access database solutions for specific business functions.

  • Written by a team of Microsoft Access MVPs, this guide shows you how to design databases for the essential activities of most small businesses, including marketing and sales, customer management, inventory management, quality control, budgeting, financial tracking and analysis, and more

  • The accompanying CD-ROM contains ready-to-load databases that can quickly be customized, saving you countless hours of development effort

  • Features a step-by-step interview process to help tailor the database structure to specific business scenarios

With this valuable guide and CD-ROM, you'll be on your way to implementing database solutions in no time.

Table of contents

  1. Copyright
  2. About the Authors
  3. About the Contributing Authors and Technical Editors
    1. Primary Technical Editors
  4. Credits
  5. Acknowledgments
  6. Foreword
  7. Introduction
    1. Who Should Read This Book
    2. How This Book Is Organized
    3. What's on the CD-ROM
  8. I. Introduction and Basic Concepts
    1. 1. Overview and Road Map
      1. 1.1. Part I: Introduction and Fundamentals
        1. 1.1.1. Chapter 1: Overview and Road Map
        2. 1.1.2. Chapter 2: Elements of a Microsoft Access Database
        3. 1.1.3. Chapter 3: Relational Data Model
      2. 1.2. Part II: Dealing with Customers and Customer Data
        1. 1.2.1. Chapter 4: People, Organizations, Addresses
        2. 1.2.2. Chapter 5: Customer Relationship Management
        3. 1.2.3. Chapter 6: Marketing
        4. 1.2.4. Chapter 7: Sales
        5. 1.2.5. Chapter 8: Production and Manufacturing
        6. 1.2.6. Chapter 9: Inventory Management
        7. 1.2.7. Chapter 10: Services
      3. 1.3. Part IV: Tracking and Analyzing Financial Data
        1. 1.3.1. Chapter 11: Accounting Systems—Requirements and Design
        2. 1.3.2. Chapter 12: Accounting: Budgeting, Analysis, and Reporting
      4. 1.4. Part V: Independent Areas
        1. 1.4.1. Chapter 13: Managing Memberships
        2. 1.4.2. Chapter 14: Implementing the Models
        3. 1.4.3. Chapter 15: SQL Server and Other External Data Sources
      5. 1.5. The Appendixes
        1. 1.5.1. Appendix A: Field Properties
        2. 1.5.2. Appendix B: Relationships, Joins, and Nulls
        3. 1.5.3. Appendix C: Resolving Relationship Triangles
        4. 1.5.4. Appendix D: Measures—Financial and Performance
        5. 1.5.5. Appendix E: References and Resources
        6. 1.5.6. Appendix F: What's on the CD-ROM
      6. 1.6. Part VI: Bonus Content
        1. 1.6.1. Chapter B1 (on the CD): Knowledge: Intellectual Property, Structural Capital, and Intellectual Capital
        2. 1.6.2. Appendix BA: Database and Business Terms
        3. 1.6.3. Appendix BB: Gathering Requirements
        4. 1.6.4. Appendix BC: Data Warehousing Concepts
        5. 1.6.5. Appendix BD: Database Platforms
    2. 2. Elements of a Microsoft Access Database
      1. 2.1. Database Schema
      2. 2.2. Tables
        1. 2.2.1. Fields
        2. 2.2.2. Field Properties
        3. 2.2.3. Indexes
      3. 2.3. Relationships
      4. 2.4. Queries
      5. 2.5. Summary
    3. 3. Relational Data Model
      1. 3.1. The Basics
        1. 3.1.1. Correlation to Microsoft Access
      2. 3.2. A Little Deeper
        1. 3.2.1. Relations, Relationships, and Keys
        2. 3.2.2. Derived Relations
        3. 3.2.3. Domains and Constraints
      3. 3.3. Normalization
        1. 3.3.1. Objectives
          1. 3.3.1.1. Objective 1: Prevent anomalies during data modification
          2. 3.3.1.2. Objective 2: Reduce the need for restructuring as new types of data are introduced, thus increasing the life span of application programs
          3. 3.3.1.3. Objective 3: Make the database more informative to the users
          4. 3.3.1.4. Objective 4: Make the collections of relations, or tables, neutral to querying patterns
          5. 3.3.1.5. Objective 5: Reduce data entry steps
        2. 3.3.2. Normal Forms
          1. 3.3.2.1. First Normal Form: Eliminate Repeating Groups
          2. 3.3.2.2. Second Normal Form: Eliminate Redundant Data
          3. 3.3.2.3. Third Normal Form: Eliminate Columns Not Dependent on a Candidate Key
        3. 3.3.3. Boyce-Codd Normal Form
          1. 3.3.3.1. Fourth and Fifth Normal Forms: Isolate Multiple (Many-to-Many) Relationships
        4. 3.3.4. When to Denormalize
      4. 3.4. Summary
  9. II. Dealing with Customers and Customer Data
    1. 4. People, Organizations, Addresses
      1. 4.1. How This Chapter Is Organized
      2. 4.2. Basic Field Information
      3. 4.3. People and Organization Information Processing and Storage
        1. 4.3.1. Personal Information Manager (PIM) or Address Book (AB) Basics
        2. 4.3.2. Questions to Ask
        3. 4.3.3. Items to Store
          1. 4.3.3.1. The Subject and Details of People
          2. 4.3.3.2. The Subject and Details of Organizations
          3. 4.3.3.3. Other Subjects and Details
        4. 4.3.4. Arranging the Data into Tables
          1. 4.3.4.1. The Person Table
          2. 4.3.4.2. The Organization Table
          3. 4.3.4.3. The Organization-Person Table
          4. 4.3.4.4. The Phone and Email Tables
          5. 4.3.4.5. Reference or Lookup Tables
        5. 4.3.5. Determining the Level of Detail
      4. 4.4. The Simple Model
        1. 4.4.1. The Person Table
        2. 4.4.2. The Email For Person Table
        3. 4.4.3. The Phone For Person Table
        4. 4.4.4. The Organization Table
        5. 4.4.5. The Email for Organization Table
        6. 4.4.6. The Phone for Organization Table
        7. 4.4.7. Lookup Tables
          1. 4.4.7.1. The Person Category Table (a Lookup Table)
          2. 4.4.7.2. The Organization Category Table (a Lookup Table)
          3. 4.4.7.3. The Organization Type Table (a Lookup Table)
          4. 4.4.7.4. The State Reference Table (a Lookup Table)
          5. 4.4.7.5. The Country Reference Table (a Lookup Table)
          6. 4.4.7.6. The EMail Type Table (a Lookup Table)
          7. 4.4.7.7. The Month Table (a Lookup Table)
        8. 4.4.8. Junction Tables
          1. 4.4.8.1. The Organization-Person Table (a Junction Table)
        9. 4.4.9. Putting the Tables Together
      5. 4.5. Differences between the Simple and Complex Models
      6. 4.6. The Complex Model
        1. 4.6.1. The Person Table
        2. 4.6.2. The Organization Table
        3. 4.6.3. The EMail Table
        4. 4.6.4. The Phone Table
        5. 4.6.5. The Address Table
        6. 4.6.6. Lookup Tables
          1. 4.6.6.1. The Address Type Table (a Lookup Table)
          2. 4.6.6.2. The Role Table (a Lookup Table)
          3. 4.6.6.3. The Title Table (a Lookup Table)
          4. 4.6.6.4. The Title Suffix Table (a Lookup Table)
        7. 4.6.7. Junction Tables
          1. 4.6.7.1. The EMail-Person Table (a Junction Table)
          2. 4.6.7.2. The Person-Phone Table (a Junction Table)
          3. 4.6.7.3. The Address-Person Table (a Junction Table)
          4. 4.6.7.4. The EMail-Organization Table (a Junction Table)
          5. 4.6.7.5. The Organization-Phone Table (a Junction Table)
          6. 4.6.7.6. The Address-Organization Table (a Junction Table)
          7. 4.6.7.7. The Organization-Person Table (a Junction Table)
        8. 4.6.8. Putting the Tables Together
      7. 4.7. Adding More Information about People and Organizations
      8. 4.8. Summary
    2. 5. Customer Relationship Management
      1. 5.1. Six Core Topics of Customer Relationship Management
      2. 5.2. Approach for Reviewing Core Topics
        1. 5.2.1. Basic Business Functions for the Topic
        2. 5.2.2. Items to Store
          1. 5.2.2.1. The Subjects and Details of the Topic
          2. 5.2.2.2. Other Types of Subjects
          3. 5.2.2.3. Arranging the Data into Tables
        3. 5.2.3. Tables in the Example Database for Each Topic
          1. 5.2.3.1. The Table and Fields
          2. 5.2.3.2. Relationship Diagram Showing the Schema or Overview of the Topic
      3. 5.3. Customer Information
        1. 5.3.1. Improving Your Relationship with Your Customers
        2. 5.3.2. Gathering Data to Analyze in the Future
      4. 5.4. Basic Business Functions for People and Organizations
        1. 5.4.1. Arranging the Data Into Tables
          1. 5.4.1.1. Customer Table
        2. 5.4.2. The Person Table Grouping
          1. 5.4.2.1. The Family Member Table
          2. 5.4.2.2. Lookup Tables
            1. 5.4.2.2.1. Marital Status Table (a Lookup Table)
            2. 5.4.2.2.2. The Family Member Type Table (a Lookup Table)
            3. 5.4.2.2.3. The Phone Type Table (a Lookup Table)
        3. 5.4.3. The Organization Table Grouping
          1. 5.4.3.1. Organization-Related Lookup Tables
          2. 5.4.3.2. Overview of Person and Organization Information
      5. 5.5. Tracking Customer Correspondence
        1. 5.5.1. Basic Business Functions for Tracking Correspondence
        2. 5.5.2. Items to Store
          1. 5.5.2.1. The Subject and Details of Customers, People, and Organizations
          2. 5.5.2.2. The Subjects and Details of Correspondence
          3. 5.5.2.3. Other Types of Subject
        3. 5.5.3. Arranging the Data Into Tables
          1. 5.5.3.1. The Correspondence Table
          2. 5.5.3.2. Correspondence Detail
          3. 5.5.3.3. The Employee Table
          4. 5.5.3.4. Lookup Data
        4. 5.5.4. Tables as Implemented in the Example Database
          1. 5.5.4.1. The Correspondence Table
          2. 5.5.4.2. The Correspondence Detail Table
          3. 5.5.4.3. The Employee Table
          4. 5.5.4.4. The Correspondence Type Table (A Lookup Table)
      6. 5.6. Tracking Correspondence Overview
      7. 5.7. Tracking Sales Information for CRM
        1. 5.7.1. Items to Store
          1. 5.7.1.1. The Subjects and Details of Sales
          2. 5.7.1.2. Other Types of Subjects
        2. 5.7.2. Arranging the Data Into Tables
          1. 5.7.2.1. The Invoice Table
          2. 5.7.2.2. The Invoice Detail Table
          3. 5.7.2.3. The Product Table
          4. 5.7.2.4. The Product Category Table
        3. 5.7.3. Tables as Implemented in the Example Database
          1. 5.7.3.1. The Invoice Table
          2. 5.7.3.2. The Invoice Detail Table
          3. 5.7.3.3. The Product Table (a Lookup Table)
          4. 5.7.3.4. The Product Category Table (a Lookup Table)
      8. 5.8. Sales Information for CRM Overview
      9. 5.9. Tracking Service and Support Information
        1. 5.9.1. Basic Business Functions for Tracking Support
        2. 5.9.2. Items to Store
          1. 5.9.2.1. The Subjects and Details of Tracking Support
          2. 5.9.2.2. Other Types of Subject
        3. 5.9.3. Arranging the Data Into Tables
          1. 5.9.3.1. The Incident Table
          2. 5.9.3.2. The Incident Activity Table
          3. 5.9.3.3. The Incident Document Table
          4. 5.9.3.4. The Incident Activity Type Table
          5. 5.9.3.5. The Incident Activity Status Table
        4. 5.9.4. Tables as Implemented in the Example Database
          1. 5.9.4.1. The Incident Table
          2. 5.9.4.2. The Incident Activity Table
          3. 5.9.4.3. The Incident Document Table
          4. 5.9.4.4. The Incident Activity Type Table (a Lookup Table)
          5. 5.9.4.5. The Incident Activity Status Table (a Lookup Table)
        5. 5.9.5. Schema of Service and Support Tables
      10. 5.10. Advertising and Marketing Campaigns, Targeted and General
        1. 5.10.1. Customer Awareness
        2. 5.10.2. Basic Business Functions for Planning and Tracking Campaigns
        3. 5.10.3. Items to Store
          1. 5.10.3.1. The Subjects and Details of Planning and Tracking Campaigns
          2. 5.10.3.2. Other Types of Subject
        4. 5.10.4. Arranging the Data Into Tables
          1. 5.10.4.1. The Campaign Table
          2. 5.10.4.2. The Campaign Activity Table
          3. 5.10.4.3. The Campaign Task Table
          4. 5.10.4.4. The Campaign Document Table
          5. 5.10.4.5. The Campaign Document Type Table
        5. 5.10.5. Tables as Implemented in the Example Database
          1. 5.10.5.1. The Campaign Table
          2. 5.10.5.2. The Campaign Activity Table
          3. 5.10.5.3. The Campaign Task Table
          4. 5.10.5.4. The Campaign Document Table
          5. 5.10.5.5. The Campaign Document Type Table (a Lookup Table)
        6. 5.10.6. Schema of Advertising and Marketing Campaigns
      11. 5.11. Internal Information Sharing
        1. 5.11.1. Basic Business Functions for Internal Information Sharing
        2. 5.11.2. Items to Store
          1. 5.11.2.1. The Subjects and Details of Internal Information Sharing
        3. 5.11.3. Arranging the Data Into Tables
          1. 5.11.3.1. The Person Table
          2. 5.11.3.2. The Employee Information Table
          3. 5.11.3.3. The Contractor Information Table
          4. 5.11.3.4. The Information Type Table
          5. 5.11.3.5. Simplifying the Design
        4. 5.11.4. Tables as Implemented in the Example Database
          1. 5.11.4.1. The Information Type Table
          2. 5.11.4.2. The Contractor-Information Table (a Junction Table)
          3. 5.11.4.3. The Employee-Information Table (a Junction Table)
        5. 5.11.5. Schema Overview of Internal Information Sharing
      12. 5.12. Improving Process Quality
      13. 5.13. Summary
    3. 6. Marketing
      1. 6.1. Database Terminology
        1. 6.1.1. Database Terminology
      2. 6.2. Marketing Campaigns
        1. 6.2.1. Identifying Required Data Elements
        2. 6.2.2. Assigning the Details to a Subject
          1. 6.2.2.1. Campaign Stages and Communications
          2. 6.2.2.2. Descriptions of the Core Subjects
        3. 6.2.3. Creating the Tables
          1. 6.2.3.1. Creating Additional Fields
          2. 6.2.3.2. Reference Codes
          3. 6.2.3.3. Expanding Product Areas
        4. 6.2.4. Creating the Tables Related to Organization
          1. 6.2.4.1. Organization Individuals
          2. 6.2.4.2. Organization Communication Details
          3. 6.2.4.3. Organization Types
        5. 6.2.5. Further Campaign Subjects
          1. 6.2.5.1. Targeted Organizations
          2. 6.2.5.2. Campaign Events
        6. 6.2.6. Summary of the Marketing Model
      3. 6.3. Questionnaires
        1. 6.3.1. Just Questions and Answers
        2. 6.3.2. The Overly Simple Inappropriate Design
          1. 6.3.2.1. The Simple Genuine Questionnaire
          2. 6.3.2.2. Identifying the Core Tables
        3. 6.3.3. The Flexible Questionnaire
          1. 6.3.3.1. Specific Data Type Answers
          2. 6.3.3.2. Determining the Data Type for Questions
          3. 6.3.3.3. Multiple-Choice Questions
            1. 6.3.3.3.1. The Question Options Table
            2. 6.3.3.3.2. The Questions Table
        4. 6.3.4. The Final Questionnaire
          1. 6.3.4.1. Questionnaire Sections
          2. 6.3.4.2. Organization Contact Interviews
          3. 6.3.4.3. Question Dependency
          4. 6.3.4.4. Alternative Design: A Question Pool
        5. 6.3.5. Marketing Combined
      4. 6.4. Summary
    4. 7. Sales
      1. 7.1. Building the Model
        1. 7.1.1. Gathering Specification Data
          1. 7.1.1.1. Obtaining Information
          2. 7.1.1.2. Identifying the Requirements
          3. 7.1.1.3. Identifying the Key Subjects
      2. 7.2. Designing the Tables
        1. 7.2.1. Customers
          1. 7.2.1.1. Customer Options
            1. 7.2.1.1.1. One Table for All Customers
        2. 7.2.2. Products
          1. 7.2.2.1. Required Product Fields
        3. 7.2.3. Orders
          1. 7.2.3.1. Required Order Fields
          2. 7.2.3.2. Consequences and Alternative Fields
        4. 7.2.4. Order Detail
          1. 7.2.4.1. Required Order Detail Fields
            1. 7.2.4.1.1. Snapshot Data and the Lookup Alternative
        5. 7.2.5. Putting the Model Together
      3. 7.3. A More Complex Model
        1. 7.3.1. Additional Requirements
          1. 7.3.1.1. Multiple Product Prices
          2. 7.3.1.2. Product Images
          3. 7.3.1.3. Employees
          4. 7.3.1.4. Multiple Notes
          5. 7.3.1.5. Order Quotes
      4. 7.4. Using the Database
        1. 7.4.1. Querying the Database
          1. 7.4.1.1. Invoicing
          2. 7.4.1.2. Order Product Totals
        2. 7.4.2. Alternative Designs
          1. 7.4.2.1. Product Grouping
          2. 7.4.2.2. Notes Assignment
          3. 7.4.2.3. Additional Order Overview Option
      5. 7.5. Summary
  10. III. Producing and Tracking the Goods & Services
    1. 8. Production and Manufacturing
      1. 8.1. Industry Terms
      2. 8.2. Enterprise-Level Systems
      3. 8.3. Purpose of the Database
      4. 8.4. Identifying the Requirements
      5. 8.5. Identifying User Expectations
      6. 8.6. Manufacturing Overview: Identifying the Core Subjects
        1. 8.6.1. The Main Subject: Manufacturing Processes
        2. 8.6.2. Additional Core Subjects
        3. 8.6.3. Relationships between Core Subjects
        4. 8.6.4. The Approach for Reviewing the Core Subjects
          1. 8.6.4.1. Reviewing the Requirements
          2. 8.6.4.2. Identifying the Subjects
          3. 8.6.4.3. Identifying the Details
          4. 8.6.4.4. Grouping the Details into Subjects
            1. 8.6.4.4.1. Evaluating and Grouping the Details - The Four Questions
        5. 8.6.5. Creating the Tables and Fields
          1. 8.6.5.1. Reviewing the Relationships
      7. 8.7. Manufacturing Processes
        1. 8.7.1. Manufacturing Process Hierarchies
          1. 8.7.1.1. Appropriate Depth of Your Process Tree
        2. 8.7.2. Identifying the Details
        3. 8.7.3. Creating the Tables
          1. 8.7.3.1. Explaining the Approach and Types of Tables
          2. 8.7.3.2. Potential Child Tables: Additional Details
          3. 8.7.3.3. Lookup Tables
          4. 8.7.3.4. Self-Referencing Table
          5. 8.7.3.5. Choosing between Alternate Designs
          6. 8.7.3.6. Many-to-Many Relationship and a Child Table
            1. 8.7.3.6.1. Self-Referencing Lookup Table
            2. 8.7.3.6.2. Supporting Tables for Processes
            3. 8.7.3.6.3. Understanding Lookup Tables
          7. 8.7.3.7. Explaining the Fields
            1. 8.7.3.7.1. Standard Fields
            2. 8.7.3.7.2. Primary Keys
            3. 8.7.3.7.3. Other Standard Fields
          8. 8.7.3.8. Creating the Tables and Fields
            1. 8.7.3.8.1. Application Performance vs. Using Calculated Fields
            2. 8.7.3.8.2. The Process Table
            3. 8.7.3.8.3. The Process Manufacturer Table
            4. 8.7.3.8.4. The Manufacturer Table
            5. 8.7.3.8.5. The Process Vendor Table
            6. 8.7.3.8.6. The Vendor Table
            7. 8.7.3.8.7. The Process Type Table
            8. 8.7.3.8.8. The Process Backup Table
            9. 8.7.3.8.9. The Location Table
          9. 8.7.3.9. Relationships
          10. 8.7.3.10. Join Lines, Relationship Symbols, and Referential Integrity
        4. 8.7.4. Manufacturing Builds
          1. 8.7.4.1. Identifying the Details
          2. 8.7.4.2. Grouping the Details into Subjects
          3. 8.7.4.3. Creating the Tables and Fields
            1. 8.7.4.3.1. The Build Table
            2. 8.7.4.3.2. The Build Stoptime Table
            3. 8.7.4.3.3. The Build Stoptime Notification Table
          4. 8.7.4.4. Reviewing the Relationships
        5. 8.7.5. Notifications
          1. 8.7.5.1. Identifying the Details
          2. 8.7.5.2. Grouping the Details into Subjects
          3. 8.7.5.3. Creating the Tables and Fields
            1. 8.7.5.3.1. The Notifications Table
            2. 8.7.5.3.2. The Conditions Table
            3. 8.7.5.3.3. The Discovery Method Table
            4. 8.7.5.3.4. The Team Member Table
            5. 8.7.5.3.5. The Severity Table
            6. 8.7.5.3.6. The Notification Type Table
            7. 8.7.5.3.7. The Cause Table
          4. 8.7.5.4. Reviewing the Relationships
        6. 8.7.6. Countermeasures
          1. 8.7.6.1. Identifying the Details
          2. 8.7.6.2. Grouping the Details into Subjects
          3. 8.7.6.3. Creating the Tables and Fields
            1. 8.7.6.3.1. The Countermeasure Table
            2. 8.7.6.3.2. The Countermeasure Team Member Table
            3. 8.7.6.3.3. The Team Member Table
            4. 8.7.6.3.4. The Action Table
            5. 8.7.6.3.5. The Countermeasure Step Table
          4. 8.7.6.4. Reviewing the Relationships
        7. 8.7.7. General Notes
          1. 8.7.7.1. Identifying the Details
          2. 8.7.7.2. Grouping the Details into Subjects
          3. 8.7.7.3. Creating the Tables and Fields
            1. 8.7.7.3.1. The General Note Table
            2. 8.7.7.3.2. The General Note Process Table
            3. 8.7.7.3.3. The Team Member Table
          4. 8.7.7.4. Reviewing the Relationships
      8. 8.8. Putting It All Together
        1. 8.8.1. Final Walk-Through
      9. 8.9. More Information
      10. 8.10. Summary
    2. 9. Inventory Management
      1. 9.1. The Approach for This Chapter
      2. 9.2. The Inventory Management Database
        1. 9.2.1. Purpose of the Database
        2. 9.2.2. Identifying Your Requirements
        3. 9.2.3. User Expectations
      3. 9.3. Inventory Overview: Identifying the Subjects
        1. 9.3.1. Core and Supporting Subjects
          1. 9.3.1.1. Core Subjects
          2. 9.3.1.2. Relationships between the Core Subjects
      4. 9.4. Approach for Reviewing the Core Subjects
        1. 9.4.1. Identifying the Details
          1. 9.4.1.1. The Standard Fields
        2. 9.4.2. The Types of Tables
        3. 9.4.3. Creating the Tables and Fields
        4. 9.4.4. Establishing the Relationships
        5. 9.4.5. Putting It All Together
      5. 9.5. Products
        1. 9.5.1. Identifying the Details
        2. 9.5.2. Identifying the Supporting Tables
        3. 9.5.3. Evaluating the Details: The Four Questions
        4. 9.5.4. Using Transient Fields
        5. 9.5.5. Using Lookup Tables
        6. 9.5.6. Many-to-Many Relationships and Child Tables
        7. 9.5.7. Design Alternatives for Quantity on Hand
        8. 9.5.8. Using a Transaction Table
        9. 9.5.9. Table Relationships for Products
        10. 9.5.10. Creating the Tables and Fields
          1. 9.5.10.1. The Product Table
          2. 9.5.10.2. The Brand Table
          3. 9.5.10.3. The Unit of Measure Table
          4. 9.5.10.4. The Product Location Table
          5. 9.5.10.5. The Product Vendor Table
          6. 9.5.10.6. The Product Vendor Cost Table
        11. 9.5.11. Establishing the Relationships
      6. 9.6. Vendors
        1. 9.6.1. Identifying the Details
        2. 9.6.2. Identifying the Supporting Tables
        3. 9.6.3. Creating the Tables and Fields
          1. 9.6.3.1. The Vendor Table
          2. 9.6.3.2. The Vendor Address Table
          3. 9.6.3.3. The Address Type Table
          4. 9.6.3.4. The Vendor Contact Table
          5. 9.6.3.5. The Contact Communication Table
          6. 9.6.3.6. The Communication Type Table
        4. 9.6.4. Establishing the Relationships
      7. 9.7. Locations
        1. 9.7.1. Identifying the Details
        2. 9.7.2. Indentifying the Supporting Tables
          1. 9.7.2.1. Explaining Self-Referencing Tables
        3. 9.7.3. Creating the Tables and Fields
          1. 9.7.3.1. The Location Table
        4. 9.7.4. Establishing the Relationships
      8. 9.8. Transactions
        1. 9.8.1. Identifying the Details
        2. 9.8.2. Identifying the Supporting Tables
        3. 9.8.3. Creating the Tables and Fields
          1. 9.8.3.1. The Inventory Transaction Table
          2. 9.8.3.2. The Transaction Type Table
        4. 9.8.4. Establishing the Relationships
      9. 9.9. Putting It All Together
      10. 9.10. Summary
    3. 10. Services
      1. 10.1. Database Terminology
      2. 10.2. Appointments Made Simple
      3. 10.3. Pet Boarding Example
        1. 10.3.1. The Business Rules
        2. 10.3.2. Identifying the Subjects
          1. 10.3.2.1. Customer Tables
          2. 10.3.2.2. Pet Tables
          3. 10.3.2.3. Booking Table
          4. 10.3.2.4. Booking Detail Tables
          5. 10.3.2.5. The Booking Table Expanded
            1. 10.3.2.5.1. Booking Types
            2. 10.3.2.5.2. Booking Status
            3. 10.3.2.5.3. Booking Staff Members
        3. 10.3.3. Checking Appointments
        4. 10.3.4. Extending the Database
      4. 10.4. Process Monitoring
        1. 10.4.1. Events Management
          1. 10.4.1.1. Event Management Requirements
          2. 10.4.1.2. Identifying the Subjects
            1. 10.4.1.2.1. Customer Data
            2. 10.4.1.2.2. Employees, Individuals, and Roles
          3. 10.4.1.3. Identifying the Event Subjects
            1. 10.4.1.3.1. The Core Event
            2. 10.4.1.3.2. Related Event Subjects
            3. 10.4.1.3.3. New Event Subjects
            4. 10.4.1.3.4. The Final Event Table
          4. 10.4.1.4. Event Detail Tables
          5. 10.4.1.5. Querying Events
        2. 10.4.2. The Process Model
        3. 10.4.3. Event Notes
      5. 10.5. Variations of the Theme
      6. 10.6. Summary
  11. IV. Tracking and Analyzing Financial Data
    1. 11. Accounting Systems: Requirements and Design
      1. 11.1. Double-Entry Accounting
      2. 11.2. Chart of Accounts
      3. 11.3. General Ledger
        1. 11.3.1. Subsidiary Ledgers
          1. 11.3.1.1. Cash Subledgers
          2. 11.3.1.2. Investment Subledgers
          3. 11.3.1.3. Accounts Receivable Subledgers
          4. 11.3.1.4. Accounts Payable Subledgers
      4. 11.4. Database Design Considerations
      5. 11.5. Accounting Database
        1. 11.5.1. Table Structure
          1. 11.5.1.1. Standard Fields
          2. 11.5.1.2. The Account Table
          3. 11.5.1.3. General Ledger Table
          4. 11.5.1.4. Lookup Tables
            1. 11.5.1.4.1. Ledger Type Table
            2. 11.5.1.4.2. Transaction Type Table
            3. 11.5.1.4.3. Vendor/Customer Table
            4. 11.5.1.4.4. Cash Ledger Table
            5. 11.5.1.4.5. Batch Table
            6. 11.5.1.4.6. User Table
            7. 11.5.1.4.7. Report Header Table
            8. 11.5.1.4.8. Report Detail Table
          5. 11.5.1.5. Optional Tables
            1. 11.5.1.5.1. Holiday Table
            2. 11.5.1.5.2. Rate Table
            3. 11.5.1.5.3. Department Table
            4. 11.5.1.5.4. Work Day Table
            5. 11.5.1.5.5. Investment Master Table
            6. 11.5.1.5.6. Investment Detail Table
        2. 11.5.2. Queries
          1. 11.5.2.1. Using Queries to Create a Virtual Ledger
            1. 11.5.2.1.1. Aggregate Queries - Account Balances
      6. 11.6. Relationships
        1. 11.6.1. Creating and Using Archive Tables
          1. 11.6.1.1. Creating an Archive Table
          2. 11.6.1.2. Verifying Income Statement Accounts
          3. 11.6.1.3. Appending Data to an Archive Table
          4. 11.6.1.4. Posting Ending Balances to the General Ledger
      7. 11.7. Summary
    2. 12. Accounting: Budgeting, Analysis, and Reporting
      1. 12.1. Budgets
        1. 12.1.1. Budget Basics
          1. 12.1.1.1. Creating a Budget
          2. 12.1.1.2. Budget Tools
          3. 12.1.1.3. Base Budget Data
          4. 12.1.1.4. Getting the Data into Excel
            1. 12.1.1.4.1. Export and Import Methods
          5. 12.1.1.5. Spreading the Data Over the Budget Period
          6. 12.1.1.6. Getting Data to Excel for Additional Analysis
          7. 12.1.1.7. Budget Entries: Manual Entry or Import
          8. 12.1.1.8. Process Flow Control
        2. 12.1.2. Applying the Budget
          1. 12.1.2.1. Reporting Variances in the Budget
      2. 12.2. Analyzing Profitability
        1. 12.2.1. Reports
          1. 12.2.1.1. Trial Balance
          2. 12.2.1.2. Balance Sheet
          3. 12.2.1.3. Income Statement
          4. 12.2.1.4. Common-Size Statements
        2. 12.2.2. Ratio Analysis
          1. 12.2.2.1. Profitability Ratios
          2. 12.2.2.2. Liquidity Ratios
          3. 12.2.2.3. Activity Ratios
      3. 12.3. Summary
  12. V. Independent Areas
    1. 13. Managing Memberships
      1. 13.1. Membership Management Functions
        1. 13.1.1.
          1. 13.1.1.1. The Member Information Table
        2. 13.1.2. Adding and Renewing Memberships
        3. 13.1.3. Keeping Track of Members
          1. 13.1.3.1. The Personal Note Table
        4. 13.1.4. Offices and Elections
          1. 13.1.4.1. Tables as Implemented in the Example Database
            1. 13.1.4.1.1. The Office Table
            2. 13.1.4.1.2. The Term in Office Table
        5. 13.1.5. Lookup Tables
          1. 13.1.5.1. The Membership Type Table (a Lookup Table)
          2. 13.1.5.2. The Member Status Table (a Lookup Table)
        6. 13.1.6. Junction Tables
        7. 13.1.7. Documenting Memberships
        8. 13.1.8. Reporting Member Information
          1. 13.1.8.1. The Privacy Level Table
        9. 13.1.9. Recording Expiration and Payments
          1. 13.1.9.1. The Payment Table
          2. 13.1.9.2. The Payment Source Table
          3. 13.1.9.3. The Payment Type Table
      2. 13.2. Communication
        1. 13.2.1. Events
          1. 13.2.1.1. Planning Events
            1. 13.2.1.1.1. The Event Table
            2. 13.2.1.1.2. The Event Document Table
          2. 13.2.1.2. Schedule and Tasks
            1. 13.2.1.2.1. The Task for Event Table
          3. 13.2.1.3. Event Staff
            1. 13.2.1.3.1. The Event-Member Table (a Junction Table)
          4. 13.2.1.4. Contracts and Contacts
            1. 13.2.1.4.1. The Event-Person Table (a Junction Table)
        2. 13.2.2. Event Execution and Follow-up
      3. 13.3. Recruiting
        1. 13.3.1.
          1. 13.3.1.1. Getting New Members
          2. 13.3.1.2. Re-recruiting Former Members
      4. 13.4. Sponsorships
        1. 13.4.1. The Sponsor Table
        2. 13.4.2. The Contact for Sponsor Table
          1. 13.4.2.1. Small Membership Organizations
      5. 13.5. Summary
    2. 14. Implementing the Models
      1. 14.1. Not-for-Profit Organizations
        1. 14.1.1. Tracking Membership
          1. 14.1.1.1. Membership Types
          2. 14.1.1.2. Dues Collection
            1. 14.1.1.2.1. Billing
            2. 14.1.1.2.2. Receipt of Payment
          3. 14.1.1.3. Member Information
            1. 14.1.1.3.1. Contact Information
            2. 14.1.1.3.2. Demographic Information
        2. 14.1.2. Tracking Donations
        3. 14.1.3. Tracking Sales
      2. 14.2. Designing the Database
        1. 14.2.1. Tables
          1. 14.2.1.1. Dues Receivable
          2. 14.2.1.2. Payment Detail
          3. 14.2.1.3. Dues Type
          4. 14.2.1.4. Dues Rate
          5. 14.2.1.5. Individual-Dues Type
          6. 14.2.1.6. Individual Detail
          7. 14.2.1.7. Detail Type
        2. 14.2.2. Defining Relationships
      3. 14.3. Components of the User Interface
        1. 14.3.1. Queries
          1. 14.3.1.1. Parameter Queries
          2. 14.3.1.2. Types of Queries
            1. 14.3.1.2.1. Select Queries
            2. 14.3.1.2.2. Action Queries
        2. 14.3.2. Forms
        3. 14.3.3. Reports
        4. 14.3.4. Modules
        5. 14.3.5. Backup during Development
      4. 14.4. User Interface Design
        1. 14.4.1. Activities Focused on Individuals
          1. 14.4.1.1. Entry Form
          2. 14.4.1.2. Add Functionality
            1. 14.4.1.2.1. Adding the list box Control
            2. 14.4.1.2.2. Adding VBA to the List Box Control
            3. 14.4.1.2.3. Adding a Subform for Dues History
          3. 14.4.1.3. Related Forms
            1. 14.4.1.3.1. Dues Supporting Forms
        2. 14.4.2. Customers and Donors
          1. 14.4.2.1. Customers
            1. 14.4.2.1.1. Data Entry Forms
            2. 14.4.2.1.2. To Look Up Individuals
      5. 14.5. Products
        1. 14.5.1. Product Maintenance
          1. 14.5.1.1. Fundraising Product Design
            1. 14.5.1.1.1. Data Entry Forms
            2. 14.5.1.1.2. Product List Reports
            3. 14.5.1.1.3. Product Price Report
        2. 14.5.2. Orders and Sales
          1. 14.5.2.1. Order Entry Form
            1. 14.5.2.1.1. Create the Order Detail Subform
            2. 14.5.2.1.2. Create a Calculated Field in the Query
            3. 14.5.2.1.3. Create the Order Detail Subform
            4. 14.5.2.1.4. Add a customer lookup
            5. 14.5.2.1.5. Adding the Customer Address Subform
            6. 14.5.2.1.6. Tab Stops and Tab Order
            7. 14.5.2.1.7. Order Totals
        3. 14.5.3. Invoices
          1. 14.5.3.1. Invoice Form
          2. 14.5.3.2. Invoice Report
            1. 14.5.3.2.1. Creating a Query for the Record Source
            2. 14.5.3.2.2. Creating the Invoice
      6. 14.6. Summary
    3. 15. SQL Server and Other External Data Sources
      1. 15.1. Client-Server Databases
        1. 15.1.1. File-Server Limitations
        2. 15.1.2. Server Databases
        3. 15.1.3. Microsoft SQL Server
          1. 15.1.3.1. Data Type Comparisons
            1. 15.1.3.1.1. Date Data Types
            2. 15.1.3.1.2. Text Data Types
            3. 15.1.3.1.3. Boolean (Yes/No) Data Types
            4. 15.1.3.1.4. Numeric Data Types
            5. 15.1.3.1.5. Binary (File) Data Types
            6. 15.1.3.1.6. Other Data Types
          2. 15.1.3.2. Server Functionality
            1. 15.1.3.2.1. Server Robustness
            2. 15.1.3.2.2. Server Objects and Transact SQL
            3. 15.1.3.2.3. SQL Server Editions
      2. 15.2. Server Database Design
        1. 15.2.1. Design Considerations
          1. 15.2.1.1. Narrow and Long Tables
          2. 15.2.1.2. Lookup Tables
          3. 15.2.1.3. Design Optimization
            1. 15.2.1.3.1. Denormalizing
            2. 15.2.1.3.2. Assisting Optimization
          4. 15.2.1.4. Problem Tables and Conversions
      3. 15.3. Optimization Suggestions
        1. 15.3.1. Appropriate Use of Indexing
        2. 15.3.2. Query Operators: Selection Criteria
        3. 15.3.3. Linked Table Implications
        4. 15.3.4. Other Access Functionality
          1. 15.3.4.1. Pass-through Queries
          2. 15.3.4.2. Local Table Storage
          3. 15.3.4.3. Access Data Projects
      4. 15.4. Other Data Sources
        1. 15.4.1. Other Server Databases
        2. 15.4.2. Data Formats
          1. 15.4.2.1. Excel Spreadsheets
          2. 15.4.2.2. SharePoint
          3. 15.4.2.3. Text Files and CSVs
          4. 15.4.2.4. XML
      5. 15.5. Summary
  13. VI. Appendixes
    1. A. Field Properties
      1. A.1. Using Field Properties
        1. A.1.1. Properties of AutoNumber Fields
          1. A.1.1.1. Field Size
          2. A.1.1.2. New Values
          3. A.1.1.3. Display Formats
          4. A.1.1.4. Caption
          5. A.1.1.5. Indexed
          6. A.1.1.6. Smart Tags
          7. A.1.1.7. Text Align
          8. A.1.1.8. When Should You Not Use an AutoNumber?
        2. A.1.2. Properties of Text Fields
          1. A.1.2.1. Field Size
          2. A.1.2.2. Format
          3. A.1.2.3. Input Mask
          4. A.1.2.4. Caption
          5. A.1.2.5. Default Value
          6. A.1.2.6. Validation Rule and Validation Text
          7. A.1.2.7. Required
          8. A.1.2.8. Allow Zero Length
          9. A.1.2.9. Indexed
          10. A.1.2.10. Unicode Compression
          11. A.1.2.11. IME Mode and IME Sentence Mode
          12. A.1.2.12. Smart Tags
          13. A.1.2.13. Text Align
        3. A.1.3. Properties of Date/Time Fields
          1. A.1.3.1. Format and Input Mask
          2. A.1.3.2. Default Value
          3. A.1.3.3. Validation Rule and Validation Text
          4. A.1.3.4. Show Date Picker
          5. A.1.3.5. Further Study on Dates and Time
        4. A.1.4. Properties of Number Fields
          1. A.1.4.1. Field Size
            1. A.1.4.1.1. Byte
            2. A.1.4.1.2. Integer
            3. A.1.4.1.3. Long Integer
            4. A.1.4.1.4. Single (aka Float), Double and Decimal
          2. A.1.4.2. Decimal Places
          3. A.1.4.3. Default Value
        5. A.1.5. Properties for the Currency Data Types
          1. A.1.5.1. Format
          2. A.1.5.2. Decimal Places
        6. A.1.6. Properties for the Memo Data Type
        7. A.1.7. Unique Index Enforced for a Text Field
          1. A.1.7.1. Single Field Index
          2. A.1.7.2. Multiple Field Index
          3. A.1.7.3. Unique Property
          4. A.1.7.4. Ignore Nulls
        8. A.1.8. Properties for the Yes/No Data Type
          1. A.1.8.1. Using Yes/No Fields in Queries
          2. A.1.8.2. Custom Presentation Using Yes/No Fields in Queries
            1. A.1.8.2.1. IIF Statements
    2. B. Relationships, Joins, and Nulls
      1. B.1. Relationships and Joins
        1. B.1.1. It's All Been Done Before
        2. B.1.2. Telling It Like It Is
        3. B.1.3. Change Happens—Redefining Relationships
        4. B.1.4. Automatic and Custom Joins in Access Queries
          1. B.1.4.1. Finding Missing Values
          2. B.1.4.2. Understanding Null
            1. B.1.4.2.1. If It's Not Something, Is It Nothing?
            2. B.1.4.2.2. What a Null Is
            3. B.1.4.2.3. The Difference between ZLS and Null
            4. B.1.4.2.4. Learn More about Nulls
      2. B.2. Domain (or Lookup) Tables
        1. B.2.1. Closed and Open Domains
        2. B.2.2. Domain Size
        3. B.2.3. Defining Your Domain Table Structure
          1. B.2.3.1. Open Domain Lookup Tables
          2. B.2.3.2. Closed Domain Lookup Tables
      3. B.3. Summary
    3. C. Resolving Relationship Triangles
      1. C.1. Non-Normalized Schema to Support a Less Complex Interface
      2. C.2. Using the Law Of Propagating Nulls to Validate
      3. C.3. Normalized Tables Supported in a More Complex Interface
      4. C.4. Sub-table for Notes
      5. C.5. Summary
    4. D. Measures: Financial and Performance
      1. D.1. Financial and Nonfinancial Measures
        1. D.1.1. Establishing the Baseline
        2. D.1.2. Selecting the Correct Measures
          1. D.1.2.1. Qualitative Measures
          2. D.1.2.2. Quantitative Measures
            1. D.1.2.2.1. Financial Measures
            2. D.1.2.2.2. Nonfinancial Measures
          3. D.1.2.3. Variances as Measures
        3. D.1.3. Examples of Measures
          1. D.1.3.1. General Examples
          2. D.1.3.2. Measures for Construction
          3. D.1.3.3. Measures Using Standard Costs
          4. D.1.3.4. Measures for Sales
        4. D.1.4. Example Tables
        5. D.1.5. Example Queries
      2. D.2. Summary
    5. E. References and Resources
      1. E.1. Our Favorite Starting Points
      2. E.2. Websites of the Authors and Tech Edit Teams
      3. E.3. Newsgroups
      4. E.4. Books
      5. E.5. Bonus Resource Database
        1. E.5.1. Forms
        2. E.5.2. Queries
        3. E.5.3. Modules
        4. E.5.4. Tables
    6. F. What's on the CD-ROM
      1. F.1. System Requirements
      2. F.2. Using the CD
      3. F.3. What's on the CD-ROM
        1. F.3.1. Access Database Designs Files
          1. F.3.1.1. Using the Files
          2. F.3.1.2. Folders, Files, and Features
            1. F.3.1.2.1. Chapter 4: People, Organizations, Addresses
            2. F.3.1.2.2. Chapter 5: Customer Relationship Management
            3. F.3.1.2.3. Chapter 6: Marketing
            4. F.3.1.2.4. Chapter 7: Sales
            5. F.3.1.2.5. Chapter 8: Production and Manufacturing
            6. F.3.1.2.6. Chapter 9: Inventory Management
            7. F.3.1.2.7. Chapter 10: Services
            8. F.3.1.2.8. Chapter 11: Accounting Systems: Requirements and Design
            9. F.3.1.2.9. Chapter 12: Accounting: Budgeting, Analysis, and Reporting
            10. F.3.1.2.10. Chapter 13: Managing Memberships
            11. F.3.1.2.11. Chapter 14: Implementing the Models
            12. F.3.1.2.12. Chapter 15: SQL Server and Other External Data Sources
            13. F.3.1.2.13. Appendixes A and B: Field Properties and Relationships, Joins, and Nulls
            14. F.3.1.2.14. Appendix C: Resolving Relationship Triangles
            15. F.3.1.2.15. Appendix D: Measures: Financial and Performance
            16. F.3.1.2.16. Appendix E: References and Resources
            17. F.3.1.2.17. Chapter B1: Knowledge: Intellectual Property, Structural Capital and Intellectual Capital
            18. F.3.1.2.18. Appendix BC: Data Warehouse Concepts
      4. F.4. Troubleshooting
      5. F.5. Customer Care
  14. VII. Bonus Material
    1. BONUS CHAPTER I. Knowledge: Intellectual Property, Structural Capital, and Intellectual Capital
      1. B1.1. Intellectual Property
        1. B1.1.1. Copyright
          1. B1.1.1.1. Duration of Copyright
          2. B1.1.1.2. Registering Your Copyright
          3. B1.1.1.3. Fair Use
          4. B1.1.1.4. Digital Millennium Copyright Act
        2. B1.1.2. Patent
        3. B1.1.3. Trade Secrets
        4. B1.1.4. Protecting Names
          1. B1.1.4.1. Trademarks and Service marks
          2. B1.1.4.2. Publishing Business Names
          3. B1.1.4.3. Registering Business Names
          4. B1.1.4.4. Website Universal Resource Locators
        5. B1.1.5. Tracking Intellectual Property
          1. B1.1.5.1. Basic Business Functions For Tracking Intellectual Property
          2. B1.1.5.2. Information to Record
            1. B1.1.5.2.1. The Subjects and Details of Tracking Intellectual Property
            2. B1.1.5.2.2. Other Types of Subjects
          3. B1.1.5.3. Arranging the Data into Tables
          4. B1.1.5.4. Tables for Tracking Intellectual Property
            1. B1.1.5.4.1. The Intellectual Property Table
            2. B1.1.5.4.2. The Intellectual Property Activity Table
            3. B1.1.5.4.3. The Intellectual Property Action Required Table
            4. B1.1.5.4.4. The Owner Table
            5. B1.1.5.4.5. The Internal Contact Table
            6. B1.1.5.4.6. The Phone Table
            7. B1.1.5.4.7. Junction Tables
          5. B1.1.5.5. The Intellectual Property-Internal Contact Table (a Junction Table)
          6. B1.1.5.6. The Intellectual Property-Owner Table (a Junction Table)
            1. B1.1.5.6.1. Lookup Tables
          7. B1.1.5.7. The Intellectual Property Type Table (a Lookup Table)
          8. B1.1.5.8. The Intellectual Property Activity Type Table (a Lookup Table)
          9. B1.1.5.9. The Owner Category Table (a Lookup Table)
          10. B1.1.5.10. The State Reference Table (a Lookup Table)
          11. B1.1.5.11. Business Functions Relationships Diagram
      2. B1.2. Structural Capital
        1. B1.2.1. Documenting Your Organization or Business
          1. B1.2.1.1. Basic Business Functions For Documenting Your Organization
          2. B1.2.1.2. Information to Record
            1. B1.2.1.2.1. The Subjects and Details of Documenting Your Organization
            2. B1.2.1.2.2. Other Types of Subjects
          3. B1.2.1.3. Arranging the Data into Tables
            1. B1.2.1.3.1. The Organization Information Table
            2. B1.2.1.3.2. The Department Table
            3. B1.2.1.3.3. The Position Table
            4. B1.2.1.3.4. The Business Function Table
            5. B1.2.1.3.5. The Step Table
            6. B1.2.1.3.6. The Task Table
          4. B1.2.1.4. Junction Tables
            1. B1.2.1.4.1. Department-Position Table (a Junction Table)
            2. B1.2.1.4.2. The Business Function–Department Table (a Junction Table)
            3. B1.2.1.4.3. The Business Function–Step Table (a Junction Table)
            4. B1.2.1.4.4. The Step-Task Table (a Junction Table)
            5. B1.2.1.4.5. Business Functions Relationship Diagram
      3. B1.3. Intellectual Capital
        1. B1.3.1. Human Capital
        2. B1.3.2. Enhancing the Relationship with Your Employees
          1. B1.3.2.1. Basic Business Functions For Employee Relationship Enhancement
          2. B1.3.2.2. Information to Record
            1. B1.3.2.2.1. The Subjects and Details of Employee Relationship Enhancement
            2. B1.3.2.2.2. Other Subjects
          3. B1.3.2.3. Arranging the Data into Tables
          4. B1.3.2.4. Tables in the Example Database for Employee Relationship Enhancement
            1. B1.3.2.4.1. The Employee Table
            2. B1.3.2.4.2. The Family Member Table
            3. B1.3.2.4.3. The Event Table
            4. B1.3.2.4.4. The Skill Table
            5. B1.3.2.4.5. The Phone Table
            6. B1.3.2.4.6. The E-Mail Table
            7. B1.3.2.4.7. The E-Mail Type Table (a Lookup Table)
            8. B1.3.2.4.8. The Relative Type Table (a Lookup Table)
            9. B1.3.2.4.9. The Event Type Table (a Lookup Table)
            10. B1.3.2.4.10. The State Reference Table (a Lookup Table)
            11. B1.3.2.4.11. Employee Relationship Enhancement Relationship Diagram
        3. B1.3.3. Supplier Relationship Management
          1. B1.3.3.1. Basic Business Functions For Supplier Tracking
          2. B1.3.3.2. Information to Record
            1. B1.3.3.2.1. The Subjects and Details of Supplier Tracking
            2. B1.3.3.2.2. Other Types of Subjects
          3. B1.3.3.3. Arranging the Data into Tables
          4. B1.3.3.4. Tables in the Example Database for Supplier Tracking
            1. B1.3.3.4.1. The Supplier Table
            2. B1.3.3.4.2. The Transaction Table
            3. B1.3.3.4.3. The Contact Table
            4. B1.3.3.4.4. The E-Mail Table
            5. B1.3.3.4.5. The Phone Table
            6. B1.3.3.4.6. The E-Mail Type Table (a Lookup Table)
            7. B1.3.3.4.7. The Transaction Type Table
            8. B1.3.3.4.8. Relationship Diagram for Supplier Tracking
      4. B1.4. Summary
    2. BA. Database and Business Terms
    3. BB. Gathering Requirements
      1. BB.1. Dos and Don'ts of Asking Questions
      2. BB.2. The Specification
        1. BB.2.1. Determine the Purpose of the Database
        2. BB.2.2. Determine the User Expectations
        3. BB.2.3. Determine the Number of Users and Estimated Data Volume
        4. BB.2.4. Determine the Anticipated Users
        5. BB.2.5. Identify Decision Makers
        6. BB.2.6. Develop a Summary of Your Findings
        7. BB.2.7. Review, Revision, and Acceptance of Your Specification
        8. BB.2.8. Develop Priorities and Time Frames for Deliverables or Milestones
      3. BB.3. Comments and Summary
    4. BC. Data Warehousing Concepts
      1. BC.1. Data Warehouse Tables
        1. BC.1.1. Fact Tables
        2. BC.1.2. Dimension Tables
      2. BC.2. Load Process
        1. BC.2.1. Extracts
        2. BC.2.2. Summarizing the Data
      3. BC.3. Table Design
        1. BC.3.1. The Fact and Dim Tables
      4. BC.4. Query Design
      5. BC.5. Reporting on the Data
    5. BD. Database Platforms
      1. BD.1. Key Factors for Selecting the Platform
      2. BD.2. Common Platforms
        1. BD.2.1. ACCDB/ACE
          1. BD.2.1.1. Volume of Data
          2. BD.2.1.2. Number of Concurrent Users
          3. BD.2.1.3. Data Security
          4. BD.2.1.4. Location of Users
          5. BD.2.1.5. Cost
        2. BD.2.2. Server Databases (in general)
          1. BD.2.2.1. Volume of Data
          2. BD.2.2.2. Number of Concurrent Users
          3. BD.2.2.3. Data Security
          4. BD.2.2.4. Location of Users
          5. BD.2.2.5. Cost
        3. BD.2.3. Microsoft SQL Server
          1. BD.2.3.1. Access Data Projects (ADP)
        4. BD.2.4. SharePoint
          1. BD.2.4.1. Volume of Data
          2. BD.2.4.2. Number of Concurrent Users
          3. BD.2.4.3. Data Security
          4. BD.2.4.4. Location of Users
          5. BD.2.4.5. Cost
      3. BD.3. Summary

Product information

  • Title: Microsoft® Access® Small Business Solutions: State-of-the-Art Database Models for Sales, Marketing, Customer Management, and More Key Business Activities
  • Author(s):
  • Release date: March 2010
  • Publisher(s): Wiley
  • ISBN: 9780470525746