book

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

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

by Teresa Hennig, Truitt Bradly, Larry Linson, Leigh Purvis, Brent Spaulding

March 2010

Intermediate to advanced

912 pages

22h 43m

English

Wiley

Read now

Unlock full access

Copyright
About the Authors
About the Contributing Authors and Technical Editors
Primary Technical Editors
Credits
Acknowledgments
Foreword
Introduction
Who Should Read This BookHow This Book Is OrganizedWhat's on the CD-ROM
I. Introduction and Basic Concepts
1. Overview and Road Map
1.1. Part I: Introduction and Fundamentals1.1.1. Chapter 1: Overview and Road Map1.1.2. Chapter 2: Elements of a Microsoft Access Database1.1.3. Chapter 3: Relational Data Model1.2. Part II: Dealing with Customers and Customer Data1.2.1. Chapter 4: People, Organizations, Addresses1.2.2. Chapter 5: Customer Relationship Management1.2.3. Chapter 6: Marketing1.2.4. Chapter 7: Sales1.2.5. Chapter 8: Production and Manufacturing1.2.6. Chapter 9: Inventory Management1.2.7. Chapter 10: Services1.3. Part IV: Tracking and Analyzing Financial Data1.3.1. Chapter 11: Accounting Systems—Requirements and Design1.3.2. Chapter 12: Accounting: Budgeting, Analysis, and Reporting1.4. Part V: Independent Areas1.4.1. Chapter 13: Managing Memberships1.4.2. Chapter 14: Implementing the Models1.4.3. Chapter 15: SQL Server and Other External Data Sources1.5. The Appendixes1.5.1. Appendix A: Field Properties1.5.2. Appendix B: Relationships, Joins, and Nulls1.5.3. Appendix C: Resolving Relationship Triangles1.5.4. Appendix D: Measures—Financial and Performance1.5.5. Appendix E: References and Resources1.5.6. Appendix F: What's on the CD-ROM1.6. Part VI: Bonus Content1.6.1. Chapter B1 (on the CD): Knowledge: Intellectual Property, Structural Capital, and Intellectual Capital1.6.2. Appendix BA: Database and Business Terms1.6.3. Appendix BB: Gathering Requirements1.6.4. Appendix BC: Data Warehousing Concepts1.6.5. Appendix BD: Database Platforms
2. Elements of a Microsoft Access Database
2.1. Database Schema2.2. Tables2.2.1. Fields2.2.2. Field Properties2.2.3. Indexes2.3. Relationships2.4. Queries2.5. Summary

