Access 2019 Complete Tutorial: Microsoft Access Made Easy

Hello my name is Sali Kaceli. The following is a  comprehensive tutorial on using Microsoft access.   During this tutorial I will cover basic concepts  related to databases and how to design and use a   basic database using access. We will start  with the 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 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   information is related from one table to another  and that's partially what is 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 them briefly are 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 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 the contract table,   and then we have invoices. So think again about  these as separate lists. So the customer table of   course it would 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 would 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 contracts table if we double click on it   we have the contract number the customer ID  contract amount the date and then 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 the database like I 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 wanted 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 we'll   learn how 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 a 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'd 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. In 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 the 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 onto and then the reports  how the data is printed out or output. Once you're in Microsoft access you'll notice  it very similar to Microsoft word and excel and   other applications. On the left hand side you'll  have the recent databases or files that you have   been working with. Then below you have here  the option to go ahead and open a pre-existing   database and then on the right-hand side here  we have the option to create a blank database   that you will be utilizing in a moment and then  further down here we have different templates   that are available in Microsoft access. 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 we understand how  databases work how to use Microsoft access.   So in our case here I'm going to very briefly  utilize here this database called students. Now   here's 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 it and all that type of thing,  in Microsoft access as soon as you choose to   create a database you have to give it a name and  you 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 for creating a database from scratch.   So first thing that you're presented here is a  tutorial on how to use this not every one of them   is going to give you this option so we're going  to simply click on get started here to utilize it.   Now a couple of things here just briefly so  that we get a couple of the concepts and I will   demonstrate these much 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 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 serve  for displaying the data in your database. So 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 a new student we simply click on new  student and then we fill in the information there.   You can add the picture you can add additional  information then click on save and then new   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 form. 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 would be stored so it's  very similar to like an excel spreadsheet   but it's just a bunch of tables and typically  in a 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 term relational databases because   the tables are linked. The other component here  is the queries, the next one would be the forms   that we just used a moment ago and then if you  wanted to create a report of all the students   you simply click on all students and it  will display a more visually pleasant   report for you to print it out. So those are  some of the basic concepts using the template.   So now stay tuned for the next session that will  learn how to design our database from scratch.   We'll create the first table and then  start building a couple additional tables.   In this session we are going to create a database  from scratch and we are going to learn about some   of the various components of a Microsoft access  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. 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 of 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. 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 for this new table.   Next to it here you have also tell me what you  want to do or the tell me feature 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 wizard  and it just basically 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. Typically you'll 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 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 then 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 record  so you'd say this is record one or record two   or records 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 we could change that to say customer  ID the the type here for customer ID   it typically it needs to be a number so notice  under the data type this is an auto number. That   means that when it goes to the next 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  short text here and then we're going to call this   last name and then the third field we are going  to make this let's say the street address and   this would be text as well. The next field here  it will be city and then the next, state and then   the next one zip. Now 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  going to 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 could 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 we could 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 example 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 the data   typically we for now we're going to enter it here  directly into the 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 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 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 some 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 the   way to differ from one customer from one record  to another is by assigning them something unique.   For example a customer ID, a unique customer  ID, and those are typically referred to that   becomes your primary key. The primary key again  that is what it 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 is a   data sheet 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 is the column for each column in   that table that we saw earlier so we could change  the names here or you could change the data type   as well. If you have a lot of data in your table  and you go and tinker and manipulate the data type   you might most likely get an error message so  keep that 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 there so 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 structure 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 long  text so you want to make sure here that   the user can enter enough text I believe  that would be 64, 000 characters that it will   accept in that field when you start typing on it.  Once you're done with any of the design changes   now we click on close here and it's going to  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 are 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 access table. Now this is not all. The 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  going to 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. In this brief video we're going to learn how  to add additional fields to a table in an   access database and then we are 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. 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 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 it's 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 we say 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 is going to allow us to put a check mark  basically have a check box. Once we have tweaked   that you can add additional fields in there by  the way you have 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 very briefly in the last session each  table needs to have a primary key and the primary   key can be a number field it could be anything but  it has to be something. Typically it's a number   and an other number something that increases or  some kind of code and it is what differentiates   one record from another. So if you have two  people with the same name and 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 change it   to this one then that means that there can be  no two people with the same name on that table   so you want to make sure that you set the primary  key on the correct field and that's why typically   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 the 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 new 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   that 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 is 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 well about this field   file one file two file three 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 we'll 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 in a 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 look up 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'm 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 notice that I have now Jim Smith, my  new customer that I entered manually or that I   entered through the form. So again the forms are  very similar to 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 used 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 and   such that's why I said that the other method 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   it's 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 a 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 I cannot display it 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 the second area here. Then   once you're all set here we can close this save  the changes to it and if we go back to our form   notice first name here it's in bold. 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're putting criteria within   that query or that question. Basically 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 and the city and  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 by 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 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  and I'm entering this using the form   now if I go and run this query notice I have  customer 4 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 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 such. So that's very   briefly the queries. If you needed to change  and we'll learn how to modify the queries so   just check the 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 under 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. Whenever you build  a report on the query it 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 street address   and whatever else that you want here zip  code and such. And then we click on next,   next and we can choose how to sort those customers  let's say by first name or by last name or by   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  sign that means that it does not fit in that   field so you need to still resize this. Or the  other method here to 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 a design view for   designing any of those components of the database.  Now when we are done with this report we close it.   Since we tinkered with the design here just click  on save to save it. Close the other elements as   well and then go here under 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 our 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. 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 you should have access to a link to  download the working files. The working file is a   zip 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 would 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 the 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 pieces 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  we 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 contract 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 a 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  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.   So in this session we're going to learn how to  create tables in an access database by importing   the data from another system. We'll 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 data type matches   from the external file with your current design  in Microsoft access. Since we are starting here   understanding the concepts here and 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 number street 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 are 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 working files  and notice we have here customer list. Click on   open and then 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 we click ok here and then it  says show the worksheet so this is our worksheet   this is 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 had for example 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 you're importing   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 going to 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   the number this would be typically it would be  a double here that you choose the data type and   such for the sake keeping it simple I'm going to  leave this as short text. Then we click on next.   Now it says do you want to choose the primary key.  Notice it's trying 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 customer ID here.   However keep in mind it cannot have two records  with the same customer ID so you need to be   sure that the data that you're 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   we'll call it customers and then simply click on  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 of  those customers there are 38 of them that it   brought from that table. Notice that the table  now it has the field names on 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   going to add it and then you choose the type  here so we're going to make the comments field   to be a long text here and then we close design  and now if we go back to customers you'll notice   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 a contracts 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 a contract list we're  going to create a new table along with a 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 allow duplicates but  typically we 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 next 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  contracts 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 in this next session we're going to learn   how to import data or how to create a  table by importing data from a csv file. 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 want to create a table called   invoices and that data we imported 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 we want to import the data from a text   file. So we click on next here locate wherever our  file is and notice it's under my working files and   this would be also in your working files if you  expanded it. 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. Then we 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. I'll go next again so 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 want 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 in this new table it's   called invoices. Notice one key aspect here  if we go to customers we have here customer ID   this will be our primary key for this table so  even if we went over here under design view notice   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 to 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 the 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 would 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 the 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 11005 that's saying that this contract  3033 belongs to customer 11005. The concept to   remember is when you're designing this you need to  design also for foreign keys 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 case in the foreign case. 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 with and typically   what you want to do is that you want to have the  primary keys and the foreign keys 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 these   and at this point we have three tables. So we  have the customers table we have the invoices   table and 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 the 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 the foreign  key in this case goes to match with the primary   key of the contracts table. So as a design that  the database you need to factor in and plan on   what the foreign keys are going to 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 the  next thing that we need to do here is   we need to go here under table tools and  then we're going to define the relationships.   So we go here under relations and then we choose  to add the customers table, we choose to add the   contracts table and then the invoices 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 organized 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 notice  it's saying it's going to 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 the 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 going to 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 with   the foreign key from the invoices table so we want  to say from invoices here we want to drag this   to the foreign key from the invoices table. Again  the same idea contract number to contract number   one 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 here 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 and the date and   the contract type. That's when you can kind of  cross and pull the data from multiple systems.   Then we click here on close and then it's going  to 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 notice I 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 the contract number   and then the amounts the date and the type.  Notice so we pulled the data from two tables.   From here you can create all kinds of other things  whether it's reports or new queries or forms and   we'll cover those in the next few sessions. One  other thing before I finalize this session I   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 in 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'll  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 going to be   a short text you want that to be a number an auto  number typically. I'm not going to 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 auto numbers but they have to match  on both corresponding tables. Remember that   in your design and it will save you a lot  of frustration. So that's how defining the   relationships in an access database works it's  one of the key aspects of designing a database successfully.   In this session I'm going to demonstrate how  to create a compound form to input data between   two or more tables in access. So once we have  linked the tables and defined 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 a form.  Forms that can be created   either on an individual table for example if I go  here under create and I go under form wizard here   and let's say I want customers I want to create  a new form for all my customers click on next   and then 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 for 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 the 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 it's a  one-to-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 again 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 you 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 on the customer area now we can  go from one customer to the next and so on.   If you 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   to add new comments or whether you want to  add new forms or new customers from here.   Now remember all the data that 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 new customer that we just   entered earlier. So that's how compound forms  work and how you create them and update them   and use them. The next session I'm going to show  you how to customize the form in Microsoft access   2016. Earlier in this tutorial I created a  form called contracts 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 that 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 formatted slightly 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 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 will be a little bit easier to read.   You want to make sure that you adjust 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 this and changing  the layout view notice that you have the form   layout tools. These are the 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   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 apply  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 that you have also various color  schemes here that you can 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 sheet and this tells us where   this field is linked to. It controls the linking  to the table itself. So for example right here it   says that it's linked to the comments field on the  customers 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 will  break that link and then the data will not be   updated. This is one way to customize this form.  The next way to customize is 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.   Notice that you can change here colors and  alternate rows and background 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 needed 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 applied 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.  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 says 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 11040 then  click on find next and then notice 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 let's say by first name or by  some other field. So I'm going to go back here and   let's say by first name you can click on the field  there and then choose Michael find 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 notice you have all the different options   here so you can simply uncheck them all and then  pick what you want here so let's say Michael and   unfortunately it's beyond the recording area here  but you can you 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  uncheck 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 with 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. In this session I'm going to demonstrate how to  create queries from multiple tables in an access   database. We'll create those queries using the  query wizard and then in the next session we are   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 which is linked to the contracts table.   So let's say that we want to 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 going to pick customer ID first   name last name state 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. Notice at this point we have  the list with 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 want to 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 the 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 the 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 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 on ok. Typically the relationship  looks like this so we have customers then we   have contracts and we have invoices. The way  it works 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 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 are 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 wanted for example 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 in 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 various fields   here and that's what we'll learn next. And before  I finalize it completely here notice also there   are additional parameters that you can utilize  here and we'll cover these 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 the   query design. So here's how it works. Let's say we  have this query here called invoices and contracts   actually we have not named this yet it's query  number one we go here under query design view   and now let's say that we wanted to see for  example 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 run this query. Now notice it's  displaying only those that have not been paid.   If we wanted to display the text here  whether yes or no or invoice is paid   with a 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 check mark   and then run it again and it will not display  it. 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 a thousand dollars 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  criteria 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 run this. So we are saying  we want the criteria all of these fields plus the   amount needs to be greater than or equal to 10,000  and then the invoice needs to be not paid. We run   this and now notice we have all these invoices  displayed. 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 example 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 criteria by city and if I run  it notice it's just going to display the city but   let's say that I want the city Lansing or Holland  so I want to display those two cities. Now in my   case here I can go back to 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. Notice 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 2,500 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   an invoice is not paid or anything from the city  called Holland. So we either have to move this   up here or utilize 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 the or here and the additional   two criterias then we run it and notice these  are the only clients that have not paid their   invoices yet with the greater than 10,000 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.   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 the  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 tables that we want   to utilize so let's say we want customers then  we want contracts and then let's say we wanted   the invoices. So I'm just going to 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 wanted to street 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 going to 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 notice it's going to 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 notice the 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'll be displayed now if we go back we  want to create here a new field and it will 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 is 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 then 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 the empty new  field here and then we want to click on builder.   On builder by the way 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 five percent late fee for  any unpaid invoices past 30 days or whatever.   So then you do that by times zero point or  as part of your database you could design   another field in there or 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 multiplied by   the actual amount of the invoice. In  our case we're just going to do it this   slightly 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 one we could have that  called late fees or late fee and then simply run   this and now notice if you scroll to the right  you have here the invoice was not paid it's twelve   thousand dollars and the late fee is six hundred  dollars 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 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 under 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 we're going to  choose the currency. And then you can even choose   a decimal places let's 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.   In this video I'm going to demonstrate how  to create reports and customize reports in   Microsoft access. This process is going to start  with the very basics and then we're going to move   into some of the more advanced features  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 then   we have a customers table and then we have an  invoices table 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 under  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  contract number we can pull also the amount   and 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 with 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 would suggest that you start with a 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 street address the city zip state and email.   So these are just the 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  that 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 are going  to just say we want to sort them by first name.   Then you can also choose additional sorting  criterias 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 going to have in  your table it's best probably to choose landscape   but for now with the number of fields that we have  we just can create it using the portrait view. And then we give it a name here so just the  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.  If I double click on it notice 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 to 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 longer notice I'm going to the right here   and such. The state doesn't have to be as wide  then the zip I can adjust this accordingly. Notice you need to adjust the  labels here on the top as well. I'm pressing here ctrl 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 wanted by the way these fields here on the top  of the labels to be in a different format notice   I'm holding down the ctrl 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. Notice also under  the format we had the select all option that I   did earlier using the ctrl 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  notice we have this like design tab arrange tab   format tab and even page setup so you can change  the look and the feel of this specific report. Notice 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 those 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 applied  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 under 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 tools on 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 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. Notice   you also have the footer here the page  footer. We can close the preview from here   and at this point let's also learn 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 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 add   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 design view. Now on the right hand side or on  the very top here notice how it says add existing   fields so you can click here and 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 we simply drag it but we need to drag it  notice in this white spots here where the detail   for the form is. We don't need to put it under  page header we want to put it under the detail   area this would be the content of 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   type of 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 in this case the label is right there  but notice it's kind of like if we move   this a little bit notice 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 it came in or the one that it brought in   delete it reposition your data field here  how you want it and how wide 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 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. Notice we have this  email field created. If we wanted to 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 had from before.   Now notice 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 in 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 notice here under border it   says to use a border style solid and I can change  that so that it's just transparent or nothing and then you can choose the border  effect as well if you need it too.   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'll go and undo some of those things that I  had done earlier so some of those changes notice   right now if I go back to view here notice I still  I 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 I can go 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 the report and also how you add additional  fields to the report if you had an existing one.   In this video I'll 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 new  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 going   to 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 in  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 there are invoices as well  so it kind of all this stuff is linked together   however now in our case we can create a report  from fields from the customers table for example   first name last name and then contact information  and also include the contract so we want 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 report 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 wanted 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 contracts table from another  table we 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 then 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   grouped 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  contracts. 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 on 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 the the report summary options. And  here let's 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 some and the system is going to  give you the total of all their reports for each   customer where 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 there. 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 contracts by customer. Now you can change this later as well but it's  important to give meaningful names as you start   with the 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   no this is not quite ready with all of 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 would be the amounts   and then we can resize this field then  we can go and get also the description   of what the contract is and then resize that  as well how we want it to look. Then also   resize other objects that you'd like here.  Now notice we'll assume here we know what   the contracts are and such but notice that 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 kind of 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 amounts area and then notice you  have here summary for customer ID and then three   records and such. You could change the wording  here. Notice it has sum you can move that field   closer and instead of just sum you could say  total and basically adjust the formatting 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   apply it to another field and notice it's applied.  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 on 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 wanted 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 when you're changing the design   of something otherwise it will save the data  automatically into your tables or into your   forms. So now if I open this up again this is what  it would 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 contract amount here notice it's  not fully lined up I could adjust the size of it   I could adjust the how much space is between  the amounts and contracts 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   notice there's a little bit more space  between each customer and the total and   the prior customer's information. So that's one  way that you can customize this and tweak this   further. As you're 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   for whatever reason is part of this report. So  instead of us recreating the report from scratch   we could simply add one more field to this report  to this existing report. So to add 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 we wanted 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   actually put it in the very beginning and place  it basically wherever you need it wherever you   want it. I'm going to delete the little label for  it on the top for now so that we keep our report   slightly cleaner adjust 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 notice  these are my existing fields I have the contract   amount I have the contract type however let's  assume that right next to contract type I want to   add another field. In this case I go here under  add existing fields and I could add the field   the same way that I showed a moment ago and drag  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   on the report it's 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 design view and if you go to any of these   fields or the data fields here let's say  contract type or contract 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 could we could select   any of the fields here we would 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. So 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 had 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 could 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 wanted  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 go 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. Then you can readjust the spacing between  them and now if we close it save the changes   run it again notice 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 use  a format painter and click on the   destination cell it'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 will 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] In this session I'm going to demonstrate how to create a query with user input and as a result of that query then we'll create also a report where the user will have to input data some kind of data and then the report will be created automatically or generated automatically. So first we have here a table called contracts and the contacts table has notice the customer ID, amount, signing date and such. By the way this works the same way for multiple tables as well as long as they are linked together so the concept it's the same whether we are using a table here or multiple tables. So in our case we'll want to create a query where the user will actually have to input let's say the the date, a specific date. So we'll go here under create, go under queries, query wizard simple query wizard, okay, and then we'll go under contracts and we'll put the number ID, amount, date, and the type. Any of the fields that you want to include. Click on next next and initially we are not inserting any criteria yet. We give it a name and then finish. At this point notice that it ran the query with all the fields that we selected and the query is listed down here. Now this stage we are going to go and change the design of this query where the user will be prompted to enter let's say the date. We are going to change the design. You can either do it open it up here this query first and then go under the home tab and then go under view and go into design view or you can right click and choose design view. Now notice here these are the fields that we have selected that we are using for our query and notice under there's an option here for a criteria. So let's say we want the signing date. We're going to put a criteria in there where the user is actually going to input that and the criteria if you're going to use user input you need to put it in brackets and then close brackets as well. We'll close it at this point. We could of course run it but we're going to close it so we save these changes. And before we run it if we go here under contracts notice that we have some dates here for example 3/1/2010 or 2/9/2010 or 5/28/2010. So  let's take note of 3/1/2010 and 5/28/2010. Now we're going to run this query that we created and notice it says enter the date. This is looking for that parameter so the user needs to just type 3/1/2010  and then hit ok and notice there are two records with this from that table. If I go and rerun this again and let's say I do now I want to run it on 5/28/2010.  And notice we have two more records as well. So that's how we create a query using user input or with user input. From here you can actually create a report. So reports usually run from data based on the tables or you can run reports from queries. So this you're learning actually two concepts: you're learning how to run a report from a query and also that query and the report then it is going to ask for user input. So let's go under create tab to create the report. Click on the report wizard and then we are going to select the query here contacts by date. Pick all the fields that you want. Click on next. Choose whether you want to do any grouping or any of that type of stuff for now we're going to leave it the way it is. Next you can choose to sort this by contract number or ID or however you want. Next and then click on next again and then give it a name contracts by date in our case. And you can name this however you want. Click on finish. Notice it's prompting us for the date  so if we say 3/1/2010, it should give us only those two records that match that criteria. If we wanted to run this again for another date we can simply go here under contracts by date user input and let's say 5/28/2010. Click ok and there is our data. Now you can customize of course this report any way you wish to the formatting and such so notice we have these number field here and such you can always customize that and choose to make that larger. And you can reference the previous video on how to do the formatting of the reports and customizing other reports. And probably at this point you are wondering well how can I specify and how can I have this query so that instead of me putting 3/1/2010 date and giving me just for that specific date can I have this query customized so I put the start date and it gives me all and also I put the end date for a particular range and then have the system list me all those criterias. So and that is very doable and you all you'd have to do basically go back here to the design view and we need to tweak and change this to include the range. So we can accomplish this by doing the followings: we enter the range, the starting range here, it has to be in brackets, you can have those words be whatever you want them to be, you put also the end range in brackets as well and you use the word and in capital letters. Now here we are saying we want anything greater or equal to the start date and anything less or equal to the end date. Then we save this by clicking on the save button here. On the top we'll close the query and then run it again. Now it says enter the start date and we enter 3/1/2010 of course it has to fall within the range of data that you have in the system in your table. We enter also the end date 5/28/2010 and notice the system displays the data that falls within those parameters. We could run this for any date ranges so we could say 1/1/2010 I want to see all the contracts from the January 1st and then all the way for the first 6 months to 6/30/2010. Click ok and notice these are all the records or all the contracts for that date range. Now you can do the same thing you can create a report for this where you can run this report at any point. The thing of the report or the advantage of the report is that you can make the data look nicer and such. If we go here under create and we go under reports and report wizard and we go under the contracts by date we insert all those fields in there, I just clicked on all the fields, press next, next, we can order them by contract number or whatever there by date however you want them. Let's do them by date, next, next. We give it a meaningful name click on finish. Notice it's trying to run it at this point so we say 3/1/2010 to 12/31/2010. Click ok notice we have the data for all of that date range. Of course you can go here and change the look and feel of this customize this report any way that you would prefer. And you can go into the edit option here under design and then view in the layout view and adjust how this is displayed. Format this any way you prefer change the look and feel of it. Close it, saving it, and you can run this report at any point for any sales or whatever it is and it works exactly the same way with multiple tables as well as long as they are linked properly. So that's how you create a report based on a query with user input whether it's single user input or multiple user input. It's one of the powerful features that most users will use in Microsoft access from day-to-day tasks and business tasks. In this session I'm going to demonstrate how to export data from access into excel. A lot of times in the business scenario and such you can run reports you can run queries or you want to export data from your database and put it into an excel spreadsheet and then work with the data and create charts and or use it for other functions from within excel. So you can do it from the tables, you can do it from a query. you can export the data from a query, or you can export the data from a report. So let's see from first from a table exporting data from a table into excel. We click here on the contracts table which looks like this at this point we right click on it and there's this option for export and you can choose to export it in any of those formats. You could also go under external data here and choose to export from up here into excel. So right click, export, excel, and then choose a location where you want to save this and how you want the formatting and the layout if you prefer. Click ok and you could choose to save those steps. Those steps by the way will show up on the external data here under the saved saved exports. Click close. Now that data should be in excel. If I go here to my file manager, notice I have a file here called contracts. I open this file and now I can work with this any way that I prefer from excel and of course they're two separate entities at this point so if you change something in excel, it's not going to update it in Microsoft access. So that's how you export data from a table. The process is exactly the same for exporting the data from a query or from a report. So for the query we run the query first so in this case I have the query here the date parameter and all that type of stuff so so this is my data and now at this point I can simply right click choose export, excel, choose where I want to store it. Give it a name, press save, and finish. And we can do this for the contracts as well by customer. Same thing with the report this is our report right click choose export, choose excel format, choose where you want to store it, notice contacts by customer report.  Click ok and now if I go to my folder where I exported this, notice the data is there and this was the query that I ran earlier for the dates from January 1 to June 30th from the query. So that's how exporting data works from Microsoft access to another system such as Microsoft excel. It's a commonly used feature in business. In this session I'll demonstrate how to create a mail merge using a query from Microsoft access. In order to create a mail merge we can either use a table in Microsoft access, an existing table, or you could use an existing query. So in our case we are going to first create the query and then we are going to create the mail merge. That way you learn both at the same time. So we go here under create, go under queries here, query wizard, and then simple query okay and then we're gonna go under customers and let's say we want to use a customer ID, customer first name, last name and then let's say the email and also let's go under the contracts table and we'll get the contract amount and then the contract type as well. So we are just creating a query at this point with those fields from two tables. We click on next, click on next. We give also a name to this call it and then click on finish. So the data is here at this point. Now to create the mail merge we can do this two ways: we can either create the mail merge directly from Microsoft access or we can go to word first and create the mail merge from there or the other option would be to dump this data into Microsoft excel and then do the mail merge from Microsoft excel. So we'll basically do it from access from this point and then I'll show you from word as well. So if we go here to the this is our query and then we go under external data notice you also can right click so we have our query here and then we go to external data and the other option here is to actually create a word merge. So word merge it says you specify the table and you create a mail merge wizard through Microsoft word. We click on Microsoft word merge here, it says do you want to link it to an existing document or do you want to create a new one? We'll just create a new one because we don't have an existing letter currently. Click ok. Now the system is going to open Microsoft word automatically and here at this stage it's asking us as to what we want to do. Do we want to create letters, email messages, or envelopes and such. Once we are in Microsoft word here we type our letter we can say "dear" and then insert field, we put first name comma and we basically just type our letter and then every so after in our letter we can actually include here various fields from our access table. Basically type the letter here with incorporating the fields that we have in our access database. So here we could use their first name, last name, we could use their contract amount stated somewhere and then a contract type. If we needed more fields then we need to go and customize our query from the access database.  So for now I'm just going to put the amount and the type here. So we say "thank you for partnering with us to meet your needs with the recent contract" and the computer will put the amount in there. You could put the contact type. Of course it has to make sense.  And then you basically finish typing your letter. Then we go here under the next stage here or start your document and then we can use the current document in our case or we could have created a use an existing document if you have a letter from before. Then we go select recipients, we'll use an existing list and then we'll select the existing list here which is basically a customer query for mail merge so we don't really need to select the list it's already selected because we started the mail merge from Microsoft access so that's how it's linked automatically there. Now here we write our letter which we actually just did earlier and then it says here previewer letters notice "dear Owen" and then contract one and then this was the contract. And then the next step here is to complete the mail merge. So we click on complete the merge and then edit individual letters. Then click ok here. Now here we can see all the various letters from that mail merge from that specific query. Now keep in mind this you don't need to modify if there isn't something that needs to be corrected and such, you need to actually go back to the database and change the information in the database and then rerun the query. The beauty of this stuff is, and I'm going to close this mail merge the results at this point so I'll say I don't need to save the results, but the beauty of this is that is that if I save this form and this is the form actually that typically has the codes and all that stuff linked to the database, if I save this, and then I close word, and then I come back here to my document, so this was my letter and two weeks have passed or something time has passed now I want to rerun this again. Notice if I open it up again it will ask me do I want to update it for link to the access query, I say yes and now I can go ahead here and go under mailings, go under mail merge, and then step by step and pretty much here I don't actually have to do them step by step, I could go here under finish and merge and then edit individual letters and it's going to actually rerun the whole query again. I know I did this fairly quickly so you can rewind the video and see it again for your benefit. But the advantage is that you don't have to always create a new form, you can simply save the form from word open it up whenever you want to rerun it and it's going to pull the data directly from that query, from the access database automatically for you. So this was the way how to do a query from Microsoft access into word. The other way to do it is that you can open word first and then go to Microsoft access and pull the data from a query or from a table and that will work as well. So if I go here to Microsoft word and I open a new document here and then I go under mailings, I go under start a mail merge, and I'd recommend that you use the mail merge wizard here.  Then start next and we're going to use a current document here or you know just this current blank document that we have in front of us. Next here we're going to select the recipients. Now notice it doesn't link automatically to our table into the database so we have to actually go in and link to it. So if I click here on browse, now I need to find my database where I have it, and double click on it, and now notice it lists all the tables and the queries so you can actually do a mail merge from actual tables or from the queries within a database. Now I click ok here. Notice it shows all the records here including the fields that we had selected as part of that query. We could filter and sort them if we wanted to do that further more but for now we're not. Then we're going to write the letter and we say "dear" and then insert field and we say first name, space last name, and then write the letter with the various fields. So I put the amount. So basically you're just putting the actual fields that you want to use. Of course this I'm doing this fairly quickly here for the sake of time. You could put other additional comments and then you can preview those letters so this is how it's going to look thank  you "dear Owen thank you for your contract type" and such and such residential number one and and so on and so on. Next you click on here and complete the merge then you choose edit individual letters. This just gives you an idea as to what they are going to look like so right now we have five letters that have been generated automatically. So that's how that works. Now the other thing that you can do is from access or or from word you could do also email merges. So if I go here to my customers know this customers table has an email field so I could actually do an email merge for those customers and I could either create a query that includes those email fields or I could use my customer table for this purpose. So if I wanted to send them contract information and all that stuff which is in a different table then you'll need to use a query and you need to include the email field in one of those queries. For the sake of time I'm not going to create a query for that at this stage but I'm just going to use the table customers which has already a field with email in it and will create an email merge. So as I have opened here the customers table and I click on mail merge or word merge. So here's customers, word merge, and then it's asking us do we want to link to an existing word document or create one? We want to create a new one. So basically you're going to write the email in word and then it's going to use Microsoft outlook to actually deliver the mail so it's important here as well, the third piece of this puzzle is actually to use Microsoft outlook and have Microsoft outlook configured to be able to receive and send emails as well. So now notice Microsoft word has been opened here in the bottom and then we say okay I want to create email messages here, then I say next here to start the document. Then it says the only use the current document or do you want to use an existing document so we'll use the current document which is right here. Then select the recipients the recipients it's already linked to the customers table in access so we don't have to really select the customers at this point or type a new one, we'll use the existing list. Then we'll write the email message and here we just write "dear" and then put their first name, last name, comma, and you're basically just picking the fields and being creative or whatever you need is to utilize those fields in your email to them. In this case we're going to tell them okay here's what we have on file with you or from you on our systems and if you have any problems or anything like that just let us know. So save the name first and last name then we could put in there street address... and you basically format this a little bit nicer if you'd like. You can go here under the home tab and customize this any way you want and so next you click on preview your email message. Then you complete the merge next and then we're going to choose here electronic mail. So this is the difference between the word email merge and a regular mail merge for letters. Here for the email merge you have to click on electronic mail and the other field here the other important thing is that you have to tell the system which field actually contains the email address. So in this drop down it has to be the field that corresponds with the email address from your access table or access query. For example here is an access this is email it's called email the label on the top here. And then you type the subject line here so this would be just like as if you were to type an individual email to somebody that would be the subject that you want to write to them and then the email format html. You want to use html particularly if you want to embed pictures and things of that nature as part of your document here. But what we talked over here it's going to be the body of the message that is going to be sent through email. Now again it's important if I press ok here it's actually going to open up Microsoft outlook and it's actually going to try to send all of those emails that are in the table in the access database so it's going to try to email here 42 customers all in one click basically. So I'm not going to press ok here because it's not going to try to send those but that's how this system will work for sending email messages. It's a powerful feature. Again you can actually go then back through Microsoft word and do the same thing like we did for the regular letters and create an email merge. You can do it either from Microsoft access, from the table itself and initiate the email merge, or you can do this from word, configure the go through the steps of the mail merge and then pull the table, pull the fields, write the email, and press send from word and then in the sent messages in Microsoft outlook you'll see each individual email that was sent out. So that's how you do mail merge and an email merge and of course you can also do labels if you needed to from here as well through Microsoft access. [Music]  In this session I'm going to demonstrate how to create a switchboard or a dashboard for your database so that whenever you open your database you'd actually instead of having a view like this and then trying to figure out as to what is where, you'd have more of a nicer view like a dashboard menu type of system where you'd be able to get to certain tables or certain queries or reports much easier and more user-friendly. So how do we create that? Unfortunately in access 2016 Microsoft hid that way. So the easiest way to get to the switchboard manager is by simply going here under the tell me feature which is this little box right here telling Microsoft access what you want to do. So we go here on the tell me feature and we type switchboard and then click on switchboard manager and it says the switchboard manager was unable to find the valid switchboard for this database would you like to create one and that is great because that's what we want to do so you click on yes and then it's going to give us a default switchboard. So once it opens the default switchboard here we click on edit and then we want to add new items to this switchboard. Then we click on new and then here is one of the options or one of the menus that we can define. So let's say we wanted to update customer records and here we go under the command then we need to tell the system as to what we want to do. In our case we want to open a form either in add mode or edit mode. So in our case we want to update existing records. So we want to choose edit mode and then we go to the form that we want to be editing. So we go under here and we choose the customer form and then click ok. Another option so we click here on new and we choose add new customer and then go under the switchboard. Click on the form for open form in add mode and then we go over here under customers form as well. So now we have two of them. Then we add another command that we want as part of our dashboard. Click on new and then let's say we want to see the contracts by date. Do you want to open a report, so you can either open forms or reports in this case or macros. So here we'll click on open a report and we'll change this let's say contracts by date and then you click on open report and then you choose a report that you want to run. Click ok and you can keep on adding more items like this as well. So we could go here and choose, then you can also choose a new item here, return, because you want to navigate back home. You choose go to the switchboard and the switchboard that you want to go is the default switchboard. So I close the main switchboard. Now I can go and create additional switchboard pages as well. So in this case I want a switchboard just called reports. Click ok and now I go into the report switchboard I created new to add a new item then I add it then I edit the report the switchboard and I add a new item there. So let's say I want in there view customers and then open a report, pick the report that you want and so and then keep on adding whatever components you have because sometimes in a database you'd have hundreds of reports and hundreds of queries and such and in this stuff here on the left it could get very messy so the switchboard actually helps you categorize what you want in the order that you want them presented. Then whenever you create another switchboard it's also important to create here return to the main switchboard option as well. So return home and then you want to go to the switchboard and you want to choose which switchboard you want to go to. Click ok and then close this stuff and then close it again. Now whenever you define the switchboard you also have to do one more thing in order to tell Microsoft access in the database so whenever it opens which form to open. So in this case we need to go to do that we need to go under the file menu we go here under options then we go under the current database and then over here where it says display, right here, we want to choose to display whenever you open the database you want to choose to display the switchboard. Then you click ok and then it says we need to close the database for this to take effect so we'll need to exit the database and now notice it presents me with the main switchboard here. Of course we could change the colors we could make this fancier and all that type of stuff as well. Typically you can hide all this other stuff here on the left and make it so it opens like this. So basically if you wanted to add a new customer, you could click on add new customer and now notice it takes you to your customer to add the new customer. If I wanted to view the contracts by date range so notice it's going to prompt me for the contracts and it's going to play me the report content that I had requested. I could review also the customer report if I needed to and there it is with a single click. So that's how you create the switchboard items here. If you wanted to change the colors and all that type of stuff you can simply go here under view and then under either design view or the layout view, you can change the theme to something more appealing to you.  And you could add more controls and you can add more text in here as well. When you're ready and done you could put a logo if you prefer and all that type of stuff, but basically this is a mechanism for your staff and yourself to be able to access this and in a user-friendly way. If you wanted that switchboard manager tool all the time you can either use the quick the tell me feature over here or you could actually go ahead and add it over here under the quick access toolbar. You can go and choose more commands and then choose to show all the commands and then scroll down here to switchboard somewhere switchboard manager, and then you add it to the right. Then click ok and it's going to show up here on the quick access toolbar on the very top so that will display you your main switchboard where you can make edits and change stuff if you need it to do that. [Music] In this tutorial I'm going to demonstrate how to add a new field to your database or to one of the tables in your database. Suppose that you designed your database and now all of a sudden you have a need to add an additional field for your customer table or some of the tables there. So what you can do is go ahead and go to the specific table and the easiest would be to go to design view here and then add a new field wherever you want that new field. So for example first name, last name and then telephone number and such but let's say I want to also post in there a picture for this customer. Then under the data type what you want to choose here is attachment and basically you're saying we're going to add a field called picture and this is going to be an attachment. Now the point, the reason why I'm choosing attachment is because I have not covered the attachment option earlier and this is new in in access. So once you define this then click on close here for the table and save the changes we are saving the changes because we change the design of the table. If we open the customers table, notice one of the fields will be attachment, like it has this attachment option right here. Now in order to add the attachments you can of course double click here and add and choose to add attachments and such the picture and then choose okay that's one way to do it. Or the other way to do this would be if we created here a new form. If we go here under form and under customers we add all these fields next next finish,  we should have in here a field for the picture. So let's say for customer Owen here we want to add his picture. Notice we click on it and notice here it says manage attachments. You can click on it click on add and then go and find the picture. Click ok and there is the picture. So that's how you add attachments to an existing table by adding the field first and then by creating the form next. You can also customize an existing form with an attachment field by editing and using the design view and the layout view. If you made it this far in the tutorial thank you for doing so. I hope that this tutorial was beneficial to you and that you can go ahead and check out also the other tutorials that I have on this channel. [Music]

Channel: Kaceli TechTraining

Views: 22,269

Rating: 4.935657 out of 5

Keywords: access, access tutorial, access 2019, access 2019 tutorial, microsoft access, microsoft access 2019, microsoft access 2019 tutorial, how to use microsoft access, how to use microsoft access 2019, how to use access 2019, how to use access, access tutorial beginners, access 2019 tutorial beginners, microsoft access tutorial beginners, microsoft access 2019 tutorial beginner, kaceli, kacelitechtraining, complete tutorial access, Microsoft access course, free course ms access

Id: C7oCwdm_wXg

Length: 179min 30sec (10770 seconds)

Published: Wed Dec 30 2020