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
- Copyright
- About the Authors
- About the Contributing Authors and Technical Editors
- Credits
- Acknowledgments
- Foreword
- Introduction
-
I. Introduction and Basic Concepts
- 1. Overview and Road Map
- 2. Elements of a Microsoft Access Database
-
3. Relational Data Model
- 3.1. The Basics
- 3.2. A Little Deeper
-
3.3. Normalization
-
3.3.1. Objectives
- 3.3.1.1. Objective 1: Prevent anomalies during data modification
- 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.1.3. Objective 3: Make the database more informative to the users
- 3.3.1.4. Objective 4: Make the collections of relations, or tables, neutral to querying patterns
- 3.3.1.5. Objective 5: Reduce data entry steps
- 3.3.2. Normal Forms
- 3.3.3. Boyce-Codd Normal Form
- 3.3.4. When to Denormalize
-
3.3.1. Objectives
- 3.4. Summary
-
II. Dealing with Customers and Customer Data
-
4. People, Organizations, Addresses
- 4.1. How This Chapter Is Organized
- 4.2. Basic Field Information
- 4.3. People and Organization Information Processing and Storage
-
4.4. The Simple Model
- 4.4.1. The Person Table
- 4.4.2. The Email For Person Table
- 4.4.3. The Phone For Person Table
- 4.4.4. The Organization Table
- 4.4.5. The Email for Organization Table
- 4.4.6. The Phone for Organization Table
-
4.4.7. Lookup Tables
- 4.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 Tables
- 4.4.9. Putting the Tables Together
- 4.5. Differences between the Simple and Complex Models
-
4.6. The Complex Model
- 4.6.1. The Person Table
- 4.6.2. The Organization Table
- 4.6.3. The EMail Table
- 4.6.4. The Phone Table
- 4.6.5. The Address Table
- 4.6.6. Lookup Tables
-
4.6.7. Junction Tables
- 4.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 Together
- 4.7. Adding More Information about People and Organizations
- 4.8. Summary
-
5. Customer Relationship Management
- 5.1. Six Core Topics of Customer Relationship Management
- 5.2. Approach for Reviewing Core Topics
- 5.3. Customer Information
- 5.4. Basic Business Functions for People and Organizations
- 5.5. Tracking Customer Correspondence
- 5.6. Tracking Correspondence Overview
- 5.7. Tracking Sales Information for CRM
- 5.8. Sales Information for CRM Overview
- 5.9. Tracking Service and Support Information
- 5.10. Advertising and Marketing Campaigns, Targeted and General
- 5.11. Internal Information Sharing
- 5.12. Improving Process Quality
- 5.13. Summary
-
6. Marketing
- 6.1. Database Terminology
- 6.2. Marketing Campaigns
- 6.3. Questionnaires
- 6.4. Summary
- 7. Sales
-
4. People, Organizations, Addresses
-
III. Producing and Tracking the Goods & Services
-
8. Production and Manufacturing
- 8.1. Industry Terms
- 8.2. Enterprise-Level Systems
- 8.3. Purpose of the Database
- 8.4. Identifying the Requirements
- 8.5. Identifying User Expectations
- 8.6. Manufacturing Overview: Identifying the Core Subjects
-
8.7. Manufacturing Processes
- 8.7.1. Manufacturing Process Hierarchies
- 8.7.2. Identifying the Details
-
8.7.3. Creating the Tables
- 8.7.3.1. Explaining the Approach and Types of Tables
- 8.7.3.2. Potential Child Tables: Additional Details
- 8.7.3.3. Lookup Tables
- 8.7.3.4. Self-Referencing Table
- 8.7.3.5. Choosing between Alternate Designs
- 8.7.3.6. Many-to-Many Relationship and a Child Table
- 8.7.3.7. Explaining the Fields
-
8.7.3.8. Creating the Tables and Fields
- 8.7.3.8.1. Application Performance vs. Using Calculated Fields
- 8.7.3.8.2. The Process Table
- 8.7.3.8.3. The Process Manufacturer Table
- 8.7.3.8.4. The Manufacturer Table
- 8.7.3.8.5. The Process Vendor Table
- 8.7.3.8.6. The Vendor Table
- 8.7.3.8.7. The Process Type Table
- 8.7.3.8.8. The Process Backup Table
- 8.7.3.8.9. The Location Table
- 8.7.3.9. Relationships
- 8.7.3.10. Join Lines, Relationship Symbols, and Referential Integrity
- 8.7.4. Manufacturing Builds
- 8.7.5. Notifications
- 8.7.6. Countermeasures
- 8.7.7. General Notes
- 8.8. Putting It All Together
- 8.9. More Information
- 8.10. Summary
-
9. Inventory Management
- 9.1. The Approach for This Chapter
- 9.2. The Inventory Management Database
- 9.3. Inventory Overview: Identifying the Subjects
- 9.4. Approach for Reviewing the Core Subjects
-
9.5. Products
- 9.5.1. Identifying the Details
- 9.5.2. Identifying the Supporting Tables
- 9.5.3. Evaluating the Details: The Four Questions
- 9.5.4. Using Transient Fields
- 9.5.5. Using Lookup Tables
- 9.5.6. Many-to-Many Relationships and Child Tables
- 9.5.7. Design Alternatives for Quantity on Hand
- 9.5.8. Using a Transaction Table
- 9.5.9. Table Relationships for Products
- 9.5.10. Creating the Tables and Fields
- 9.5.11. Establishing the Relationships
- 9.6. Vendors
- 9.7. Locations
- 9.8. Transactions
- 9.9. Putting It All Together
- 9.10. Summary
-
10. Services
- 10.1. Database Terminology
- 10.2. Appointments Made Simple
- 10.3. Pet Boarding Example
- 10.4. Process Monitoring
- 10.5. Variations of the Theme
- 10.6. Summary
-
8. Production and Manufacturing
-
IV. Tracking and Analyzing Financial Data
-
11. Accounting Systems: Requirements and Design
- 11.1. Double-Entry Accounting
- 11.2. Chart of Accounts
- 11.3. General Ledger
- 11.4. Database Design Considerations
- 11.5. Accounting Database
- 11.6. Relationships
- 11.7. Summary
-
12. Accounting: Budgeting, Analysis, and Reporting
-
12.1. Budgets
-
12.1.1. Budget Basics
- 12.1.1.1. Creating a Budget
- 12.1.1.2. Budget Tools
- 12.1.1.3. Base Budget Data
- 12.1.1.4. Getting the Data into Excel
- 12.1.1.5. Spreading the Data Over the Budget Period
- 12.1.1.6. Getting Data to Excel for Additional Analysis
- 12.1.1.7. Budget Entries: Manual Entry or Import
- 12.1.1.8. Process Flow Control
- 12.1.2. Applying the Budget
-
12.1.1. Budget Basics
- 12.2. Analyzing Profitability
- 12.3. Summary
-
12.1. Budgets
-
11. Accounting Systems: Requirements and Design
-
V. Independent Areas
-
13. Managing Memberships
- 13.1. Membership Management Functions
- 13.2. Communication
- 13.3. Recruiting
- 13.4. Sponsorships
- 13.5. Summary
-
14. Implementing the Models
- 14.1. Not-for-Profit Organizations
- 14.2. Designing the Database
- 14.3. Components of the User Interface
- 14.4. User Interface Design
- 14.5. Products
- 14.6. Summary
- 15. SQL Server and Other External Data Sources
-
13. Managing Memberships
-
VI. Appendixes
-
A. Field Properties
-
A.1. Using Field Properties
- A.1.1. Properties of AutoNumber Fields
-
A.1.2. Properties of Text Fields
- A.1.2.1. Field Size
- A.1.2.2. Format
- A.1.2.3. Input Mask
- A.1.2.4. Caption
- A.1.2.5. Default Value
- A.1.2.6. Validation Rule and Validation Text
- A.1.2.7. Required
- A.1.2.8. Allow Zero Length
- A.1.2.9. Indexed
- A.1.2.10. Unicode Compression
- A.1.2.11. IME Mode and IME Sentence Mode
- A.1.2.12. Smart Tags
- A.1.2.13. Text Align
- A.1.3. Properties of Date/Time Fields
- A.1.4. Properties of Number Fields
- A.1.5. Properties for the Currency Data Types
- A.1.6. Properties for the Memo Data Type
- A.1.7. Unique Index Enforced for a Text Field
- A.1.8. Properties for the Yes/No Data Type
-
A.1. Using Field Properties
- B. Relationships, Joins, and Nulls
- C. Resolving Relationship Triangles
- D. Measures: Financial and Performance
- E. References and Resources
-
F. What's on the CD-ROM
- F.1. System Requirements
- F.2. Using the CD
-
F.3. What's on the CD-ROM
-
F.3.1. Access Database Designs Files
- F.3.1.1. Using the Files
-
F.3.1.2. Folders, Files, and Features
- F.3.1.2.1. Chapter 4: People, Organizations, Addresses
- F.3.1.2.2. Chapter 5: Customer Relationship Management
- F.3.1.2.3. Chapter 6: Marketing
- F.3.1.2.4. Chapter 7: Sales
- F.3.1.2.5. Chapter 8: Production and Manufacturing
- F.3.1.2.6. Chapter 9: Inventory Management
- F.3.1.2.7. Chapter 10: Services
- F.3.1.2.8. Chapter 11: Accounting Systems: Requirements and Design
- F.3.1.2.9. Chapter 12: Accounting: Budgeting, Analysis, and Reporting
- F.3.1.2.10. Chapter 13: Managing Memberships
- F.3.1.2.11. Chapter 14: Implementing the Models
- F.3.1.2.12. Chapter 15: SQL Server and Other External Data Sources
- F.3.1.2.13. Appendixes A and B: Field Properties and Relationships, Joins, and Nulls
- F.3.1.2.14. Appendix C: Resolving Relationship Triangles
- F.3.1.2.15. Appendix D: Measures: Financial and Performance
- F.3.1.2.16. Appendix E: References and Resources
- F.3.1.2.17. Chapter B1: Knowledge: Intellectual Property, Structural Capital and Intellectual Capital
- F.3.1.2.18. Appendix BC: Data Warehouse Concepts
-
F.3.1. Access Database Designs Files
- F.4. Troubleshooting
- F.5. Customer Care
-
A. Field Properties
-
VII. Bonus Material
-
BONUS CHAPTER I. Knowledge: Intellectual Property, Structural Capital, and Intellectual Capital
-
B1.1. Intellectual Property
- B1.1.1. Copyright
- B1.1.2. Patent
- B1.1.3. Trade Secrets
- B1.1.4. Protecting Names
-
B1.1.5. Tracking Intellectual Property
- B1.1.5.1. Basic Business Functions For Tracking Intellectual Property
- B1.1.5.2. Information to Record
- B1.1.5.3. Arranging the Data into Tables
- B1.1.5.4. Tables for Tracking Intellectual Property
- B1.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.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 Diagram
- B1.2. Structural Capital
-
B1.3. Intellectual Capital
- B1.3.1. Human Capital
-
B1.3.2. Enhancing the Relationship with Your Employees
- B1.3.2.1. Basic Business Functions For Employee Relationship Enhancement
- B1.3.2.2. Information to Record
- B1.3.2.3. Arranging the Data into Tables
-
B1.3.2.4. Tables in the Example Database for Employee Relationship Enhancement
- B1.3.2.4.1. The Employee Table
- B1.3.2.4.2. The Family Member Table
- B1.3.2.4.3. The Event Table
- B1.3.2.4.4. The Skill Table
- B1.3.2.4.5. The Phone Table
- B1.3.2.4.6. The E-Mail Table
- B1.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 Diagram
- B1.3.3. Supplier Relationship Management
- B1.4. Summary
-
B1.1. Intellectual Property
- BA. Database and Business Terms
-
BB. Gathering Requirements
- BB.1. Dos and Don'ts of Asking Questions
-
BB.2. The Specification
- BB.2.1. Determine the Purpose of the Database
- BB.2.2. Determine the User Expectations
- BB.2.3. Determine the Number of Users and Estimated Data Volume
- BB.2.4. Determine the Anticipated Users
- BB.2.5. Identify Decision Makers
- BB.2.6. Develop a Summary of Your Findings
- BB.2.7. Review, Revision, and Acceptance of Your Specification
- BB.2.8. Develop Priorities and Time Frames for Deliverables or Milestones
- BB.3. Comments and Summary
- BC. Data Warehousing Concepts
- BD. Database Platforms
-
BONUS CHAPTER I. Knowledge: Intellectual Property, Structural Capital, and Intellectual Capital
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
You might also like
book
Databases for Small Business: Essentials of Database Management, Data Analysis,and Staff Training for Entrepreneurs and Professionals
This book covers the practical aspects of database design, data cleansing, data analysis, and data protection …
book
Microsoft Dynamics NAV 2016 Financial Management - Second Edition
Master the world of financial management with Microsoft Dynamics NAV 2016 About This Book Familiarize yourself …
book
Power Verbs for Managers and Executives: Hundreds of Verbs and Phrases to Communicate More Dynamically and Effectively
Electrifyall your leadership communications... at every level, in every encounter The right verbs • help you …
book
Introductory Accounting
Accurate accounting is the key to a successful business. But when you're dealing with assets and …