Lessons

This is the foundation of the database. We will construct the tables and forms necessary to store information about people, organizations, and companies (called Entities). Each entity can have an unlimited number of phone, email, and physical addresses. We will use Helper data tables to store all of the minor information like name prefixes, suffixes etc. so that we don't clutter the database with dozens of tiny tables. We will create a function to assign EntityCode values to each person so we don't expose our AutoNumbers.

Database Files

FREE Version (ACCDE)
PAID Version (ACCDB)

Resources

ABCD Home Page
Video Lessons Download ZIP File (646 MB)

Learn More

Free Access Beginner 1
Not Exposing Data to Customers (reason for the EntityCode)
Why AutoNumbers are Good
Access Expert 1, Expert 2, and Relationship Seminar
Access Developer 1 for VBA Beginners
ZIP Code Lookup Template
Access Insider Circle
Random Numbers
DLOOKUP Function
Normalizing Data

Notes

Importing Data: A few of you have asked for instructions on how to import data from your existing databases or Excel sheets to the ABCD. Considering the structure of the tables (Entity with Phone, Address, Email all in separate tables) this can be a challenge. I include a good example of how to do this in my Normalizing Data Extended Cut video. Enjoy.

Pre-Requisites Required

In order to purchase any level of the ABCD, you MUST have purchased all previous levels. For example, you cannot buy only Level 5. You must first have Levels 1 through 4, then you can buy Level 5. Sorry, but this is a hard pre-requisite.

Videos

I am recording videos showing how the database is built, but mostly so that you can understand the construction and be better equipped to make any modifications or additions you may need on your own. This will be marketed as a finished database without the need for any enhancements, but, of course, one of the main reasons to use Access is so that you can make changes yourself. The videos will be a quick run-through of how each feature is built, as I'm building it. I will be using advanced features like SQL and VBA from the start, so you may want some SQL or VBA Developer Lessons under your belt if you're going to try to follow along. This is NOT a teaching database. Free Technical Support will NOT be offered.

Tech Support

There is NO free Technical Support provided on the ABCD if you have not taken ALL of my previous Developer lessons. There are a lot of very difficult, advanced programming concepts used in the construction of this database. I do not have the time to provide free support if you get stuck trying to make modifications. All I will do is point you to the appropriate Developer lesson(s) that cover the issue. There is a reason why (a) I say this is NOT a teaching database, and (b) there are pre-requisites. Feel free to ask questions, but I can't spend half an hour explaining concepts that are already covered in my other lessons.

Licensing

Unlike my other courses and seminars which provide you with royalty-free databases that you can modify and distribute yourself, the ABCD does NOT come with a royalty-free license. You may only customize the database for your business needs and for use within your organization. You may not resell or distribute it to others without express written permission. In addition, if you are using it in an organization with more than 10 users, you must purchase additional licenses. Contact me for additional information. I will post additional details when the Core database is completed.

Ordering

There are two purchase options available for the ABCD.

You can purchase a fully editable and customizable copy of the database in addition to the video tutorials that show how it was built. This is the option you should pick if you want to make changes to the database for your own business needs, and you want to learn how the database was constructed to assist you. As a reminder, these are not teaching videos. They are simply provided to show you how the database was built. Free support is not guaranteed if you have questions. I do my best to answer questions, but I can't promise anything. You must purchase any pre-requisite parts or modules required. For example, you may not purchase Core Part 4 without first having parts 1 thru 3.
Once the Core is finished, you can optionally purchase a use-only version of the database. This is the best option if you only plan on using the database for your personal or business needs, but do not intend on modifying the structure of the database. Free instructional videos will be provided to teach your staff how to use it.

Your Feedback

I will be releasing new Core features and modules on a regular basis. You can see the list of features that are scheduled for development on the Coming Soon page. I would very much like your feedback on what features you think should be available. If you have a specific interest for your business type, let me know.

Comments for ABCD Core Part 1

Age	Subject	From
24 days	ABCD Part 1, Lesson 4 Error Possible Fix	Dan North
5 months	Helper Table	Adrian Connolly
2 years	Embeded Subforms	Philip Carley
3 years	Part numbers do not match	Alejandro Juarez
3 years	Several People Same Address	Donald Lader
4 years	ABCD	Tim Anderson
4 years	Different Entities	James Gray
4 years	Websites	Ramona Woitas
4 years	Make Entity Code	Ramona Woitas
4 years	Several People Same Address	Angelika Senn
4 years	Importing Data to ABCD	Richard Rost
5 years	Core 1 Lesson 9 Person Code	Ron VandeVelde
5 years	Transfer Data	Harold Sheats
5 years	Person Entity table	Scott Axton
5 years	Helper Form	Joseph Messina
5 years	HelperT question	Ron VandeVelde
6 years	ABCD Part 1	Michelle Maughan
6 years	ABCD Addressee Lines	Richard Wilson

Subscribe to ABCD Core Part 1
Get notifications when this page is updated

Intro

