Microsoft Access 2016 Complete Tutorial - Access Made Easy by Sali Kaceli

[Music] [Music] hello my name is Sally casselli the following is a comprehensive tutorial on using Microsoft Access 2016 during this tutorial I will cover basic concepts related to databases and how to design and use a basic database using Access 2016 we will start with a very basics of the application and of databases overall and then we'll get moving into working with data in a database the tutorial is designed to be concepts based and simple to understand the best way to learn is to follow the video hands-on on your computer so let's get started first some general concepts related to databases and how databases work first we're going to start with the definition of a database a database is a collection of data that is stored in a computer system a database allows their users to enter access and update and analyze their data quickly and easily they are a powerful tool that you see them all the time when you go to the doctor's office when you go to the grocery store when you go online to purchase something all that information is usually stored in what's called a database the easiest way to understand the database is to think of it as a collection of lists so think of it for example if you're running a business you have a list of customers you have a list of orders from those customers you have a list of items that you are selling to your customers or services that you're providing to your customers and then you might have a list of invoices and other purchases from vendors and things alike when you put all those lists together in a database those lists are linked together and the information is related from one table to another and that's partially was referred to as a relational database so where does access fit into this Microsoft Access is a relational database software program or database management system DBMS that runs on Windows operating system it is used to manage data that is organized into lists such as information about customers products vendors employees projects so basically Microsoft Access is a relational database management system created by Microsoft to store organize and manipulate data as well as to select and report on it so some of the components just to summarize and briefly our tables queries forms and reports tables that's where the data is stored within the database then the queries retrieve specific data from the tables or other queries and displays only the data that you specify queries allow you to ask questions about the data in your tables for example I want just the first name last name and email address then the form is used to enter new records in a table that's the data entry component of it and to edit or delete existing records into the table usually the data is not entered directly into the table manually but it is done through the forms and then the reports or the fourth component here and those would be very similar to the output they summarize the fields or records from a table or a query in an easy-to-read format for example that would be a report that you print out something that you print out to give to your manager for the sales of that day now what does all of this look like in an actual database here's a really simple database that we have we have a database called the customer sales here and we have a bunch of tables in this case we have the customer table we have a contact table and then we have invoices so think again about these as separate lists so the customer table of course it will have the list of customers we have here the customer ID company first name last name telephone number street address city state and so on so those will be very similar to what we have seen in Excel you can sort this data you filter it and things of that nature but think of it just as an as a list now besides the customers if you're running a business then you'd probably have different contracts or different sales so in this case this is another example of a table the contacts table if we double click on it we have a contact number the customer ID contract amount a date and in the type and then notice we have another table here called invoices and the invoice table has the date number and the item what it was for the amount and whether it was paid or not so those are the tables again this is where the data is stored into a database think of it when you hear tables storage the next component in a database I have mentioned earlier it is the queries so if we go here the queries for example are like we have the table customers notice we have a whole bunch of fields here the fields are those names of the columns here and let's say that we want you to generate a list of only the first name last name and then let's say email address and some other pieces of information here we create what's called a query and all you don't have to create queries momentarily here so notice I have another entry here under the customer table and this is a query that we have run that I defined earlier if I double click on it notice I have only the first name last name telephone number and email address so that think of it the processing of data based on a criteria and this is an example of a form the form is basically instead of you going to the customer table scrolling all the way to the bottom and entering the data manually here which is not recommended you would go under the customer data entry form for example in a form similar or nicer than this and enter the records right here and you just basically fill in the data the real business this is what the assistant would utilize for entering the data into the into the database and then the final component as I mentioned earlier it is the reports and reports are very similar to queries but they are just designed so they can be printed out and they look nicer so these were some of the basic components of a database the tables are where the data is stored the queries are how the data is manipulated then the forms are how the data is updated and added on to and then that reports how the data is printed out or output [Music] once you're in Microsoft Access you'll notice it very similar to Microsoft Word Excel and other applications in office 2016 on the left hand side you'll have the recent documents or the recent databases are files that you have been working with then below you have here the option to go ahead and open it a pre-existing database and then on the right hand side here we have the option to create a blank database that will be used utilizing in a moment and then further down here we have different templates that are available in Microsoft Access 2016 these are pre-configured Access databases that you can utilize we're going to utilize one of those templates very briefly just to learn a couple of the concepts but we are actually in the next session here we're going to learn how to design a database from scratch so that we understand how databases work how to use Microsoft Access so in our case here I'm gonna very briefly utilize here this database called students this is new in Access 2016 now here is a concept that you might want to keep in mind when dealing with Microsoft Access databases unlike Word documents in Excel where you open a document and you create the document and then you manipulate you know what type of thing in Microsoft Access as soon as you choose to create a database you have to give it a name and you have the first thing that you do is you save that database so in our case here we have to give it a name note the location where it's going to be saved and then you click on create in this case we are utilizing a template so it's predefined with all the components and such so it's slightly different on creating a database from scratch so first thing you are presented here is a tutorial on how to use this not every one of them is going to give you this option so we are going to simply click on get started here to utilize it and now a couple things here just briefly so that we get a couple of the concepts and I will demonstrate these more as we get started in the next module and typically a database has four components just like in a computer that you have the four basic functions of a computer where you have the input that the computer accepts input it stores the data by storing it in the hard drive it processes the data and then it outputs the data in an Access databases you have a similar concept as well you have the tables which serve to store the data that's where the data is actually stored you have the queries which are very similar to the processing of the data on the computer and for now just keep those in the back of your mind and then you have the forms in an Access database as well that serve as a mechanism to enter the data into that database and then finally you have the fourth component here the reports that the serve for displaying the data in your database it's very similar to the output in your computer so in our case here we have this form so if we wanted to add in your students we simply click on your student and then we fill in the information there you can have the picture you can have additional information and click on save and then you and then you add the next student and so on now this window what we are using right now this is referred to as the forms so this serves as an input for this database now that data is not really stored in the form the data is actually stored in the table here on the students table and we'll learn more about this in the next session so now if we look here this is how it will be stored so it's very similar to like an Excel spreadsheet but it's just a bunch of tables and typically in the Microsoft Access database or in any database you can have two tables three tables hundreds of tables or even thousands of tables and those tables are typically linked together via what's called primary keys and foreign keys and we'll learn about those as well they are kind of related one table is related to the other table via those keys and that's where you hear about the terror relational databases because the tables are linked the other component here is the queries we don't have any of those for now that we could utilize the next one would be the forms that we just used a moment ago and then if you need to create a report of all the students you simply click on all students and it'll display a more visually Pleasant report for you to print it out so those are some of the basic concepts using a template so now stay tuned for the next session that we'll learn how to design our database from scratch we'll create the first table and then start building a couple additional tables [Music] in this session we're going to create a database from scratch and we are going to learn about some of the various components of a Microsoft Access 2016 so we can kind of understand how the application works and where the different components are so once we open Microsoft Access we click here on the blank database and like we learned earlier the first thing that we need to do after we click on a blank database we need to give it a name and take note where you're saving this database now as soon as you open the Access database here what we have is very similar to Microsoft Word and Excel and other applications in office 2016 on the very top we have the quick access toolbar with a bunch of commonly used options we have the file tab here and then we have these different tabs so home the most commonly used functions very similar to other applications then we have here the create tab this is to create different components related to databases for example creating a table creating queries creating forms and reports then we have the external data tab where this is basically for us to get data from other systems and import it and link it into a database here in Microsoft Access and then database tools this is another tab where we can design the database and define the relationships or define basically any tools related to the database here for whether to repair the database to create macros or other components then we have here this new tab called table tools so table tools here this is very similar to the contextual tools in Microsoft Word or Excel basically a new tab that shows up in the context of what we are doing so right now we are creating a new table and it's giving us options here for this new table next to it here you have all so tell me what you want to do or that tell me this is new in office 2016 so basically if you wanted to learn how to use query wizard or how to create a new form or anything like that you simply type in there how to do that so for example query your wizards and you just basically it takes you directly to that option in order to learn how to perform a specific task in Microsoft Access and then notice on the bottom here you have a couple of other options and I'm not going to take the time to tinker with those too much it's basically the design view and the normal view and we'll cover those shortly now creating our first database here databases as I mentioned earlier they are designed using tables so typically you have at least one or more table so now this is our first table that we are working with and we'll give it a name shortly so it will actually ask us to save and give a name to this table in a moment typically the way tables and databases work is is that one of the fields by the way these are referred to as the fields so you'd have the ID field the first name field and a last name field and so on so the columns which we refer to them where you'll hear the term field then you'll also hear the term a record so we'd say this is records one or record two or record the record is think of it as the row here so you have more than one piece of information related to a record so you have for example first name last name address and so on related to that specific customer here this would be the field name so right now it says ID so you get to change that to say customer ID the type here for customer ID it typically it needs to be a number so notice under the data type this is an other number that means that when it goes to the neck customer it'll go so from customer one to number two number three number four automatically the data type for each one of those fields typically has to be specified the next one it's asking us to what type do we want to make this next field here so the next field here we are going to make it text and this will be first name then the next one we're going to make this field type as well a short text here and then we're going to call this last name and then the third field we are going to make this that say the street address and this will be text as well the next field here it will be City and then the next state and then the next one zip and the zip code we want that probably as a number field and then the next one you can pick whatever other field that you're gonna utilize but take note here that it can be various other fields so it could be for example a date field when they signed up to be your customer and such or you can have an attachment for this customer or you could be able to post a hyperlink field as well so in this case we are going to create a field here for attachment and that would be for example for the picture for that customer or various other fields basically this step it's referred to as the designing this table so we are defining how the fields are going to be formatted if we are done with the design at this point because simply we could do a couple things at this stage we could either enter the data directly from this table that we and by the way the data that you enter from now on from this point on it has to be matching the type that you defined a moment ago for some of this the zip code has to be a number it can't have letters in there and things of that nature the other thing to keep in mind is is that data typically we for now we are going to enter it here directly table but typically it's not entered from the table itself it's typically entered from the form of the database and we'll learn about this shortly as well let's enter just one record here for the sake of testing and then if we go to the next record you notice it entered the customer ID automatically so the concepts so far that we covered in this session keep in mind when you define those fields you need to specify the data type and it's very important to think it through as to all the fields that you want in a table when you're designing your database so then you want to make sure that all the fields that you'd want in that particular table they are included in there you can add them later as well but it creates it causes complications it's best to think it over initially the other thing is is that you need to consider categorizing the data accordingly in various categories and then these categories they become your tables so for example you want to make sure that let's say customer information anything related to the customer such as the address the preferences and mailing address and that type of thing you want to keep it in one table then anything related to orders you want to keep it on the order table anything related to payments you would keep it in the payments table anything that you want to keep related to inventory you want to keep it an inventory table and even the inventory could have all kinds of sub tables as well the key there is to categorize information in major categories those categories become at least a table of some sort and then you define the data type for each field here and then you have to make sure that whatever you enter in that field you want to make sure that it matches that type of data also remember as you design your database you need to have kind of a key differentiator between the records in your table so for example if you had two customers named as Hubert Sims and such you want to make sure that how do they differ and their way to differ from one customer from one record to another is by assigning them something unique for example a customer idea unique customer ID and those are typically referred to that becomes your primary key the primary key again that is what will differentiate between two records once we are done with designing our first table here you'll click on close here on the top right of this table and now it will ask us to give it a name to save the design for this table it will actually save the design along with the data that we just entered and now notice here under the tables list here on the left hand side we have customers information now to open this up you simply double click on it and you'll be able to view it and enter new records in there as well if you need it to change the design you could simply click here to add additional fields or another method to change the data and change the design for this table is also by using the design view so notice here under the Home tab we have view and there are a couple of views there's a data feed view what we currently are seeing and utilizing and then you also have the design view let's click here on design view and this is a more sophisticated way it's a little bit more complicated if you're not used to working with databases but yet it's actually a lot more powerful and a lot more useful so here what you can do is basically you can modify the structure of this table on the left-hand side you have the field name which was the column for each column in that table that we saw earlier so we could change the names or it could change the data type as well if you have a lot of data on your table and you go and Tinker and won and manipulate the data type you might the most likely get an error message so keep it in mind as you design your tables to try to do it as best as possible in the beginning whether it's the data type the layout of the fields and such now from here from the design view like I mentioned earlier you can change the data type so you can say okay under the state I want that instead of 255 characters I want that to be only the two-digit abbreviated version of it or you can make it 40 characters long or whatever the length of the field is you can define the field you can also change the format and the mask and all kinds of default values and you can control all kinds of additional settings here so I'm not going to go into the more fancier options here but for the big picture you can change the stuff from the datasheet view or you can change it from the design view from here notice as well that you can change the order of those fields by holding down the mouse and you can move one field above the other one as well and change the order of those fields to add new ones you can add them here in the bottom and then you have to define the data type as well so for example this is a field for comments so this would be a long text so you want to make sure here that the user can enter enough text I believe that'll be 64,000 characters that it'll accept in that field one you start typing on it once you are done with any of the design changes now we click on close here and it's gonna save the structure typically this save option it takes place only when we change the structure of it so one of the concepts here is is that if you change the structure of your table and the design of it then it's going to ask you and prompt you to to save it however if you're simply entering data the data is saved automatically into your database so that's another concept to understand when using databases the other thing is that databases are designed to be used by multiple users at the same time so once you have finalized your design you can have 10 users 15 users 50 users or however many users access and update the table at the same time and work on the same file the same database at the same time so those are some of the very basic concepts on getting started with an Access database and an access table now this is not all next we are going to enter some more data into this table and then we're going to create a query we're going to create a form and then we're going to create a quick report with just one table and then furthermore so stay tuned we're gonna create multiple tables and we're going to link those tables together and then we're going to utilize the more intermediate functions in Access databases and using Microsoft Access so stay tuned [Music] in this brief video we are going to learn how to add additional fields to a table in an Access database and then we're going to learn how to create queries how to create forms and how to create a report based on a table in our database stay tuned we are going to also learn later how to create multiple tables in a database and how to link those tables together and utilize additional functions in Microsoft Access 2016 so we have our table that we designed earlier and now we want to add let's say to make a small change as I had mentioned earlier you don't want to make too many changes after data has been entered in the table but this is just for demonstration purposes so to enter a new field we could do it a couple of ways here we could either click here on click to add and then choose a type of field that we want to add or we can go here under view and then choose the design view so design view is this next one now we scroll all the way to the bottom here and then let's say we want to state whether this is a new customer or not so it's going to be a yes or no field so it's a new customer and then we want to change this on the drop down here to be yes or no what the system is going to do there it's going to allow us to put a check mark basically have a check box once we have to tweak that you can add additional fields in there by the way you have here under description in the design view you have an area where you can actually put notes design notes for you as a designer of this database you could say additionally you can go up further for any of those fields and such as we covered there very briefly in the last session each table needs to have a primary key and the primary key can be a number field it can be anything but it has to be something typically it's a number in an other number something that they increase is or some kind of code and it is what differentiates one record from another so if you have two people the same name in the same address then in order to differentiate them the best way would be to simply assign them a new ID or to have different IDs so the primary key typically in the database design here since we are in this view it's represented with this little golden key if you wanted to change it to a different field and make that the primary key you can however it has to be the right kind of field if you changed it to this one then that means that there can be no two people with the same name under that table so you want to make sure that you set the primary key on the correct field and that's why it's an ID or something a number that is generated by either the system where you assign it manually but that number has to be unique so I'm going to make this back as the primary key and now let's suppose that we are done with a design here we can click on close save the design changes and now let's learn how to create a form forms typically are utilized for entering data you could enter the data in here but if you have a lot of fields here or those columns and you have a lot of data first it would be very dangerous that to delete records that you're not supposed to delete but secondly it's not the most user-friendly interface to enter your data so therefore what we can do here is we can go under the create tab and you want to create what's called a form the forms you can create them from scratch you can design them from here or you can use the form wizard and I'd recommend that you utilize the form wizard it's much simpler much easier to do this go under form wizard here and basically the way it works is if you tell the system which table you want to use in our case we have only one table here so then you pick here what fields you want to include in that form by the way you can design multiple forms based on the same table you're basically saying I want to feed data only for first name last name and address and you give access to a specific individual to just utilize that form while other individuals may have access to update more areas of your database so here we are just picking the fields that we want you can pick them one at a time or you can simply add all the fields in one shot here by clicking on this double arrow icon and then click on next then you can choose how you want your form to be displayed and organized and you can play with this on your own but typically columnar it's basically going to go up and down in the sequence basically then click on next and then you give it a name and then it says open the form to view or enter the information or open to modify the design even further so in our case here we are simply going to click on finish notice it has designed our form for us to enter the data so notice it has this it's displaying the first record that we had entered already in our table now you might say what about this field file1 file2 file 3 this is because we chose one of the fields to have attachments and these are just the attachment fields here also keep in mind that you can design this form and only learn about changing the design of forms later as well but for now we are just learning about the basic concept of how forms work in a database and how they relate to tables so stay tuned for the other functionality there so in our case here the form here serves to display information that is already stored in the tables or it also serves for us to enter new records and create add new customers here so if I click here add a new blank record we leave the customer ID alone here and then we just fill in the information now notice that this it's it's not quite lined up properly and all that type of thing I'll show that at the moment how to readjust the size of those and then you basically just fill in the information if we go to the next record it's basically it's stored what we just entered and then you can just create a new one and keep on doing this so typically it's the forms that your assistant is going to use to enter data and lookup information typically your users do not really need to touch the tables and entering data and such because the system will enter the data using the forms so hopefully you get the idea there now if I am to go here to my table by the way you need to close any of those other things that you might have opened here in the main area and we open the table here no this is I have now Jim Smith my new customer that I entered manually or did I enter through the form so again the forms are very similar to a the input function in the computer and you utilize them to review what's stored in the table and to store new data in the table now to modify this form just very briefly here you can click here under view and then choose layout view and you can kind of just simply resize this stuff if you need it to so this is one way to resize it or to adjust the design of it it's somewhat simpler the other method to modify the view of this form is by clicking here on view just like we use the design view to modify the design of the table we can use the design view to modify the design of our form here so if we click on design view now it becomes a little bit more complicated than such that's why I said that the other that was slightly simpler here is where you can change in a more precise way the design of this so here you could actually go and say instead of first name without the space there you could simply go ahead and change it put the space in there you can format that to be in bold and you could change the design of those fields and such additionally from this view and it's not time for us to learn about it but if you needed to its these second fields here the ones in white that are basically connecting so you don't want to change this wording here to put it off space that's the code that it links to the table because basically it's saying it's going to pull from that first name field on the actual table so you don't want to change these if you right-click in here and unfortunately you're not displayed here for you to view it but if you go to properties in the bottom because of my recording the way I have the recording of the screen and notice that the control source it says here so it's pulling the data from the customer information table and it's pulling the from the field first name so this is more complicated but just for your information you don't want to change this second area here then once you're all set here we can close this save the changes to it and then if we go back to our form know this first name here it's in bold and such so hopefully this makes sense on how the forms relate to the tables in an Access database now let's create a quick query here and let's see how queries work in a very basic way so if we go here to our table and we click on create and now we go here under the query wizard and the way queries work is basically you can connect to a table and you are saying list all the customers from that table or list only the customers from a specific zip code or a specific city or a specific state so you are putting criteria within that query or that question busy you're simply asking a question give me limited information based on this specific criteria so here let's say we want the first name last name and then the street address in the city and a zip code then we click on next click on next and then it says customer information query click on finish and notice we got only what we requested so we have first name it's listing all the customers by first name last name street address city and zip the concept to keep in mind here is is that there is no data stored within this query itself if the data is still stored in the table the query just stores the parameters of what our criteria is so to run this query all you have to do is you double click here on the query and it pulls the data right away if we were to go and change specific data here for a specific customer let's say we had a fourth customer here and I'm entering this using the form now if I go and run this query you know this I have customer four listed here so the idea is is that you can create as many queries as you want whatever options that you want you know the manager might say I need the query I need to know how many sales so you did today so you could say okay give me all the customers give me all the items that were sold but based on a specific day of the week or and such so that's very briefly the queries if you needed to change and would learn how to modify the query so just check next tutorial on customizing and working with queries in a database now next in this session we are going to learn how to create a report reports in a database are a key element as well it's very similar to outputting information you want to print it out particularly it's to organize the data in a certain way it's very similar to queries but the queries are displaying the data in very excel like worksheet here the reports are designed to look nicer so you can print them out and hand them over to somebody so to create a report again you go under the create option here the create tab and then you want to go on the reports and my suggestion would be that you utilize the report wizard so here you're picking what table you want to pull the report from notice that you can pull a report from queries as well such reports are built from tables or from queries but whenever you build a report on the query if the report is actually going to that query and pulling the data so for now we're going to use it directly from the table and then we say I want the customer ID let's say the first name last name and the State address and whatever else that you want here zip code and such and then we click on next now this stage we have the option next and we can choose how to sort those customers let's say by first name I by the last name of my customer ID and you can put different searching options here or sorting options click on next click on finish and now notice here we have the report called customer information notice it's slightly more visually pleasing here and you can also adjust the design of this very similar to how we did earlier for other components here so we can go and adjust the design by going to the layout view and then resize those fields accordingly now typically the the numbers here this number of sign that means that it does not fit in that field so you need to still resize this or the other method here to adjust the design of this is by going here under view and then go under design view so again the concept is whether you want to change the design of the tables the forms or the queries or reports you have the different views that you can change the design of them and particularly you want to use the design view for designing any of those components of the database now when we are done with this report we close it since we thinkers with the design here just click on save to save it close the other elements as well and then go here under a customer information this is our report and that's the data that it's pulling from the table here so hopefully all this makes sense in how a database are a very basic database with one table works and how the various elements of the database particularly those key four components there are other components that you add as well such as macros and things of that nature but these are the main key components of a database that you utilize typically [Music] now in this session we are going to learn how to create additional tables in our Microsoft Access database typically an Access database or any database out there will have more than one table and you can have hundreds and thousands of tables depending on how big your database is for this tutorial as you should have access to a link to download the working files the working file is as if file here and what you need to do is you need to double click on it after you have downloaded it and then you want to click on extract all once you extract them all take note where it's going to extract this stuff click on extract here and then you'll see three files in there so we'll utilize those three files to build an Access database or to basically build three additional tables by importing the data from those files in order for us to get a better understanding of how Microsoft Access works if we had to enter the data manually it'll take us a very long time so we're going to build three tables one of them is going to be based on customers like the contact information for our customers the next one is listing the contracts for those customers and then and a third table is going to be the invoices so stay tuned how we do this with the next session here keep in mind that this data it's two of those species of data are in Microsoft Excel so it looks like this and you can have that data in Microsoft Excel already in a system of some sort so basically have the customer the company first name last name and the information related to the customer notice the customer ID here is a unique number then the next one the contract list notice you have the contact number the customer ID you have the contact amount it's another Excel spreadsheet and then you also have this invoices list and I have this on purpose here as a text file because you can import data from a CSV file or at the comma separated values file and this is what that would look like so you have the different values here the invoice and all the fields are separated by commas that's why it's CSV because of the comma separated values and then notice that the each value here it's in quotes so we'll learn about how to import this into Microsoft Access database and create three tables from these [Music] so in this session we're gonna learn how to create tables in an Access database by importing the data from another system will import the actual data and also the design for the tables in one shot from those systems to save us time you can import the data into an existing table in Microsoft Access as long as the fields these columns and also the datatype matches from the external file with your current design in Microsoft Access since we are starting here understanding the concepts here using Microsoft Access we're going to bring both the design because I hope you understand how to design the table and the fields and such by what we have covered so far in our tutorial if you wanted to do this on your own and such what you'd have to do basically design a table if you wanted to bring it into an existing design you'd have to take let's say the customer list and when you design your table in Microsoft Access when you go to those fields here in the design view those fields have to match so you have customer ID here from your excel file or external system it needs to match along with the data type then you'd have to have another field in here called company and you'd have to create that then first name last name telephone numbers 3 to address all of those have to be in exactly the same order if you're going to use an existing table in Microsoft Access in our case we are not going to use the existing table so we're going to create a new table by importing the data from an external data file so here's how it works we go here under external data and then since we know that our data file was an excel file we can go here under excel and then we go and locate that file that we extracted earlier from the working files so we go here under browse go under the working files and notice we have here customer list click on open and I here we want to import the source data into a new table in our current database if we wanted to add just the data then we'll choose the append option here to just simply add it to the existing table you can also link it to an external table or external data there as well but that's beyond the scope of this tutorial for now so we are going to click on import data to create a new table click OK here and then it says show the worksheet so this is our worksheet this is for what's in that table then we click on next and then this is very important this is where a lot of users get lost so we want to choose here the first row contains the headings so the first row in our Excel spreadsheet has frozen all the field names customer ID company field name last name so this is just that label that's what we are telling the system that that's what that is then we click on next and then here we say you can now specify about each item that your imported select the fields below and do you want to allow duplicates yes or no now typically in a database for example for the customer ID we don't want to allow duplicates so you'd say no duplicates in our case we're just bringing this to play with so we minimize any issues and we'll just leave it alone now the data type here it says it's gonna be short text typically you want to make sure that your customer ID it's actually a number field so we can change that at this point and say number there's a B typically it'll be a double here that you choose the data type and such let's say keeping it simple I'm gonna leave this a short text then we click on next now it says do you want to choose the primary key notice it's starting to create a new one for you you could create this manually or automatically here the system will create it for you or you can go and say no I'll use my own key because I already have the summer idea however keep in mind it cannot have to record so that were the same customer IDs you need to be sure that the data that you are bringing in from an external system to create this new database it actually does not have any duplicates in it then we click on next here so our name for this table will call it customers and then simply click on the finish notice we can save the steps if we want it but we don't really need to save at this point we click on close and now you have another table here and if you double click on it you have all those customers there are 38 of them that it brought from that table notice that the table now it has the field names in the very top here and it also has the various fields such as the email field and all that type of thing if you needed to create additional fields then you simply can go here under the Home tab and you could go and add a new field so if you wanted for example a field called comments it's gonna add it and then you choose the type here so we're gonna make the comments field to be a long text here and then we close the design and now if we go back to customers you'll know this that you have another field called comments so that's how you create a table by importing the data from an excel file let's create another table and contract table so again we go here under external data we go under Excel and then we choose the file that we want to import we're actually going to get the contact list we're gonna create in your table along with the structure for that table from your external data file from our excel file and then we click OK go next here we tell it that the first row has the actual information next then the contract number right now we are choosing to a lot of duplicates but typically you don't want because this is going to be our primary key the unique identifier for each record here and we're going to leave it alone for now then we click on then you want to make sure that you're choosing your own primary key and you're making the contract number as the primary key for here then we click on next and then we just say this is going to be called the contacts table click on finish click on close and now if we close this we'll have contracts and customers and notice there are 65 contracts at this point and then we also have the customers now this next session we're gonna learn how to import data or how to create a table by importing data from a CSV file [Music] in this session we're going to learn how to create a third table including the structure from a CSV file in Microsoft Access so we have here this invoices list we're on the create a table called invoices and that data we import it from some kind of system out there that's comma separated values so to create a new table we go here under external data and well to import the data from a text file so we click on next here locate wherever our file is and notice it's entered my working files and this will be also in your working files if you expanded it if we choose invoices list we click on open then click ok and then we want to tell the system here that this is a delimited type of data file which the values are separated by a comma and you'll know that when you receive the file from whatever system there it'll typically be a CSV file and if you click on next and then we want to tell the system that this is a comma separated values so the values here for each column they are separated by a comma that'll go next again so you choose comma and notice here it says first row contain the field name so make sure you select this one if you skipped it go back and try to follow it so we went to comma first row contains the field names click on next and then we leave these the way they are for now click on next then make sure you choose your own primary key and the invoice number is going to be the primary key next and then this is going to be invoices and then click on finish so now we have an additional table here and this no table it's called invoices notice one key aspect here if we go to customers we have your customer ID this will be our primary key for this table so even if we went over here under design view you know this customer ID has this golden key here so that's our unique identifier for this table now if you go to another table here so if we go for example two contracts and open this up and go under design view notice that contract number is the unique identifier for this table anytime you're designing a new table that table has to have some kind of unique identifier or primary key now notice as well here that this table also has another field called customer ID and the customer ID here we saw it that it was a primary key for the customers table that we saw earlier so it was on this one what that means is is that for the table contracts the primary key is contract number that's the main differentiator and now customer ID is what's called a foreign key basically it'll give us the option to link customer ID from this table to the customers table with customer ID field so the concept there is is that in a database in a relational database the tables are linked together one to another and such using primary and foreign keys the foreign key is the common field between two tables it's that field that is the secondary it's not the primary key on that second table so for example here we are saying we have a contract with this number but then this contract is associated with a customer of a specific number so if we go here under for example customer 1105 and we go here under contracts and we see eleven double oh five that's saying that this contract 30 33 belongs to customer 11 double five the concept to remember is when you're designing this you need to design also for foreign key is the common field that will connect one table to the other and we'll learn how to connect those tables in a moment here now even if you went here under invoices you'll notice that the invoices has an invoice number that's a primary key but then you have a contract number which will eventually connect to the contract number here from the contracts table and in the invoices table so the primary key is this one but then this is the foreign key for this table so primary keys and foreign keys are two very important concepts that play an important role when you're designing the database because they'll need to be linked together and that's what we'll learn in the next session here we'll learn how to link the tables using the primary keys and foreign keys [Music] in this session I will demonstrate how to link tables in Microsoft Access database in Access 2016 or how to define the relationships between tables in Access 2016 so a relational database like Microsoft Access 2016 it requires that the tables are linked and typically the key component for linking tables in a database is the proper design of the tables to start well and typically what you want to do is that you want to have the primary keys and the foreign key is properly defined in the tables before we further explain this I'm going to clean up our database here so I'm going to first delete this table that has nothing to do with what we are planning to do in the next couple minutes here I'm going to delete these queries as well by the way you don't want to do this on a real database unless you're sure that you want to delete this and at this point we have thick tables so we have the customers table we have the invoices table in the contracts table to learn how we did this please refer to the previous tutorial we have the primary key for customer ID this is the primary key for this table and then under contracts we have the primary key being a contract number and then the foreign key it's a customer ID which is supposed to match with the customer ID which is the primary key on the customers table and then the same way under invoices we have the invoice number which is the primary key for the invoices table but then the contract number which is a foreign key in this case those two match with a primary key of the contracts table so as it designed that the database you need to factor in a plan on what the foreign keys are gonna be and how the tables are going to be linking with one another when it comes time to link them and this has to be done during the design process now at this stage we need to close the tables and then next thing that we need to do here is we need to go here under table tools and then we are going to define the relationships so we go here under relations and then we choose to add the customers table which used to add the contracts table and then they invoice his table and then close this now notice you can even organize them any way you want here you want to organize them fairly logically if you can so that you see the structure depending on how you organize your database to link those tables together notice we have here customer ID from the customers table and we have the foreign key here under contracts for the contracts table so what you do is you click on customer ID from the main table and you drag it and you hold the mouse directly on customer ID on the contracts table here and then you let it go now this point you notice it's saying it's gonna use the customers table linking to customer ID in the contracts table it's going to be a relationship of one-to-many what that means is is that you can have one customer with multiple contracts and that's hopefully what you want if you have a small business you want multiple contracts from the same customer you could also enable here what's called a referential integrity I'm not going to check it for now to keep this simple but that means that if you deleted a customer you're probably never gonna have that customer again then you want to delete also their contracts that's what their referential integrity is it's going to do a cascade update and cascade delete if you remove the main source it's going to remove the items that follow with it if a student drops from college you want to drop also the courses and things of that nature then here we click on create and notice that you have a line between the two the next thing that you want to do then is notice now we have the contracts primary key here could link will the foreign key from the invoices table so we don't just say from invoices here we want to drag this to the foreign key from the invoices table again the same idea contract number two contract number want to many relationships that means that you can have multiple invoices for the same contract that's what that means and by the way you can move this however it makes most sense to you if for some reason you chose the wrong thing notice that they have to match your customer ID has to be under customer ID it can be linked to a different field if for some reason you had it improperly matched there you can right click so or select it once and then you can choose either edit relationship or simply delete and then if you want to delete it you say yes and then now it's disconnected and then you can redo it again so you just drag your customer ID to customer ID and then click on create again and now you'd have it the way you wanted it once you're all set with this you need to click on close and that's where the system is going to save these links between the tables because unless these tables are linked together we cannot get data from one table to the other and when we link these tables through this relationship that we can actually go and say I want for example a query here or a report of some sort that I want the first name last name and then I want the contract number and then I want the amount in a date and the contact type that's when you can kind of cross and pull the data from multiple systems then I click here on close and then it's gonna ask us to save the relationship here we say yes and now the relationship has been defined and now we can actually create queries we can create reports we can create forms to pull data from multiple tables here so this is where the beauty of Microsoft Access actually takes place let's very briefly learn just for the sake of testing at this point how to create a quick query on pulling data from two or more tables just before I finish this section so you get an understanding of how the relationship connection here between the tables what the advantage is so if we were to look here at customers you know this have the customers table but I don't really have any contract information for this customers table and if I go to contracts I have only contract information but I don't really have any customer information other than this ID so now what we want to do is we want to pull the customer information and the contract information and have it displayed a certain way so here's how it works you go here under create and then you go under query wizard and then we click on OK and then we want to pull here first from the table customers notice that we have three choices where we can choose from so we choose customers and then now it's just a matter of whatever we want here so we say I want the first name the last name and then let's say I'm interested now from that table I'm interested on only these two fields the next thing that I want is I want to go under contracts and then I want to pull here the contract number I want to pull the contract amount I don't care for the customer ID because I know I have their first and last name already and then I want to see the date and the type of the contract so notice it's about six fields here from two tables now I click on next and then click on next again and now we give it a meaningful name so whenever you're defining the queries you want to define it with a meaningful name contracts by first and last name click on finish and now notice that we have a new query created and now we have the data first name and last name and a contract number and then the amounts the date and the type know this report the data from two tables from here you can create all kinds of other things whether it's a reports or new queries or forms and we'll cover those in the next few sessions one other thing before I finalize this session should have mentioned it earlier is is that in order for you to link those tables correctly you need to make sure that the fields that are going to be linked through the relationship are of the same data type and this is where a lot of users get frustrated because this does not work what that means is is that on the customers table when you go to design your table you need to make sure if that is a number field if it's numbers you need to make sure that this customer ID over here on the next table and the foreign key it's actually a number field as well it's the same type of field again here the contract number on this table in the contracts table that needs to match exactly the same data type formatting otherwise they will not link and you'll get errors on your linking and more concretely you can see that in my case if I go here to design view for the customers table I have it under customer ID I have it a short text field typically it's not gonna be a short text you want that to be a number and all the number typically I'm not gonna change it right now but typically it's an auto number and needs to match number and auto number they can be used as one type of data so that's fine but you just need to be sure they match so in my case I have short text here and if I go to my next table under contracts if you look for my customer ID here it's going to be short text as well that's why I didn't run into any problems so those can be numbers or other numbers but they have to match on both corresponding tables you remember that in your design and it will save you a lot of frustration so that's how to defining the relationships in an Access database works and it's one of the key aspects of designing a database successfully [Music] [Music] in this session I'm going to demonstrate how to create a compound form to input data between two or more tables in Access 2016 so once we have linked the tables and define the relationships in a database then you can do a lot of wonderful things and you can enjoy working with Microsoft Access and now we have linked those tables together and we have defined the relationships to basically create form forms they can be created either or not an individual table for example if I go here under create and like you'll under form wizard here and let's say I want customers and I want to create a new form for all my customers click on next and the next and then next again so there is my form for the customer so this is a simple form in Microsoft Access to create a compound form and by the way the form now has been saved here under forms customers go under create here go under form wizard and then we choose customers let's say I want everything from my customers table and then I want also data from our contracts table and here under the contracts I want to make sure I have a contract number I don't need the customer ID because it's going to be in the customers table then I want the contract amount and then the signing date and then the type as well and then click on next at this point it asks us how do you want to organize the data we want to see basically since our customers are going to have more than one contract so want too many relationship we want to organize it by customer so we have customer one and then you can see three or four contracts customer two one contract or five contracts or 15 contracts or whatever they have and then we leave everything else alone here and then click on next then here we click on next and then we give it a name so we could actually say contracts by customer and then click on finish by the way make sure you give a meaningful name there and now at this point notice what happens here so we have the customer on the top here their customer information but then right below this customer we also have the orders or the contracts you could go and add new contracts if you wanted here so he could have and such so now for for this customer we have a new contract created as well so basically at this point your assistant can use it to look up customers and their information or you can use it to update the new contracts or new orders related to that customer or your customers notice you have also these controls here so you can go from one customer to the next so you go next here and by the way you'd have to adjust this so if you click here on customer around the customer area now we can go from one customer to the next and so on if we want to go from one order to the other and add new orders you have also those controls for this sub form this form here is a mechanism to enter new data view data you have the main form which is contracts by customer but then right below it you have the sub form which is pulling from the contracts table so we have the data from two tables being displayed at the same time and then you have the main controls for the main table over here and you can create new records as well if you need it by using this button right here and then in the bottom here you have the actual orders or the contracts for the customers to update data in here whether you want to change that your comments or whether you want to add new forms or new customers from here now remember all the data as we enter from here it actually goes and gets stored in the actual tables so if we go here under customers we should have this you know customer that we just entered earlier so that's how compound forms work and how you create them and update them and use the next session and we'll show you how the customer is the form in Microsoft Access 2016 or you're in this tutorial I created a form called contacts by customer this is what it looks like so far and the purpose of this session is how to customize this form there are three different views for customizing anything in Microsoft Access here on the left hand side you have the layout view and then you have also the design view one thing is you can do definitely is you can customize this and make it smaller or bigger or however you want to resize this so let's say you want to make the text there format it's like it different and you have the formatting tools and basically change it however you want very similar to Microsoft Word now the next thing that you might want to do here is it that you don't really need this customer ID data so big and such so you can resize it and we are doing this by using the layout view the next one is go through each field here and customize this to your liking additionally you can change the formatting for this so it'll be a little bit easier to read you want to make sure that you're just only the stuff here on the left and not elsewhere and basically you're formatting this to whatever you want so you're customizing it to your liking since we are formatting there isn't changing the layout view notice that we have the form layout tools these are other contextual tools related to this form in Microsoft Access we have these different tabs here that you can change and tweak and such but one of the cool things here is is that you can apply themes instead of you spending all afternoon tweaking this and changing this probably the width of these fields customize them manually here as far as the colors and such what you could do is you can go and simply a one of those themes so you pick from one of those themes here and notice it's going to change the design whether it's the font or whether it's other components related to this form so pick one of those themes and then further adjust it and then notice if you have also various color schemes here that you again apply didn't change too much but you get the idea and then you can apply also the different fonts as well so that's one way to customize this if you wanted to tinker and insert images and such you can utilize this and you add logos and such and then you could click here under property feet and this tells us where this field is linked to or it controls the linking to the table itself so for example right here it says that it's linked to the comments field on a customer's table and then notice there are lots of controls here that you can change typically you don't want to tinker with this or to change this to something different because then it'll break that link and then the data would not be updated this is one way to customize this form the next way to customize it is we going into the design view and this is another mechanism so I'm going to save these changes first this is a little bit more in-depth it shows the data where it's linked behind the scenes with what fields and you can line things exactly the way you want them appropriately and all that type of thing you notice that you can change here colors and alternate rows and background the image and all that type of thing and the fill and all that type of stuff that is more advanced that you can resize stuff move things around if you need it to but in a nutshell that's how you customize you customize it by going through these different views that we just went over here once you are all set with a design and such then you can close your design save the changes and then open it up again and it should have why the changes that we made earlier you want to make this forms as nicely as you can so that your staff that is utilizing these forms is actually using them effectively and also it's pleasant and easy for them to enter the data keep in mind that you can create more than one form based on the data so if one of your assistants needs access to only a couple pieces of data here then you create a form specifically for them and for others you create a more complete form and such so hopefully that makes sense and that it was helpful in this session I'm going to briefly demonstrate how to search for specific records by a specific field via a form in a Microsoft Access database particularly in Access 2016 so supposedly this is our form here and we have contracts by customer and now your assistant is keeping track of your customers and also keeping track of orders and such and now a new customer called and how can she pull up the information for a new customer so you can pull up the information by using a variety of methods here by using any of these fields all that the assistant would have to do is click on a field that's a searching by customer ID she simply needs or he needs to simply click on the field here click on find and then type the customer number so for example 11 0 40 then click on find next and then though this we have that specific customer in here the other thing that you can do or she could do it or anyone could do here is searching that say by first name or by some other field so I'm going to go back here and let's say my first name you can click on the field there and then is Michael fine and you can have this over here as well and there is Michael Ingram phone number and information related to that specific user then you can find additional ones if needed as well so notice there are two records with that you can also filter the records by a specific field so let's say you wanted all the records to be displayed the customers that start with the name first name Michael so you can select it here and go under filter and then you notice you have all the different options here so you can simply uncheck them all and then pick what you want here let's say Michael and unfortunately it's beyond the recording area here but you can get the idea it's going to be Michael or whatever but let's say Jessica here I'm not sure that there is more than one Jessica but let's say Jessica and John and then I'm gonna check Michael here now notice there are three filtered results that showed up here and we can go from one to the other to the other so there were two customers or the name John here keep in mind whenever you have filtered stuff it's going to display only what you filtered by and then this field right here where it says filtered it's going to be highlighted there you can do that with any of those fields by either searching and finding records from up here or by filtering from this option over here [Music] in this session I'm gonna demonstrate how to create queries from multiple tables in an Access database we'll create those queries using the query wizard and then in a next session we're going to do the advanced query method in order for us to create queries from multiple tables we need to first make sure that the tables have been linked via these relationships here so you can check that from the database tools and then relationships and you should see these links and notice here that we have the customers table just linked to the contracts table so let's say that we want to create a listing or a query as the technical term is of the customer ID the first name last name street address and then the city zip state and then we want to also list the contract information for these customers so here's how we do that we'll close this first and now we go here under create and then we go under query wizard and then click on OK and then we are going to go first to customers we're gonna pick customer ID first name last name street address city zips and then we go to the next table here we go under contracts and then we'll pick up the contract number and then the amount date and type then we click on next and then click on next again and then here we could have some kind of meaningful name and then finish you notice at this point we have the list where the data that we requested customer first name last name and such and the data has been pulled from both tables so that is creating a query using the query wizard you could do also the similar thing as well if we save this now we can go and create one for invoices so let's say we're gonna see the contracts and how many invoices remain to be paid so we go here under query wizard and then go under simple query wizard again and we can do this with two other tables so we can go under contracts we can get the contract number and then we can go under invoices and then pick the invoice number a date and whether it was paid or not and then next and then give it a meaningful name click on finish and here we have a contract number the customer ID the contract amount date the type the invoice specific number the date that the invoice was issued and whether the invoice was paid so next we are going to learn how to utilize the advanced queries in a database in this session I'm going to briefly demonstrate how to utilize the query design in Access 2016 so far in our access tutorial we have learned how to use the query wizard in defining and designing a query but in most cases in Access 2016 and previous versions of access a more effective way to utilize queries and design queries is to use a query design the way that works is that instead of you going through step-by-step and adding specific fields in a query you can actually design this query using this method so to utilize the query design we click here on query design icon and then the big idea here is is that you pick the tables that you want to work with initially so in our case here the tables have been linked by using the relationship module that we saw earlier and now we'll pick the tables that we want to utilize and then click okay typically the relationship looks like this so we have customers then we have contracts and we have invoices the way it works is is that you have these tables here with all the different fields from each table and you pick specific fields from each table and you're creating a new query so for example we want here first name last name and let's say the invoice number and typically you can either double click on these fields that you want to add to the query down below or you can simply drag these fields down here like we did a moment ago if you double click on this asterisk sign it will insert all the fields that are part of that table now here we want for example the the invoice number the contract number and I'm double clicking at this point the item and the amount and then we want also whether it was paid or not now let's assume that these are the fields that we want in our case now you can run this and see what it looks like notice you have the first name last name actually they're kind of backwards you can readjust that we can go back and readjust it and we have invoices paid or not if we want to tweak this query again we go here under design mode and go into design view and then tweak this again so if we want it frizzle first name to be first simply drag this to the left and once we move the fields the way we want or customize this view the way we want then we can run this again and now know this first name is in the beginning and then you have last name and so on so that's how you briefly utilize the query design in Microsoft Access 2016 an Access database notice as well if we go back here to the design view you can define the sorting order you can define specific criteria whether the criteria is either or or different various criteria by the various fields here and that's what we'll learn the next and before I finalize it's completely here notice also there are additional parameters that you can utilize here and we'll cover this shortly as well such as the query builder and the totals field and such in this session I'm going to briefly demonstrate how to utilize criteria how to define the criteria within a query in Microsoft Access 2016 using a query design so here is how it works that saying we have this query here called invoices and contracts actually have not named this yet it's query number one we go here under a query design view and now let's say that we wanted to see for example with only the invoices that have not been paid if we go under the invoices table and notice here that this is a yes or no field to enter a criteria so we see only the invoices that have not been paid we go here under the design view and what we want to do is here under invoices paid notice there is an option for criteria there is a row here that we can put a criteria for this specific field so we want to display only the invoices that have not been paid in that case we want to display those that meet the criteria for no so we simply have to put no in there and now click somewhere else outside of this area and then around this query now notice it's displaying only those that have not been paid if we want it to display the text here whether yes or no or invoices paid or with the wording no next to it just to be sure we make sure that this check mark and by the way that should be there automatically make sure that it has the check mark right here now if we go and run it again notice it says invoice paid no and it's displaying only those if we want to hide that field we go back to the design view and then just take out the checkmark and then run it again and it will not display so that's how you insert a criteria within a query using the query design now you can have multiple criterias as well if we run this query right now notice that there are invoices ranging from $1,000 to more than thirty thousand here so let's say we want to see only the invoices that have not been paid of greater than ten thousand dollars so to insert that criterion we go back to the query design here and then under the amount for the specific criteria we want to put them so the both criterias would be met we say greater than equal ten thousand and then click anywhere outside of this field and then around this so we are saying we want the criteria all these fields plus the amount needs to be greater than or equal to ten thousand and then the invoice needs to be not paid we run this and now notice we have all these invoices display the other thing that we could do is we could sort this and we can go back here to the design view again and customize this further so under the sorting criteria we say we want to sort this in descending order when I see the largest amounts first followed by the smallest ones so notice we are doing three things so far click on run again and now notice the $105,000 invoice comes first and then the rest are following that then if we go back to the design view we can even insert as many criterias as you want and I hope you get the idea so you could sort for example by a specific zip code or by a specific city and so on under city for use only if I wanted to add an additional field and I want to insert it right there just simply drag it in there and then we could have various criteria so right now I don't have any credit by City and if I run it no this is just gonna display the city but let's say that I want the city Lansing or Holland so I'm display those two cities now in my case here I can go back to the design view and I'll enter two criteria so one of them will be Lansing now you have to type that correctly and I can say or Holland so it could be either one of them and then run them know this it is displaying only the city Lansing or Holland but then notice that the criteria is not quite what we were expecting earlier notice that we have 2500 so it's no longer just 10,000 or more the reason for that is because we have here in our criteria stating that the criteria could be Lansing greater than 10,000 and invoice is not paid or anything from the city called Holland so we either have to move this up here or euro is the criteria either or that we are using earlier but then keep in mind that it's not going to apply so we have to put them like this with or here and then the additional two criterias then we run it and notice these are the only clients that have not paid their invoices yet with greater than 10000 balance and only Lansing or Holland just for those two cities so that's how the query design works with multiple criterias within the query [Music] in this session I'm going to demonstrate how to use the query design and also define calculated fields or have calculated fields as part of a query design let's assume that we have a bunch of customers they have not paid their invoices and we want to calculate a late fee so part of our query we want to display what the late fee calculation would be so here's how we do that we go here under the query design and then first thing we need to pick the table so that we want to utilize so let's say we want customers and then we want contracts and then let's say we wanted the invoices so I'm just gonna list those three tables that we are utilizing at this point but in reality I'm going to use only customers and invoices in this case so we want the first name I'm double clicking on them last name and then let's say you want to stay the address city state and zip and then we want also the invoice number the date the item the amount and then we want whether it was paid or not I'm gonna resize this I'm just dragging it up so we can see this a little bit easier and now if I go and run this query no this is gonna display the data that we picked however it's not filtering yet or it's not giving us only the unpaid invoices to fix that we go under design view again and then we go under invoices we say not paid so whether it was paid under the criteria now there's a criteria row here we say no now the next thing that we want to do and by the way if we run this again notice it will the display now if we go back we want to create here a new field and we'll say late payment and then part of that field we wanted to calculate what the late payment would be so the way you do that is by clicking here on this option that it's called builder so we want to build a new calculated field we click on builder so first I selected the new field where we want to do this and secondly we want to utilize the Builder function before we can utilize the Builder function it's best to save this query first so we could say just click on the X here on the top right and we'll say yes then we'll call this late fees and then click OK now we go back here to the late fees and we run this query then we go under the design view by the way you could right click on it and choose design view as well to get to it and then we scroll all the way to them the MT new field here and then we want to click on builder on builder by the way it once you save it notice we have all these fields right here under the expression categories that's basically telling us that these are all the fields that are being utilized as part of this query so all that we have to do is we click on the invoice amount and double click on it and notice it's putting it in brackets and such and then we do the asterisk which is the multiplication so we want to say the amount multiplied by some kind of percentage so you could say it's 5% late fee for any unpaid invoices past 30 days or whatever so then you do that by x zero point or as part of your database you could design another field in the error column to say late fee like how much the late fee would be and then in that way you don't have to enter the 0.5 manually you can just multiply the late fee multiply it by the the actual amount of the invoice in our case we're just gonna do it this likely manually so we have the invoice amount times 0.5 and then we click on OK you could have simply selected another field there instead of 0.5 like I mentioned a moment ago now we click OK and then notice now it enters all kinds of codes here now instead of you choosing to have that as expression 1 we could have that called late fees or late fee and then simply ear on this and now notice if you scroll to the right you have here the invoice that was not paid it's $12,000 and the late fee is $600 and it has calculated this by the way for all the amounts now if you don't want this invoice paid no you can just simply hide it like and I'll show that in a moment let's say we want to format this in in currency in the dollar amounts we can do that as well and we go back here under the design view and then we want to choose to not display whether it was paid or not just that column we want to hide it and then the amount here if you click on the actual field and go on to property sheet here on the top or you could right click and choose property and then under the format we want to click on the drop down here and then we're going to choose the currency and then you can even choose a decimal places that say two decimal places and then you can close the property sheet here now if you run this again notice that the late fee will be in currency and that's how you calculate the late fee using the query design in Microsoft Access 2016 now we save this and then at any point for any of the customers as you enter and change data this will be generated automatically [Music] in this video i'm gonna demonstrate how to create reports and customized reports in Microsoft Access 2016 this process is going to start with the very basics and then we're going to move into some of the more advanced features to customizing reports in Microsoft Access so in this case we have a database with three tables and if you wanted to learn more as to how to work with an Access database please refer to the previous videos on this tutorial so we have three tables we have a contracts table let's assume we have contracts for customers and we have a customers table and then we have an invoice is stable so the tables look like this now in our case we also have those tables linked together which is a common feature of an Access database or any of the relational databases out there so as such if we go here under database tools and then we go on their relationships you'll see that we have those tables linked together and if you want to learn more about these you can check the previous tutorials as well and now we can create reports based on each one of those tables individually or we can create reports by pulling data from multiple tables for example if we wanted customer ID from the customers table and the first name and last name from the customers table then we can also pull for example the contact number we can pull also the amount and a signing date and the contact type because those two tables are linked together so let's learn how to do this to create a report we need to go here under create tab and then we can click here under reports and notice we have this whole section the reports so we can create using the design option the blank report start from scratch or we can use the report wizard if you're just starting with the databases I'll suggest that you start with the report wizard as that is going to be the simplest way for you to learn how to do this so we go here under report wizard and then you'd basically pick the first table that you want to pick the fields from so in our case we go to the customers table and let's say we want the first name and we want the last name and let's say we want the state address the city zip state and email so these are just some of the fields from that table now in our first instance here we are going to simply create a report just from one table so as we get the idea and then we are going to pull the data from multiple tables so we go here under next and then we choose how do we want to group those if we wanted to create groupings of those customers in our case for now we're just going to leave this alone and then we click on next and then here it's asking us do we want to sort those customers so we could technically choose to sort those by first name or by last name or by zip or any of those fields here so we're going to just say we want to sort them by first name then you can also choose additional sorting criteria as well subsequent ones typically the first option here is going to take priority then it's going to go to the second priority and so on so we click on next and then it's going to create the report here in the tabular format and then you can also choose whether you want the orientation to be portrait or landscape if it's a lot of fields that you're gonna have in your table it's best probably to choose landscape but for now with one of the number of fields that we have we just can't create it using the portrait view and then they give it a name here so just customer list we can call this whatever we want customer list and then it's going to give us a preview of this report we click on finish here and notice the preview has been created so if I close it here on the right hand side now notice under reports I have a new customer list report I double click on it you know this I can see all those customers here now you'd say well it's kind of cutting here the email field on the right-hand side how can I change that how can I customize that we can customize any of those reports that we create a couple ways or you can do this in a couple ways you can either go here under view as soon as we have selected the report here we can click on View and you can change it the layout view here so that's one way to customize it and this is under the layout view we can go and simply resize the fields so that's one way to do it so notice I'm just readjusting the width of those fields now probably the address needs to be a little longer notice I'm going to the right here and such the state doesn't have to be as wide than the zip I can adjust this accordingly you know this you need to adjust the labels here on the top as well I'm pressing here control a to select everything and we can also go here under format and we can change for example the font for this to be a smaller size so you can control how this will display by utilizing or changing the font visual aspects of it that's one way for you to customize this if you want it by the way these fields here on the top the labels to be in a different format you notice I'm holding down the control key here and I'm selecting all those labels now under the format then I can go here under format and then make this bold I can choose a different font size and all that type of stuff now there's also under the format we have to select all option that's I did earlier using the control a key on the keyboard if you wanted to change the top or the heading of this you can simply go here and double click on the heading and then customize this any way that you want so that's one easy way to customize this report that we just created here notice that there are different designs here that you can apply as well to this report so notice you have these new tabs here on the very top under and these are typically referred to as the contextual tools since we are tinkering and working here with the report module of the database and adjusting the layout and the design of it you know this we have this like design tab arrange tab format tab and even page setup so you can change the look in the field of this specific report you know this also under the design tab you also have these design themes so if I wanted to apply a different look and feel to this I can apply one of the themes now to go back to the normal view for this report we go here under view and then report view and notice it has changed it has been modified with the changes that we apply the earlier another way to customize your report is also to utilize the design view and I'll go more in depth about this shortly here so you go here on the design view after we have selected our report and this is kind of more complex and again notice you have here again the same contextual tool some of it the very top however you can change here notice it says page header if I wanted to move this field a little bit farther to the left and adjust the width and such again these are the labels notice I can do them more precisely from here so you can adjust the width of those fields here and you get the idea at this point now these would be just the labels on the top and these would be the actual fields in the bottom notice you can also adjust the footer what it will show up as at the bottom of the report from here as well so let's assume that I didn't need the email field you can simply press delete from here and delete both the label and the actual field the bottom stuff here it's actually the field from the table that it's linking to the table and then we can close this report here on the top save the changes and now double click on the report again and notice we do not have that field for the email also if you wanted to see a preview of this you can click here on the top left and choose print preview and this is how a report will look at this point now this you also have the footer here the page footer we can close the preview from here and at this point let's also learned how to add a field to an existing report so let's assume that this is our report and then somehow we missed adding the email field to an existing report of of course we could create something from scratch however in certain cases your report might be fairly complicated and you want to add another field or a couple fields so here's how you have the other fields to it you can right click on on the report and choose design view or you can go up here under view under the Home tab and choose design view so you'll kind of want to do this from the design view now on the right-hand side or in the very top here notice how it says add existing fields so you can click here on add existing fields so notice we have the list of fields here from our table and we can move this a little bit and then we want to add here the email field so it's simply drag it but we need to drag it notice this white spots here where the detail for the form is but we don't need to put it under page header we want to put it under the detail area this will be the content over our report we are kind of linking this field with the actual table from the customers table here so this is not the label it's actually the data from the table so we simply drag it and we just drop it where we want it then adjust the positioning of it on that report how you want it how wide you want it and all that stuff and then notice at this stage it's not giving us the option to put this the label for it because if I run this right now I save the design notice it's not going to have the label here on the top as to what this is we need to basically go under the design view again and and in this case the label is right there but know this it's kind of like if we move this a little bit no there's the email label it's over here to the left what you can do is you can simply click on that email label the one that came in or the one that it brought in delete it reposition your data field here how you want it and how why do you want and then above it under the header area that's where you need to create a new label for this so to add the label on the header what you do is you go here under the label area here on the design notice there's this icon label here and then choose where you want that label and how wide you want it and then type in their email hit enter and then a reposition it the way you want it realign it same thing with the one here as well and let's go under view here and choose report view you notice we have this email field created if we wanted to change the formatting and adjust the formatting again you can simply go under view go under layout view and then under design apply a different theme or apply the theme that you have from before now this is in certain spots here we have this or for this we might still need to adjust the formatting notice this has a border alone around it this field so we might need to adjust the formatting for it and get rid of the border to change the border there are a couple ways to do that you can either right click here under the field and notice I'm under the layout view you can do this also from the design view and if I go here under properties for this object so once I go to the property sheet for this specific field now this here under border it says to use a border-style:solid and I can change that so that it's just a parent or nothing and then you can choose the border effect as well if you need it to if I go here under and close this now that border should not appear anymore notice it's gone if I wanted to change the size of the font of course go back to layout view and for this field you can go under the format tab and then change the size to match the rest of the form and then if we go here under design tab again and if we go and view this report this is how it will look at this point instead of spending all this time to customize the look and feel of this you can also go back here and use what's called the format painter so you can copy the formatting of a specific cell and adjust it for other cells so if I go back here to my layout view and I will go and undo some of those things that I had done earlier so some of those changes now this right now if I go back to view here you notice I still have my border stuff again here go under format painter here from the layout view and I can copy the formatting of an existing cell and apply it to another one so you can your to any of these cells go under format here and then use this icon right here the format painter that copies the formatting of an object and applies it to another object so click on it and then go to the other object any of these here and apply it and now it's all changed automatically so that's the easier way to do this then close it click on Save Changes and now you can double click on it and this is your report and this is the print preview of this report so this is how you create a report this is how you customize the report and how you remove fields from a report and also how you additional fields to the report if you have an existing one in this video demonstrate how to create a report from multiple tables in Microsoft Access reports are one of the most commonly used features of an Access database as a user most of the time you're not going to be creating databases and you're not going to be designing them and you're not going to be creating your tables in a database even though you can do that however most of the time you're going to be creating queries in a database and you're gonna be creating reports from the existing data from that database now the reports can come in handy whether you're in a corporate environment where you have a corporate database or whether you have an Access database but the concept it's going to be pretty much the same since the tables are linked typically and the linking of the tables you can see it here under the database tools and relationships we have customers customers have contracts and then for each contract their invoices as well so it kind of all this stuff it's linked together however now in our case we can create a report from fields from the customers table phrase on first name last name and then contact information and also include the contract so we're going to see who our customers are what contracts those customers have so that's what we're going to learn in this session so to create a report you can go here under create and then we want to go under this section here under the reports section and we want to look here under the reports section my suggestion if you're starting is to use the report wizard so we click on that and then we are going to pick some fields here from the customers table so we go for example if you one of the customer ID you can pick it as well click on add customer ID first name last name and let's say we want just their telephone number and the email address notice you can pick only certain fields then you can also go here under the drop-down before we move to the next step and we can pick from the contacts table from another table you can pick fields from that table as well because those tables are linked with primary keys and foreign keys so we go here under table contracts and now let's say we want the contract amount and them in the contract type so that's all we want in our case then we click on next since we are using multiple tables the system is asking us do we want to group the report results by customer or by contracts so in this case if you have a customer that has multiple contacts you want all those contracts for that customer group together so in my case I'm going to choose customers and it's going to list me the customer and then a sub listing of all that customers contacts so that's what this is for so you have customer up here and then the contracts will be listed right below it then we click here next and then so what kind of levels do we want here so we could say we want the customer ID first name last name telephone email and then the contact information right below it so then we click on next and then how do you want to sort those so we could sort those records and then you could also choose here that the report summary options and here you could say you want to see what the average is or what the sum of all the contracts was and all that type of stuff for each customer you can just choose here sum and the system is going to give you the total of all their reports for each customer or all of their invoices or contracts for each customer so this option is going to be available if you have numbers in their number values or amounts in their then we click on next and then it's asking us do we want to have the orientation as portrait or landscape and you can tinker with that and check it out but in our case since we do not have as many fields it's okay to have this portrait then we click on next again and then we have to give a name for this report so in our case we'll say customers and contracts or you could say contacts by customer now you can change this later as well but it's important to give meaningful names as you start with a report then click on finish and this is how our report will look like so this is kind of a preview we need to kind of adjust it a little bit notice we have these amounts or dollar signs here and all this type of stuff so we'll need to customize this in a little bit so for now I'm going to close it here to customize this further we go under view and if you wanted a preview of it at this stage you could do a preview but it's not ready yet you know this is not quite ready with all its stuff yet we can go back here close the print preview option go under layout view and we can adjust the layout of this a little bit so notice how we have the amounts here we could actually move this farther to the left so these will be the amounts and then we can resize this field then we can go and get also the description what the contract is and then resize that as well how we want it to look then also resize other objects as you'd like here now notice we'll assume here we know what the contracts are and such but now there's they're the actual labels are here on the right hand side I'm going to delete them for now just so that for simplicity because we can know the amount here and also we know what the contract was now this over here this is supposed to be the total for each contract because we have chosen the totals so this you might want to move it also right below the announce area and then you know this you have here summary for customer ID and then 30-year records and such you could change the wording here now this it has some you can know that field closer and instead of just some you could say total and basically just the format in any way you want remember you can also apply themes for this to customize it so it looks slightly fancier without you having to waste your afternoon with this and then if this is not very useful in your case you can simply delete that specific field here and then also remember that you can apply here the formatting so let's say I wanted the formatting to be something like the previous cells here to remove this border and notice you can go under format take the format painter applied to another field and notice it's apply so that's an easier way to apply the formatting and again you can take the time to customize this and make it as fancy as you want of course you can also adjust the labels here in the very top you could format that differently as well and let's assume that you wanted these labels here on the top you want them bold you can again format them any way you want let's say that you want it also the actual customer information just the customer data a certain font or color or whatever you could format it however you want and it would make it more visually pleasing so this is one way to customize this using the layout view so if I close this and save the changes by the way you have to save the changes in Microsoft Access only one year change in the design of something otherwise it will save the data automatically into your table so into your forms so now if I open this up again this is what it will look like if I go to the print preview this is what it'll look like again for the print preview not bad you have the customer 11 or 11 15 here whatever that number is and notice you have all their contracts and also the total for each contract here and you have also the footer for this page for this report now if you wanted to make this and make more changes in a granule format and such and go into more detail for tweaking the formatting of this you can also use under if I close here the print preview you can use the design view and under the design view you can adjust here additional components for example the contact amount here notice it's not fully lined up I could adjust the size of it that adjust how much space is between the amounts and contacts and the total here and format certain things a different way and let's say between each customer I wanted a little bit more space I could adjust the spacing after each record and so on now if I go and save this and run it again by double clicking on it now this there's a little bit more space between each customer and the total and the prior customers information so that's one way that you can customize this and tweak this further as you are working with the reports and such and we learned about this a little bit in a previous video sometimes you might want to add a new field to the report so for example we have this report here but for whatever reason we wanted to also know here what the actual contract ID is whatever reason is part of this report so instead of re creating the report from scratch we could simply add one more field to this report to this existing report so to have the field you could either go here on layout view and then under this area right here add an existing field you could do it from here and you could choose to add so right now it's showing us the fields from the customer table however we could choose all tables and we want it under the contract table and really want it let's say the contract number and we want that contract number at the end of the contract information area or wherever you need it so we could drag it from here if actually put it in the very beginning and place it basically wherever you need it wherever you want it I'm going to delete a little label for it on the top for now so that we keep our report slightly cleaner just the size of it accordingly how you want it change also the font for it and that's one way to display it in there using the layout view the other way to display this field in there to add this field and this is what you'll probably use in most business environments is by using the design view so if I go here to my report I'll close it for now open up the report again and go under view design view or I could right click on it and choose design view that's another way to do it now at this point you notice these are my existing fields I have the contract amount I have a contract type however let's assume that right the next contract type I want to add another field in this case I go here under add the existing fields and I could have the field the same way that I showed a moment ago in their ad contract number over here I want to delete the label for it unless you have a use for it at this point in this view and probably don't need the number to be that long and then that field at this point should have been added so if I close it and run it I should have the contract number at the end of each contract for each customer here so at this point let me explain how this data is actually linked to the and how you control the linking of each field to the actual table because the data from the report is actually pulled from the tables it's not really stored in the report it's actually just at any time you're under the report its querying it's looking up the data in the table and it's displaying whatever the criteria is that you have specified go here under the design view and if you go to any of these fields or the data fields here let's say contract type or contact amount and such you can select the field and then just go under the property sheet property sheet it's going to do is it's going to bring up the property details for this specific field that we just selected and then here it's telling us how it's going to format it and all that type of stuff and it's the contract amount that we have selected currently it's good we could select any of the fields here we'd want to from this report and we could customize here any of the details how we want this to be formatted basically so this is just the formatting currently for this item we're controlling how the formatting is going to display for that value so far however if we go here under the data tab and the control source is where is this linked to it's telling us what the source of the control to what table and what field is it picking so in this case it's actually going to the contract amount from the contracts table and if you click on the drop down if for some reason you need to link it to a different field that you have selected initially in your report you could just pick it from here or if you want to link it to a completely different field that does not show up over here under the list of fields for your current report you can click on these three dots and then go under your database that you have opened and you go to link it to a different field within one of your tables so for example under contracts here if I wanted to link it to the date or to another one whatever it may be that I want it simply select it from here and then click OK and then run the report again and it's going to link it to a different control source from a different field on that table that you select so that's how we can create a report from multiple tables and also group the results and create a calculated field for the amounts within each order for each customer and group those orders together so that's one way to do that so if you want to further customize this report and add or change the order of those fields and such you can simply drag those fields by going here to the design view so if you're here under design view and let's say we wanted to change the order of those fields you can simply shuffle the fields around here so this will put the contract number in the beginning you can resize this how you want it the amount now it'll be in the end also the total we'll put the total on the right-hand side at this point right below the amount and you can readjust the spacing between them and now if we close it save the changes we run it again you know this we have the number in the beginning we have the actual contract information and then we have the amount on the right-hand side if we wanted to further customize it in this case such as formatting and such we can go here under view layout view and tweak this a little bit more to use a format painter and click on the destination cell and you'll apply the formatting of the previous cell that you have selected now if we close it save the changes run it again now it should have been readjusted the layout of it if you want to see a preview of it click on print preview and at this point it'll show up like this of course you can customize this stuff and make it as fancy as you would like you can make the reports very complex it's very customizable basically [Music]

Channel: Kaceli TechTraining

Views: 428,213

Rating: 4.8820744 out of 5

Keywords: Microsoft Access 2016 tutorial, access 2016 complete tutorial, free tutorial on access 2016, access 2016 for beginners, queries in access 2016, reports in access 2016, creating tables in access 2016, creating forms in access 2016, primary keys in an access database, linking tables in an access database, access 2016 for students, access 2016 tutorial, skaceli, tutorial on microsoft access 2016, using access 2016

Id: xRYSP-yFgb0

Length: 139min 47sec (8387 seconds)

Published: Mon Jan 01 2018