Hey everyone, Kevin here. Today I want to show you how you can use Microsoft Access. So many of you have requested this video in the comments, so here it is. Also, I wanted to do this video as well because I kind of have a soft spot in my heart for Microsoft Access. When I first met my wife, we were both working at Microsoft at the time, and she was on the Microsoft Access team, so I really felt like I had to do this video. Now you might be wondering, what is Microsoft Access? It’s a relational database program, and as we jump into this video, we’ll find out more about what that even means. But at a very high level, you can use Microsoft Access to track customers, to track orders, to track assets, and the list goes on and on of the types of things that you can use or track with a database. Now one of the first questions you might have is why would I use something like Access? I could just enter information into an Excel spreadsheet. Why would I ever need to use a database? I’m in Microsoft Excel here and sure I can track information here as well. Let’s say I want to track orders for the Kevin Cookie Company. I have customer information over here on the left and then I have the order information over on the right-hand side. So this works too, right? Well, actually, it’s not really that efficient at tracking this information. Let’s say that maybe Bill comes in and he really loves our cookies, so he placed another order. So, I want to enter another row with his new order. So here I’ll take all of his customer information, I’ll paste it down here, and then I’ll enter in the additional order details. Now this is what’s referred to as a flat file. There are no relationships between this data, and it’s really inefficient. So here you’ll see now that I’ve had to copy Bill’s data from up here and I’ve had to paste it in again. So right now, I’m carrying his data twice. With a database you don’t have to do this. We could set up different tables for the customer information and a different table for the order information. Also, with an Excel spreadsheet, you’re limited to just over 1,000,000 rows of data, and so if you have more data than that, well, unfortunately Excel won’t be able to handle that. Also, if I go back to all the order information here, if I want to extract interesting insights from this data, maybe I want to write a query, and I can do a little bit of that with Excel. I could filter the different columns, I could insert a pivot table, I can try to manipulate the data to get an interesting view, but sometimes you just need to be able to write a query and then to pull together a report, and unfortunately, that’s not that easy with Excel. So that’s where databases win out. All in all, Access is a fantastic tool for individuals or for small businesses that need to track things. However, if you’re let’s say a mid-sized company, or even a large company, you’ll probably start to realize some of the limitations of Microsoft Access, and to be totally fair, when I worked at Microsoft, I was on some teams that used Access just as a really quick and simple database. If you’re a larger company, you’ll probably look at other options like Oracle, MySQL, Microsoft SQL Server, Mongo DB, and the list goes on and on of solutions that are more scalable. However, what I will say is Microsoft Access is a fantastic tool to learn the fundamentals of database design. To learn the fundamentals of database design, in this tutorial, we’re going to build our own database today, and you’re welcome to follow along. You know, I’ve been meaning for a long time to create a database for the Kevin Cookie Company to track our orders and this seems like the perfect opportunity. Yes, I did figure out a way once again to incorporate the Kevin Cookie Company into a video. We’ll start off by creating tables, then we’ll go ahead and create an order entry form. This way other people can come in and add data to our database. Then we’ll write a query so we can extract interesting insights from our data, and then at the very end, we’ll create a report so we can share some of those insights with others. With all that said, by the end of this video, you should have pretty good working knowledge of how Microsoft Access works. All right, well enough talk, why don’t we jump on the PC and let’s start building our very first database. To get started with Microsoft Access, go ahead and launch the application, and that’ll drop you on the start page, or right here, what’s also referred to as the home view. Right here at the top you can create a new database. We can start off with a blank database, and in a moment, we’ll do that. Over on the right-hand side, you also see a whole bunch of different templates. If there’s a template that matches what you’re trying to do, this could help you save a little bit of time and you can go in and just tweak it. There’s also a massive collection of templates that you can look at by clicking right here. Down below you can search for different databases you might have worked on in the past and here you can see all of your recent databases. One of my favorite features, when you hover over right here, you’ll see a pin icon. When you click on this, this will add it to the pinned view. So, this is just a quick way to get back to the files or the databases that you use most often. OK, to get started, let’s go back up to blank database and let’s click on this. This opens up a dialog where we can type in a filename for this database and I’m going to call this cookie orders, and if you want to follow along, feel free to give it the same name. I’ll leave it in the default location right here in documents, and next, let’s click on create. This now drops us into a new Access database, and congratulations, you’ve now created your first database. There’s not much here yet, but don’t worry, we’re going to go through step-by-step how to build this out, and before we jump in, I do want to orient you to the experience and just talk about some very, very basic database terminology. Over on the left-hand side, we can see that Access created Table1 and we’re going to start building out Table1 first, and like I said, I want to track customers and orders for the Kevin Cookie Company. So, we’ll use Table1 to track the customer information. So, we’re going to keep that separate from the order information. Also, over here on the right, if you’ve ever used Microsoft Excel before, this probably looks familiar. We have all of these different cells. The terminology here is a little bit different. In Excel, we refer to these as columns, and we refer to these as rows. In Access, it’s a little bit different. We refer to a column as a field, and then a row is referred to as a record. So, we’re going to be adding records to our database. Let’s get started now by adding some fields to this table. Now up here, the first field is referred to as ID, and once again, I want to use this table to track all of our customer information. ID on its own isn’t that descriptive. I want to update it to be Customer ID. To update the name, you can simply double click on the field and then here I can type in my own value. I’ll type in customer ID, and that looks good now. Once I’m done typing that in, hit the tab key and that’ll bring you over to the next field, and here, first off, you need to choose the data type. The data type defines what type of data you’re going to insert in. For this, I wanted to track customer information. So, this is going to be things like the customer’s name, the first name, the last name, the e-mail address, the phone number, and all of those pieces of information, or all of those fields will simply be the short text data type. However, later on when we get to the order sheet, we’re going to add numbers, and so we’ll select a different data type. As you’re entering data, feel free to look through all the data types to see if one of these best matches the data that you want to enter. For the first field, I want this to be the first name, so I’ll select short text. Now it’s called short text, but it allows up to 255 characters, which is actually a pretty good length, so it’ll capture all of the different fields that we want to insert into this table. If you want long text, that allows it to go much, much longer, but once again, short text should be sufficient. I’ll select this. Once I select that data type, now I can type in the name for the field. I’m going to call this first name. Once I finish typing that in, I’ll hit tab and here I can select the data type for my next column and for this one, this is going to be the last name, and I’ll go through and add all of the different fields for the customer tracking table. I’ve now added all of the different fields to this table, and if you’re following along, feel free to pause this video and you can then enter in all of these different fields. The data type for all of these is short text. At the very end, I want to add one additional field, and this one is going to be notes. So, let’s say maybe a salesperson for the Kevin Cookie Company comes in, they add a customer and maybe you want to include things like the customer’s kid’s names. There’s nothing like remembering the customer kid’s names to close a deal. Or maybe you want to include other information like the customer’s birthday. All that type of stuff. And this might be a little bit longer than short text, so here I’ll click over here, and instead of going with short text, I’ll go with long text, and for this field, I’ll call it notes. This should now include all of the different fields that we want to include as part of this customer table. Now that we’ve entered in all of the different fields, I want to show you how you can go back and make edits to these. So, let’s say you enter something in, but maybe you screwed something up or you want to change the text. Right here, I can click on one of these field headers and you can very quickly update the text. Also, when I click on one of these field headers right up here, that opens up the fields ribbon, and right here I can always go back and modify the data type. So just because you choose a data type doesn’t mean you’re locked in on it. Right down below, I can also adjust the width of these different fields, and here if I right click on one of these field headers, it opens up a context menu where I can do all sorts of things. For example, I can hide fields, I could also unhide fields. Here you can freeze fields if you’ve ever done that in Excel before. It’s the same exact concept and right down here I could insert a field. Let’s say I want to insert something between e-mail address and phone, I can do that. I could also rename here again, and I can delete fields. So, all types of different actions I can take to make sure that the table has the structure that I want it to have. Now that we’ve gone through and we’ve created all of our different fields, I want to show you a quick way where you can look at your fields and also modify the different data types. Down here, in the bottom right-hand corner, you can enter what’s called the design view, and when I click on that, first I have to name my table. Right now, it’s Table1, but I want it to be more descriptive. Once again, this is tracking customer information, so I’ll call this table customers. Once you finish typing in the name, click on OK. This now drops me into the design view and here again I can see all the different field names. You can also add field names here. This is a pretty quick way to add additional fields. Also over on the right-hand side, here you can quickly verify what the data type is for each one of your fields, and here too you can click on it, and you can change the data type very easily. Over on the right-hand side, let’s say maybe your field names not really that descriptive, you can also add a description with some additional details. Let’s jump back into the datasheet view. To do that, right down in the bottom right-hand corner again, we can toggle back and forth between these different views. I’ll click into the datasheet view. This drops us back into the datasheet view, and we’re ready to start adding some records. Right now, we have all the fields in place, and I want to add the first customer and the first customer is me. I’m probably one of the best customers. Any time you finish a batch of cookies, I end up eating a lot of them. So right here, I’ll go in and fill out my details. Feel free to do the same for yourself. Here I’ll type in my first name, I’ll go over and I’ll type in my last name. One thing to call out, as I start typing in information here, you’ll see that it automatically assigned an ID. So, every time you go through, and you add a new record, it’ll automatically get an ID assigned to it. As a quick note on this ID, this is also what’s referred to as the primary key. And what is a primary key? Well, this is a unique identifier. So basically, every single customer record that we add to this table will have its own unique identifier. So, no other customer will have the same ID as me, and of course, I’m the number one customer. I probably eat the most cookies, so it’s really fitting that I have the number one. We’re also going to use this primary key to connect to other tables later on, and don’t worry if that sounds complicated, I’ll show you exactly how we go about doing that. In the meantime, let’s go through and fill out the details of the first customer. Once you enter in the first customer, hit the enter key and that’ll bring you down to the next record and now you can go through and add some additional customers. Now just for purposes of this demonstration, I want to go through, maybe add about four or five different customers just so we have some examples in the data. Feel free to do the same and make up some customer information. I’ve now entered in a whole bunch of sample customer information. If you don’t want to type in customer information but you still want to follow along, I’ve included the link to this database and the description. This will allow you to access this database. We’ve now finished all the customer information and I now want to add one more table with all of the order information. To add another table, let’s go back up to the top ribbon and let’s click on create. Right over here we can create a new table. I’ll click on the one that says table. This drops us into a new table, and just like before, we need to build this out. Right up here, the first field is called ID and I want this to be more descriptive. I’ll double click on this just like we did before and this time I’ll call it Order ID. For the next field, I want to tie this back to the customers table. Once again, this is one of the big benefits of databases. You can relate information, so you could connect one table to another table. So here I’ll click on tab and for this field I want it to be a number data type. I’ll click on number and now I can type in a field name. For this one, I’m going to type in Customer ID. We’re going to connect based on that primary key from the previous table. I want to add a few more fields. For this one, I want to include the order date and a date is the date and time. I’ll select that as the data type and here I’ll type in order date. I’ll add another field for cookies ordered and this is going to be a number data type. Here I’ll call it cookies ordered. I’ll add another field for the revenue. Here I’ll click over here, and this is going to be a currency type. Next, I’ll type in revenue. I’ll add one more field called order filled. This is basically so our warehouse staff can go in and indicate whether the order has been fulfilled or if it’s unfulfilled and they still have to package it and ship it. Here, I’ll click on this field and right down here there’s the option for yes, no. So, if they say yes, it’s already been shipped. If not, it hasn’t, and I’ll call this field order filled. Lastly, I want to add one more field, however this one is going to be a little bit different. This is going to contain the revenue per cookie, and we have all of the information stored within here to be able to calculate that. Here, I’ll have the revenue and I’ll also have the number of cookies ordered. So right here let’s click on this dropdown where we can select the data type and this is going to be a calculated field. We’ll see in a moment what this does. Right down here, let’s go down to calculated field and revenue per cookie, that’s going to be a number, so let’s select the number data type. This opens up an expression builder in Access, and you can build all types of different expressions. One way to think of it is it’s kind of like entering in a formula. Now once again, I want to calculate the revenue per cookie. So down here I see some of the expression categories, I’ll take the revenue, and when I double click on that, you see it inserts revenue here and I want to divide by the cookies ordered. So, I’ll answer the divide sign, and then right down here, I’ll click on cookies ordered. So, I’m going to take the revenue divided by the cookies ordered and that looks good. Next, let’s click on OK. This drops me back into the table view and now I can type in a name for the field. For this one, I’ll type in revenue per cookie. I can now fill in some order details. So over here for the first customer, this will be Kevin who orders some cookies. Now if you remember back on the customers table, customer ID one is for Kevin. So right back here in my order table, I’ll type in customer ID number one. Right here, I’ll enter in the order date. I entered in an order date and here I can type in cookies ordered. Now if you know Kevin as a customer, he really loves cookies, so we’re going to put down an order for 50 cookies, and these were some pretty premium cookies. They’re about $2.00 per cookies, so I’ll enter revenue of $100. Now that I have that entered in, one thing you’ll see is it automatically calculates the revenue per cookie. Right over here, the order hasn’t been filled yet, so I’ll leave that checkbox unchecked. Now that we’ve finished filling out the basic structure of this table. Once again, if we want to rename it, we can go down here and click into the design view and then we’ll be able to rename this. Alternatively, I can also close this table and it’ll ask me if I want to save it. I’ll click on yes, and here I can now give it a name. I’ll call this table orders. I’ve now finished saving my orders table and over on the left-hand side you’ll see that I have two tables now. I can go up here and I can close the customers table and now all of my tables are gone. But don’t worry, we can easily get them back. Over on the left-and side, I can click on customers, and I can click on orders and that brings those tables back into view. I now want to go through and add some additional sample orders. So, I’ll go through and maybe type in five or six different orders. I’ve now gone through, and I filled out a whole bunch of sample data. Now one of the great things about creating a database, here you’ll see with the customer ID I don’t have to repeat the customer information. I simply have to reference that ID and then for this order I can fetch all of the customer information. However, first off, I have to connect this customer ID to the customer table over here. How do we do that? So, up at the top, let’s go up to database tools on the ribbon. Once we click on that, there’s an option for relationships. Let’s click on this. Within relationships, I can define how different tables relate to one another and over on the right-hand side, I see all of my tables. I have two tables. I’ll click on customers, and I’ll pull that into this view. Next, let’s go over to orders, click on that, and pull that over as well. So here now I see my two tables and you can see all of the fields in those two different tables. Right up here you can see a key icon and that indicates what the primary key is in that table. Now here I have the customer ID in my orders table, and I also have the customer ID in my customers table. Now I can simply click on the customer ID, and I’ll drag that over to the customer ID over in the orders table. That opens up a prompt where I can edit the relationship. So here I’m saying that this value or this field in the customer table is the same as this field in the orders table. So right now I’m saying that these two values or fields are the same. Next, I’ll click on create and you’ll see now that there’s a connection between these two. So, this is how Access knows how these two tables relate to one another. Once we’re all done with this, let’s go to the top and click on close. This opens up a prompt to save the relationships. I’ll click on yes. Along with manually entering in data into your database, you can also import it from other sources. Right up here on the ribbon, there’s the option for external data. When we click on this, over on the left-hand side, you can see all the different places that you can bring data in from. For example, you can bring it in from a file, from a database, from different online services. So, there are lots of different ways that you can get data into Microsoft Access. So far, we’ve been entering data directly into the table view. But let’s say you want to have other people in your organization come in and add data. This might not be the most user-friendly view. Instead, you might want to create a form that makes that easier. Over on the left-hand side, I’ll select the customer table. Right up here, let’s click on create on the ribbon, and right here in the middle, there’s a section for forms. This will help us create a form for data entry and you could also use it to review the different records that are in your tables. There’s a form wizard that’ll help you through the process. You could also start from blank. There’s a form designer, or you could simply create a form. This is going to be the easiest way to create a pretty effective form, so I’ll click on form right here. This now drops me into a new form and down below I can see a sample of what the form looks like. So, this will be a lot easier for others to come into and start adding information, and they can also use it to review information. Now, because we connected the customer table to the orders table, you’ll see here all of the customer information shows up and down below it has all of the related order information. So here, if I jump through the different records, here I can go to the next record. Here I see Sheryl as my second customer, and she has two orders associated with her. Right up on top, I can design what my form looks like, so I can choose different themes. I can choose different colors. I can add different controls to my form. I can even add a logo if I wanted to personalize it with the Kevin Cookie Company logo. Down in the bottom right-hand corner, just like we could do in the table view, here I can launch the design view, and within the design view, I can modify what this form looks like. Right up here there’s a form header. There are also details, and here I see a footer. I can take these different elements and I can move them around. I could design the form how I want it to look. Over on the left-hand side right down here, I could also launch the form view. If you’re going to have people in your organization going through and filling out forms, this will be likely the view that they see. Here they’ll see all the customer information, but they won’t be able to modify the form. To add a new customer record, you simply go down to the bottom and you can click on this icon to add a new blank record. When you click on that, you can then go through and you can fill out the form and right down here someone could go in and add some additional order IDs. So, this makes it really easy to get new data into your database without having to see the tables and all of the details of the database. Once you’re all done customizing your form, we can close out this form. Right up here, let’s click on the X icon and I want to save this form. So, I’ll click on yes and then you can give it a name. I’ll call this order form. Once you’re done typing in the name, click on OK. Over on the left-hand side now, you’ll see that it added a new category. So, we have our tables and now we have a new form. I can double click on order form, and this will open up that form again. In the introduction, I mentioned that much of the power of databases comes from being able to run queries, and in a moment, I’ll show you how you can run and also write your very own query. To create a query, let’s go up to the top ribbon and click on create. Within create, right here near the middle, there are two different options for queries. You can use the query wizard, and there’s also something called query design. We’re going to use query design. You’d be amazed at how many advanced queries I’ve written using query design. Let’s click into this. This opens up the query designer and I want to write a pretty basic query that shows me all the orders that haven’t been filled yet. I need to provide this to the warehouse crew so they can send out the orders. Now, right here in the designer, right now we just have a blank slate. How do we start building out a query? And once again, this is just a drag and drop way of building queries, so it’s extremely easy. In a moment, I’ll show you what the SQL looks like to actually run the query, but we’re just going to start out with this GUI or this graphical user interface first. Over on the right-hand side, you see this pane that’s called add tables and we want to query against the data that’s in these tables. So, I’ll take customers first and I’ll drag it out into the view. Here I’ll expand the rectangle just so we can see all of the details. Right next to it, I also have orders. Let’s click on that and let’s pull that in as well. Here I’ll also adjust the size of this rectangle. In an earlier step we went through, and we defined a relationship between this customer ID and this customer ID. So, these two tables are connected. So, I could do things like for a specific customer show me all of the orders because these two tables are related. Now, once again, I want to show all orders that haven’t yet been filled, and for this I’m going to send it to the warehouse crew. And so, they’re going to need stuff like, well, what is the customer’s name, and what is their address where they should send the order. So over here I want to pull in some information for this query. I want to show the first name, so I’ll double click on first name. Here you see that first name shows up down below. I also want the last name. I want the customers address, the city, the state, and also the zip and the country. They’re going to need all of this information to send cookies to this customer. Now over on the right-hand side, I have some additional information and the warehouse crew is going to have to know, well what was the order date. They also need to know how many cookies were ordered, but they don’t need to know stuff like the customer ID or the order ID. That’s not really essential for what they’re doing so I won’t select these. Right down here there’s also the option that says order filled. Let’s click on that one. This is all going to run a query and it’ll give me back all of this information. But once again, I only want to show the orders that haven’t yet been filled. You’ll see down here, it shows the field, it shows the table it’s coming from, here I can sort it. I could also decide whether I want to show it or if I don’t want to show it and right down here, I can define criteria. So, this is kind of like or basically it’s a filter for one of these fields. Over on the far right hand side, I see order filled, and for this one I’m going to enter an equal sign and then type in no. So, this means that the order hasn’t been filled yet. So right now, I have my query in place. Down in the bottom right-hand corner, I can click on the datasheet view. Let’s click on this to see what the results are. When I click into the datasheet view, look at that. That’s our first query, so here it returns all of the different fields that I selected, and it also only returns all of the options where the order hasn’t been filled yet. Now one of the really neat things is here now I’m merging together customer information so data from the customers table together with data from the orders table so it brings it all together in one view and that’s what I can pull off using a query. Now, so far, we just used a graphical user interface to pull off this query and it’s pretty impressive what you can do, but you might want to get your hands dirty and write a sequel query. Down in the bottom right-hand corner, we can see the SQL query that powered this. When I click on SQL, right here, I can see the actual SQL query. So right up here, we’re selecting all of these different fields, we use a select statement, and then we call out, well, hey, I want the first name, I want the last name, and we go through, and we identify all of the different fields that we want. Then next we’re saying where it’s coming from, so we’re pulling it from the customers table and then we’re going to join that with the orders table and we’re making that connection on the customer ID. And then down below this is where we have our criteria. So, we’re saying where order and orders filled equals no. So, this is the actual SQL query so if you want to get your hands dirty, and especially as you want to start running some more advanced queries, you can start experimenting with this. Now the really neat thing is you can use this designer to pull together some really impressive queries and then if you want to see how it works behind the scenes, you can click into SQL and you can see what the actual query is. Now that we’ve finished entering in our query, let’s go up here and let’s close out this view, and I want to save this query, so I’ll click on yes, and we’re going to call this unfilled orders. Once you’re done typing in the name, click on OK. Over on the left-hand side, you’ll see a new category now for queries and this shows our unfilled orders. So, if you want to quickly see all unfilled orders again, you can simply double click on that and right now I have a view of all the unfilled orders. Now the really neat thing is as you go in and you add, let’s say, additional customers and additional orders, if you have any unfilled orders and you run your query, you’ll immediately see them on this page. So, let’s say I have warehouse staff and they always need to know what orders have we not finished yet. They can simply run this query and they’ll immediately see all the orders that need their attention. So, it’s pretty powerful stuff. Lastly, I want to show you how you can create a report. So, let’s say that maybe management or someone else in the organization needs to see a summary of data, or maybe for the warehouse crew instead of having them come into this Access database, and run the query, maybe every morning I want to print out a sheet of all the orders that they have to fulfill and I can use a report to do that. To create a report, just like we created a query, let’s go to the top ribbon and click on create. Over on the right-hand side, there’s a section for reports and we can click right here to create a report and there are a few different options. You could use a report wizard. You could use a report designer. You could even start with a blank report. So, depending on how much you want to customize it, you have different options here. Now I want to create a report that shows all the unfilled orders. So, I’ll make sure to select this query over on the left-hand side. If, say, you wanted to create a report of all of your customers or all of your orders, you can select those tables and then click on report and that’ll create a report of those items. Here, with unfilled orders selected, I’ll click on report. This now generates a report with all of the unfilled orders. So here I see all of the information that I selected in the query, and it’s all formatted in a very nice way, and here I can go over and I can see all of the details. Right up here, I can format the report however I want. I could go through. I can choose themes. I can choose colors, fonts. Even down here, I can preview what it will look like when I print it out. Here I can see a report view and here I can access the report designer. So, I can go through and design specifically how I want this report to look. So, I have quite a bit of customization. Once you’re all done configuring your report, you can close it out, and then you can save this report. Once you save it, it’ll add a new category over on the left-hand side, and here now I see the report here. So once again, I can very quickly navigate between my tables, my queries, my forms, and my reports, and just like that, we have now gone through all of the core functionality of Microsoft Access. This gives you all of the basics to start building out your own databases, to run queries, to create forms, and even to run reports. Of course, there’s a lot that you can customize. There’s a lot of advanced functionality, but this gives you all of the basics. The last item I want to show you is how you can save your database. To save, up in the top left-hand corner, click on the file menu and then go down to save as, and here you can save it as an Access database, and just like that, we’ve now created our very first database and we’ve saved the database. So, congratulations, you are now proficient in the fundamentals of designing databases. All right, well, that’s how you can get started using Microsoft Access. If you found this video helpful, please give it thumbs up. To see more videos like this in the future, make sure to hit that subscribe button. Also, if you want to see me cover any other topics in the future, leave a note down below. That is, after all, where this video idea came from. All right, well, that’s all I have for you today. I hope you enjoyed it, and as always, I hope to see you next time. Bye.