Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If you're new to Databases and are just beginning to understand how to construct a Relational Database, you may have created a table that has what's called redundancy, in other words, you may be unnecessarily having to type in the same data repeatedly, instead of creating relationships between separate tables. Let me show you what I mean. Open up this Order table. In it I see multiple orders from the same customer and I've had to type in their address repeatedly, this even opens me up to typos. If I scroll over to the right hand side, I can see my Orders and the Product IDs, but then I've written in the Product name and the Size many times.
If you watch our Access 2010 Essential Training course, we go to detail into how to properly design your Database to prevent this, but at the moment, here I am with redundancy, and I want to fix it. This process is called Normalization. To normalize my database, go to the Database tools ribbon and then click on Analyze Table. This wizard will allow me to split my table into three separate tables, one, containing a list of all my customers a second, table listing all the orders they placed and a third table with product information.
The first two screens give me information about the process. This window allows me to pick my table, I only have one, so I'll click Next. Now, I have the option of either letting Access put my tables for me or I decide manually. We're going to let the wizard decide, and I'll click Next. Now here is Access's best guess that how my tables should look. I'll move Table3 over here to the right, and I'll extend them all down, so that I can see all the fields. This first table is based on my Order, so I'll double-click where it says Table1 in the header and type in the name Orders.
Note that your table names do have to be different from any existing table names, I'll click OK. The second table has information about each of my customers, so I'll double-click where it says Table2 and type in Customers. This third table contains information about my products, so I'll double-click where it says Table3 and change the name to Products and click OK. Next, we'll look at the fields and make sure they wound up in the right places.
This ID is for each order, so that's perfect. Address though, belongs to the customer, so I'll move that over to this table and drop it between CompanyID and Address 2. The same with City, I'll click on it and drag it over above State. OrderDate is the date of my order and Quantity is the quantity of products ordered. The lookup to Products, will tell me what products they ordered and the lookup to Customers will tell me which customer placed the order. Now let's take a look at our Customer fields. CompanyID is the unique identifier, so I am going to drag that up above Company, then Address, Address 2, City, State, Zip, Business Phone, those are great.
Size though, belongs over here on the right-hand side with Products, and then I have my Product ID, my ProdName and Size. Now my fields are all distributed properly, I'll click Next. The next step is to set my Primary Key fields or my unique identifiers. In my Customers table, it has offered to create one for me, but our CompanyID is a unique identifier, so I'll click on it and then click on the key in the upper right-hand corner. The same with Product, that's an ID field already, so I'll designate it as my Primary Key by clicking on the key in the upper right-hand corner.
Now everything looks great, I'll click Next. In the next step, my Table Analyzer Wizard has found some typos; the company Avalon, when I look at the P .O. Box, has two different P.O. boxes. 483 is correct, so I'll leave the check mark here. I'll click this Next Key button to see additional records with issues. Delish Foods, the correct address is actually 2257, so I'll put a check mark in the box below. This Next Key button is grayed out, because I don't have any more typos, so I'll click Next.
It now gives me the option of creating a Query that will re-create my original table, I'd like to use that, so I'll leave this on Yes, and I'll click Finish. I had left my Order table open so it's going to change my Query to Order_NEW and I'll click OK. The Help opens up, I am going to go ahead and close that. Here's the new query bringing together all of the information from my tables in a format very much like my old table, I'll go ahead and I'll close that, I am going to right-click on the tab and choose Close.
Here's my original table, still open, I'll close that as well. And in fact, I can now delete it so I'll right-click on Order, my old table, and delete it. I'll say Yes to that. So now I am left with three tables, Customers, Orders, and Products. When I double-click on Customers, here is all the information about my customers, when I double-click on Orders, here are the information about each of my orders and when I click on Products, here is all the information about my products.
All separated out into three separate tables. Access has also set my Relationships for me. Up on the Ribbon, I'll click the Relationships button, and here I can see my three tables with my relationships between three, already set. I'll close this window, as well. It asks if I want to change it, and I'll say Yes, click back on the orders table. Access's handy Table analysis tool will automatically split your existing tables and set up the appropriate Relationships, so that you don't have to do it manually.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 87243 Viewers
80 Video lessons · 136313 Viewers
59 Video lessons · 54977 Viewers
52 Video lessons · 68845 Viewers
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.