In this video, we begin building the core of the Access Business and Contact Database (ABCD) in Microsoft Access, focusing on designing a production-level database suitable for managing both people and companies as entities. I will show you how to set up the database so each contact or organization can have unlimited phone numbers, addresses, and email addresses, as well as how to use a single helper data table to keep your lookup values organized. We will also cover creating unique entity codes and setting up the main entity list and main menu. This is part 1.

Transcript

Welcome to the Access Business and Contact Database Part 1, the Core Database. Brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

What is the ABCD? Well, it is a database that I am building to be a professional, quality, production-level Access database that you can actually use to run your business on. It will be modular, so it will have many different parts and you can pick and choose what you like.

Unlike pretty much everything else on my website, this is not a teaching database. I am recording videos as I build it, but the goal is to show you how the database is built so you understand it and can modify it for your purposes. I will also point out other lessons where I cover certain things like how to build combo boxes, how to write VBA code, and so on.

Part 1 represents the core of the database. Our basic unit is the entity. An entity can represent a person, a group, a company, and so on. In fact, initially I started building it as just a person table and then later on, as we got into it, I decided to switch things around and add company or organization name to it. You will get a full explanation of that in lesson 10.

Something new that I am doing is that each entity, each person, and each company can have an unlimited number of phone numbers, addresses, and email addresses. There will be more items added on later too. Instead of just putting one phone number field, for example, you can store their home phone, their work phone, their cell phone, and the same thing with email addresses and physical addresses too.

We are also doing something new by building what I call helper data tables. Now, instead of having a million different tables in your database where you have a list of name prefixes like Mr. and Mrs. and Miss, a list of suffixes in a separate table, genders in a table, relationship types, phone types, and so on, we are going to put all that in one big table called a helper data table. Then we will use that table to build all of our combo boxes. This keeps your database from getting cluttered with all these thousands of different little tables.

Then we are going to create something called an entity code, a person code, a group code, or whatever you want to call it. Essentially, we are going to use ID auto numbers throughout all of our database tables as you are supposed to, but you do not necessarily want the customer seeing their customer code. We are going to create an entity code that each person is going to get. We will also assign them to orders later on. That way, if you get a new customer and you only have, let's say, six customers, they will not know that they are customer number six. We will generate the code to create an entity code.

To round it all off, we will make an entity list form and a main menu. In the entity list, it will show you the person, the company, or both. In later modules, we are going to give the user the option to switch between those different formats.

The main reason why I wanted to add the entity instead of making it either a person table or a company table is because you can have both: some of your customers might be people and some might be companies. I wanted this database to be able to handle both types.

If you are just purchasing the ABCD to use for your business and you are not planning on modifying it yourself, then you do not need to take any of these prerequisite courses. However, if you are planning on doing modifications, I strongly recommend that you take my Access Beginner series in its entirety, at least Expert Level 1 and 2, so you know relationships. Expert Level 3 introduces you to SQL, which I will use a lot of in this database, and Developer 1, which is an introduction to VBA. Because of the level of SQL, I also strongly recommend SQL Seminar Parts 1 and 2.

I will be using Microsoft 365 to build the database. However, you should be fine if you are using 2007 and later. I do not think I have used any features so far that require anything newer than 2007. I do recommend the latest version of Access. Of course, 2019 is the latest version as of right now or 8365 subscription.

If you have any questions about the material presented in class, please feel free to post it in the forum for this lesson or you can post it in the Access forum on my site. If you need help customizing the database for your needs beyond what is covered in class, visit my consulting page. If it is something that I think will be useful for other people, I will throw it in one of the future modules. If not, we can work on something for your database on a one-on-one basis. There is my email address. Again, I prefer you contact me through the website, but if email is your only option, there it is.

Coming up in future modules, here are some of the other ideas I have. We will be adding groups. We will be adding relationships between the entities, contact management, mailing lists, miscellaneous data sets, all kinds of new items coming up. I have already got people interested in modules for insurance policies, budgeting, and accounts payable.

If you have something that you want to see added to the database, contact me.

All right, so let's get started with lesson one.

Quiz

Q1. What is the primary purpose of the Access Business and Contact Database (ABCD)?
A. To provide a teaching database with sample data
B. To serve as a modular, production-level Access database for running a business
C. To demonstrate advanced Excel features
D. To provide a database only for contact management

Q2. What is an "entity" in the context of this database?
A. Strictly a person record
B. A type of phone number
C. A record that can represent a person, group, company, etc.
D. An invoice or order

Q3. What is a key feature of entities in this database?
A. Each entity can only have one phone number and address
B. Each entity can store unlimited phone numbers, addresses, and email addresses
C. Entities are only linked to orders
D. Entities must be companies, not people

Q4. What is the purpose of the helper data table in the database?
A. To store only gender types separately
B. To reduce the number of small tables by combining data like prefixes, suffixes, and phone types in one table
C. To store financial data
D. To manage all of the orders

Q5. How does the database handle the display of customer codes to customers?
A. Displays the actual auto number ID
B. Always uses the company name as the code
C. Uses generated entity codes, distinct from auto number IDs, so customers do not see their row number
D. Shows the entry date as the code

Q6. Why was the structure changed to the entity model instead of a separate person or company table?
A. To make the database run faster
B. So it can handle both people and companies as customers
C. To reduce memory usage
D. To support only small businesses

