  фот you learn and discuss. Discos is a first structure using Microsoft Access Database Management System. Database Management System means say too that anybody living in this technological age should use. If you are not using it, either you are not aware of it or you do not have the skill to use it. This course is designed to clear all these obstacles so that you can start using it in a few minutes from now. The course outline. The first is introduction followed by the page we are now which is what you learn in this course. Then we are going to call database management. A database management is designed around four objects. The first is the table. We will learn to create table and we will learn how to manage table. The next is the query. The query is asking questions. First, the table has all the contents, all the information, all the data in your database. The query asks questions to table. Give me this specific information. It will now give. Why without table? There won't be a database management because table contains all the information. The information is not in the table. It is not in your database. In the other language, query is the reason we design database. If you can query your database, that database is useless. So they all have their functions. Then we now go to form. We don't want to stress people in using table-like structure in keying data. We want the interface to be friendly. Even a child, anybody can use it without scales. Because of that, form was invented. Form will present the data entry to you. The next is the reports. Reports is the database language for printing. This is because you usually do not print everything in your database. You print selected things you need. That's why it's called reports. In this section, we will learn how to create reports and how to manage reports. There are silent strengths in database management that will make you excel. We will highlight those things. Join me to learn and start using Assess. A few minutes from now. I am waiting for you. You can enroll now and enjoy database management. Thank you, thank you, thank you. Welcome to this section. In this section, we will look at Microsoft Assess environments. As usual, to open any app, you look at the touch bar. If it is here, you can click on it. This is Assess icon. You will go to the program menu. If it is in this panel, you click it. Then watch your place. You will see it is this left hand side. Any software or file in your computer. If it is not here, it is not in your file. Another quick access to get to your files is through the search engine. Once you start typing, you will see Assess. So we open Assess here. Assess opens to this Explorer window. This is the blank Assess file. We are going to start with. Assess has some apps that you may be interested in. A lot of them you can download from the Internet. If you don't know how to use Assess. So as we begin, let's go straight and use the blank. Again, we have these recent files. If you are working on file you are looking. You will see your recent file. So we click blank database. Database always wants you to create a file before you start a work. This is because most of the things you are doing in database are in the memory before you saved them. They don't want and they are fragile while in memory. So they don't want you to lose your job. Let's call this Assess in the evening. Let's return it. Then it's good now that we are working on database to name your files officially. Don't give gap. Don't use graphical symbols. So with this we can say Assess. This is what we call Camel case. If you use it, it will say be readable. Assess in the evening. So we use this Camel case. You can use underscore or hyphen. But avoid using space. So Assess in the evening is a name we are giving it. So we click create to create database. One thing you will know about database. There are objects that make up the database. They are not much. They are only four and you must master each very well. The first one is a table. This Assess opens to a table. This is the table he has given a name table one for us. A database must have a table. And table is very important for the database. Because every information in the database it contains in the table. Actually it is called database. Database comes from database. A basin of water you go there to collect. The actual thing goes in the water is the table. Other apps go there to collect the information they need. So the Microsoft calls these objects. All these things that hold the database. And they are four in number. This is table. The ones created register their presence here. Table is only one partially created. Because it has not given a real name. Then another object in the database is. Let's go to create so that we can see all of them. This is table. This area is a table. We can create table and manipulate table in this form. Then query is the other objects. Query will go to database to collect information. While the table takes the privilege of being the holder of information in the database. Query is the reason you design a database. You design a database so that you can query it. You can ask questions. Who is this person? Where is this in inventory control? Which clients has purchased what? So that is the work of query to ask questions without query. Database is useless. Then we now have form. Another object. The form is to present an interface where we can fill our information into the table. Just like using pen and paper. So that even uninitiated anybody who can type can enter information for us. So that is the work of form. To present a form for you. Your information looks like form that anybody can query and fill. Anything you type in form will be stored in the table. So the next thing is the reports. The report is the print out or just print for the database. We call it report because in most cases in database we are not printing everything in it. We are just printing part of it. The portion that interest us. If I have database that has the information of a citizen, everything about it. And I'm interested in creating database for the person. Because collect the information that has to do with database. If I want to do national ID card for the person. I'll collect the only information that the person need to present as in a card. So that's why it's called report. So in this lecture or discourse we'll learn how to create all these tools. Once you create, you can create and manage all these things. You've known everything about database. And you can use database very well. So see you in the next section. We'll talk about table. This is about access environment. Welcome to this section. In this section we're going to discuss database table. The first step we'll take is to open a database. You click here once. Remember that in database these are your recent. This is the blank. If you're interested in templates, click here. We want to start a new one. Click here once. We'll ask you for the name of your database. We'll name this database X, Y, Z school updates. X, Y, Z school. We'll use a camel method by typing capital Z to make it readable. X, Y, Z school updates. Okay. We click create. Database opens to this window. This window give you table one, a table. And the list of the objects will appear here. Any objects will appear here. You can recall that the database have four objects. And the objects are tables, queries, forms and reports. We're going to treat all of them. We'll go back to home. Then we're in database. This is table one. When we want to change or do any activity, it will force us to change and give it a reasonable name. So we click design view. And the next part, table two. I'll show you how you can design your table using this window. Most people prefer using design. So we'll start with design. We'll still show you how to do the other one. It's left for you to know what to use in designing your table. So we go to design view. And we call it this XYZ tab one. XYZ tab one. You give a name that will make you remember the database that owns it. So we are in design view now. The first view is the primary key. Every database must have a primary key. It's with the primary key that your database is indexed. It is a unique key. It doesn't have duplicates. A key identifies an object. If you're designing for driver's license, driver's license number is a good candidate. If you're designing for vehicles, vehicle plate number is a good number. If you're designing for human beings, things like security number. So there should be something to identify a field. Here in our ID, we'll choose matriculation number because it's a school. So we call the primary key. We give it a name XYZ. We can use ID. We can use mat number. Let's use matriculation number. Matriculation number. So this is the data type. What type of data will you put into your table? Database one to know beforehand. The type of data so that it can reserve the space for you. Here we have all types of data types. We are going to exhaust all. So if we use number, it means only number is what will enter here. It can't take any data. If we use it in auto number, it will put in any data. The computer will number it serially. It's not the best for you because you want to use your inventory number, your client number, driver's license number. So we will choose to use the matriculation number here and the stolen number that will enter here. The next field will be for name. Name is a reserved name keyword for access. It will accept it. It will have a lot of keywords access to not accept. If access complains about the naming, change the name you use. For the first name we use f name. Later you will see how you can write it in full. But this is what we want to be in our table. We don't want to use very long number. Here does not accept space, but it can take underscore or hyphen. So you will be careful on the data you put here. So the next, this we want it to be short text. For any data you choose, like this we say number, you come here that's a field size. If we click here we can modify the data more. Long data will take several billions of numbers. We just want integer. We don't want to waste space by reserving big space for it. The same thing if we click here, the data type has given it 255 characters. I think for our name, 15 characters will be okay. So we have n name, son name which is s name. So s name definitely is short text. We will do the same thing here. The reason we are doing this is that a space of that character will be reserved for us. Otherwise the data, when you are filling the form, it will reserve very big space for you. If you press enter, it will go to the next line. Enter, so you have to use your mouse to click to go to the next box. But with this now, it will just reserve 15. When you press enter, you can go to the next input. You will see that when we are discussing form. The next thing we want to discuss is age. We will just use numbers, vary our field names and types so that we can accommodate as much of the fields as possible. So the next one we will use, we want to use yes or no. So we will use married. If people are married, they will tick. So this is yes or no answer. If you are married, you say yes. So the next one we want to treat will use registration dates. This will give us the opportunity to use dates. And the data type we will choose will be dates and time. So we come here, we use fees. We want to use currency field assuming that students pay space session. So fees one will be for section one. This is for session one or first semester. So here we will use currency. Currency will give us US is the default. US currency. If you click here, you can choose other currencies. But dollar is the default. You can see it. We have euro. So you can choose any of them. Then here we want fees two. So in fees two the same thing we will use fees. We will use currency. So the next one we are going to use peaks. We want our passport to be here. So we use peaks. So depict pictures, peaks. So here are our peaks. The field that is good for pasting picture is OLE. Object leaking and embedding. So we have fees, we have peak. Then we have sex. The sex is male or female we want to use. We will use only two characters. It's okay for us. So we use we want country. In this country column we want people to choose from the listed country. We do this so that we can use the look up wizard. Look up wizard when you click it it will have a drop down like what we have now. So students that are feeling the form can select. So we use look up wizard. So the look up wizard will access if we have a table. In most cases you have table that contains all the countries. So you have to choose it. But because we are practicing we want to type our own options. So students can pick from our options. So we click next. It wants us to put in the names of the countries for students to choose from. We have US. We have UK. We have Nigeria. We have South Africa. These are the list of names students will choose from. We have Japan. We have China. We have India. We have Russia. We can keep on typing. It can contain as many countries as we put. And you can come back here to increase it, to edit it and add more countries. For now it will stop here. When you are practicing make sure you include your countries number there. So we click. Then the title is okay for us and we say finish. So by time we are filling the form you see what we have done. The next thing we have now used is the attach and the hyperlink. They are straight forward. If you click hyperlink just put as many hyperlinks as you want to a website. When people click it they get to a website. If you use attachment it accepts any type of data document files, folders, anything but it needs advanced knowledge to manage. So we will use it here but we will use it in our advanced database. So again the country I think twenty characters will be okay for us for the country. Which other one? The sex of choosing. Okay. If you look down here you see a lot of options you can set against this database but we are not going to discuss it now. We discuss all these options in advanced level but let me show you some important tones. If you go to the multiplication number you find that it is required it means that required yes students must key it in before they continue. Then we look at index yes it is indexed it does not accept duplicate. Other data fields is not required because students can have the same name. So required no. People can jump without typing it will accept it. Indexed yes. So all these are the rules we have set for the database. We will discuss that in advanced level for now we will finish designing our database. There is one thing remaining. Let's do a calculated field. We did not do a calculated field. Okay. We insert the field for the tutorial select row then insert a row the calculated field is best done when you key in the field before naming it. If you try to name it before selecting calculated you may have problems on recognizing the name. Continue to give you error that the field name will not go. So starts with calculation is the only one that has this exception so that it will write the expression first. So we have a field one this is where you do your calculation you have the expressions you have the elements then here all our fields we can calculate through it and here are the expression values. You can do this to do a great job in calculation. So we just double click field one. What do you want to do? If you have several fields to calculate do the same. Use any mathematical symbol if you can like addition, brackets subtraction, multiplication it will carry it out. It's all open to you to use. Just write the expression right. So for now we just want to do field one plus field two. So we say okay it has accepted it as calculated without name. So here we can now write x, y, z total field total field we have to mark this step if you don't get it right you keep on having errors so this is calculated field. So we now say go to view it's wanting us to save it so we are here this x, y, z calculated field. So this actually how to put in the calculated field. You can use any other name for your calculated field. So here all thumbs up we finish designing our data base table. We are going to key in one record here now and reserve key in more records when we get to form. So here we have math number let's call this one 0001 the name is Jane the surname is Peter the age is let's say 18 married this person is not married so we don't have to key in this registration date will bring out a calendar for us because we have said that the field is a calendar browse to get a date you have a number and put it in. Remember one you can just key in the data here but any date number format you are using must match the one in your computer like here month come before day and you must put it in that order otherwise you go to your computer and change it this is the structure that is in my computer down to the right and pointing at my date format that is why I am using this date format so here we will put in fee let's say 500 and here will put the fee let's say 600 ok we will brush over so that it will capture it well 600 so in total fee here we are not we can put the picture here we will put the picture in form for sex is female we write enter F for country when we click here we see all the countries we put so we can choose country from this drop down so this is the extent of the field we record it so this is how to create table and enter data in table but entering data in table is difficult there is only the people that are somehow literate entering data that can manage it that is why we have form form will create an environment for you to just enter data as if you are using pen and paper any data you enter in form still finds its way to the table so in the next exercise we will discuss table but before then we will show you another alternative of designing table from this area this is how we are taking for now see you in the next section thank you welcome to tables part 2 in this part we want to show how you can design your table using the table view let's create a new table for this purpose we will go to create and we will say table this is our initial table let's close it it's still here this is our new table is table 1 remember here you can delete you can rename your tables when we started microsoft access it opens a table like this for us the idea is that we can go ahead and put design our table here if you like this option you use we have used the design view option in this option the data type is showing and we said the data type in the other side we selected number meaning that is only number that we can enter here then the field name we call x y z you may prefer this field so using the design view so we now go to the second field here and we want surname in this second field click this arrow and select what you want that first name we say is short text so we have i think we have first name the first time so we can say f name for first name so that's the field name we choose so we go to the second one and we select short text you see that is showing us the field for the short text we use 15 we are going to edit the other one too so we use 15 characters so this is how to use this one and this is s name if you like prefer designing this right double click to say s name then 15 characters that's what we've designed so the next thing that follow the full look at our first table let's call up the first table to have a look at it the first table have age and we have married so we go back to the second table and get to that point so we have age here age is a number and the field name for age we call age so we go to the other one after age we have married so we go to this field and say is yes or no answer we look for yes or no and we call the field married and we have selected yes or no here so is showing so the age if we click is a number and the format is number so that's okay so we have the surname we have age the field is showing here so if you prefer to design your table this way continue in a database you can have as many table as you want and work with the table in the advanced section of this course you will learn how to do more things for now we will use one table so see you in the next section where we will discuss form remember that the purpose of this second part of the table is to show you how you can create your database using this field that using this table view field does not stop you from switching over to design view we have to save this so let's call it x, y, z tab 1 table 1 or table 2 because we have the other one so we don't confuse it so this is what we've just done we can continue here we can switch over and continue so now I'm sure you know how to use any of the two fields the two methods that is the essence of this you can design your table this view you can design the other view and you can switch from this data sheet view to design view at any point in time so thank you in the next video lecture we will discuss form welcome to this section in this section we will learn how to design our form remember that form gives us an interface that we can use to key in data as if you are using normal pen and paper so first we go to create as you can see we have different ways we can create a form as a starter we start with the form wizard which is this in the advanced learn course we can look into other forms but in this one the wizard will help us to quickly design our form so we click form wizard the form wizard want to know the table we want to use remember in your database you can have many tables now have only one and it's telling us, showing us the table these are the fields we are going to fill in the form it showed us all the field we have in the table if we want to use selected field we can use this arrows to select it for example I want only this and this oh I don't want this I use this to send it back oh I want all of them I use this to send all of them to this place oh I don't want country in it I use this to send it back so we use this to send everything back then we use this to select everything these are the selected fields we are going to work on we go to next the next if our table is table it will be like excel sheet we have now achieved anything we want it column so that it will look like our real paper and pen form we go to next it suggest a name for us won't go by this name because it has table in it so we go with the one that has form so we choose form okay so we go to finish that's all it requires from us you click finish this is the form we can fill this is the form we can fill we added this picture before so here are the numbers this number 2 from our record this is our first record we can rename it record 1 so we have 1001 1001 then this is the name we have entered before and the figures we entered in the table these are our selections so we use this to go to the next blank table form to fill this is just one step to the next table incidentally our next one is the blank and this is the last one so we can use this or this to go to it you use this to move around your table this is to go back this is to go to the very very first one this is to go forward and this is to move to the very last one so we go to the next step the next step we must key in this primary key before any other thing otherwise the page will not save so here we want to give this 1002 based on our numbering system then we will start putting the name let's get this name as maybe Maggie Maggie and the surname is Bloom so Maggie Bloom we have age here maybe 24 married years date the person registered we can use the calendar or we can manually enter the dates so we select a date here remember the date format if you know that it begins with months and followed with year you can use backslash and still enter your date like this it will accept it so fees the first fee she paid that's $400 I brought you over this is faster than you know type what you want then the second fees is fees $600 by time we click here the total will appear because you are using calculated fee so how do we put picture here you can't insert file here but if your picture is open you can just copy it and paste it the fastest way to do that so we go to images I have some images here I will look for some images in your own case you have collection of students passports to use so in the images here I will look at the files I have let's say this is the woman in question you look at image format this is web format it opens with web I have the jpeg of fees so it's not every file that will accept the certain file formats but when you copy no problem you worry less about the file formats so if we use this it's likely to open in web but we are just copying in it so it doesn't mean so we can right click here and copy the image paste your passport into the access so we go to access and paste it it's still waiting for us bear is our access again so we right click here and paste and the picture is here so we still have the paste here if you prefer to use that one so this how to get your pictures to the page then the sex we can see is female and the country we have to select we can't type in just anything we have to select so we select a country fine so we created our second database to go to the next one we just press button this is record 3 so in this record now we have to scroll up to the first one so we give the number 003 so we will be in Syria with what we numbered we make this one male so let's call this person Paul and let the sign name be maybe Kettle the age is 23 is married dates registered we select any date for the person I will use 12 okay first fee this person paid 1000 remember you can use calculated fee to say what the balance will be it's just this plus this minus the total so paid 4000 now 400 so the total fee will appear here so the picture now we want to attach and we have to use our method we call up the image right click copy image and we can just go and paste it life is easy so we go to our database and paste so that's how to create your database this sex is your form this sex is male as we can see and let's use capital M and the country who can choose India okay so we are entering our data and as you can see anybody can enter same data notes had it been we did not limit this to 10 characters we left it at 200 and something they will be so large they will be big like this we can manage them so all this is because of the constraint we gave in selecting the field it helped us I can just finish filling one press enter it will go to the next field so now that you know how to create a form and you know how to enter form what next we want to create 10 records we have 3 already as you can see here this is the third one 3 of 3 that is your assignment your homework I want you to use to create 10 record at least if you make it 20 or 30 or 50 better try to use records that you will use in real life so that you will put the access in the evening into use immediately I am here to answer your questions if you have any difficulties so with this we come to an end of creating the file in form part 2 we will discuss how to retouch our form so now that we finished we saved it we can close it is telling us about what we copied in the clip board so we said this record has been changed okay save record we will save it so we are now we now have form and this name of the form and name of our table does not appear good for our official work so let's rename it I purposely do this so you know that you can always rename your data so we call this XYZ school record XYZ this is table XYZ school this is the main record the main table we can as well call it main table main tab okay so main tab we named this as such so here we have a form and we wanted to follow these formats so we renamed this form XYZ school XYZ school main form main okay we forgot our camel case so that's what we are writing can be readable XYZ school fine it's now readable it's now that I notice that the main it's not well spelled because I'm using this case so so we've saved this and we've saved this so our work is saved now so we close this when we come back we'll try to beautify our data base see you in the next section tag form 2 thank you welcome to form part 2 I've done my own part to update my own form I hope you've done that to your form 2 I've added 10 records so I have one to turn records so now we have 10 records to work on the purpose of this section is form design or design our form but before then I'll check my table you'll find that the form what I feel in the form has already been updated in the table so that's why we use form it's a very gives us quick access to entrained data so right now I'm going to call up form the purpose of this section is for form design without wasting time we now go to the form design form design we can change all that you're seeing here to look appealing but without touching things in the table so the first step is to go to the design view we are in the form any page in the form so this is the okay so this is the form design view what do we want to do all this our tables we can move them around for each field in our table we will have 2 things that represent it here the one is the label and the other one is the data box we won't change this field name if you change it it will lose the link to your table but you can do whatever you like with the label you can even delete it so here we want to give this a good name so we call it matriculation number matriculation we are not restricted to using certain characters or anything it's a label we can use anything so we now give our fields a meaningful name so this we use fname we can just say first name or enter your first name anything we like we can type here enter or just say your first name so anything we like we can type it did not end there we can format them so I can right click here and choose the background color I like I can also look up here if I go to formats I will see all the tools I can change the type size I can do whatever I want with the color and the attributes I can center I can do anything let's just change all of this text so blue so the deeper color so we can change it and we change the background these are the things we won't touch all these are labels up here we can delete whatever label that is here and give it a name we come to design from design if we use text box it means we want to type we want to type the type of expression we use to calculate fields we just want a label this we can put anyway so this is XYZ school XYZ school we are giving it a good title now we can use our formatting tool to center to do whatever we want so we go to format again this is the box we are still typing inside it has not seen it as a text box click outside and come back it has seen it as a text box now so this letter is too dull for us we want to make it yellow yellow is still bad maybe because of the background we give it a lighter background we make the character readable we increase the size we have the liberty to do whatever we want with all this so we are just beautifying our table we can change the background entire background here by right clicking on the background and we choose any color make your background color light ok so that's about designing how about moving these boxes yes we can move them around this is passports many people want the passports at the upper part notice that when I move this by dragging here this follows it that is the label if I want to move them separately I will move the pick I will move them by this anchor they can move without following each other now so I will both select I will drag to select the entire of this I will push it down a little bit most people want their passports up here so I will get the passports we are just designing the form up here I can drag to change the size of this it will see contain our information this I can now type what I want inside here so we drag this to the header that's not our intention so we remove it from the header area to the normal area this part of the design you have the flexibility you won't be scared you can always change things this is the header area so it move down to accommodate the picture so we are putting it back here this one is by the side of our form will bring it to a normal place we can put the logo here just say insert go to your form design and you can insert your logo here you can copy and place inside the box so you have the liberty to do whatever you want so this all we are taking about form design so if I want this to move with the box I will pull it from here if I want any to move without the box I will move only it all these boxes I can change their size the same in actual field work you make this form look like the exact form you met in the field so people don't even feel when they are using it that it is different from what they know physically initially so we call the students passports so always remember that you won't change anything inside here if you change it, it won't have a link to your table again inside your dream so with this we can save our work we can go to the data sheet we go home we go to form view so this is what we have design, this is what we have so beautiful so here we can go from one table one form to the other so this brings us we are bringing up we are at the last page these are the pages I have and we are moving around our pages so with this we end the form design in the next section we will talk about the query see you in the next section hello welcome to this section we are done with form now we will create query query is the reason why we create database we want to ask questions we want to query the data in the table so first of all before we start query let's have a look at our table these are the data we have in our table so we will now go to query so we go to create query we look for query wizard we click query wizard we need simple query if we go to advance access we can discuss other areas for now we click ok so asking us the field we want to use for the query we will select all the fields because we are learning in some instances you just need few fields from your database and you can create as many query as possible if the target is for things like driver's license I just collect relevant information then if it is for another thing then we will now collect for that particular thing for now we are using all our fields so we click next we are here he is telling us we want detail from not the summary we want everything we need a name and the other query but I don't like the table there so we call it mem query so we click finish here the query is done now we have not query the query so the table is just like the table we supplied it until we ask questions until we ask questions so we go now and start asking our query question I noticed something here in the query that this one has no number and the field especially field so anything we do in the query will eventually go to table so based on that we can give this one a number and we can call it 1011 1011 anything we do in the query will eventually save in the table and the name let's use GD GD the surname Alaska okay we just added the name there so save then we go to design view always remember to get to design view you must click home button our repeats before you go to design our data sheet view click here at times you are in create you won't see it you will be looking for it so once we know that we go to design view it's here that we ask questions this is where the actual database action is without asking questions there won't be database the first field is the field we recorded in our table the second is the name of our table that we want to query then we can use sort if we don't sort it to sort normal but if we click we can say direction of the source these are the things it will show anything we remove here will not appear after the query let's remove this up to the age marriage is the first thing that will appear if you go to data sheets you see that who asked is not to show so we go back to design view it will show us all this so how do we ask questions two ways number one criteria what are you looking for we use our logical logic to ask questions so if we come to age we can say equal to greater than let's search for those that are ages greater than 21 so I will say greater than 21 under the age and in line with criteria this is what we are looking for so when we go to the view which is here data sheets view you see that all this age are above 24 so we go back to above 21 we go back to query if we add two criteria it means this and the other one let's check for those that are above 21 and are married with this we say greater than 21 and this the same line means and so it has to combine they must be 21 and they must be married so this is supposed to be in quotes this yes we use yes or no field but database is very unforgiving if we don't put quotes it will put it for us so based on that we say click so here these are only married and they are above this age so that's how we query every field anybody anything we are looking for let's look for those that are 21 and there is a particular name so let's look for Peter that is 21 the first name is Peter and it's above 21 let's see whether we have such a person so this is where you search for information we check ok nobody has that criteria ok so we go to design view we remove this we are going to use our name or is this one or this let's look for somebody that has the first name Peter only one criteria we are giving now does anybody be a Peter name Peter here nobody be a Peter so we look for anybody that is Jane here we search we have one Jane here ok so we now go to design view we want to look for people that are Jane names that are Jane and at the same time or Paul either way anyone if they are Jane or they are Paul I didn't put the brackets the system is putting it for me that's very good so we click view ok we have only one person that is Jane and we have only one person that is Paul so this is how to query all these fields use what you use in the field if you want to check for those that have fees that have their fees above those are paid more than this dollars this is where you put it if you want a date you put dates those that registered before this those that registered after this date if you want to search for only mail you put mail here let's do that one we go to design view you have to remove this before you put another criteria otherwise all of them will be working together so we go to sex here and choose what we are looking for only mail in this database so we look for sex this year so we are looking for mail and we use M for mail so that's all so we now go to search so these are mail ok now after searching the difference between searching and search and query is that search we create a table if we want a table to be created for everybody that is everybody that is mail we can create a table as we are increasing capturing data into the table this data will grow once you see mail it will capture it will do that right away so this is the query this is our query and we finish with query here we have not query anything so this query will still contain every information in the database let's quickly do a query that will just show us only those that are married so if we click close this query we save it if we have not saved it to ask us to save once we have saved we have this query let's create another query that will target those that are married I will click create let's go to query query is that we know all this we know all this we know all this now we are interested to mail so we have to add the name here xyz school mail query school mail query so this query will permanently collect the names of people that are mail so we save search we have not query it yet so we go to design view remember I click the home view go to design view and click design so sex is what we are looking for this database we are building it for only mail we say mail so we search these are the mail there after that it saved so this database will contain mail mark the database if we go and increase the table and we have anybody and that person is mail you see that it will increase the database of the mail let's see what we have here we have this data one two three four we have four people that are mail so let's go to our main database so your query is dynamic it will go to table and collect relevant information so here we are in the main table we can as well do that in form anywhere we choose all we need we want to add a data so we now add data here in the main table and this data will give it zero will give it one zero one two following our numbering system and this person is Peter and Peter his name is Calypso Calypso then the age we have some age here that are both the normal age I don't know how they get there so we correct it this person is 15 this person is 16 okay we will continue what we are doing here and the age for this person is maybe 23 and the important thing that it is mail then we put data for registration so any field you use to query and store it it will grow all these fields we can put it and other ones but let's save this and check our mail we'll save it we'll finish updating the table so we close all this you have to close the query before it can update so we have closed everything by time you call up the mail query you feel that Peter is there okay so we will go and check again to know why we expect Peter to be here in the mail data so we will check our database whether we saved it well so we go here again okay we use marriage not mail so it will appear as marriage that's okay that will tell us that our database is working so here we go to the main table and we are interested in saying that Peter is a mail here is the line for Peter this is the line for Peter okay sex as if we had one Peter there before okay so this Peter do we have another Peter no so this Peter is mail we save it we save Peter that is mail so it has entered your query database and in mail query it will show that we have one Peter that is mail okay we save we close we save this we have Peter Peter is a mail okay we save okay this is query it's not even this okay okay this is our mail query we have it so Peter is in mail query so it has captured it maybe we didn't close the table then so once you close it updates so this is how to create tables a lot of query tables so see you in the next session welcome to the second part of query so we use these parts to put some query table is very important to us table contain all our data form is very important to us with form we can have with form we can fill in data and manage data too if we are editing the information it gives us the real life view of our data so we can easily update the data with open days it's open here with open days it's open here if you are working on one of them it's better to keep the other one closed if I work on this the table will not update immediately until I close it so another now to have mistake as I'm updating I'll close the data I'm going to update it I'll close the table form on any table query is a table and query can generate many tables if we have a database we use query so isolate only the things we need we can build a form based on that more than one person can be working on the same database on the same table like in a business organization people that are into accounting may be working on the finance aspect of the client while those that are management will be updating the client record while those are in inventory will only be interested in inventory what's held every information together is the unique number that is the customer number if we are to take school for example somebody is working on results like the teacher all the teacher want to know is student's number and the subject he has taken will just go there and score it's not interested on other things like the age, sex and other things so we can use query to isolate information to separate information like here we've created a query that addresses only the mail in this record we can create another query that will hold only the female in this case we don't need every information it may be only the first name, second name and the sex we need if it is what the person is capturing we just want to capture people and know their age if it is an election we want people that are above 18 so we can create a table that just queries and we get somebody that is all those that are above 18 so somebody feeling the firm is interested in them if you are above 18 please come over here it will be capturing your data all of them are going into the same database so it helps us to distribute level so that many people will be working on the same database so here just like a summary if you come to create you go to query we have query with that here we say ok one simple query to give us the list of all the tables you see that even the query is a table we can use to query so we can choose to query a query we can query people that are above 20 then we will now know those that are above 20 that are married so we have two queries so we can use the same query and combine our query to create this information as people are capturing data in the main table the information updates itself automatically so this is all the important thing about query we have to revisit this issue so that you know the power of query query is the reason you are designing database because if you don't have don't know how to query your database the database is useless next so we go into reports reports is actually printing the database meet you in the next class you are welcome to this section in this section of this course reports reports looks exactly like similar to form but is designed for printing so we get streets streets and look at it always remember that reports is database language for printing is called report because usually we don't print everything we just select some aspect of the database and print so we go to create and look for reports as usual we go for reports wizard in the advanced level we will touch other ones okay the report opens this way what are we reporting on as you know we can report just in everything we can report on all this query for now we will report on the table but if you need to report on any of other tables like query table you can do that since we are learning we will collect all the information all the fields for reports so we click next so it's telling us how to arrange this information it's lined out the way we arranged it in a report we may want to change the position that's when we can click and push up or push down to change the position but we are comfortable with the position now so we say next so it will say whether to sort it ascending or descending if we ignore it it will sort it ascending so we ignore it so if your report is tabular it's like excel at times you need some reports to be tabular but with what we have with pictures in them we may want it to be columnar so we say columnar columnar look like real form so it can be portrait and it can be landscape based on the information you have it can always change from landscape to portrait our information is not very wide so we use portrait but you can try out the landscape we click next we want to get the name of our report you see using the name tab so we call it main reports we put reports here we will have to use our camel style so we have reports that's the end we click finish once we click finish you get a form like display that is ready to print your information okay this is our report you find that it's a print preview it's just ready to print our information this is our first page we can go from one page to the other using this tool this is the second page of our report we have this date 10 in 8 instead of us is 2 of 11 you can edit with little scale you can so see the fantastic thing we've done you can see notice that we may prefer this picture to be here and do some adjustments exactly the way you adjusted the form is the way you are going to adjust this one you are in print preview you can change the size of the paper the margin anything that has to do with the printing are here so you can use them to adjust to the printing you can have several pages in one page then to do anything will close the print preview always remember this this is print preview and for you to do any work click base so if you close the print preview you say okay we are now in design view automatically it takes us to design view if you want to go to print preview again we say I have to go to home and go to view and choose report view or print preview so the same way we designed form is how you are going to design this we are not adding anything so we won't waste time talking about this design if we want the picture to come up here take the picture here you have level for the picture you can take you can color the background you have the freedom to design this if it is a card you are printing like a ATM card you see here you will design the page to look like that if it is company card ID card you see here you design a form arrange the information you have if this is an ID card this may be the size we want and we have few information there may be name age will not be there and date of employment may be there so this is how to design your form now that we have changed certain things here we can color this we can give it a good name XYZ school we change this name to XYZ school you do anything you want with the level XYZ school you still have your format you have your alignment you have everything all your knowledge in micro world in Microsoft world you have your background be free to use any tool you want you are free to change the background if you like change anything here this is a report you may want to type this things in full in level but don't touch this text box information if you change any name you don't have a link with your database again you have destroyed the link so but this level you can do anything you want with them even when you delete them it means that this box has no level so once you know this you must start your database design so we go back how do we go back to the view we finish our design so we go back to the database this on page remember that you have to be on page to see the view so we go to the print preview to see our adjustment and to print our reports so we click print preview so the next thing you say print and it will print all of them so we go back to the end of this learn access microsoft access in the evening the master class the advanced class is learn everything in microsoft access please if you enjoy this lecture and roll on that and that's when we touch every bits of information microsoft access just the tools as it is and that is why the name is learn everything in microsoft access I look forward to seeing you in that class thank you for staying with me till end of the class thanks thanks thanks and role plays in my learn everything in microsoft access I want to be with you I look forward to seeing you thank you