Access 2019 Beginner Tutorial

hi everyone i'm trish connor cato and i'll be presenting microsoft access 2019 this video course is for beginner through experience level users looking to sharpen their skills and increase efficiency in microsoft access access is microsoft's relational database application used to organize and store data access also has reporting capabilities so what will you learn we start with navigating a completed database while exploring the concept of relational databases and about the objects they contain including how data is related between tables we'll move on to learning the access interface so you become comfortable in the working environment then we dive into how to plan design and create database tables we'll continue by importing table data from excel as well as manually entering table data sorting and filtering techniques will be explored before we move on to exploring table relationships which are the key to a successful relational database we will create database queries forms and reports we'll spend time on learning how table relationships affect queries and how to automate queries and how to create action queries our advanced training starts with advanced report and form design techniques we'll learn how to create switchboards write macros and use access visual basic for applications also known as vba for complex automation we'll end by covering database security and customization options this course has six modules on the slide deck on my screen you can see the lessons that are contained in module one feel free at any point to pause the video so you can study the screen module one lesson one is an introduction into how databases operate we'll get started by exploring a relational database that you will upload into access once we get into the application we'll go over the components of the database known as the database objects and begin to explore how data is related between tables we're going to use several files in this module they can be found in the video description below the files are listed on the slide feel free again to pause the video go ahead and grab those files and put them in a folder on your desktop once you have the files go ahead and open the access application once you launch access you're on the welcome screen we're going to use a pretty popular freeware database to explore the different components of a relational database so on the left side of this screen you're going to click on the new icon and instead of scrolling through a bunch of database templates we're going to search for it so in the search bar you're going to just type northwind and press enter you're going to click on the northwind icon one time and at this point it's going to give it a generic database name we're going to swap that name and just call it northwind more descriptive and then you're going to click that little open file folder to the right of where you typed in northwind so you can navigate to the working directory where you saved all the files for this module go ahead and navigate to that directory now after navigating click ok and it will bring you back to this screen where you're going to click on the create button it'll tell you that it's preparing the template for your use and the first thing i'm going to point out is the obvious you have this big screen it's known as the startup screen that's showing it's telling you to use this sample database you're going to look at that security warning the yellow band and you're going to click on enable content once you do that it allows you access into the database it this particular database is very sophisticated much more sophisticated than the one that we're going to start creating from scratch so it has a login dialog go ahead and just click on login and now you're in the northwind database relational databases are effective because they relate separate database objects so we're going to do a tour of the access interface in just a little bit but for right now i want to focus on the objects that create a relational database so on the left side of your screen right now it's the area is known as the navigation pane and it's collapsed so if you click that double arrow it will expand the navigation pane we're looking at this is the northwind database we're looking at a customized navigation pane we want to switch it to the default and you'll learn more about the navigation pane a little bit later in the course so to switch it to the default layout we're going to do the drop down arrow when you hover over it it says northwind traders and in that list up top on the upper half of the list you're going to click on object type and so now and i'm going to expand my navigation pane if i put my mouse pointer in between it and the current form that's on your screen i'll see that i get a double arrow and i can expand it so this is showing all of the access objects that make up this relational database we have tables queries forms reports macros and modules i'm going to just bring up a slide to explain what these different objects do we'll look at each of these objects in just a moment but it's important for you to know what object does what tables are the only objects that store your data in a relational database you use queries to get information about your data you can ask questions of your data you can use forms for a variety of purposes they can be used for data entry or to display information to the user when we first got into the access database the northwind database it was a form that displayed that told us to go ahead and click enable content your database reports can be distributed macros in access just like they do in other office programs automate tasks and the modules in access automate tasks using visual basic for application programming language we're going to take a look at examples of each of these objects in the northwind database now we're going to use the navigation pane on the left side to explore these objects and see how they work so i'm going to just expand tables notice the drop down arrow to the right of it i'm going to click on it and if you scroll through this list you'll see all of the tables that are in this northwind database notice the icons kinda looks like an excel spreadsheet let's double click customers to open that table in what's known as datasheet view tables have two different views which you'll learn as we go through the course in datasheet view you can see that it looks very similar to an excel spreadsheet where you have columns of data each column has a column heading the name of the column as you can see on the screen and in access databases the columns are known as fields and then you have rows of data a row of data is known as a record so the first row of data could be said to be anabetics record of data you'll notice all of the fields so the company last name first name so on and so forth you'll learn about how to navigate in tables when we start building our own database from scratch in the meantime what i'm going to do you'll notice the tab right above the company field and it says customers i'm going to right click on that tab and i'm going to choose close so now i'm just back to the forum when we went into the database once we got past the login screen this is the form that displays remember tables are the only database objects that store your data i'm going to collapse tables now and expand queries if you want to expand your navigation pane so you can see the full query name feel free to do so and the query we're going to explore is product category sales by date if we double click it it opens it looks very similar to a table of data but it's actually the results of the query so this particular query product category sales by date is showing the order date the sum of the quantity and the category queries have multiple views tables only have two queries have three and we'll explore those views as we go through the course right now we're going to close the product category sales by date query by right clicking its tab and choosing close and then i'm going to collapse queries go ahead and close that form that's on the screen it's called the home form now if you don't want to use the tab to close it all the way to the right you'll see a x and you can use that x to close any database object that you want to get off of your screen i'm typically in the habit of just right clicking the tab so either way you want to use it go ahead and close that form and then expand forms in the navigation pane so the name of that form was the home form it's the form that shows up on the screen after you log in go ahead and double click home in the forms list and it opens up the form again we're going to collapse forms and expand reports in your navigation pane we have a report called top 10 biggest orders you can double click on it and you can see it and so this is something that can be distributed you can distribute the report in a variety of different ways it's readable user friendly and this one is just happens to be showing the top 10 biggest orders out of all the orders in the database go ahead and close that report and by the way reports actually have four different views and you'll learn those views as we go through the course and i'm going to collapse reports in the navigation pane let's expand macros so macros give you the ability to automate tasks if you've ever recorded a macro in excel or you've ever written one using visual basic it's a little bit different in excel in access you actually build the macro it's not a recording of what you're doing in the application so there is a macro called autoexec in the northwind database by the way you can only have one auto exact macro in a database and if you want to take a look at what the macro is doing you can right click on it on auto exact and go to design view so what it's doing is when we first got hold of the northwind database and we had that opening screen that said enable click on enable content the reason why that came up is because the current project is not trusted okay so if the current project is not trusted and you'll learn about macros later in the course so this may look very confusing to you if the current project is not trusted then it opens that form the startup screen and that's the form that tells you to enable content if the current project is trusted meaning the northwind database then it opens the login dialog and so in our case it's not trusted we haven't saved it to a trusted location is what that's talking about and you'll learn more about that later in the course go ahead and close the auto exec macro and this time just double click it and that runs it so it's going to bring in the login dialog and when we log in it's going to take us back to this home screen because at this point we've already accessed it we've saved it and it's considering it to be trusted we can collapse macros now and then modules a little bit more intense because they deal with writing visual basic for application code so right click on your inventory module and go to design view and it takes you into the visual basic for applications editor window where you're seeing all of this vba code you'll learn a little bit more about this in the course we're going to go ahead and close that editor window and we can collapse modules now in a relational database the data is stored in tables and the tables have relationships between the differing data sets we're going to examine these relationships in a little bit more detail so tables have the relationships between them and then tables also have related objects like queries forms reports macros modules so in order to see how tables are related to each other we're going to go up to the ribbon and we're going to go to the database tools tab on the ribbon when you go to database tools tab you'll see on there that you have the opportunity to access the relationships window and that's where we want to go right now it looks like a hot mess in here especially if you're not used to seeing a relationships window before so i'm gonna just make this a little bit cleaner what i'm gonna do is i'm gonna just move this employee's table tile over a little bit and move orders over so i can clearly see the relationship between orders and customers which is indicated by this line between the two tables it's known as a join line notice on the customer side of the join line it has the number one and on the order side of the join line it has what appears to be the infinity symbol that is indicative of a one to many relationship in plain english you could say that one customer has many orders in order to relate a table to another in a relational database there has to be a common field between the two tables good database design would say that the common field should be called the same exact thing between the two tables but sometimes you won't see that makes it easier if they're named the same so access doesn't care what you name the field but it just makes it easier on the person who is creating the database if they have a common name now this is an instance where the name is not common but we can deduce what it is in the customers table it's using the id field the id field is known as a primary key field you'll learn more about this later in the course when we create our database and a primary key field is used to make every record in the table unique so even if you had two customers with the name james brown their ids would be different noting that they're two separate individuals so the id field in the customers table is the primary key it's also what's used to relate it to the orders table in this case in the orders table it is called the customer id field so one customer id can be on many orders in the orders table that is the common field that makes the relationship possible you'll learn more about different relationship types what you can do with the joins in a later part of this course i'm going to go ahead and click the close button on the ribbon to get out of the relationships window it asks me if i want to save my changes to the layout i'm going to say no i move some table tiles around so it won't save those changes and now we're back to the home form that came up earlier this is a good time to start exploring the access interface like all the other microsoft products access 2019 has the microsoft ribbon and on the home tab you can see you have your traditional clipboard group a sort and filter group and we'll be using the ribbon extensively as we go through this course you have the create tab on the ribbon when you're going to create stuff you're going to become intimately acquainted with this tab as well as the external data tab if you want to bring stuff into or send stuff out of access this is the tab you're going to be working from we have our database tools tab where we just went to get to the relationships window you'll learn a little bit later about some of the other options here you can see that they have a macro section and they also have an analyze section which we'll visit later in the course you have your help tab on the ribbon another way of getting help is using the light bulb over here tell me what you want to do and then because we're in a form the home form you have the contextual ribbon tab under the banner form tools you have form data sheet so if we wanted to do some formatting on this form or look at the property sheet so on and so forth that would be the tab that we would be using again that tab would only be on your ribbon if you absolutely have a form open so you also have some other things at the bottom if you look at the bottom of the form in particular the northwind traders form you have a record navigation section and that can be used if you have multiple records and you want to go through them in a form tables have these most objects have these navigation areas so you'll get some extensive hands-on experience with that as well and then we have the status bar now for this form it's all the way at the bottom of your screen so the status bar depending on what object you're in can display different information and also depending on what view of the object you're in so this is giving the end user additional information about this form it says double click to go to details down in the status bar so if i'm on that form and i have this first active order selected i should be able to double click to get to details for that particular record you'll also learn how to populate the status bar during this course to the right of your status bar you have your different view buttons and you'll learn about different views again as we go through these different objects when we create our own database now we're going to get into creating a new database from scratch the database that we're going to create is not going to be as extensive as the northwind database that we've been viewing due to our time constraints but you do have the northwind database and you'll learn how to examine its objects so that you can recreate them if the need arises we're going to start by going over the planning steps to create a new database and then move on to actually creating the different objects when it comes to planning a new database you have to be clear on its intent you would want to confer with colleagues to figure out is a database even really needed and then what would be its business purpose who will use it and how will the end users be trained that's something that's all often overlooked by the person building the database typically they're the person that's going to train the end users on how to use it there are a few naming conventions that you should become familiar with we didn't see this the actual um prefix in the northwind database but when we build our database we're going to use these object prefixes it helps to identify them so the way that you name a table there are some examples up there tbl customers the name of the table is usually pluralized tbl is the prefix that we use for tables to identify them in a database if the table name has multiple words you'll see the example of tbo order details with the o and the d capitalize all much together this is a standard convention that's used for naming database objects so i'll direct you as we're creating our database but you use the prefix qry for queries frm for forms rpt for reports mcr for macros and mod for modules the first thing we're going to do is we're going to go ahead and use the file tab on the ribbon to close the northwind database so when you do file you'll see the close option right there and now you're in access but you don't have a database open we're going to start a new one from scratch so we're going to go back to file on the ribbon and we're going to click on new again and this time we're going to click once on blank database and we're going to name this database office supplies and we're going to put it in our working directory so remember to use that file folder to the right to navigate to the directory that you want to save the database in go ahead and save the database as office supplies and click create when you create a database a new blank database it gives you one table it names it table one and it opens it in datasheet view database tables require a separate planning process which we're going to review right now so we'll go over the planning steps for the tables create the tables in design view and then you'll learn how to import data into the tables from excel think of your database tables as a multi-subject planner for one topic in our case we're creating an office supplies database so that's our topic and in the planner you have related subjects which are your tables you use one table per one subject typically you'd want to plan your tables on paper first conferring with colleagues making sure what you're calling the table what you're going to name it and what you're going to name the fields in the table it's important to make sure that they're understandable to the end users and then you also are going to want to break down your data to the lowest form so for example if you have a customer name field if you want to be able to sort that field you would want to split it into multiple fields perhaps customer first customer last customer middle if you're capturing that you don't want to put it all in one field because then it's hard to sort it so address fields you typically will have address one address two city state and zip not just one ad address field so on this slide i have a sample plan for the customer table that we're going to build giving you all the field names that we're going to be using in that table so we need a unique identifier field a primary key field and we're going to call it customer id so each customer record is a unique record because even if everything else is the same in the record the customer id will be different we're going to have company name address 1 address 2 so on and so forth in this table that we're going to create as mentioned when we created from a blank database it gave us one table and it put it in datasheet view we're going to right click on that table one tab and we're going to go to design view i typically create my new tables in design view and you'll see why in just a moment at this point it wants us to save this table and give it a name so we're going to use our object prefix tbl all lowercase and then customers plural with a capital c and click ok so now it switched us to design view if you notice in your navigation pane as well as on the table tab it's updated the name from table 1. now in design view you can name your fields that are going to be in the table and you can assign a data type when you name the fields in the table the description which is optional if you're in the table in datasheet view that description will show in the status bar so we saw a description for form that showed in the status bar in the northwind database earlier so the blank table only gives you one field it's called id and it set it to an auto number field and it also made it the primary key field which is indicated by the little key icon in the row header to the right of id so you'll see that and also on the ribbon you'll see that the primary key button is shaded meaning it's active for that field so we want to change the name of the field we don't just want it to be called id we're going to just put the word customer in front of it and an auto number field means that the end user will not be typing in the customer id it will start with the number one for the first customer then it will go to number two for the second customer access will control that so we're going to leave that data type at auto number we're going to go to the next field name entry the next field name is company name typically you won't see spaces in the field names just initial capital letters for every word and when you tab over to the data type it defaults to short text which we're going to leave it on the next field that we're going to input for this customers table we're going to do address one that's also going to be short text then we'll do address two then we're going to have city also short text the next one would be state and zip now we're leaving zip short text we're not going to be doing any calculations on the zip code so they can be a text field if you're going to be doing calculations on a numeric field then you would want to make it some type of a number field but in our case we're good with just leaving it as a text field the next field is going to be phone and we're leaving all of these at short text for right now we have email but then the email field when you tab over the data type you can either do the drop down which i'm going to do and you can see all the different data types for an email we'd want it to be a hyperlink so from within access you can click on the hyperlink and have it generate your email in your email program so email is the hyperlink data type we have two more fields we're going to do active and this one we're going to do the data type drop down and we're going to choose yes no or you could have just typed the letter y over there and it would automatically populate it with yes no so yes no when we take this table to data sheet view presents as a check box if it's checked it means yes if it's not checked it means no and we have our last field which is employee and we're going to leave that as a short text field now you'll notice on the bottom half of the screen you have a field properties area on the bottom half of the screen and we're going to change a couple of properties for just a couple of these fields for our customers table so what we're going to do on the upper half where you've listed all your field names and their data types right we're going to click on the state field and now that's the active field and so the field properties are for the state field for a short text field the default field size is 255 characters well we want to force the end users to make sure the states are only two characters so we're going to change the field size property to two and we're just going to tab out of that field size area now you're in the format area in the format area we also wanted to force it to uppercase so regardless of how the end user types it in it will only be two characters and we want it to be uppercase so we're going to do the greater than symbol in the format field property which is how you force something to be upper case regardless of how it's typed in and then for the phone field we're going to do something so on the upper half we're going to click on the phone field in the field list on the upper portion of the screen and you know people type phone numbers a host of different ways some people put the area code in parentheses some people just don't and they put a dash between the area code in the first three digits so we want consistency consistency is key in a database we don't want any users typing phone numbers any way that they want to we want it to be a consistent format to be able to do that we're going to put what's called an input mask on the phone field so in the field properties you're going to click in the box that says input mask and then all the way over to the right i always use the wizard to do this instead of typing an input mask myself because it could be tricky proposition so all the way over to the right of the input mask field you'll see that ellipsis button and you're going to click that and that will take you into the input mask wizard since we've made changes to this table we have to save it first so just go ahead and click yes and then it takes you into the input mask wizard there's already an input mask for phone number that's the first on the list it's selected and we want it in that format so we're going to do next and now the only thing here is we want to require the area code right now you'll notice that the area code is populated with nines which means it's optional so we're going to change those three nines to zero and after you do that you're going to get rid of and it's kind of like an over type mode so you'll see it just overwriting the nines with zeros and then we want to get rid of the exclamation point before the opening parenthesis when the area code is optional we want the we want it to fill from the right of the phone number so you don't get a situation where it starts the phone number where the area code should be and that's what the exclamation point is controlling now that we made those numbers for the area code required we can get rid of that exclamation point because we won't be needing it to be there anymore so you want to delete that we're not going to change the placeholder character you'll see what that looks like when we actually complete this table and start entering records into it and we're going to just do next so the last question is how do you want to store the data this only applies to if you're going to export the data out of access so if you export a phone number without the symbols it'll just be all numbers none of the symbols i always like to export it with the symbols in the mask so i'm going to make that choice here and then i'm going to do next and i'm going to finish so that's set up if you look at that input mask if you had manually typed it you would have had to type it the way that it is showing in the input mask field i think using the wizard is far easier now we're going to create another table the employees table so the first thing i'm going to do is i'm going to use the tab and i'm going to right click on it and close my customers table so notice it's asking me do i want to save changes to the design of the customers table since we were forced to save it we made several design changes including some properties so we're going to go ahead and say yes to that and it just shows in our navigation pane now we want to create another table so we're going to go up to the create tab on the ribbon and we want to do it in design view so in the tables group on the create tab you're going to go ahead and click on the table design button and it brings you into what's now called table one because we renamed that to tbl customers for the other one and it this one does not have a primary key or an id field at all you only get that when you start from a blank database that initial blank table that they give you is set up that way but any other tables you create you're going to have to set up your own primary key field in the table so access will notify you if you go to save a table without a primary key but it doesn't care whether you set one or not so you're going to have to be on top of that so we're going to do the employees table now and the first field name is going to be employee id and we're going to make that an auto number field again unless we already had existing employee ids that we wanted to use we can use auto number we only have three more fields for this table so the next field is going to be employee last and that's going to be short text notice that i'm mushing all the words together i'm not using any spaces in my field names then we have employee first and lastly we're going to have extension and again we're not doing any calculations using the numeric extension so we're going to leave that as a short text field now we have to save this table so right click on the tab and go to save and we're going to call it tbl capital e employees and click ok so this is where it tells us there is no primary key defined although a primary key isn't required it's highly recommended right you won't be able to create a relationship with another table do you want to create a primary key now i'm going to go ahead and say yes and it will just put it on the first field in the list now let's say we decided before getting that prompt that we wanted to do the primary key so what i'm going to do is i'm going to click on the employee id field and i'm going to click on primary key on the ribbon to remove it so to get it back i can do it by the same method just the opposite i can go click primary key on the ribbon or i can right click on it and choose primary key so that's how you can manually set it go ahead and save and close your employees table so we've manually created two tables in design view and now we're going to use the import data from excel feature to get the other tables that we're going to use in this database when you import from excel you have three different options you can import into a new table which we're going to do next you can append a copy of records into an existing table which is how we're going to partially populate the customers and employees tables that we've created or you can link to the data source by creating a link table which we'll cover in a later module for right now we're going to import our other table data using the import into new table feature one of the files that you receive from the video description is import into access and we're going to be utilizing that file to bring our other tables for our database in so in order to do that we're going to go to the external data tab on the ribbon and the first button is new data source and you're going to hover over from file and click on excel and then you're going to need to use the browse button over on the right to browse to your working directory and double click the import into excel file that's in your directory once the file name is populated you'll notice the bottom half of the screen you have your choices there import the source data into a new table in the current database that's the choice we're going to stick with right now in a little while you'll learn how to append a copy of the records to an existing table and later in the course you'll learn how to link to the data source by creating a link table so all we have to do is click ok on this screen and you'll see at the top of this import spreadsheet wizard it's showing the worksheets that are in that excel file so we've already built customers and employees tables we haven't populated them we're going to click on categories and you'll see sample data that's in that excel file and we're going to do next the first row does contain column headings it detects that that's your category id and category and so we're going to do next now up top you can give it some field options if you want up top i usually do this once the data is in access so i'm going to just do next on this screen and it's saying let access add primary key it defaults to that so now you have another id field which we don't want we're going to use category id as our primary key so we're going to select the option button choose my own primary key and it defaults to the first field which is category id and we're going to do next so import to table so again this is into a new table the only thing we have to do we want the table to be named categories but we want that object prefix of tbl in front of it and then we're going to just click finish we're not going to import from this particular table again so we don't have to save the import steps we could doesn't hurt anything so go ahead and check save import steps and we'll leave the default name you could also create an outlook task which we don't have to do here and we're going to do save import and so now we have our categories table now let's just take a second here and look at this if we double click tbl employees we don't have any records in it i'm going to close it and the same would be true for customers but if you double click tbl categories you have the records in the table because they were in the excel spreadsheet and i'm going to close that one so now we want to import other tables so this way let's use the saved import on the external data tab click on saved imports and there's our import into access it's already selected you can change the name of it at that point if you want and we're gonna do run at the bottom so this is going to want to overwrite the existing table categories we're going to say no here so we're going to just start it from scratch again i'm going to go ahead and close that go back to new data source from file excel you're going to browse again and navigate to your working directory and select the import into access excel file again once you do that you're going to go ahead and click the ok button and this time we're going to select order details so after order details we have two more tables i'm going to have you pause the video after we work through this one together and come back and repeat the same steps and bring in the orders table and the products table just remembering to put the tbl in front of the name so here's all the the information that's in that excel spreadsheet we're going to do next it contains column headings it's next again we're not doing anything on this screen so next again and you're going to choose your own primary key and we want the order detail id field the first field and next and you just got to put the tvl in front of the table name and you're going to do finish and we're not going to save the import steps so you can just close that now at this point i want you to pause the video go to new data source excel you know from file excel and grab those two other sheets so that they come in as new tables so now your navigation pane should be populated with the six tables we're going to be using you brought in orders and products now that we have our tables in access we're going to make some modifications to the products table so if you right click on the products table and you go to design view it opens in that view if you just double click on it it's opening in datasheet view so we have a product manual field name here and the data type is short text we actually have two product manuals that we want to use as attachments in the database so we're going to change short text to attachment data type just for the product manual field so it's letting me know because i bought this in from excel it's not going to let me set it to a different data type so i'm going to click ok on that and i'm going to delete that product manual field by right clicking on its row header and choosing delete row and i'm going to click yes to permanently delete it and then what i'm going to do i'm on a category id field and i'm going to use the insert key on my keyboard to create a new row above it right where product manual used to be and i'm going to name that field product manual and i'm going to give it the attachment data type and then i'm going to save that table and i'm going to right click on the tab again and i'm going to switch to data sheet view so you'll notice that you have the paper clip icon right it doesn't say the name of the field it just shows a paper clip icon for the name of the field when you make it into an attachment data type let's right click on the tab and go back to design view i don't want just a paper click to show so i'm clicking in the product manual field and down in the field properties the caption property will overwrite what that column heading says so there i'm going to put product manual all mushed together because the other field names are much together if i wanted the header in datasheet view to be different so say for product name i could caption that field product space name but we're not going to take the time to do that in this course we just want the product manual to say product manual instead of the paper clip icon so now i'm going to just save this table and then i'm gonna right click and go back to datasheet view and you can see that it now says product manual at the top for that field heading now there were two word documents in the video description that you should have already put in your working directory or if you need to pause and go get them that's fine and one of them is called super duper cash register description or instructions and the other is simple cash register instructions so what i'm going to do we're going to access them from within here and just like in excel like i want my product name field to be wider so i'm going to put my mouse between product name and price headings and double click that's very much like how you do it in excel and i'm going to go to the attachment field for the super duper cash register and in that field i'm going to right click and i'm going to choose manage attachments so now you're going to click on add and you're going to navigate to your working directory where you save the files from the video description and you're going to grab the super duper cash register word document once you do that you're going to click ok and notice now it has a one next to the paper clip in that record indicating that there is one attachment if you wanted to access that attachment you would right click right in that paper clip field again go back to manage attachments and you can see over on the right that you can open the attachment from in here or remove it you could save it with a different name if you had multiple attachment you could save all of them at the same time so we're going to just cancel out of there now we have another attachment and that's what a simple cash register record so i'm going to right click on the paper clip manage attachments click on add and grab the simple cash register word document and then just click ok so we've added the attachments we started by trying to change the data type of that field and it wouldn't let us mostly because it came in from excel and so we deleted that field and recreated it and gave it the attachment data type go ahead and close your products table and we did do layout changes so it's only going to ask for layout changes so we're going to select yes to save those changes now we're gonna populate our customers and employees tables partially from excel so we're going to be using that same import from access file and the same wizard that we used before so what i like to do especially if i'm bringing in data into multiple tables is in the navigation pane and you don't have to do this i like to click on the table that i'm getting ready to use just to keep myself on track makes no difference in access whatsoever whether it's clicked or not so on the external data tab of the ribbon i'm going to go back to new data source from file excel and i'm going to navigate to my working directory to that import into access excel file import into access is what you're browsing for this time on the lower half of the screen we're going to use the option button append a copy of the records to the table now this is always going to default to the first table in the list which is in alphabetical order i clicked on customers just to remind myself that that's the table that i want to import into so i'm going to do the drop down where it says tbl categories and select tbo customers and i'm gonna do okay similar screen it's showing you the sheets that are in excel you're gonna make sure you're on the customer sheet it's the first one in the workbook so it should already be selected there and we're gonna do next it dims out first row contains column headings because the column headings are already in the table we set up the column headings when we created it so that doesn't matter here and we're going to do next it's importing to tbl customers once you click finish you can double click your customers table in the navigation pane and you can see that those records have been imported i'm gonna go just go ahead and close the customers table but before i do that let me say this on a form it will show as a check box but that active field we made it a yes no data type so negative one in this instance means yes and a 0 would represent no so now i'm going to go ahead and close that table i'm going to have you follow the same steps to bring in information into the employees table so go ahead and pause the video to do that so when you're done you can open your employees table and you'll see that it's populated with 29 records i'm going to go ahead and close it so far we've populated our tables by importing data from excel into access now you're going to learn how to manually enter data directly into a table and pick up some data entry shortcuts and techniques along the way we'll be using the manual data entry word document that was in the video description so i have that document open on my screen right now we're going to actually enter two customers into the customers table and two employees into the employees table based on the information in this document so we're not going to use the yellow shaded entries at this time we'll use those later on in the course so just customer ids 17 and 18 and employee ids 30 and 31 are what we're going to enter so back in access i'm going to just double click the customers table to open it in datasheet view and after the last record customer id 16 you'll notice in the row underneath that customer id says new so we made our customer ids auto number fields which means we're prevented from typing one in so we're gonna click in the first blank company name field and we're gonna start typing our entry so the first company we're going to enter is called astro advertising and as soon as you start typing it notice it gives a customer id of 17 it's incrementing them by one and we're going to tab over to the address 1 field tabbing is the best way to work when you're entering data in tables the address is 17 main and then we're using st for street so you want to be consistent we bought a lot of this data in from excel in excel they're using st for street so we're going to repeat that theme here in access we don't have an address too so we're going to go over to the city field by tabbing and the city is houston and then tab over to the state field so remember when we designed this table we made the state field so it will only have two characters and they'll be forced to be upper case so try to type texas and after you type the second character it's not going to let you so backspace and just type tx and i'm doing it in lower case and when i tab you'll notice that it puts it in uppercase that was the greater than symbol in the format field property for state so now we're ready to enter the zip code and that is 77084 [Music] and we're going to tab and so the phone number field is where we put an input mask so it will format the phone numbers like the rest of them above it so we're just going to type in the raw numbers if you're training end users on how to use a database like this and you've done an input mask you need to tell them just type in the raw numbers no punctuations so we're gonna just type in seven one three as soon as we type seven you'll see the input mask it puts the parentheses around it it's flashing where the next field is you know that's available to enter in so we're going to just do 555 7633 and we're going to tab now we're at the email field it's astro houston.com and notice it's showing as a hyperlink because that's the data type that we set on the email field now astro advertising is not an active client so we're going to do excuse me it is an active client so we're going to put negative 1 in there for active on a form it will show as a check box so you won't have to worry about knowing whether it's negative one or zero and typically with a relational database you would want your data entry to be done on a form not necessarily in the table the table is the only thing that holds the data so you don't want a lot of people going into the table for data entry you'll learn how to create forms that they can use for data entry in a future lesson here so all we are missing now is it has employee numbers in here for this one we're going to just put in the employee name so the last name of brewster and we'll come back in it had the numbers in the spreadsheet or brought them in as by their id numbers so we can we can address that later what we're actually going to want to do later is have a drop down list so the end users can select the employee and not have to type it in which lends itself to more consistency now what i'm gonna do to save this record you don't have to do a save here you only have to save layout changes so if we were to go into design view and change a data type or do a field property those are the types of changes that need to be saved records that you're entering are saved when you navigate out of that row so i'm using my down arrow to go to the next row and that saved record with customer id 17. so now i'm in the company name field under astro advertising we have one more to enter here and it's waters are us kind of like toys r us the address is one waterfall lane and for lane they are actually spelling out the word lane and we're gonna go over to the city that's citrus heights tab to the state and it's ca for california and the zip code is 95610 our phone number six five five five forty three eleven we do not have an email for this customer and this customer is not active so we're gonna leave active on zero which means it's not active and therefore they don't have an employee attached to them so i'm gonna just use my down arrow and then i'm going to close the customers table now you're going to double click your employees table and using the same word document in the same method you're going to enter two employees at the bottom of this table go ahead and pause the video to do that just a quick note if you look down at the status bar right now it just tells you that you're in data sheet view let's switch so you should have added the employee ids 30 and 31 and we're gonna switch we're gonna right click on the tab and switch to design view for the description for the extension field just put in four digit extension and then switch back over to datasheet view and it's going to prompt you to save your changes that is a design change those are the changes that need to be saved so we're going to say yes and now you're back in datasheet view if you click in any of the extension fields you'll see in your status bar that it shows that description so we saw that for a form earlier it's by using that optional description field that information will show up in the status bar which could be helpful for the end user now that we have most of our data into the database tables you're going to go into how to rearrange your records by sorting them and there's some cool ways to sort and filter data in access and the filtering is especially cool a lot of these filtering things started in access and now are in excel as well so in addition to sorting you'll be hiding records by filtering i'm going to use the customers table for this this time go ahead and leave the employees table open you can have more than one object open at the same time i'm going to just double click tbl customers and notice now i have the two tabs one for employees and the active one is for customers so we're gonna get into sorting and filtering data here starting by sorting so we decide that we want to see our customer records sorted by state so in order to do that i mean it's a microsoft product so there's multiple ways of doing the same thing we could use the home tab of the ribbon it has a sort and filter group or i normally do it right at the column heading the field heading so where it says state i'm going to do the drop down and i'm going to just do sort a to z so i have all of my california customers grouped together and then idaho illinois so on and so forth so that is an easy way to sort if you look at the drop down arrow next to state now it has a thin up pointing arrow right next to it because that's showing that that field is sorted and specifically in ascending order so when you sort records it just rearranges them this was in order by customer id originally now it's sorted in ascending order by state and then we decide that we want to filter some records so when you're filtering records you'll only see the rows of interest it's a way of hiding some records and there's a whole bunch of different ways that you can filter as i mentioned so what we're going to do is we'll filter this in a specific fashion and we'll use several different ways of filtering so again you have that sort and filter group you can click on filter the filter button and because i was already in the state field it brings up what's almost a traditional filter screen from excel right so i am going to uncheck select all and just check ca for california and click ok so now i'm only seeing the california customers that's one way of filtering now a couple of visual cues for you to know that your table is filtered a couple of things first of all on the ribbon the toggle filter button in filter group will be shaded when you have a filtered record set the other way of seeing it is way down at the bottom of your screen right above the status bar you'll see that it says it's filtered if you want to get rid of the filter well you can click that toggle filter button on the ribbon and now you have your full list back it's still sorted in ascending order by state i'm going to show you another way of doing the same filter this time i'm going to just right click in any of the customer state fields that have ca for california so i'm just using the first customer there i'm right clicking in the state field and i'm going to choose from this list equals california that's another way of doing the same thing conversely now we want to clear that filter so you can go ahead and toggle filter again and then right click on california for that first record again and i'll show you the opposite choice does not equal california so when i click that one it shows me all the other states except california i think that's a pretty cool way of being able to filter by using the right click menu now we're going to toggle filter to clear that filter and i'm going to show you another way of filtering i'm just going to click on any field in that table and this is called filtering by form and access so in order to get to it you're going to go in the sort and filter group you're going to go ahead and click on that advanced button and choose filter by form so it brings up whatever the last filter was right so not equal to california is the last thing we did in that state field and we're going to just delete that right so filter by form it basically gives you a blank form that you can fill out what you're looking to see in that state drop down we're going to select or and then we're going to click on toggle filter so now we're filtered for oregon and that's kind of how that works if you toggle filter again then you have your full list and if you want to undo that sort we can sort by customer id field in ascending order to get it back to the way it originally was so i'm going to do the drop down arrow next to customer id and i'm going to choose sort smallest to largest and now we're back to the way we were this time i'm going to want to close both of these tables at the same time so i can right click on either table tab and choose close all so now that we've sorted the table the customers table that is considered a design change so we're going to say yes to save those changes and both tables are closed now we briefly toured the relationships window when we were reviewing the northwind database now we're going to get into relating table data so we're going to explore different relationship types you kind of got to know that in the background before you create relationships we're going to go back to the relationships window and we're going to create the relationships for our database there are three types of relationships that you can create in an access database the most common and it's the one that we saw in the north wind was the relationship between the customers table and the orders table that was a one-to-many relationship again one customer has many orders it is the most common relationship type and it's typically the default relationship type when you create a relationship the many-to-many relationship cannot be created naturally in access meaning there's no way you can take two tables and create a many-to-many relationship you have to have a third table and it's normally called a junction table something like that so really a many-to-many relationship is two different one-to-many relationships connected by a junction table so many products are on many orders many orders have many products that's kind of the english language version of a many-to-many relationship and then you have a one to one which is not common really certain situations require it so one department has one manager that would be the english language translation of that type of relationship we're going to create six relationships and all of them will be one to many now the slide that's on the screen right now just so you know this entire slide deck is also in the video description but you don't have to worry about grabbing it right now i will guide you through the relationships that we're going to create which are listed on this slide so in access we're going to go back up to the ribbon and we're going to click on database tools and in the relationships group we're going to click on relationships to bring back the relationships window so the thing is we don't have any tables in our relationships window at this point because we haven't created any relationships so the first thing that happens is the show table box comes up and categories tables already selected i'm going to click add at the bottom i'm going to click on the next table customers ad and select the next four tables one at a time and add them to the relationships window once you have them added go ahead and click the close button and you'll see the tiles representing each of the tables in your database now in the relationships window so one of the things i like to do when i'm working with table tiles and this is not needed i mean access doesn't care is i kind of like to have my tiles so i can see all of the fields in that particular table right so just so i don't have to scroll down i like to see all of the fields in a particular table so you can manually take a few moments and get it sized just so you can see the fields and not have a scroll bar in the table so in order to create a relationship you're just going to drag the common field from one table to the other so the first relationship we're going to create is between the orders table and the order details table so in the orders table the common field is order id i'm going to grab click and hold on the order id in the orders table tile and drag it and drop it on top of order id in the order details table tile and when you let go you get the edit relationships dialog box so at the top you can see the table is tbl orders and the field is order id and then you have tbl order details and order id so at this point if you look all the way at the bottom of this screen the relationship type is one to many now we'll do a deeper dive into relationships security in a later lesson but for right now we're gonna just click create and so i'm gonna just move my products table out of the way so i can drag orders over and i see the join line between the tables now what's different right now is it doesn't have a one and an infinity symbol like when we looked at this in the northwind database and you'll see when that actually happens when we revisit relationships a little bit later but right now it's a one-to-many relationship if i double-click the join line it brings me back into edit relationships and i'm going to just cancel that i can also right click on the join line to get back into edit relationships or to delete the relationship so we have our first relationship created now we want to one-to-many relationship between products and order details i'm going to just drag my products tile down here again the position of the tiles makes no difference to access and for this one the common field is product id so in the products table i'm going to click and hold on product id and drag it and drop it on the product id field in the order details table again it shows the table instances the common field and the relationship type at the bottom go ahead and click create the next one we're going to create is going to be between categories and products oh so i'm not going to move my categories instance it's not necessary and the common field is category id so in my categories table i'm going to just click and hold on category id and drag it on to category id in the products table another one-to-many relationship and i'm gonna create i think i misspoke i think i said we're gonna create like a whole bunch more but i i think i might have misspoke so we have categories and products related we have two more to do so we are going to relate the customers table to the orders table and the common field is customer id so i'm going to click and hold on it in the customers table drag it and drop it on to customer id in the orders table and create and we have one more and that's the relationship between employees and customers now this one is interesting so i said good database design should have the fields the common fields named the same right so in here in our customers table we have employee and in our employees table we have employee id it would be better if they were named the same thing but they don't necessarily have to be when i typically design a database i make sure those matching fields have the same name but this one i did this on purpose because i want you to see that it doesn't really matter so we're going to drag employee id from the employees table onto employee in the customers table and then click create so now we've created our relationships all of them are one-to-many which is the most common relationship type so the big question is why are we relating these tables well when we get to queries and even report design and stuff like that if we want to ask a question of the database and the data is stored in two separate tables the relationship between the table will allow us to pull that information into the query which you'll see when we get to queries we're going to go ahead and close our relationships window it will always ask you if you want to save the changes to the layout once you've been in here and done some work and you're going to say yes now that we have our table relationship set we're going to start creating some commonly used queries and we're going to do it two different ways in this lesson we're going to use what's called the simple query wizard which will guide you through the steps and then we'll create a query in design view before we get into our queries i just want to show you an impact in a table that happens as a result of creating a one-to-many relationship so let's open the orders table by double-clicking it and you'll notice right be after the row header before the order id you'll see a plus sign so we related this table to the order details table and one order may have many details so this is the one side of the relationship and that's why it has a plus sign so when you click the plus sign for order id 1 it shows you the order details for that particular order so you don't have to open the order details table and reference it by order id it's right there on the one side of the one-to-many relationship and i'm going to just collapse that and close the orders table now also with the customers table i'm going to open it when we input customers it had all numbers here at least on my screen and i since went and changed the numbers for the employee to their actual names so if you want to pause the video and take a moment and open your employees table and swap out the employees that are coming in by number if this pertains to you it happened on mine on my system may not have happened on yours but go ahead and replace them with the employee names that are showing on my screen once you're done with that go ahead and close the customers table and we're going to start creating our queries now first we're going to use what's called the simple query wizard so we're going to go to the create tab on the ribbon and in the queries group you'll see query wizard go ahead and click on it so you have four different types of query wizards and we will explore other types but for right now we're going to stick to the simple query wizard which is already selected so go ahead and click ok now when you do that it shows a list it says tables and queries right which fields do you want in your query you can choose from more than one table or query so we're going to just do a simple one we're going to use one table for for this query and you can choose you can base a query off of another query or a table we don't have any other tables and any other queries yet so when we do the drop down we just see our tables and we're gonna create a query for products so we're gonna base it off of tbl products and once you select that it'll show you all the available fields on the left hand side okay and because we added a product manual file and attachment you have all these other things they're not going to pertain to what we're doing right now but just we had the product manual feel but we had an attachment so it created more fields subfields to that product manual field for us so now what we're going to do is select the fields that we want to see in the query so we're going to double click product id or you could have clicked it once and done the right pointing arrow the single right pointing arrow the next field that we want is product name we also want the price field and the category id field so we're just gonna get those over to the right side under selected fields and we're going to click next at the bottom now when you get to this next screen would you like a detail or summary query we're going to leave it on detail where it shows every field of every record and we'll do next and what title do you want for your query well this is the title that actually shows in the query but it's also what the name of the query is going to be so we're going to name this using our naming convention for a query we're going to change tbl to qry which is the prefix for query and we are going to let's see just trying to clean up my typing here and we're just going to call it qry product so we can get rid of the word query at the end of it just q r y products so the reason why these prefixes become important is because typically in a database you'll have a table called products you'll have a query called products you might have a report called products so this differentiates the the different objects and we're going to click finish so it opens the query you'll notice in your navigation pane you have a query section now and it has your qry products in it and you're seeing the fields that we requested so that is using the simple query wizard you can go ahead and close that query and we're going to use the query wizard again to create another query a simple query wizard and we want it based off of the customers table so the fields we want in this query we want company name so another way of getting it over to the right is just double-click the field name and it switches it from the left to the right we want the phone field and we're selecting the fields in the order we want them to show in the query and then we want address one city state zip and we're gonna also add the employee name so we're gonna do next and we're going to name it qry customers so you can edit that and then you can get rid of the word query at the end of it unnecessary kind of redundant it's already grouped in with the queries in your navigation pane and we're gonna click on finish so there is the query the question we asked of our data with the information that we asked it to provide us with go ahead and right click on the tab and close that query now that we've used the simple query wizard twice we're gonna create a query from scratch essentially by doing it in design view so we're gonna recreate this products query that we use the simple query wizard for but we're going to do it from scratch so on the create tab of the ribbon in the queries group you're going to click on query design and when you get into query design it asks you to it brings up the show table box so it's asking you which tables are you using in this query now that we have relationships we can use multiple related tables in a query in our case we're just using one table so i'm gonna double click tbl products that's another way of getting it into design view so and then i'm gonna close and i'm gonna just with the tile i'm going to expand it so i can see all of the fields and you use the grid underneath to build your query so what we're going to do is this we're going to just select the same fields that we used before and this is not a hard process what i'm going to do is i'm going to double click the product id field in the table tile and it puts it in the first column in the grid underneath and by the way if you want more grid space you can do the divider between the grid and the upper half where your table tiles will show so you get a little bit more space so i just double click that field and it told me the name of the field and the table that it's coming from in that first column in your grid so the next field that we put in our products query is product name i'm going to double click it i'm also going to double click price notice it's going into subsequent columns so you don't have to worry about that another way you can get the fields down there is to drag and drop them so for category id i'm going to click and hold in the table tile and i'm going to drag it to the field box in the fourth column i think double clicking is a lot easier just my opinion so now that we have this query set up right we're good to go we're ready to view it so if you look you're on the query design tab of the ribbon right now because we're in query design right so you get that contextual tab and the first button on that tab is view and the second one is run we're going to go ahead and click the run button and so it looks just like query products that we already created but we just did it a different way in design view i'm going to right click and go back to design view for that query right and now it's time to save it so i'm going to just right click the tab and i'm going to save it and i want to name it qry products so it won't let us have two objects with the same name so i'm gonna just temporarily put a number one behind it so qry products one and click ok and now close it in order to delete a database object or rename a database object it must be closed so we're going to delete our products query that we use the simple query wizard to create so i'm just going to right click on it in the navigation pane and choose delete and it will confirm the deletion by saying yes and then i'm going to right click on qry products one and i'm gonna choose rename and i'm gonna just get rid of the number one at the end of it and click away from it so again objects have to be closed in order to delete them rename them so on and so forth now that we've created a few queries you're going to learn how to add criteria for text number and date fields and you'll get an introduction to using wildcards in criteria so let's start with just regular criteria first we're gonna open the customers query in design view by right-clicking it in the navigation pane going right to design view even though we built this query using a simple query wizard we can always go and edit it in design view so what we're going to do here is we want to add some criteria so we want this query to just show customers in a particular zip code so what we're going to do is in the zip column on the lower half in your grid there is you'll notice in the grid you have a field row table sort show criteria and or we're going to be in the criteria cell on this one for the zip code field so right underneath the show check mark in the zip column you're going to click and you're just going to type in 97045 now a suggestion whenever you're using the grid here in query design you're going to want to tab after you type something out if it needs double quotes around something when you tab it will give it to you so the zip field remember is a short text field text fields have to be enclosed in double quotes you don't have to type them you just have to remember to tab outside of the cell after you get it in there so now if you want to see if this works you have a choice we can view it or we can run it i'm going to just go and click the upper half of the view button there and you can see that it's just showing me two customers that are in that zip code i can click the upper half of the view button again to get back to design view so we made that change to this query pretty simple quite criteria we just went to the zip criteria cell and we put in the zip code that we were interested in seeing and so let's go ahead and delete that zip code criteria and we want to do a different criteria we want to see all states in our customers table all of the states that begin with the letter i so in your state criteria field that cell underneath the check box for state in the state column you're going to type the letter i and you're going to type in asterisk and so i asterisk means show me everything that begins with the letter i i'm going to tab out of that cell and you'll see what it does in order to do something like that you have to use the keyword like so like i and the i and the asterisk have to be in double quotes we didn't have to type that we just had to remember to tab out of that cell go ahead and click the upper half of your view button and you can see that we have the customers from the states of idaho and illinois are two i states within the data you can click the upper half to get back to design view and go ahead and you know what we can keep we can keep that like i criteria in there actually no let's get rid of it i have a better idea let's get rid of like i for right now just so we have no criteria in here and then just go ahead and close it and if it prompts you to save changes say no because we don't want any of that criteria in there so let's say we wanted another query i'm deviating a little bit but if we want to have our total customer query like we do and we want another one that's just for the i states right we can copy so i'm going to right click on qry customers and i'm going to copy and then i'm just going to right click in the navigation pane and paste so it comes up with paste as copy of query customers we're going to delete the words copy of and at the end of qry customers we're going to put idaho illinois so i just did capital eyes on those qry customer idaho illinois and click ok now right click on that new query and go to design view and go ahead for the state in your criteria cell put in i asterisk and tab out of there and then you're going to right click on the tab save it and close it so now you have your intact all of your customers query and if you double click query customers idaho illinois you'll see that it just has that criteria being recognized in there so you can keep you can have both and i'm going to just close that one now in the slide deck that's in the video description i have a link to microsoft site that gives you all the information about wild cards in queries and parameters in access so i just clicked on the link and this is what it brings up so it's showing you the asterisk here right we did i asterisk so that's like anything starting with the letter i right it doesn't matter what follows it so it's this this is a good reference article for you so if i did an asterisk in this example asterisk owner asterisk it would show in that particular field wherever the letters o w n e r r so let's say it was a company name field right and somewhere in that field it has that so we're putting an asterisk which means any number of characters before and an asterisk afterwards if we wanted to find something that started with owner followed by any number of characters we'd use the second example here owner and then asterisk and then we had and that's what we did with the state the letter i and then an asterisk so i followed by anything and then if you have it at the beginning any number of characters followed by o-w-n-e-r so there's more things that we haven't gotten to in this article but i wanted to give you this reference article like for example criteria is let's say we had company name for example we could do like and then in quotes and square brackets a through h right that means any company name in my example that begins with the letters a through h something like that or contains the letters a through h now the second example number four there it has like a through h in the brackets and it has the asterisk afterwards so any company name that begins with the letters a through h is what that one does so it has in this article it has a table of all the wild card characters what they do and it gives you an example of how to use it on the right so i thought that would be a good idea to include in this course just so you have it for your reference we'll be getting into more advanced queries once we get to modules three and four so module one has been action-packed uh we got introduced to the concept of relational databases uh we reviewed the north wind database uh the freeware database that you can get from within access you learned about the access environment we then went over the planning steps and created a new database and we did the same for database tables we created a couple of tables but then we used the import from excel to create and populate the balance of our tables we sorted and filtered table data we created the relationships between the tables and we went over some commonly used queries and customized them with criteria now we're going to move on to module 2. thanks for watching don't forget we also offer live classes and office applications professional development and private training visit learnit.com for more details please remember to like and subscribe and let us know your thoughts in the comments thank you for choosing learn it [Music]

Channel: Learnit Training

Views: 14,615

Keywords: learn access 2019, access 2019, access tips and tricks, access training, access tutorial, how to use access, access help, access for beginners, access for dummies, access, learn access, microsoft access, microsoft access templates, microsoft access database engine, import from excel access, queries access, query access, databases access, simple query wizard access, microsoft excel, access office 365, microsoft access guide, microsoft access tutorial, microsoft access help

Id: dtgyERaPFTM

Length: 102min 25sec (6145 seconds)

Published: Fri Oct 15 2021