How to use Microsoft Access - Beginner Tutorial

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 this 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 million 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 of 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 could 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 midsize 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 mongodb 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 okay 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 file name 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 table 1 and we're going to start building out table 1 first and like i said i want to track customers and orders for the kevin cookie company so we'll use table one to track the customer information so we're gonna 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 will 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 email 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's going to be notes so let's say maybe a sales person for the kevin cookie company comes in they add a customer and maybe you want to include things like the customer's kids names there's nothing like remembering the customer kids 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 could 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 email 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 table one 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 of 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 anytime we 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 and 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 a link to this database in 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 a 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 drop down 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 enter the divide side and then right down here i'll click on cookies ordered so i'm going to take the revenue divided by the cookies ordered 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 is a customer he really loves cookies so we're gonna put down an order for 50 cookies and these were some pretty premium cookies they're about two dollars per cookie so i'll enter revenue of a hundred dollars 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 check box unchecked now that we 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 could go up here and i could 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 hand 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 the customer information however first off i have to connect this customer id to the customer table over here how do we do that well 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 is a section for forms this will help us create a form for data entry and you can also use it to review the different records that are in your tables there's a form wizard that will help you through the process you could also start from blank there's a forum 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 could go to the next record here i see cheryl 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 id 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 could 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 of 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 this 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 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 customer's 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 now 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 sql 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 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 the 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 order 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 of 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 of 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 will create a report of those items here with unfilled order 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 could choose themes i could choose colors fonts even down here i can preview what it'll look like when i print it out here i can see a report view and here i can access the report designer so i could 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 forums 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 a 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 alright well that's all i have for you today i hope you enjoyed and as always i hope to see you next time bye you

Channel: Kevin Stratvert

Views: 706,007

Keywords: kevin stratvert, access, microsoft access, access tutorial, tutorial, learn access, ms access, database, data base, db, how to, learn, excel, microsoft, ms, help, how to use access, learn ms access, learn microsoft access, stratvert, kevin, table, query, sql, office, microsoft 365, office 365, access 2019, 2019

Id: ubmwp8kbfPc

Length: 31min 6sec (1866 seconds)

Published: Thu Feb 25 2021