Q7. What form will allow users to see people, companies, or both in the database?
A. Entity list form
B. Invoice form
C. Helper data form
D. User settings form

Q8. If you want to modify the ABCD database, which prerequisite courses are recommended?
A. Only Access Beginner level
B. Access Beginner series, Expert Levels 1–2, Expert Level 3, Developer 1, and SQL Seminar Parts 1 and 2
C. Only SQL courses
D. Only Access 2007 or earlier lessons

Q9. What Microsoft Access versions are stated to be compatible with the database built in class?
A. Only Microsoft 365
B. Any version before 2000
C. Microsoft 2007 and later
D. Only Access 2019

Q10. Where does Richard suggest you post questions about the material?
A. Only by phone
B. Send letters by mail
C. In the forum for the lesson or the Access forum on his site
D. On third-party Access forums

Q11. Which is NOT specifically mentioned as a planned future module for the database?
A. Insurance policies
B. Game score tracking
C. Budgeting
D. Accounts payable

Q12. What is the next step after the introduction video?
A. Fill out a contact form
B. Start with lesson one
C. Install additional software
D. Take an online survey

Answers: 1-B; 2-C; 3-B; 4-B; 5-C; 6-B; 7-A; 8-B; 9-C; 10-C; 11-B; 12-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone introduces you to the Access Business and Contact Database, or ABCD. This is Part 1, covering the core database. I am your instructor, Richard Rost.

The ABCD is designed as a production-quality Microsoft Access database that you can use to manage your business. What sets it apart is its modular structure, allowing you to choose which features suit your needs and plug in different parts as your business grows or changes.

Unlike most of the training materials on my website, the ABCD is not a demonstration tool. While I record instructional videos as I build it, my primary goal is to walk you through the real-world development of this database so you understand how everything fits together and can adjust it to suit your own business processes. Along the way, I will reference other lessons for deeper dives into topics like combo box design and VBA programming.

This first module focuses on the foundation of the database: the concept of entities. An entity could be a person, a group, a company, or any similar contact that your business interacts with. Initially, the design started with just a person table, but I soon decided it made more sense to create an entity that could also represent companies or organizations. This transition gets a detailed explanation in lesson 10.

A major improvement in this project is the ability to assign as many addresses, phone numbers, and email addresses as needed to each entity, whether they are a person or a company. Instead of limiting records to a single contact field, you can store multiple types of contact information for each entity. This flexibility means you can have home, work, and mobile phone numbers, as well as several email and physical addresses all tied to the same record.

I'm also introducing a new approach to managing lookup values. Rather than cluttering the database with separate tables for prefixes, suffixes, genders, relationship types, phone types, and so on, all these lookup values are consolidated into a single helper data table. This streamlines your database design and makes it easier to manage and expand combo box options without adding more tables every time you want a new type of value.

Another concept you will see is the use of entity codes, such as person codes or group codes. Each record will still have a unique ID auto number, as best practices suggest, but typically you would not want customers to see their record numbers directly. The entity code provides a more professional and flexible identifier, allowing you to control how codes are displayed to users or assigned to things like orders. This also prevents your customers from drawing conclusions based on their record number, such as realizing they are only the sixth customer in your system.

To finish off this part, I will show you how to build an entity list form and a main menu. The entity list form will let you see people, companies, or both, with plans in later modules to let users customize the display format.

An important design goal for the ABCD is handling both individuals and businesses equally well. By using the entity model, you avoid having to separate your customers into people or companies. The system is built to support both without extra complexity.

If you are purchasing ABCD just to use for your business and do not plan to modify it, no prior course experience is required. On the other hand, if you want to customize this database, I highly recommend taking the Access Beginner series, especially Expert Levels 1 and 2 to get comfortable with relationships. Expert Level 3 covers SQL, which will be used extensively in this system. Developer 1 introduces VBA, and familiarity with SQL Seminar Parts 1 and 2 is also highly recommended due to the amount of SQL involved in this project.

All development is done using Microsoft 365. However, if you are using Access 2007 or a newer version, you should be able to follow along just fine, as I have not relied on features exclusive to newer editions. Still, I do recommend using the latest version if possible.

If you have questions about any material presented, feel free to post them in the lesson's forum or in the general Access forum on my website. If you need customizations beyond the course content, check out my consulting page. For suggestions that might benefit other users, I may add a new module to cover your idea. Otherwise, we can arrange individual support. While my email address is provided, I prefer initial contact through the website where possible.

Upcoming modules will continue to expand the ABCD's capabilities. Planned additions include support for groups, entity-to-entity relationships, contact management, mailing lists, and more. There is also interest in modules for insurance policies, budgeting features, and accounts payable.

If you have ideas or features you want to see added, I encourage you to reach out and let me know.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Overview of the ABCD database project
Designing a modular Access database
Explanation of the core entity table
Supporting people and companies in one table
Adding company or organization name to entity
Storing multiple phone numbers, emails, and addresses per entity
Using a helper data table for lookup values
Combining multiple lookup types in one table
Creating and assigning unique entity codes
Building an entity list form
Designing a main menu form
Rationale for supporting both people and company customers