How to use Microsoft Access – Beginner Tutorial

How to use Microsoft Access – Beginner Tutorial
Spread the love

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 mid-size 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 my    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 one and we’re    going to start building out table one    first and like i said i want to track    customers and orders for the kevin    cookie company so we’ll use table 1 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 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 of 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 name is 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’s 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’ll    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 can 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 can 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    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 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    [Music]    you    

admin

Leave a Reply

Your email address will not be published. Required fields are marked *