29,99 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Fachliteratur
- Sprache: Englisch
This book is a practical introduction to building and managing Access desktop databases. Instead of merely explaining Microsoft Access, it helps you complete tasks related to creating, using, and managing a database. The hands-on approach focuses on creating a traditional Access desktop database and emphasizes splitting the database for optimized multi-user access. The primary project, the Employee Training Database (ETD), offers practical, real-world experience.
The book guides you through database creation and management. It starts with planning and designing a database, followed by creating and setting up Access tables. It then covers primary keys, indexes, and table relationships, and populating tables with data. The middle sections include working with queries and designing and customizing forms.
The latter part focuses on designing and using reports and customizing them. It concludes with compacting, splitting, and securing the database for optimized performance and security. These concepts enable efficient database management and enhance multi-user collaboration. This book transitions you from a novice to a proficient user, equipped with practical skills and comprehensive knowledge. Companion files with hands-on examples enrich your learning.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 534
Veröffentlichungsjahr: 2024
Ähnliche
Access® 365Project Book
LICENSE, DISCLAIMER OF LIABILITY, AND LIMITED WARRANTY
By purchasing or using this book (the “Work”), you agree that this license grants permission to use the contents contained herein, but does not give you the right of ownership to any of the textual content in the book or ownership to any of the information or products contained in it. This license does not permit uploading of the Work onto the Internet or on a network (of any kind) without the written consent ofthe Publisher. Duplication or dissemination of any text, code, simulations, images, etc. contained herein is limited to and subject to licensing terms for the respective products, and permission must be obtained from the Publisher or the owner of the content, etc., in order to reproduce or network any portion of the textual material (in any media) that is contained in the Work.
Mercury Learning and Information (“MLI” or “the Publisher”) and anyone involved in the creation, writing, or production of the companion disc, accompanying algorithms, code, or computer programs (“the software”), and any accompanying Web site or software of the Work, cannot and do not warrant the performance or results that might be obtained by using the contents of the Work. The author, developers, and the Publisher have used their best efforts to insure the accuracy and functionality of the textual material and/or programs contained in this package; we, however, make no warranty of any kind, express or implied, regarding the performance of these contents or programs. The Work is sold “as is” without warranty (except for defective materials used in manufacturing the book or due to faulty workmanship).
The author, developers, and the publisher of any accompanying content, and anyone involved in the composition, production, and manufacturing of this work will not be liable for damages of any kind arising out of the use of (or the inability to use) the algorithms, source code, computer programs, or textual material contained in this publication. This includes, but is not limited to, loss of revenue or profit, or other incidental, physical, or consequential damages arising out of the use of this Work.
The sole remedy in the event of a claim of any kind is expressly limited to replacement of the book, and only at the discretion of the Publisher. The use of “implied warranty” and certain “exclusions” vary from state to state, and might not apply to the purchaser of this product.
The companion files on the disc are also available for downloading by writing to the publisher at [email protected].
Access® 365Project Book
Hands-On Database Creation
Julitta Korol
Copyright ©2024 by Mercury Learning and Information. An Imprint of DeGruyter Inc. All rights reserved.
This publication, portions of it, or any accompanying software may not be reproduced in any way, stored in a retrieval system of any type, or transmitted by any means, media, electronic display or mechanical display, including, but not limited to, photocopy, recording, Internet postings, or scanning, without prior permission in writing from the publisher.
Publisher: David PallaiMercury Learning and Information 121 High Street, 3rd Floor Boston, MA [email protected] 800-232-0223
Julitta Korol. Access® 365 Project Book: Hands-On Database Creation ISBN: 978-1-68392-094-6
The publisher recognizes and respects all marks used by companies, manufacturers, and developers as a means to distinguish their products. All brand names and product names mentioned in this book are trademarks or service marks of their respective companies. Any omission or misuse (of any kind) of service marks or trademarks, etc. is not an attempt to infringe on the property of others.
Library of Congress Control Number: 2023945502
232425321 Printed on acid-free paper in the United States of America.
Our titles are available for adoption, license, or bulk purchase by institutions, corporations, etc.For additional information, please contact the Customer Service Dept. at 800-232-0223(toll free).
All of our titles are available in digital format at academiccourseware.com and other digital vendors. Companion files for this title are available by contacting [email protected] sole obligation of Mercury Learning and Information to the purchaser is to replace the disc, based on defective materials or faulty workmanship, but not based on the operation or functionality of the product.
Contents
Introduction
Acknowledgments
PART I PLAN, DESIGN, AND CREATE A DATABASE
Chapter 1 Planning and Designing a Database
About Access Databases
About Access Objects
The Employee Training Database (ETD)
Planning Your Database
Some Database Design Questions to Ask
Business Rules for the Database
Creating a Database Diagram
Action Item 1.1
Designing the Database
Guidelines for Creating Tables
Understanding Table Relationships
About Primary Keys
About Foreign Keys and Null Values
What Is Database Normalization?
Data Normalization Rules
Benefits of Data Normalization
What Is Database Denormalization?
Action Item 1.2
Summary
Chapter 2 Creating a Desktop Database
About Access Database File Formats
Creating an Access Desktop Database
Completing the Initial Table Structure
Naming Conventions in Access
Naming a Field
What Is the Data Type?
Saving the Table
Working in the Table Design View
Modifying a Field Size
Specifying the Field Format and Input Mask
Specifying the Default Value for a Field
Specifying Validation Rule and Validation Text
Changing a Table Design
Modifying the Field Data Type
Changing a Field Name
Action Item 2.1
Changing a Field Location
Action Item 2.2
Inserting a New Field
Action Item 2.3
Deleting a Field
Action Item 2.4
Entering Data in a Table
Copy, Export, and Remove Tables
Action Item 2.5
Printing a Table Design
Summary
PART II CREATE AND WORK WITH DATABASE TABLES
Chapter 3 Creating Access Tables
Creating a Table in Design View
Action Item 3.1
Creating a Table Using Application Parts
Creating a Table Using a Make Table Query
Creating a Table With a Data Definition Query
Creating Remaining Tables in the ETD Database
Action Item 3.2
Summary
Chapter 4 Setting up Primary Keys, Indexes, and Table Relationships
Relating Tables
Action Item 4.1
Action Item 4.2
Action Item 4.3
Creating Many-to-Many Relationships
Action Item 4.4
Establishing Table Relationships Using Data Definition Language
Displaying the System Objects
Action Item 4.5
Creating the Relationship Report
Action Item 4.6
Indexing Fields
Summary
Chapter 5 Populating Access Tables With Data
Importing Data Into an Access Table
Importing Versus Linking
Importing Data From a Text File
Importing Data From Excel Worksheets
Action Item 5.1
Importing Data From XML Files
Importing Data From a Tab-Delimited Text File
About Datasheets and Subdatasheets
Action Item 5.2
Action Item 5.3
Importing Data From Another Access Database
Reusing Saved Imports
How to Deal With Unsuccessful Imports
Importing Worksheet Ranges
Action Item 5.4
Action Item 5.5
Action Item 5.6
Summary
PART III CREATE AND WORK WITH QUERIES
Chapter 6 Creating and Working With Select Queries
Action Item 6.1
Creating a Query
Filtering a Query
Creating a Calculated Field in a Query
Using the Expression Builder
Understanding Query Totals and Grouping
Creating a Parameter Query
Using Multiple Parameters
Action Item 6.2
Creating Crosstab Queries
Developing SQL Skills using Select Queries
Action Item 6.3
Predicates in SQL Statements
Using the DISTINCT Predicate
Using the DISTINCTROW Predicate
Using the TOP Predicate
Understanding and Using Subqueries
SQL Keywords used in Subqueries
The IN and NOT Operators
The EXISTS Predicate
The ANY, SOME, and ALL Predicates
Understanding Null Values in Data
Finding Unmatched Data
Action Item 6.4
Finding Duplicate Data
Action Item 6.5
Creating and Using a Union Query
Action Item 6.6
Speeding Up Your Select Queries
Summary
Chapter 7 Performing Database Operations With Action Queries
Using Make Table Queries
Action Item 7.1
Using Append Queries
Using Criteria With Append Queries
Action Item 7.2
Action Item 7.3
Using Update Queries
Action Item 7.4
Action Item 7.5
Using Delete Queries
Other Types of Queries in Access
Summary
PART IV CREATE AND WORK WITH FORMS
Chapter 8 Designing and Using Forms
About Access Forms
Designing Forms
The Form Tool
The Form Wizard
The Form Design and a Blank Form
The Multiple Items Forms
The Split Form
The Modal Dialog Forms
Modal Forms versus Popup Forms
The Navigation Forms
Navigation Forms vs Switchboard Forms
Understanding Form Views
Understanding Form Properties
Format Properties
Data Properties
Event Properties
Other Properties
Creating and Modifying a Form
Understanding Form Controls
Selecting and Deselecting Controls
Resizing a Control
Moving and Aligning Controls
Adjusting Spacing Between Controls
Copying Controls
Action Item 8.1
Working With Controls and Their Properties
Action Item 8.2
Summary
Chapter 9 Form Customization
Applying Special Effects to Form Controls
Changing a Control Type
Action Item 9.1
Combo Box and List Box: Synchronization
Action Item 9.2
Using Functions in Form Controls
Summary
PART V CREATE AND WORK WITH REPORTS
Chapter 10 Designing and Using Reports
Designing Reports
The Report Tool
The Report Design and a Blank Report
The Report Wizard
The Labels Wizard
The Main Parts of a Report
Modifying the Report
Action Item 10.1
Adding Page Breaks
Using Expressions in the Group Header
Understanding Circular References
Summary
Chapter 11 Report Customization
Creating a Record Source for the Report
Defining the Grouping Structure in a Report
Working With the Report Header
Preparing the Page Header
Preparing the CourseName Header
Preparing the SessionID Header
Preparing the Detail Section
Using an Immediate IF Function
Preparing the CourseName Footer
Preparing the Page Footer and Report Footer
Adding Page Breaks
Events in Access Reports
Format Event (Section Formatting)
Publishing the Report
Action Item 11.1
Creating a Report Selection Form
The Option Group Control
Macros: A Brief Introduction
Summary
PART VI COMPACT, SPLIT AND SECURE THE DATABASE
Chapter 12 Compacting, Splitting, and Securing an Access Database
Compacting and Repairing Your Database
Database Compact and Repair: Precautions
Action Item 12.1
Using the Compact on Close Option
Backing Up and Restoring Your Database
Using Access to Back Up a Database
Restoring a Database From a Backup Copy
Splitting Your Database
Understanding Linked Tables
Using the Linked Table Manager
Reviewing the Back-End Database
Securing an Access ACCDB Database
Encrypting a Database With a Password
Decrypting Database
User-Level Security in Access Databases
Migration Process: From Access to SQL Server
Summary
Appendix: Microsoft Access File Formats
Index
Introduction
Microsoft Access has been the top database management solution for individuals and businesses for over two decades. Many of today’s database systems started as small Access databases or were ported from Microsoft Excel spreadsheets. With Microsoft’s commitment to continuing the development of the product, Access keeps getting better with each new update. Across all types and sizes of organizations, people continue to utilize Access to collect, track, and analyze data from multiple sources and build useful forms and reports. Non-developers are creating various Access databases because with Access they can quickly move forward and get their job done without requiring much support from the IT (Information Technology) department.
Access 365 Project Book: Hands-On Database Creation is a hands-on database project that guides you through the process of building a traditional Access desktop database that is later split into two files: a back-end database that contains tables with data, and a front-end database that contains forms, reports, queries, macros, and code modules. By separating the data from the rest of the database, the Access database can be easily shared by multiple users over a network. When you build a so-called “split database,” you can provide a faster experience to your database users, and later the database can be migrated to another database system with fewer issues and fewer objects that need to be redone.
In this book, instead of working through some random examples, you will walk through a series of steps that will teach you the essential skills you need to acquire to develop a desktop database from beginning to end. The focus of this book is the creation of the Employee Training Database (ETD) database. If you are looking for a “tell it all” Access reference, this is not a book for you. This book is not meant to provide a complete guide to all Access features because no project requires the use of all the capabilities of a particular software. There are numerous books on the market that will give you an in-depth look into Microsoft Access, but they do not walk you through a complete project of building an Access database from beginning to end.
This book does not explain all the details of working with Access. Instead, it focuses on topics that are essential in the process of building and enhancing your database. The required Access features and capabilities are introduced exactly when you need them to complete a particular task in your database project. If you are looking for a book you can work with for full engagement and to get a sense of accomplishment when completed, this is the right place to start.
The book’s approach is to learn by doing. For most people new to database topics, there’s no better way than step by step. Simply turn on the computer, open this book, launch Access, and work through all the guided Hands-On exercises. But before you get started, allow me to give you a short overview of the things you’ll be learning as you progress through this book.
This book is divided into the following six parts:
I. Plan, Design, and Create a Database
II. Create and Work With Database Tables
III. Create and Work With Queries
IV. Create and Work With Forms
V. Create and Work With Reports
VI. Compact, Split, and Secure the Database
Each part of this book builds on previous parts. Each chapter will draw from material covered in previous chapters and introduces you to new techniques that are necessary for completing each new phase of the database project.
Part I focuses on introducing you to some database concepts. Access databases are created by using Access objects. These are tables, queries, forms, reports, macros, and modules. You must understand these and numerous other terms before you can create meaningful databases for yourself and others. In this part of the book, we determine the purpose and scope of our database project and examine the database planning, designing, and data normalization processes. We also begin setting up the database we’ll be working with throughout this book project.
Chapter 1: Planning and Designing a Database
In this chapter, you learn about Access and its various database objects. You will find out details about Access tables, the importance of primary keys, and the role of foreign keys and table relationships. This chapter explains how database planning phase plays a vital part in building and designing a successful database, and how the process known as normalization can ensure the correct flow and storage of data.
Chapter 2: Creating a Desktop Database
You start this chapter by creating a blank Access database. You learn about Access database files and naming conventions. You also learn how to design your first table and enter, modify, delete, and save your data. You get to know various properties that allow you to control how the data is formatted and displayed. You find out about default field values and learn how to control user’s input with validation rules and validation text. You get comfortable working in Table Design and Datasheet view. You also learn how to copy, export, and delete a table. You finish this chapter by generating and printing your first table structure.
Part II introduces you to several techniques of implementing database tables. As tables consist of fields, you will learn about data types that the fields can hold to correctly store your data. You also learn how to set up primary keys, indexes, and create relationships between your tables, and explore several methods of filling in Access tables with data from various file formats.
Chapter 3: Creating Access Tables
In this chapter, you learn how to use the Tables group on the Ribbon’s Create tab to create Access tables. Any other object you create later in this database will depend on these tables. As tables consist of fields, you learn about various data types that the fields can hold to correctly store your data. The available data types are discussed and implemented throughout the book.
Chapter 4: Setting Up Primary Keys, Indexes, and Table Relationships
In this chapter, you learn how to add primary keys and indexes to your database tables using Access built-in tools. You also learn how to create relationships between your tables using both the Edit Relationship dialog box and the Data Definition Language SQL commands.
Chapter 5: Populating Access Tables With Data
This chapter deals with several ways of filling in Access tables. Here you learn how to import data from spreadsheets, text and XML files, and other Access databases.
Part III teaches you essential database operations such as adding, updating, and deleting records. You learn how to use the Query Design view to create and run various types of database queries that will help you analyze data and locate records based on supplied criteria. You also learn how to calculate, group, and summarize data.
Chapter 6: Creating and Working With Select Queries
Database queries are essential in all sorts of database operations. You can use Access wizards or Query Design view to create your queries. Queries are questions you pose to the database to get you a set of data you need or to perform certain operations on the data. In this chapter, you learn various methods of creating basic Select queries that retrieve data from one or more tables with or without criteria, sorted in ascending or descending order. You also learn how to use various expressions, aliases, and aggregate functions. You practice creating more advanced Select queries that can use parameters to set a query’s criteria at runtime and crosstab queries that summarize data in a familiar row/column format. You will spend quite a bit of time in this chapter learning the SQL language behind queries.
Chapter 7: Performing Database Operations With Action Queries
In this chapter, you are introduced to other types of queries that allow you to perform database operations such as updating, deleting, and appending records to our database tables. You learn the functionality of each type of action query starting from making a new table, appending new records to an existing table, modifying values in specific fields in a table, and deleting data from a table.
Part IV focuses on creating and working with Access forms. You learn about designing various types of forms that can be used for data input and viewing.
Chapter 8: Designing and Using Forms
You start this chapter by creating a form using the Form Wizard. Next, you learn how to start from a blank form. You work in Design or Layout view and learn how to use various form properties to set up your forms. Various form controls (text boxes, combo boxes, buttons, hyperlinks, etc.) are discussed and used in this chapter. You also learn about the Visual Basic Editor window and use various built-in functions to create complex expressions.
Chapter 9: Form Customization
In this chapter, you spent quite a bit of time on form design and learn how you can customize a form by adding advanced controls such as a combo box and a list box, and how these controls can interact with each other. You work here with various control properties that will give your form a more polished look. You learn how to use events to ensure that a control refreshes whenever the value in another control changes. You are also taught how you can use domain aggregate functions in your form controls, specifically DLookup() and DCount(), to display data from other tables based on given criteria.
Part V guides you in the process of creating reports that provide your data for analysis, presentation, and printing.
Chapter 10: Designing and Using Reports
In this chapter, you learn about different options for creating reports. You are introduced to report wizards that help you create various reports. You also learn about report and report control properties that can make your reports more visually appealing. You find out about various report parts and learn how to add page breaks and use expressions in reports.
Chapter 11: Report Customization
In this chapter, you work with numerous report and control properties and learn about report sections and their customization. You create a report completely from scratch and go through various stages of the report design process step by step. In this chapter you also discover how forms can provide a handy user interface for your reports. You learn how to convert the Wizard-generated embedded macro into Visual Basic and learn how to modify it to extend it to other form controls quickly. You learn new VBA keywords and constructs like Select Case. You advance your knowledge of building more complex expressions by using functions such as Intermediate IIF and Switch.
Part VI focuses on making your desktop database capable of running well in a network environment.
Chapter 12: Compacting, Splitting, and Securing the Database
In this chapter, you are introduced to topics that allow you to run your Access database in a multi-user environment. You learn the steps that you need to follow in the process of splitting an Access database into a front-end file and back-end file and find out how to work with the Linked Table Manager to manage the linked tables in the front-end database. You also learn how to use the Compact and Repair Database, Database Backup, and Encrypt with Password features that are built-into Access. You find out why you cannot use the User-Level security in the ACCDB databases and what resources you can use when you decide to migrate your Access database back-end to SQL Server.
Appendix: Microsoft Access File Formats
This appendix provides more information about various Access file formats that are available in various versions of MS Access.
SUMMARY OF FEATURES
Understand the concepts of database planning, information gathering, and database normalization.
Discover various methods of building database tables and establishing table relationships.
Learn about database keys, indexes, and NULL values.
Populate tables by bringing data from different file formats (Text, XML, Excel, and Access).
Create Select queries and learn the SQL language behind the queries.
Learn how to perform important database tasks such as inserting, appending, and deleting data.
Create simple and complex expressions that use various built-in functions that help you retrieve data and make decisions.
Learn various methods of creating forms and making required changes by controlling form and form controls’ properties, and use domain aggregate functions in your form controls: specifically DLookup() and DCount().
Create a custom data entry form and learn how to synchronize combo box and list box controls.
Learn how to work with report wizards to generate quick, out of the box reports.
Create a custom multipart report and use various report and report section settings to get the desired look and feel.
Learn about report section events and how to convert embedded macros into Visual Basic code and then modify the code to suit your needs.
Customize the behavior of forms and reports by working with events in Form and Report code modules.
Learn how to use the Compact and Repair and the Backup features to maintain your database.
Split an Access database and secure it for a multi-user access.
WHO IS THIS BOOK FOR?
This book is a practical introduction to building and managing Access desktop databases. Instead of simply explaining the inner workings of Microsoft Access, which you will find in many reference books, this book makes sure that you, the reader, can complete with ease all the required tasks related to the creation, use, and management of an Access database. The focus of this book, learn by doing, will give you the skills you need to feel comfortable working with Access databases.
For success with this book, the assumption is that you have basic familiarity with Access user interface. This book also touches upon several programming techniques via the Access built-in language, Visual Basic for Applications (aka VBA). For VBA basics, you may want to work through the examples presented in my recent book titled Access 2021 Programming Pocket Primer (Mercury Learning and Information, 2022).
To follow along with the database project in this book, you will need a standalone version of Microsoft Office Professional 2021 or Microsoft 365 edition. If you don’t have access to this software, Microsoft offers a free trial.
COMPANION FILES
The example files for all the hands-on activities in this book are available in the companion files included with this book. Replacement files may be downloaded by contacting the publisher at [email protected]. Digital versions of this title are available at academiccourseware.com and other digital vendors.
Acknowledgments
I would like to express my sincere gratitude to everyone who helped me in the process of writing this book. Without their support and guidance, this book would not have been possible.
First and foremost, I would like to thank my publisher, David Pallai, for believing in my vision for this Access 365 Project Book, and giving me the opportunity to share it with the world. Many thanks to Eugene Zheleznov, my dear colleague, friend and mentor, for his support, advice and encouragement. Eugene offered his expertise and time in assisting me in the database design process and provided many useful suggestions to make this book more helpful to you. I am especially grateful to my project manager, Jennifer Blaney, who was always there to answer my questions, provide feedback, and keep me on track. She is a true professional and a pleasure to work with.
I would also like to thank the IBI Copy Editing team member who meticulously reviewed every word and sentence in this book correcting my errors and improving my style and clarity.
Finally, I would like to thank my compositor, SwaRadha Typesetting, who transformed my manuscript into a beautiful and easy to follow book. They did an amazing job with the layout, design, and typography. They also handled the technical aspects of the production with ease and efficiency.
I hope that you will enjoy working and learning from this book as much as I enjoyed writing it. Thank you for your interest and support.
Julitta KorolOctober 2023
Plan, Design, and Create a Database
P a r t I
Part I introduces you to database concepts. Access databases are created by using Access objects: tables, queries, forms, reports, macros, and modules. You must understand these and numerous other terms before you can create meaningful databases for yourself and others. In this part of the book, we determine the purpose and scope of our database project and examine the database planning, designing and data normalization processes. We also begin setting up the database you’ll be working with throughout this book project.
Chapter 1 Planning and Designing a Database
Chapter 2 Creating a Desktop Database
Planning andDesigning aDatabase
C h a p t e r 1
In general terms, a database is a collection of information concerning a certain topic and is composed of one or more tables. While it is considerably easy to get started with a Word document or an Excel spreadsheet, creating a database requires a lot of preliminary thinking and information gathering. Even if you are already familiar with the concept of database tables, please do not skip this chapter. The initial steps which you must take prior to designing your database will determine whether your efforts will meet with a success or failure. This chapter gives you some background knowledge about Access, including the planning and design tasks that you need to complete outside of Access before you are ready to mold the information you have gathered into a working and user-friendly database application.
ABOUT ACCESS DATABASES
Microsoft Access stores data in related tables, hence it’s called a relational database. Most databases include many related tables. By using multiple tables, you can decrease the input of redundant data and simplify data entry. We will expand on this concept known as normalization as we progress through this chapter.
In a relational database data in one table is related to data in another table by a common field. A classic example are Customers and Orders tables, where the Customers table stores information about customers such as their ID, Name, Address, Phone and so on, and the Orders table contains information about each order placed by a customer. Each row (record) in the Orders table must include a column (field) that identifies the customer who placed the order. The customer information is stored only once in the Customers table and by including the CustomerID field in the Orders table we can easily retrieve Customer data when needed without entering it again. Instead of storing customer data together with the order data like you would do in a spreadsheet, in a relational database you will split data into multiple tables. This approach will greatly simplify data entry and reporting and prevent duplication of data. Figure 1.1 illustrates table relationships using the sample Northwind.mdb file shipped with earlier versions of Microsoft Access.
Figure 1.1.
Relating Access tables by a common field in a sample Northwind database.
Each box in the above diagram represents a single Access table. The data in each table is stored in fields A field is like a column in an Excel spreadsheet. A field can hold a specific type of data such as text, number, logical value (true or false) and so on. Some fields are designated as primary key fields. These fields uniquely identify each record in a table. In database terms, we will refer to rows as records. In the above relationship diagram, the CustomerID field in the Customers table has a primary key symbol next to it. Each CustomerID is unique; there are no two customers with the same ID. In the related Orders table, the CustomerID key is known as a foreign key. Primary and foreign keys are used for joining tables, so that you can extract related data. A customer can have many orders. Each order has one customer. This type of a relationship is known as one-to-many and is depicted by 1 and the infinity symbol at the end of the joining line as you can observe in Figure 1.1. You will learn the details of fields and relationship types as you begin the process of designing your database.
ABOUT ACCESS OBJECTS
Microsoft Access databases are built, maintained, and automated using Access objects. An object is a thing that is specifically designed to enable a specific feature that you will need to create and work with in your database. The six main Access objects and their short descriptions are listed in Table 1.1.
Microsoft Access Objects
You will get many chances of working with Access objects listed in Table 1.1 as you design, work, automate, and maintain your database. Just by looking at the objects listed in Table 1.1, it is easy to notice that Microsoft Access provides a complete programming environment that, in addition to creation of database tables, allows you to build your data entry screens and reports, and includes multiple ways of accessing and automating your database via queries, macros and programming code written in modules.
THE EMPLOYEE TRAINING DATABASE (ETD)
The database you will be designing, creating, and working with in this book is called the Employee Training Database (ETD). This database will aim at addressing the needs of any company or organization that is looking for a simple and reliable system of scheduling and keeping track of employee training. The training management is poorly executed in many companies. Frequently, the employee training data is stored in spreadsheets, on various scraps of paper, or in other systems that were not designed to handle the training needs. As employee training is ongoing, it makes sense to spend time and design a dedicated database system that will eliminate wasted time, resources and money lost in attempting to keep track of this process. Think of how much frustration can be avoided daily if the employee training data has its own system that is easy to work with and maintain.
The ETD database will be a welcome solution to someone like Frank, who was seeking a nice customizable template to track employee training but found none. When he tried to create one himself in Microsoft Access, he realized that he was lost in the myriad of features that Access offers out of the box. Which features should he use first? How will he connect them into a meaningful system of storing and presenting data? Will it be usable when he’s done?
By following easy, step-by-step instructions in this book, you can avoid many of the pitfalls that Frank had encountered and learn how to go about the process of building a simple and maintainable Training Management System.
PLANNING YOUR DATABASE
A primary purpose of a database is effective data storage and easy information retrieval. The database planning phase plays a crucial role in ensuring that the database you design will serve its intended purpose. Without a solid plan, your database project may prove to be nothing else but a costly waste of time and other resources that were provided. So, where do you start? The steps you need to take will depend on the size of the database project. Complex databases will require a great number of elements to consider and thus, will take longer to plan. However, no matter how big your project is, it is a good idea to start by setting up a meeting with the stakeholders—people that are most interested in bringing the database project to its successful completion. These individuals should be your starting point of reference. Find out what is expected of the new system, how it will benefit its users, and what is the expected delivery time.
Some Database Design Questions to Ask
Is there currently any other system in use that serves a similar need?
If some of the information you need is already available in another system (documents, spreadsheets, or other databases), find out who you can contact to obtain samples of the data and discuss how that data is being used. It is beneficial to get the names and types of columns (fields) used, as well as any diagrams presenting the flow of data. Ask if you can link directly to other databases to provide data lookups in your database forms.
Make a list of all the resources you were able to identify; state their format (i.e., text file, Excel spreadsheet, SQL, MySQL, or other database table, another Access database, diagram, JSON, or XML/HTML file). Don’t forget to include information that may have been shared with you via email, text, or Zoom.
List the names of identified fields/columns.
Make a list of proposed names for the database.
Make a list of proposed tables.
Ask for examples of the use cases for the database.
Identify groups of users who will use the database on a regular basis. Ask what type of information they will need to store and retrieve. How would they like their data entry forms to look like? And what about the reports? How many reports they need and what kind of details they want to include? Will the reports be run manually or must be scheduled to run automatically at specific time intervals?
Ask various people to prepare mock-up forms and reports on paper or in a computer program they are comfortable with. The idea is to get as much end-user input as possible in this initial planning stage. Be sure to take a careful note of the specific wants of those individuals so you can include their requests early in the database design.
Schedule regular follow-up meetings with stakeholders and end-users to keep them informed about your progress and listen to their feedback. Your goal is to have a well-planned and useful database, and that requires a well-coordinated team effort.
Business Rules for the Database
It is important to determine what kind of logic you will need to implement in the database. Business rules are policies, procedures, or standards that a company has adopted which need to be enforced by the database system you design.
The following are some examples of business rules you could implement in your database:
An employee can only register for one course in one quarter.
An employee cannot take the same course more than once in a year.
An employee must fill out a feedback form after each course.
A course cannot be given in the same room in the same period while another course is taking place.
Each course may have one or more prerequisite courses.
Business rules restrict the allowable data values for a database object in one way or another. They are often implemented via constraints which are rules that are placed on a database object such as a table or a column. Later in this book you will learn about types of constraints, including referential constraints, that enforce relationships between database tables; NOT NULLconstraints, that specify whether null values are permitted for the field; and CHECK constraints, that use logical statements to validate whether a value is permitted in a field.
Creating a Database Diagram
After you have gathered substantial amount of information about the requirements for your database, decide what tables you will need and what data will go into each table. Because your tables will be in various relationships with one another, you will need to give a lot of thought to the primary key fields that uniquely identify each record in a table. When you perform a database search by this key, you should only get one record in return. Sometimes one key field is not enough to ensure the uniqueness of the data. You may need to combine two or more fields to meet this condition. We will talk about the selection of keys in Chapter 2.
It is important to make a preliminary diagram of a database showing the tables and listing the fields that they should have. Highlight the proposed relationships between the tables by drawing the lines between them. This diagram will be very helpful to you in the design phase.
Figure 1.2 depicts a table relationship diagram for the ETD database you will be building in this book.
Figure 1.2.
Table Relationships in the Employee Training Database (ETD)
Action Item 1.1
The Companion files include an Excel workbook named etd_Fields.xlsx.You will find there the Fields worksheet with a listing of the fields as well as field data types of the data collected during the planning stage. Add a new worksheet to this workbook for each table depicted in Figure 1.2 and assign each field from the etd_Fields worksheet to an appropriate table. When done, check your work against the etd_Tables.xlsx file in the Companion files.
DESIGNING THE DATABASE
The first law of database design is to accurately define the requirements. You must conduct a thorough analysis of what you want to do and how you want to do it. In our Employee Training database all employees can sign up for offered training sessions but only some employees should be allowed to administer the database. These admin users in addition to reading the data; could insert, update, and delete data. In database terms, they can perform Create, Read, Update, Delete (CRUD) operations. The database should be able to keep track of all the training offerings and employees who enroll in them. It should contain forms that allow for easy data entry as well as reports that provide detailed summaries of completed training for the company management needs.
With the database planning phase completed, you are now ready to call on Microsoft Access to help you implement your database design. Access offers many built-in tools, known as Wizards, to make this process as much enjoyable and efficient as possible. The design process you are embarking on will continue throughout the book as you create tables, define relationships between them, set up primary and foreign keys and indexes, include validation and business rules, design your forms and reports, create statements to retrieve, insert, update, and delete data. You will enhance your design skills by learning how to automate certain database tasks via programming code. The skillset required to create even a simple database is enormous and the only way to get the hang of it is simply by doing. As you are working in Access it will be easier to find out any mistakes made in your initial mockup design. Corrections are also easier to make when the only user is the database designer. So, where do you start?
You cannot have a database unless you’ve got at least one table, so let’s create one.
Guidelines for Creating Tables
Decide which table you are going to create first. Look at the diagram shown earlier and pick a table that seems central to your database design. As the ETD database will track employee training, it is wise to start by creating a table for employees. In your real-life database project, you may decide to create a link to the Employees table that already exists in your Human Resources database instead of creating a new table. However, to keep this database entirely standalone, we’ll build our own Employees table from scratch. In later chapters you will fill in this table with the existing HR data.
When creating tables pull out the spreadsheet from Action Item 1.1 earlier in this chapter and look over the fields that were identified for the database. Are all those fields needed? Cross out the ones that you are not planning to include in your forms, reports, or calculations. Make sure that each field you keep goes into one table. Each piece of information in a database should be kept only in one place so that the process of updating data is quick and efficient. The data that may be needed for calculations can be stored in a database field, but you must avoid storing any data that can be calculated (or derived) from existing fields. Take for example the age of an employee. If you are already storing the birth date, skip the age field. Access has many built-in math functions that can get you the answers you need for your forms and reports, or any decision-making process you need to implement.
Most databases rely on so called lookup tables. These tables usually contain a code and a description. For example, States table can have two fields: one with a state code and the other with the full name of the state. Lookup tables are very helpful as they prevent errors that may result from erroneous data entry in multiple places. The code description can be easily looked up simply by storing only a code field in the tables you need.
Understanding Table Relationships
When you need to display data from more than one table you will need to understand how to connect tables with one another so data can be easily retrieved. This process called joining is essential to understanding the relational databases. There are three basic types of relationships between tables:
one-to-one
one-to-many
many-to-many
The one-to-one relationship indicates that for every record in the first table only one record exists in the second table. Notice that in our ETD database (see the diagram in Figure 1.2), there is no one-to-one relationship. These types of relationships are very rare. Often time there are used in situations when you simply don’t want to store certain information in the same table for security reasons. For example, the knowledge of some personal data may be confidential and restricted to a small group of authorized individuals, so keeping that data in a separate table would absolutely make sense. Take the Employees table. To keep the Social Security number of each employee restricted to only authorized persons, you could create an ESSN table that is joined to the Employees table by the EmployeeID field. The ESSN table would only have two fields: EmployeeID and SSN. Only authorized people should be able to query the ESSN table. The relationship between the Employees table and the ESSN table would be a one-to-one.
While the one-to-one relationships are rare, the one-to-many relationships are the most common type of the relationships in database systems. In our ETD database there are several such relationships. For example, the Employees table is related to the Registration table as a one-to-many relationship. This tells Access that for each Employee in the Employee table there can be many registration entries in the Registration table as any employee can register in multiple training sessions. In other words, many registration records can be associated with a single employee.
The third type of a relationship, many-to-many, serves all those situations where each record in both tables can be related to none, one, or multiple records in the other table. This type of relationship requires a separate join table. The join table, also called a junction table or an intermediate table, will have at least two fields: a primary key field from one table and a primary key field from another table. To better visualize this scenario, look at the OrderDetails table depicted in Figure 1.1. Notice that the OrderDetails table has both the OrderID field from the Orders table and ProductID field from the Products table. Without the join table it would be impossible to connect Orders and Products tables to get the details of each order placed. As there are many products and there are many orders, it is said that these tables are in a many-to-many relationship.
In our ETD database, you will create a join table named SessionInstructor. This table will join the Instructor and TrainingSession tables as depicted in Figure 1.2. These tables are in a many-to-many relationship as there are many instructors and many training sessions. By creating a join table SessionInstructor, you will be able to determine which sessions are taught by which instructor and vice versa..
About Primary Keys
As mentioned earlier, a primary key is a field that uniquely identifies each record in a table. To find that key, ask yourself a question: If I choose this field as a primary key, can I be certain that this field is different for every record in a table? For example, if you are creating a bookstore database, the International Standard Book Number (ISBN) printed on the back of the book cover can be used as a primary key in the Books table because each book has a unique ISBN number. How about the Social Security number for the Employees table? Although SSNs are unique, for privacy reasons, it is not recommended that you use them as primary keys. When deciding on a primary key keep in mind the following:
A primary key must be unique; you cannot have a duplicate value in this key.
A primary key cannot contain a Null value; the value for that field must exist when the record is created.
Once created, the primary key cannot be changed.
Default order of data displayed in an Access table is by primary key, so selection of your key is important.
The keys play fundamental role in establishing relationships between tables. Unless you find a unique key, you will not be able to relate (or connect) your tables.
Sometimes finding a unique key will seem like a daunting task. If one field cannot guarantee the uniqueness of data, try to identify two or more fields that together have values that are different for any given record. These types of primary keys are known as composite primary keys. If none of these methods work, you can assign each record in a table a unique number. This is easily done with Microsoft Access autonumbering feature. Access will number the record as you enter it, thus making it unique.
Primary key fields speed up searching for records as Access automatically creates an index for each primary key field. Indexes are discussed in Chapter 4. Keep in mind that Access will allow you to have a table without a primary key, but you will not be able to set up relationship with other tables unless you add that key.
About Foreign Keys and Null Values
When you include the primary key from one table as a field in a second table to form a relationship between the two tables, the key in the second table is called a foreign key. While primary keys are used to ensure the integrity of the records within a table by guaranteeing their uniqueness, foreign keys are used to protect the integrity of data spread over multiple tables. Each value in a foreign key field must exist as values in the primary key field of the table being referred to. This concept is called a referential integrity rule. This rule prevents us from entering a child record when there is no parent record in the first table. A foreign key can be used as a check constraint by limiting a column to the defined set of values in the parent table. Foreign key columns almost always contain repeating values. Unlike primary key columns, it is possible for a foreign key column to contain Null values. In database terms, Null refers to data that’s either missing or is unknown at the time of data entry. Keep in mind however that if the foreign key contains a Null value, you won’t be able to relate the foreign key records to the records in the parent table uniquely identified with a primary key. To protect the integrity of your data, you should require a value in foreign key fields. You will learn how to check for Null values and how to use Null later in your database project when you start working with Access expressions and functions.
You will work with foreign keys while creating table relationships in Access.
What Is Database Normalization?
Normalization is a process of organizing the data in a database. The Normalization process includes creating tables and establishing relationships between those tables. During the normalization process you apply a set of rules to your unnormalized data. These rules help eliminate data redundancy and protect data integrity. The normalization rules were first proposed in 1971 by Edgar F. Codd, an English computer scientist, as part of his relational model (RM) for database management. While studying various relations (tables), Codd discovered that unnormalized relations present some problems when attempts are made to update, insert, or delete data from a relation. He called these problems anomalies.
Figure 1.3 illustrates an update, insertion, and delete anomaly as presented in Wikipedia: https://en.wikipedia.org/wiki/Database_normalization.
Figure 1.3.
Database Normalization – Wikipedia, The Free Encyclopedia1,2,3
1. Nabav, “Example of a relational database table that suffers from an update anomaly”, 2012-02-17, accessed March 23, 2023, https://en.wikipedia.org/wiki/Database_normalization#/media/ File:Update_anomaly.svg
2. Nabav,“Example of an insertion anomaly in a relational database”, 2007-08-07, 2008-02-13, accessed March 23, 2023, https://en.wikipedia.org/wiki/Database_normalization#/media/ File:Insertion_anomaly.svg
3. Nabav,“ Example of a relational database table that suffers from a deletion anomaly”, 2008-08-08, 2008-01-23, accessed March 23, 2023, https://en.wikipedia.org/wiki/Database_normalization#/media/ File:Deletion_anomaly.svg
The Update anomaly refers to a situation where an update of a single data value requires updates of multiple rows of data. As Figure 1.3 illustrates, to update employee’s address we may need to change one or more records. Update anomalies often result in inconsistent data as it is easy to miss some of the data that should have been included in the update but weren’t. To avoid update anomaly, it is important to store only one copy of the data and refer to it wherever required instead of duplicating it.
The Insert anomaly refers to a situation where a row of data cannot be inserted into a table because information that should be kept in two separate tables is kept in one. As Figure 1.3 illustrates, because we don’t know the course code for Dr. Newsome, his personal information cannot be inserted. To avoid insert anomaly it is important to keep data related to different categories in separate tables. The faculty information should not be embedded in the Courses table.
The Delete anomaly is the opposite of the insert anomaly. Because Faculty and Courses data are kept in one table, deleting a row of data for Dr. Giddens (see Figure 1.3) will result in loss of his personal information as well as the information about courses he was assigned to teach.
Data Normalization Rules
The normalization rules are formally known as normal forms. Each rule is called a normal form. Each normal form has rules that must be satisfied before you can go to the next form. How far you should normalize depends on whether you want to optimize your database storage and updates or get more efficient querying capabilities. Too much normalization increases the complexity of your queries as multiple tables must be accessed to retrieve data. To achieve the highest level of normalization, start with unnormalized data and organize your data into tables in such a way that it complies to first normal form, then second normal form, until the data satisfies at least the third normal form. This is a progressive process in which each step must be completed before higher level of database normalization can be achieved. The Action Item 1.2 at the end of this chapter provides a link to a 5-minute overview and examples of normal forms. The three normal forms will cover most cases encountered while designing today’s database information systems.
Benefits of Data Normalization
By following specific normalization rules, it is possible to determine the most efficient way to divide the information into tables so that each piece of data is stored only once in the database and all related information can be easily retrieved. As Codd stated, by normalizing the database you can avoid undesirable insertion, update, and deletion dependencies and reduce the need to restructure the database as new types of data are introduced. Changes made to a fully normalized database should only minimally affect the applications that interact with that database. A properly normalized database saves time and money, while the unnormalized database will lead to maintenance headaches in the future.
What Is Database Denormalization?
Normalization leads to creating more tables and joins. Sometimes you will need to denormalize the database. Suppose your database users report serious performance issues while running complex queries that retrieve company’s asset data. You have tried to eliminate these issues by tuning your database, and even upgraded your computer hardware, but the issues seem to persist. At this point, denormalization may be the solution you need to consider. You may improve the performance of your database by storing some redundant and summarized data in tables and combining some tables that were split during the normalization process. Keep in mind that denormalization should be your last resort. By denormalizing data, you will reintroduce data anomalies that you tried to eliminate in the process of normalization, thus you will end up with a database application that is faster but less accurate. You need to decide which outcome will be most beneficial to you.
Action Item 1.2
Get familiar with the normalization concepts before proceeding to the next chapter. Spend the next five minutes reading Microsoft documentation at: https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description
When you are done reading, don’t miss the opportunity to download the Office Products Troubleshooting PDF file that will serve you as a go to reference on Microsoft Access and other Microsoft 365 products. You should see the download PDF link in the left scroll area. And, in case you missed it, use this link:
https://docs.microsoft.com/en-us/office/troubleshoot/opbuildpdf/clienttoc/toc.pdf?branch=live
SUMMARY
In this chapter, you began your journey into the exciting world of Access database development. As you have seen, properly designing a database, even a small one, is a big job. To get good results you must have a clear understanding of the underlying data and its various relationships. You were introduced to the required database terminology and basic database concepts that should help you working with the remaining chapters of this book. If some of these concepts still feel foreign to you, don’t panic. You will have plenty of time to assimilate this new knowledge as you progress through this book and learn how these concepts are implemented piece by piece in Access. If you picked up this book because of its hands-on approach, get ready for the next chapter where we begin building the Employee Training Database.
Creating aDesktop Database
C h a p t e r 2
Now that you are familiar with the basic database terms and you’ve gathered your company’s requirements for the training database, it’s time to begin the implementation phase by building an Access desktop database.
ABOUT ACCESS DATABASE FILE FORMATS
Depending on your needs, Access databases can be created in various file formats. In this book you will use the ACCDB format to create the Employee Training database. The ACCDB file format, which is recognized by its .ACCDB extension in the file name, was first introduced in Access 2007 and has been the default file format for desktop databases since then. In earlier versions of Access, the MDB file format was used. For a complete listing of Access file formats and their differences, please see Appendix A.
CREATING AN ACCESS DESKTOP DATABASE
When you launch Microsoft Access, you are given options for creating a new database or for opening an existing Access database (see Figure 2.1). On this screen you will also find various predefined templates that Microsoft includes with Access free of charge. These templates provide examples of prebuilt Access applications that you can use as a starting point in your database development or as a handy learning tool. The search box available on this screen enables you to search for additional templates that are available online. This book’s focus is on creating a database from scratch, so templates are not covered. However, it is recommended that you take time to download and explore some of these templates to get familiar with the structure of various databases.
NOTE
Files for the Hands-On project may be found in the Companion files.
Hands-On 2.1 Creating a Blank Database
Launch Microsoft Access and choose New (blank database) as shown in Figure 2.1.
Figure 2.1.
Creating a desktop database (Step 1).
Access displays the blank database dialog box (see Figure 2.2) where you can specify the location and database name. The default database name is shown automatically in the File Name and so is the default file location.
Click the file folder image next to the File Name box in Figure 2.2.
Figure 2.2.
Creating a desktop database (Step 2).
Access displays the File New Database dialog box (see Figure 2.3).
Select the folder where you would like to save the database and enter ETD as the database name as shown in Figure 2.3. Notice that you can create a new folder for your database by clicking the New folder button in this window. For easy reference all objects and files created in this book will be stored in the AccessProjectBook1 folder. A database file is like any other computer file, so it can be moved or copied later to another preferred folder.
Figure 2.3.
Creating a desktop database (Step 3).
When you click OK in the File Name Database dialog, you will be returned to the blank database screen where all the default options are replaced with your selections as shown in Figure 2.04.
Click the Create button to create the ETD database in the AccessProjectBook1 folder.
Figure 2.4.
Creating a desktop database (Step 4).
Access automatically opens the newly created ETD database for you. This database contains a blank table named Table1 with one field named ID (Figure 2.5). This is the starting point in building any Access table. The asterisk denotes an empty record. Notice that when the table is in the Edit mode the Table Fields is selected in the Ribbon menu.
Figure 2.5.
Creating a desktop database (Step 5).