3. Relational Data Model
3.1. The Basics3.1.1. Correlation to Microsoft Access3.2. A Little Deeper3.2.1. Relations, Relationships, and Keys3.2.2. Derived Relations3.2.3. Domains and Constraints3.3. Normalization3.3.1. Objectives3.3.1.1. Objective 1: Prevent anomalies during data modification3.3.1.2. Objective 2: Reduce the need for restructuring as new types of data are introduced, thus increasing the life span of application programs3.3.1.3. Objective 3: Make the database more informative to the users3.3.1.4. Objective 4: Make the collections of relations, or tables, neutral to querying patterns3.3.1.5. Objective 5: Reduce data entry steps3.3.2. Normal Forms3.3.2.1. First Normal Form: Eliminate Repeating Groups3.3.2.2. Second Normal Form: Eliminate Redundant Data3.3.2.3. Third Normal Form: Eliminate Columns Not Dependent on a Candidate Key3.3.3. Boyce-Codd Normal Form3.3.3.1. Fourth and Fifth Normal Forms: Isolate Multiple (Many-to-Many) Relationships3.3.4. When to Denormalize3.4. Summary
II. Dealing with Customers and Customer Data
4. People, Organizations, Addresses
4.1. How This Chapter Is Organized4.2. Basic Field Information4.3. People and Organization Information Processing and Storage4.3.1. Personal Information Manager (PIM) or Address Book (AB) Basics4.3.2. Questions to Ask4.3.3. Items to Store4.3.3.1. The Subject and Details of People4.3.3.2. The Subject and Details of Organizations4.3.3.3. Other Subjects and Details4.3.4. Arranging the Data into Tables4.3.4.1. The Person Table4.3.4.2. The Organization Table4.3.4.3. The Organization-Person Table4.3.4.4. The Phone and Email Tables4.3.4.5. Reference or Lookup Tables4.3.5. Determining the Level of Detail4.4. The Simple Model4.4.1. The Person Table4.4.2. The Email For Person Table4.4.3. The Phone For Person Table4.4.4. The Organization Table4.4.5. The Email for Organization Table4.4.6. The Phone for Organization Table4.4.7. Lookup Tables4.4.7.1. The Person Category Table (a Lookup Table)4.4.7.2. The Organization Category Table (a Lookup Table)4.4.7.3. The Organization Type Table (a Lookup Table)4.4.7.4. The State Reference Table (a Lookup Table)4.4.7.5. The Country Reference Table (a Lookup Table)4.4.7.6. The EMail Type Table (a Lookup Table)4.4.7.7. The Month Table (a Lookup Table)4.4.8. Junction Tables4.4.8.1. The Organization-Person Table (a Junction Table)4.4.9. Putting the Tables Together4.5. Differences between the Simple and Complex Models4.6. The Complex Model4.6.1. The Person Table4.6.2. The Organization Table4.6.3. The EMail Table4.6.4. The Phone Table4.6.5. The Address Table4.6.6. Lookup Tables4.6.6.1. The Address Type Table (a Lookup Table)4.6.6.2. The Role Table (a Lookup Table)4.6.6.3. The Title Table (a Lookup Table)4.6.6.4. The Title Suffix Table (a Lookup Table)4.6.7. Junction Tables4.6.7.1. The EMail-Person Table (a Junction Table)4.6.7.2. The Person-Phone Table (a Junction Table)4.6.7.3. The Address-Person Table (a Junction Table)4.6.7.4. The EMail-Organization Table (a Junction Table)4.6.7.5. The Organization-Phone Table (a Junction Table)4.6.7.6. The Address-Organization Table (a Junction Table)4.6.7.7. The Organization-Person Table (a Junction Table)4.6.8. Putting the Tables Together4.7. Adding More Information about People and Organizations4.8. Summary
5. Customer Relationship Management
5.1. Six Core Topics of Customer Relationship Management5.2. Approach for Reviewing Core Topics5.2.1. Basic Business Functions for the Topic5.2.2. Items to Store5.2.2.1. The Subjects and Details of the Topic5.2.2.2. Other Types of Subjects5.2.2.3. Arranging the Data into Tables5.2.3. Tables in the Example Database for Each Topic5.2.3.1. The Table and Fields5.2.3.2. Relationship Diagram Showing the Schema or Overview of the Topic5.3. Customer Information5.3.1. Improving Your Relationship with Your Customers5.3.2. Gathering Data to Analyze in the Future5.4. Basic Business Functions for People and Organizations5.4.1. Arranging the Data Into Tables5.4.1.1. Customer Table5.4.2. The Person Table Grouping5.4.2.1. The Family Member Table5.4.2.2. Lookup Tables5.4.2.2.1. Marital Status Table (a Lookup Table)5.4.2.2.2. The Family Member Type Table (a Lookup Table)5.4.2.2.3. The Phone Type Table (a Lookup Table)5.4.3. The Organization Table Grouping5.4.3.1. Organization-Related Lookup Tables5.4.3.2. Overview of Person and Organization Information5.5. Tracking Customer Correspondence5.5.1. Basic Business Functions for Tracking Correspondence5.5.2. Items to Store5.5.2.1. The Subject and Details of Customers, People, and Organizations5.5.2.2. The Subjects and Details of Correspondence5.5.2.3. Other Types of Subject5.5.3. Arranging the Data Into Tables5.5.3.1. The Correspondence Table5.5.3.2. Correspondence Detail5.5.3.3. The Employee Table5.5.3.4. Lookup Data5.5.4. Tables as Implemented in the Example Database5.5.4.1. The Correspondence Table5.5.4.2. The Correspondence Detail Table5.5.4.3. The Employee Table5.5.4.4. The Correspondence Type Table (A Lookup Table)5.6. Tracking Correspondence Overview5.7. Tracking Sales Information for CRM5.7.1. Items to Store5.7.1.1. The Subjects and Details of Sales5.7.1.2. Other Types of Subjects5.7.2. Arranging the Data Into Tables5.7.2.1. The Invoice Table5.7.2.2. The Invoice Detail Table5.7.2.3. The Product Table5.7.2.4. The Product Category Table5.7.3. Tables as Implemented in the Example Database5.7.3.1. The Invoice Table5.7.3.2. The Invoice Detail Table5.7.3.3. The Product Table (a Lookup Table)5.7.3.4. The Product Category Table (a Lookup Table)5.8. Sales Information for CRM Overview5.9. Tracking Service and Support Information5.9.1. Basic Business Functions for Tracking Support5.9.2. Items to Store5.9.2.1. The Subjects and Details of Tracking Support5.9.2.2. Other Types of Subject5.9.3. Arranging the Data Into Tables5.9.3.1. The Incident Table5.9.3.2. The Incident Activity Table5.9.3.3. The Incident Document Table5.9.3.4. The Incident Activity Type Table5.9.3.5. The Incident Activity Status Table5.9.4. Tables as Implemented in the Example Database5.9.4.1. The Incident Table5.9.4.2. The Incident Activity Table5.9.4.3. The Incident Document Table5.9.4.4. The Incident Activity Type Table (a Lookup Table)5.9.4.5. The Incident Activity Status Table (a Lookup Table)5.9.5. Schema of Service and Support Tables5.10. Advertising and Marketing Campaigns, Targeted and General5.10.1. Customer Awareness5.10.2. Basic Business Functions for Planning and Tracking Campaigns5.10.3. Items to Store5.10.3.1. The Subjects and Details of Planning and Tracking Campaigns5.10.3.2. Other Types of Subject5.10.4. Arranging the Data Into Tables5.10.4.1. The Campaign Table5.10.4.2. The Campaign Activity Table5.10.4.3. The Campaign Task Table5.10.4.4. The Campaign Document Table5.10.4.5. The Campaign Document Type Table5.10.5. Tables as Implemented in the Example Database5.10.5.1. The Campaign Table5.10.5.2. The Campaign Activity Table5.10.5.3. The Campaign Task Table5.10.5.4. The Campaign Document Table5.10.5.5. The Campaign Document Type Table (a Lookup Table)5.10.6. Schema of Advertising and Marketing Campaigns5.11. Internal Information Sharing5.11.1. Basic Business Functions for Internal Information Sharing5.11.2. Items to Store5.11.2.1. The Subjects and Details of Internal Information Sharing5.11.3. Arranging the Data Into Tables5.11.3.1. The Person Table5.11.3.2. The Employee Information Table5.11.3.3. The Contractor Information Table5.11.3.4. The Information Type Table5.11.3.5. Simplifying the Design5.11.4. Tables as Implemented in the Example Database5.11.4.1. The Information Type Table5.11.4.2. The Contractor-Information Table (a Junction Table)5.11.4.3. The Employee-Information Table (a Junction Table)5.11.5. Schema Overview of Internal Information Sharing5.12. Improving Process Quality5.13. Summary
6. Marketing
6.1. Database Terminology6.1.1. Database Terminology6.2. Marketing Campaigns6.2.1. Identifying Required Data Elements6.2.2. Assigning the Details to a Subject6.2.2.1. Campaign Stages and Communications6.2.2.2. Descriptions of the Core Subjects6.2.3. Creating the Tables6.2.3.1. Creating Additional Fields6.2.3.2. Reference Codes6.2.3.3. Expanding Product Areas6.2.4. Creating the Tables Related to Organization6.2.4.1. Organization Individuals6.2.4.2. Organization Communication Details6.2.4.3. Organization Types6.2.5. Further Campaign Subjects6.2.5.1. Targeted Organizations6.2.5.2. Campaign Events6.2.6. Summary of the Marketing Model6.3. Questionnaires6.3.1. Just Questions and Answers6.3.2. The Overly Simple Inappropriate Design6.3.2.1. The Simple Genuine Questionnaire6.3.2.2. Identifying the Core Tables6.3.3. The Flexible Questionnaire6.3.3.1. Specific Data Type Answers6.3.3.2. Determining the Data Type for Questions6.3.3.3. Multiple-Choice Questions6.3.3.3.1. The Question Options Table6.3.3.3.2. The Questions Table6.3.4. The Final Questionnaire6.3.4.1. Questionnaire Sections6.3.4.2. Organization Contact Interviews6.3.4.3. Question Dependency6.3.4.4. Alternative Design: A Question Pool6.3.5. Marketing Combined6.4. Summary
7. Sales
7.1. Building the Model7.1.1. Gathering Specification Data7.1.1.1. Obtaining Information7.1.1.2. Identifying the Requirements7.1.1.3. Identifying the Key Subjects7.2. Designing the Tables7.2.1. Customers7.2.1.1. Customer Options7.2.1.1.1. One Table for All Customers7.2.2. Products7.2.2.1. Required Product Fields7.2.3. Orders7.2.3.1. Required Order Fields7.2.3.2. Consequences and Alternative Fields7.2.4. Order Detail7.2.4.1. Required Order Detail Fields7.2.4.1.1. Snapshot Data and the Lookup Alternative7.2.5. Putting the Model Together7.3. A More Complex Model7.3.1. Additional Requirements7.3.1.1. Multiple Product Prices7.3.1.2. Product Images7.3.1.3. Employees7.3.1.4. Multiple Notes7.3.1.5. Order Quotes7.4. Using the Database7.4.1. Querying the Database7.4.1.1. Invoicing7.4.1.2. Order Product Totals7.4.2. Alternative Designs7.4.2.1. Product Grouping7.4.2.2. Notes Assignment7.4.2.3. Additional Order Overview Option7.5. Summary
III. Producing and Tracking the Goods & Services
8. Production and Manufacturing
8.1. Industry Terms8.2. Enterprise-Level Systems8.3. Purpose of the Database8.4. Identifying the Requirements8.5. Identifying User Expectations8.6. Manufacturing Overview: Identifying the Core Subjects8.6.1. The Main Subject: Manufacturing Processes8.6.2. Additional Core Subjects8.6.3. Relationships between Core Subjects8.6.4. The Approach for Reviewing the Core Subjects8.6.4.1. Reviewing the Requirements8.6.4.2. Identifying the Subjects8.6.4.3. Identifying the Details8.6.4.4. Grouping the Details into Subjects8.6.4.4.1. Evaluating and Grouping the Details - The Four Questions8.6.5. Creating the Tables and Fields8.6.5.1. Reviewing the Relationships8.7. Manufacturing Processes8.7.1. Manufacturing Process Hierarchies8.7.1.1. Appropriate Depth of Your Process Tree8.7.2. Identifying the Details8.7.3. Creating the Tables8.7.3.1. Explaining the Approach and Types of Tables8.7.3.2. Potential Child Tables: Additional Details8.7.3.3. Lookup Tables8.7.3.4. Self-Referencing Table8.7.3.5. Choosing between Alternate Designs8.7.3.6. Many-to-Many Relationship and a Child Table8.7.3.6.1. Self-Referencing Lookup Table8.7.3.6.2. Supporting Tables for Processes8.7.3.6.3. Understanding Lookup Tables8.7.3.7. Explaining the Fields8.7.3.7.1. Standard Fields8.7.3.7.2. Primary Keys8.7.3.7.3. Other Standard Fields8.7.3.8. Creating the Tables and Fields8.7.3.8.1. Application Performance vs. Using Calculated Fields8.7.3.8.2. The Process Table8.7.3.8.3. The Process Manufacturer Table8.7.3.8.4. The Manufacturer Table8.7.3.8.5. The Process Vendor Table8.7.3.8.6. The Vendor Table8.7.3.8.7. The Process Type Table8.7.3.8.8. The Process Backup Table8.7.3.8.9. The Location Table8.7.3.9. Relationships8.7.3.10. Join Lines, Relationship Symbols, and Referential Integrity8.7.4. Manufacturing Builds8.7.4.1. Identifying the Details8.7.4.2. Grouping the Details into Subjects8.7.4.3. Creating the Tables and Fields8.7.4.3.1. The Build Table8.7.4.3.2. The Build Stoptime Table8.7.4.3.3. The Build Stoptime Notification Table8.7.4.4. Reviewing the Relationships8.7.5. Notifications8.7.5.1. Identifying the Details8.7.5.2. Grouping the Details into Subjects8.7.5.3. Creating the Tables and Fields8.7.5.3.1. The Notifications Table8.7.5.3.2. The Conditions Table8.7.5.3.3. The Discovery Method Table8.7.5.3.4. The Team Member Table8.7.5.3.5. The Severity Table8.7.5.3.6. The Notification Type Table8.7.5.3.7. The Cause Table8.7.5.4. Reviewing the Relationships8.7.6. Countermeasures8.7.6.1. Identifying the Details8.7.6.2. Grouping the Details into Subjects8.7.6.3. Creating the Tables and Fields8.7.6.3.1. The Countermeasure Table8.7.6.3.2. The Countermeasure Team Member Table8.7.6.3.3. The Team Member Table8.7.6.3.4. The Action Table8.7.6.3.5. The Countermeasure Step Table8.7.6.4. Reviewing the Relationships8.7.7. General Notes8.7.7.1. Identifying the Details8.7.7.2. Grouping the Details into Subjects8.7.7.3. Creating the Tables and Fields8.7.7.3.1. The General Note Table8.7.7.3.2. The General Note Process Table8.7.7.3.3. The Team Member Table8.7.7.4. Reviewing the Relationships8.8. Putting It All Together8.8.1. Final Walk-Through8.9. More Information8.10. Summary
9. Inventory Management
9.1. The Approach for This Chapter9.2. The Inventory Management Database9.2.1. Purpose of the Database9.2.2. Identifying Your Requirements9.2.3. User Expectations9.3. Inventory Overview: Identifying the Subjects9.3.1. Core and Supporting Subjects9.3.1.1. Core Subjects9.3.1.2. Relationships between the Core Subjects9.4. Approach for Reviewing the Core Subjects9.4.1. Identifying the Details9.4.1.1. The Standard Fields9.4.2. The Types of Tables9.4.3. Creating the Tables and Fields9.4.4. Establishing the Relationships9.4.5. Putting It All Together9.5. Products9.5.1. Identifying the Details9.5.2. Identifying the Supporting Tables9.5.3. Evaluating the Details: The Four Questions9.5.4. Using Transient Fields9.5.5. Using Lookup Tables9.5.6. Many-to-Many Relationships and Child Tables9.5.7. Design Alternatives for Quantity on Hand9.5.8. Using a Transaction Table9.5.9. Table Relationships for Products9.5.10. Creating the Tables and Fields9.5.10.1. The Product Table9.5.10.2. The Brand Table9.5.10.3. The Unit of Measure Table9.5.10.4. The Product Location Table9.5.10.5. The Product Vendor Table9.5.10.6. The Product Vendor Cost Table9.5.11. Establishing the Relationships9.6. Vendors9.6.1. Identifying the Details9.6.2. Identifying the Supporting Tables9.6.3. Creating the Tables and Fields9.6.3.1. The Vendor Table9.6.3.2. The Vendor Address Table9.6.3.3. The Address Type Table9.6.3.4. The Vendor Contact Table9.6.3.5. The Contact Communication Table9.6.3.6. The Communication Type Table9.6.4. Establishing the Relationships9.7. Locations9.7.1. Identifying the Details9.7.2. Indentifying the Supporting Tables9.7.2.1. Explaining Self-Referencing Tables9.7.3. Creating the Tables and Fields9.7.3.1. The Location Table9.7.4. Establishing the Relationships9.8. Transactions9.8.1. Identifying the Details9.8.2. Identifying the Supporting Tables9.8.3. Creating the Tables and Fields9.8.3.1. The Inventory Transaction Table9.8.3.2. The Transaction Type Table9.8.4. Establishing the Relationships9.9. Putting It All Together9.10. Summary
10. Services
10.1. Database Terminology10.2. Appointments Made Simple10.3. Pet Boarding Example10.3.1. The Business Rules10.3.2. Identifying the Subjects10.3.2.1. Customer Tables10.3.2.2. Pet Tables10.3.2.3. Booking Table10.3.2.4. Booking Detail Tables10.3.2.5. The Booking Table Expanded10.3.2.5.1. Booking Types10.3.2.5.2. Booking Status10.3.2.5.3. Booking Staff Members10.3.3. Checking Appointments10.3.4. Extending the Database10.4. Process Monitoring10.4.1. Events Management10.4.1.1. Event Management Requirements10.4.1.2. Identifying the Subjects10.4.1.2.1. Customer Data10.4.1.2.2. Employees, Individuals, and Roles10.4.1.3. Identifying the Event Subjects10.4.1.3.1. The Core Event10.4.1.3.2. Related Event Subjects10.4.1.3.3. New Event Subjects10.4.1.3.4. The Final Event Table10.4.1.4. Event Detail Tables10.4.1.5. Querying Events10.4.2. The Process Model10.4.3. Event Notes10.5. Variations of the Theme10.6. Summary
IV. Tracking and Analyzing Financial Data
11. Accounting Systems: Requirements and Design
11.1. Double-Entry Accounting11.2. Chart of Accounts11.3. General Ledger11.3.1. Subsidiary Ledgers11.3.1.1. Cash Subledgers11.3.1.2. Investment Subledgers11.3.1.3. Accounts Receivable Subledgers11.3.1.4. Accounts Payable Subledgers11.4. Database Design Considerations11.5. Accounting Database11.5.1. Table Structure11.5.1.1. Standard Fields11.5.1.2. The Account Table11.5.1.3. General Ledger Table11.5.1.4. Lookup Tables11.5.1.4.1. Ledger Type Table11.5.1.4.2. Transaction Type Table11.5.1.4.3. Vendor/Customer Table11.5.1.4.4. Cash Ledger Table11.5.1.4.5. Batch Table11.5.1.4.6. User Table11.5.1.4.7. Report Header Table11.5.1.4.8. Report Detail Table11.5.1.5. Optional Tables11.5.1.5.1. Holiday Table11.5.1.5.2. Rate Table11.5.1.5.3. Department Table11.5.1.5.4. Work Day Table11.5.1.5.5. Investment Master Table11.5.1.5.6. Investment Detail Table11.5.2. Queries11.5.2.1. Using Queries to Create a Virtual Ledger11.5.2.1.1. Aggregate Queries - Account Balances11.6. Relationships11.6.1. Creating and Using Archive Tables11.6.1.1. Creating an Archive Table11.6.1.2. Verifying Income Statement Accounts11.6.1.3. Appending Data to an Archive Table11.6.1.4. Posting Ending Balances to the General Ledger11.7. Summary
12. Accounting: Budgeting, Analysis, and Reporting
12.1. Budgets12.1.1. Budget Basics12.1.1.1. Creating a Budget12.1.1.2. Budget Tools12.1.1.3. Base Budget Data12.1.1.4. Getting the Data into Excel12.1.1.4.1. Export and Import Methods12.1.1.5. Spreading the Data Over the Budget Period12.1.1.6. Getting Data to Excel for Additional Analysis12.1.1.7. Budget Entries: Manual Entry or Import12.1.1.8. Process Flow Control12.1.2. Applying the Budget12.1.2.1. Reporting Variances in the Budget12.2. Analyzing Profitability12.2.1. Reports12.2.1.1. Trial Balance12.2.1.2. Balance Sheet12.2.1.3. Income Statement12.2.1.4. Common-Size Statements12.2.2. Ratio Analysis12.2.2.1. Profitability Ratios12.2.2.2. Liquidity Ratios12.2.2.3. Activity Ratios12.3. Summary
V. Independent Areas
13. Managing Memberships
13.1. Membership Management Functions13.1.1.13.1.1.1. The Member Information Table13.1.2. Adding and Renewing Memberships13.1.3. Keeping Track of Members13.1.3.1. The Personal Note Table13.1.4. Offices and Elections13.1.4.1. Tables as Implemented in the Example Database13.1.4.1.1. The Office Table13.1.4.1.2. The Term in Office Table13.1.5. Lookup Tables13.1.5.1. The Membership Type Table (a Lookup Table)13.1.5.2. The Member Status Table (a Lookup Table)13.1.6. Junction Tables13.1.7. Documenting Memberships13.1.8. Reporting Member Information13.1.8.1. The Privacy Level Table13.1.9. Recording Expiration and Payments13.1.9.1. The Payment Table13.1.9.2. The Payment Source Table13.1.9.3. The Payment Type Table13.2. Communication13.2.1. Events13.2.1.1. Planning Events13.2.1.1.1. The Event Table13.2.1.1.2. The Event Document Table13.2.1.2. Schedule and Tasks13.2.1.2.1. The Task for Event Table13.2.1.3. Event Staff13.2.1.3.1. The Event-Member Table (a Junction Table)13.2.1.4. Contracts and Contacts13.2.1.4.1. The Event-Person Table (a Junction Table)13.2.2. Event Execution and Follow-up13.3. Recruiting13.3.1.13.3.1.1. Getting New Members13.3.1.2. Re-recruiting Former Members13.4. Sponsorships13.4.1. The Sponsor Table13.4.2. The Contact for Sponsor Table13.4.2.1. Small Membership Organizations13.5. Summary
14. Implementing the Models
14.1. Not-for-Profit Organizations14.1.1. Tracking Membership14.1.1.1. Membership Types14.1.1.2. Dues Collection14.1.1.2.1. Billing14.1.1.2.2. Receipt of Payment14.1.1.3. Member Information14.1.1.3.1. Contact Information14.1.1.3.2. Demographic Information14.1.2. Tracking Donations14.1.3. Tracking Sales14.2. Designing the Database14.2.1. Tables14.2.1.1. Dues Receivable14.2.1.2. Payment Detail14.2.1.3. Dues Type14.2.1.4. Dues Rate14.2.1.5. Individual-Dues Type14.2.1.6. Individual Detail14.2.1.7. Detail Type14.2.2. Defining Relationships14.3. Components of the User Interface14.3.1. Queries14.3.1.1. Parameter Queries14.3.1.2. Types of Queries14.3.1.2.1. Select Queries14.3.1.2.2. Action Queries14.3.2. Forms14.3.3. Reports14.3.4. Modules14.3.5. Backup during Development14.4. User Interface Design14.4.1. Activities Focused on Individuals14.4.1.1. Entry Form14.4.1.2. Add Functionality14.4.1.2.1. Adding the list box Control14.4.1.2.2. Adding VBA to the List Box Control14.4.1.2.3. Adding a Subform for Dues History14.4.1.3. Related Forms14.4.1.3.1. Dues Supporting Forms14.4.2. Customers and Donors14.4.2.1. Customers14.4.2.1.1. Data Entry Forms14.4.2.1.2. To Look Up Individuals14.5. Products14.5.1. Product Maintenance14.5.1.1. Fundraising Product Design14.5.1.1.1. Data Entry Forms14.5.1.1.2. Product List Reports14.5.1.1.3. Product Price Report14.5.2. Orders and Sales14.5.2.1. Order Entry Form14.5.2.1.1. Create the Order Detail Subform14.5.2.1.2. Create a Calculated Field in the Query14.5.2.1.3. Create the Order Detail Subform14.5.2.1.4. Add a customer lookup14.5.2.1.5. Adding the Customer Address Subform14.5.2.1.6. Tab Stops and Tab Order14.5.2.1.7. Order Totals14.5.3. Invoices14.5.3.1. Invoice Form14.5.3.2. Invoice Report14.5.3.2.1. Creating a Query for the Record Source14.5.3.2.2. Creating the Invoice14.6. Summary
15. SQL Server and Other External Data Sources
15.1. Client-Server Databases15.1.1. File-Server Limitations15.1.2. Server Databases15.1.3. Microsoft SQL Server15.1.3.1. Data Type Comparisons15.1.3.1.1. Date Data Types15.1.3.1.2. Text Data Types15.1.3.1.3. Boolean (Yes/No) Data Types15.1.3.1.4. Numeric Data Types15.1.3.1.5. Binary (File) Data Types15.1.3.1.6. Other Data Types15.1.3.2. Server Functionality15.1.3.2.1. Server Robustness15.1.3.2.2. Server Objects and Transact SQL15.1.3.2.3. SQL Server Editions15.2. Server Database Design15.2.1. Design Considerations15.2.1.1. Narrow and Long Tables15.2.1.2. Lookup Tables15.2.1.3. Design Optimization15.2.1.3.1. Denormalizing15.2.1.3.2. Assisting Optimization15.2.1.4. Problem Tables and Conversions15.3. Optimization Suggestions15.3.1. Appropriate Use of Indexing15.3.2. Query Operators: Selection Criteria15.3.3. Linked Table Implications15.3.4. Other Access Functionality15.3.4.1. Pass-through Queries15.3.4.2. Local Table Storage15.3.4.3. Access Data Projects15.4. Other Data Sources15.4.1. Other Server Databases15.4.2. Data Formats15.4.2.1. Excel Spreadsheets15.4.2.2. SharePoint15.4.2.3. Text Files and CSVs15.4.2.4. XML15.5. Summary
VI. Appendixes
A. Field Properties
A.1. Using Field PropertiesA.1.1. Properties of AutoNumber FieldsA.1.1.1. Field SizeA.1.1.2. New ValuesA.1.1.3. Display FormatsA.1.1.4. CaptionA.1.1.5. IndexedA.1.1.6. Smart TagsA.1.1.7. Text AlignA.1.1.8. When Should You Not Use an AutoNumber?A.1.2. Properties of Text FieldsA.1.2.1. Field SizeA.1.2.2. FormatA.1.2.3. Input MaskA.1.2.4. CaptionA.1.2.5. Default ValueA.1.2.6. Validation Rule and Validation TextA.1.2.7. RequiredA.1.2.8. Allow Zero LengthA.1.2.9. IndexedA.1.2.10. Unicode CompressionA.1.2.11. IME Mode and IME Sentence ModeA.1.2.12. Smart TagsA.1.2.13. Text AlignA.1.3. Properties of Date/Time FieldsA.1.3.1. Format and Input MaskA.1.3.2. Default ValueA.1.3.3. Validation Rule and Validation TextA.1.3.4. Show Date PickerA.1.3.5. Further Study on Dates and TimeA.1.4. Properties of Number FieldsA.1.4.1. Field SizeA.1.4.1.1. ByteA.1.4.1.2. IntegerA.1.4.1.3. Long IntegerA.1.4.1.4. Single (aka Float), Double and DecimalA.1.4.2. Decimal PlacesA.1.4.3. Default ValueA.1.5. Properties for the Currency Data TypesA.1.5.1. FormatA.1.5.2. Decimal PlacesA.1.6. Properties for the Memo Data TypeA.1.7. Unique Index Enforced for a Text FieldA.1.7.1. Single Field IndexA.1.7.2. Multiple Field IndexA.1.7.3. Unique PropertyA.1.7.4. Ignore NullsA.1.8. Properties for the Yes/No Data TypeA.1.8.1. Using Yes/No Fields in QueriesA.1.8.2. Custom Presentation Using Yes/No Fields in QueriesA.1.8.2.1. IIF Statements
B. Relationships, Joins, and Nulls
B.1. Relationships and JoinsB.1.1. It's All Been Done BeforeB.1.2. Telling It Like It IsB.1.3. Change Happens—Redefining RelationshipsB.1.4. Automatic and Custom Joins in Access QueriesB.1.4.1. Finding Missing ValuesB.1.4.2. Understanding NullB.1.4.2.1. If It's Not Something, Is It Nothing?B.1.4.2.2. What a Null IsB.1.4.2.3. The Difference between ZLS and NullB.1.4.2.4. Learn More about NullsB.2. Domain (or Lookup) TablesB.2.1. Closed and Open DomainsB.2.2. Domain SizeB.2.3. Defining Your Domain Table StructureB.2.3.1. Open Domain Lookup TablesB.2.3.2. Closed Domain Lookup TablesB.3. Summary
C. Resolving Relationship Triangles
C.1. Non-Normalized Schema to Support a Less Complex InterfaceC.2. Using the Law Of Propagating Nulls to ValidateC.3. Normalized Tables Supported in a More Complex InterfaceC.4. Sub-table for NotesC.5. Summary
D. Measures: Financial and Performance
D.1. Financial and Nonfinancial MeasuresD.1.1. Establishing the BaselineD.1.2. Selecting the Correct MeasuresD.1.2.1. Qualitative MeasuresD.1.2.2. Quantitative MeasuresD.1.2.2.1. Financial MeasuresD.1.2.2.2. Nonfinancial MeasuresD.1.2.3. Variances as MeasuresD.1.3. Examples of MeasuresD.1.3.1. General ExamplesD.1.3.2. Measures for ConstructionD.1.3.3. Measures Using Standard CostsD.1.3.4. Measures for SalesD.1.4. Example TablesD.1.5. Example QueriesD.2. Summary
E. References and Resources
E.1. Our Favorite Starting PointsE.2. Websites of the Authors and Tech Edit TeamsE.3. NewsgroupsE.4. BooksE.5. Bonus Resource DatabaseE.5.1. FormsE.5.2. QueriesE.5.3. ModulesE.5.4. Tables
F. What's on the CD-ROM
F.1. System RequirementsF.2. Using the CDF.3. What's on the CD-ROMF.3.1. Access Database Designs FilesF.3.1.1. Using the FilesF.3.1.2. Folders, Files, and FeaturesF.3.1.2.1. Chapter 4: People, Organizations, AddressesF.3.1.2.2. Chapter 5: Customer Relationship ManagementF.3.1.2.3. Chapter 6: MarketingF.3.1.2.4. Chapter 7: SalesF.3.1.2.5. Chapter 8: Production and ManufacturingF.3.1.2.6. Chapter 9: Inventory ManagementF.3.1.2.7. Chapter 10: ServicesF.3.1.2.8. Chapter 11: Accounting Systems: Requirements and DesignF.3.1.2.9. Chapter 12: Accounting: Budgeting, Analysis, and ReportingF.3.1.2.10. Chapter 13: Managing MembershipsF.3.1.2.11. Chapter 14: Implementing the ModelsF.3.1.2.12. Chapter 15: SQL Server and Other External Data SourcesF.3.1.2.13. Appendixes A and B: Field Properties and Relationships, Joins, and NullsF.3.1.2.14. Appendix C: Resolving Relationship TrianglesF.3.1.2.15. Appendix D: Measures: Financial and PerformanceF.3.1.2.16. Appendix E: References and ResourcesF.3.1.2.17. Chapter B1: Knowledge: Intellectual Property, Structural Capital and Intellectual CapitalF.3.1.2.18. Appendix BC: Data Warehouse ConceptsF.4. TroubleshootingF.5. Customer Care
VII. Bonus Material
BONUS CHAPTER I. Knowledge: Intellectual Property, Structural Capital, and Intellectual Capital
B1.1. Intellectual PropertyB1.1.1. CopyrightB1.1.1.1. Duration of CopyrightB1.1.1.2. Registering Your CopyrightB1.1.1.3. Fair UseB1.1.1.4. Digital Millennium Copyright ActB1.1.2. PatentB1.1.3. Trade SecretsB1.1.4. Protecting NamesB1.1.4.1. Trademarks and Service marksB1.1.4.2. Publishing Business NamesB1.1.4.3. Registering Business NamesB1.1.4.4. Website Universal Resource LocatorsB1.1.5. Tracking Intellectual PropertyB1.1.5.1. Basic Business Functions For Tracking Intellectual PropertyB1.1.5.2. Information to RecordB1.1.5.2.1. The Subjects and Details of Tracking Intellectual PropertyB1.1.5.2.2. Other Types of SubjectsB1.1.5.3. Arranging the Data into TablesB1.1.5.4. Tables for Tracking Intellectual PropertyB1.1.5.4.1. The Intellectual Property TableB1.1.5.4.2. The Intellectual Property Activity TableB1.1.5.4.3. The Intellectual Property Action Required TableB1.1.5.4.4. The Owner TableB1.1.5.4.5. The Internal Contact TableB1.1.5.4.6. The Phone TableB1.1.5.4.7. Junction TablesB1.1.5.5. The Intellectual Property-Internal Contact Table (a Junction Table)B1.1.5.6. The Intellectual Property-Owner Table (a Junction Table)B1.1.5.6.1. Lookup TablesB1.1.5.7. The Intellectual Property Type Table (a Lookup Table)B1.1.5.8. The Intellectual Property Activity Type Table (a Lookup Table)B1.1.5.9. The Owner Category Table (a Lookup Table)B1.1.5.10. The State Reference Table (a Lookup Table)B1.1.5.11. Business Functions Relationships DiagramB1.2. Structural CapitalB1.2.1. Documenting Your Organization or BusinessB1.2.1.1. Basic Business Functions For Documenting Your OrganizationB1.2.1.2. Information to RecordB1.2.1.2.1. The Subjects and Details of Documenting Your OrganizationB1.2.1.2.2. Other Types of SubjectsB1.2.1.3. Arranging the Data into TablesB1.2.1.3.1. The Organization Information TableB1.2.1.3.2. The Department TableB1.2.1.3.3. The Position TableB1.2.1.3.4. The Business Function TableB1.2.1.3.5. The Step TableB1.2.1.3.6. The Task TableB1.2.1.4. Junction TablesB1.2.1.4.1. Department-Position Table (a Junction Table)B1.2.1.4.2. The Business Function–Department Table (a Junction Table)B1.2.1.4.3. The Business Function–Step Table (a Junction Table)B1.2.1.4.4. The Step-Task Table (a Junction Table)B1.2.1.4.5. Business Functions Relationship DiagramB1.3. Intellectual CapitalB1.3.1. Human CapitalB1.3.2. Enhancing the Relationship with Your EmployeesB1.3.2.1. Basic Business Functions For Employee Relationship EnhancementB1.3.2.2. Information to RecordB1.3.2.2.1. The Subjects and Details of Employee Relationship EnhancementB1.3.2.2.2. Other SubjectsB1.3.2.3. Arranging the Data into TablesB1.3.2.4. Tables in the Example Database for Employee Relationship EnhancementB1.3.2.4.1. The Employee TableB1.3.2.4.2. The Family Member TableB1.3.2.4.3. The Event TableB1.3.2.4.4. The Skill TableB1.3.2.4.5. The Phone TableB1.3.2.4.6. The E-Mail TableB1.3.2.4.7. The E-Mail Type Table (a Lookup Table)B1.3.2.4.8. The Relative Type Table (a Lookup Table)B1.3.2.4.9. The Event Type Table (a Lookup Table)B1.3.2.4.10. The State Reference Table (a Lookup Table)B1.3.2.4.11. Employee Relationship Enhancement Relationship DiagramB1.3.3. Supplier Relationship ManagementB1.3.3.1. Basic Business Functions For Supplier TrackingB1.3.3.2. Information to RecordB1.3.3.2.1. The Subjects and Details of Supplier TrackingB1.3.3.2.2. Other Types of SubjectsB1.3.3.3. Arranging the Data into TablesB1.3.3.4. Tables in the Example Database for Supplier TrackingB1.3.3.4.1. The Supplier TableB1.3.3.4.2. The Transaction TableB1.3.3.4.3. The Contact TableB1.3.3.4.4. The E-Mail TableB1.3.3.4.5. The Phone TableB1.3.3.4.6. The E-Mail Type Table (a Lookup Table)B1.3.3.4.7. The Transaction Type TableB1.3.3.4.8. Relationship Diagram for Supplier TrackingB1.4. Summary
BA. Database and Business Terms
BB. Gathering Requirements
BB.1. Dos and Don'ts of Asking QuestionsBB.2. The SpecificationBB.2.1. Determine the Purpose of the DatabaseBB.2.2. Determine the User ExpectationsBB.2.3. Determine the Number of Users and Estimated Data VolumeBB.2.4. Determine the Anticipated UsersBB.2.5. Identify Decision MakersBB.2.6. Develop a Summary of Your FindingsBB.2.7. Review, Revision, and Acceptance of Your SpecificationBB.2.8. Develop Priorities and Time Frames for Deliverables or MilestonesBB.3. Comments and Summary
BC. Data Warehousing Concepts
BC.1. Data Warehouse TablesBC.1.1. Fact TablesBC.1.2. Dimension TablesBC.2. Load ProcessBC.2.1. ExtractsBC.2.2. Summarizing the DataBC.3. Table DesignBC.3.1. The Fact and Dim TablesBC.4. Query DesignBC.5. Reporting on the Data
BD. Database Platforms
BD.1. Key Factors for Selecting the PlatformBD.2. Common PlatformsBD.2.1. ACCDB/ACEBD.2.1.1. Volume of DataBD.2.1.2. Number of Concurrent UsersBD.2.1.3. Data SecurityBD.2.1.4. Location of UsersBD.2.1.5. CostBD.2.2. Server Databases (in general)BD.2.2.1. Volume of DataBD.2.2.2. Number of Concurrent UsersBD.2.2.3. Data SecurityBD.2.2.4. Location of UsersBD.2.2.5. CostBD.2.3. Microsoft SQL ServerBD.2.3.1. Access Data Projects (ADP)BD.2.4. SharePointBD.2.4.1. Volume of DataBD.2.4.2. Number of Concurrent UsersBD.2.4.3. Data SecurityBD.2.4.4. Location of UsersBD.2.4.5. CostBD.3. Summary

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

Chapter 15. SQL Server and Other External Data Sources

Although this book is intended to help you design databases in Access, the principles of good database design are not dependent on the software you use to build your database. The same rules, processes, and relationships apply across the board and if you design first on paper, as many professionals do, then the database software does not even need to be a consideration during your design phase.

However, when you build the database, the software platform you choose can subtly affect the design. That is because considerations beyond the normalization and design process have to be taken into account. For example, how is your database going to be used? Will the company that uses it be adding a hundred new records a month or a hundred thousand? Will it be accessed by five users at once or fifty? How secure will it need to be? Such considerations are a fundamental part of choosing what software you will use to implement your database, for there is a choice to be made. That choice can influence the design and, more importantly, how you work with your data.

Server databases, and in particular Microsoft SQL Server, are very common tools for the Access developer. So, although this book is about Microsoft Access, you still have database choices available to you and in this chapter you will see what they are, how you make them, and what benefits they can provide to your organization.

Before we get into the technical details, we want to share ...

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: 9780470525746Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

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

by Teresa Hennig, Truitt Bradly, Larry Linson, Leigh Purvis, Brent Spaulding

Chapter 15. SQL Server and Other External Data Sources

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.