Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Before we can began to build an Access 2010 database, it's important to understand fundamental database concepts and terminology. First, what is a database? A database is a collection of information about one subject. Real life examples might be a telephone book, a way to track your music collection, or the way your business attracts its customers in sales. A database program gives you a way to manage that data and to analyze it to make it useful. You can sort the information in many ways, extract just certain pieces of info, or summarize it in reports.
Let's take a look at the terminology you'll need. First off are Tables. A Table is one collection of information and it's organized into Fields, Records, and Data. Fields are the columns in the table. They are the categories of info you are collecting. In a phone book, these will be the person's name, address and phone number. Each is one person or thing you are collecting information about. In a phone book, each person's listing is their record. Data values are the actual pieces of information themselves. Alicia Katz Pollock, 123 Main Street, (800) 555-1212.
Many people try to store this information in Excel, because it's organized in rows and columns. But Excel is a flat file, meaning that all it gives you is one long list. Access is a relational database, and it gives you additional organizational tools that are far more efficient, flexible, and less prone to error. As you can see here, if we capture our customer orders in Excel, for each one, we'd have to repeat the customer's information, the products, orders and the prices. That's a lot of repetitious typing, and you open yourself up to data entry errors, like this Zip Code right here.
That was keyed in wrong. In a Relational database, the orders are tracked in three different tables: one with information about the customers, one table with information about the products we carry and the third table with the orders themselves. Now, to make this work we have to add some techniques into the table structures: Primary Keys and Foreign Keys. Primary Key is the unique identifier for each record in the table. You have to have some way of distinguishing each entry. I can't use Gino's Pizza because it's not unique. There may be only one in my neighborhood, but I am there is more than one Gino's Pizza in the United States, and I would love for all of them to be my customers.
So I added an extra field called the Primary Key and create a numbering system, so that each store has its own id number. Next, these Primary Keys become Foreign Keys, also known as common fields, linking the related information between two or more tables. It's the field that both tables have in common, and this allows me to gather data from both when I take an order, or run reports. That brings me to Relationships. Access has a window where I tell it how the Primary Keys and Foreign Keys relate to each other.
The most common is a one-to-many relationship, where one record in the first table has multiple occurrences in the second table. Here, one customer, Gino's Pizza, hopefully will order from us many times. There are other relationship types as well. But one-to-many is the most common. Our next terminology are Data Types. Data Types define your fields so that Access knows how to manage them appropriately. Let's take a look at the different types. AutoNumber sequentially increments each record, and it's commonly used as a Primary Key field.
Text is used for alphanumeric characters, any letters and numbers. Number is used for numbers that you actually calculate. Currency is for dollars, cents, and foreign currency. Data/Time is for dates and times. Yes/No fields are for either/or scenarios, and they can be defined as On/Off, Yes/No and True/False. Associate files from other programs, such as Word or Excel, and include them along with your record. Hyperlinks are used for e-mails and Web site URLs. Memos are for when you have long fields, long commentary.
It allows you to take notes. Calculations do math based on data in your other fields. Lookups gather their data from other tables. By using the appropriate data types, you'll build your database in accordance with industry standards. When you are planning your database, you need to take a look at the big picture to make sure you are creating your tables effectively. This process is called normalization, and there are accepted standards in the database industry. First, you want to use the smallest meaningful fields possible. You don't want one field called Name.
You want two fields, Last Name and First Name. Otherwise, how would you sort by Smith if the person's Name field said Fred Smith? The next phase is looking at your data. If you find that you are entering the same information repeatedly, that's a sign that those fields should be broken out into a separate table and then linked with a common field. Also, think about the big picture and your long-term growth plans. It's easy to plan ahead, but challenging to go back and add new tracking fields once you have been in business for some time. For example, you might not need, right now, to track how your customers found out about you, but if you add that to your marketing plan two years from now, you won't have that data for any of your current customers.
So now, we are really ready to begin our essential training. In this course, we are going to build a working database for Two Trees Olive Oil. We'll create four tables: one with our line of olive oils, one of our employees, one table with our customers and a forth tracking all orders placed. Once that's done, we'll make forms for data entry, queries to analyze our company in sales, and reports so we can print it all out. Tables, forms, queries, and reports will all be referred to as your database objects throughout the course.
Now that you are familiar with the terms I am going to use, let's get started.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.