IGCSE ICT 2016 May June Paper 22 Data Manipulation Database Reports



hello guys welcome to this video tutorial my name is Evans and in this video i'm gonna continue looking up the June 2016 pepper to two in the last video we have had been looking at document production and in this video I'm gonna take a dive into data manipulation now like I said the past video that I'll put up these files that you need to work with for this paper under the video for like the one that I'm working on today I'll put up again a link already zip these files okay so if you need to download them just click on the see more just below this video and you'll be able to download these files okay for the section that I did yesterday of document production I already done uploaded the files a link to the files rather to my Dropbox and you guys will be able to pull off these files of my Dropbox alright so I'm gonna continue looking at this paper now we're just going to focus on document rather data manipulation it's gonna be quite a lengthy video as well because there are some things that I need to explain I would also love to mention that this WIPA guys you really needed to understand your questions very well otherwise you'll be making silly mistakes that to cost you a lot of Max when I was trying to go through this paper I don't know three days ago so just to see what questions will come in and stuff I noticed some part where I almost missed understood the question rather so then I I just concluded to say hey you really need to take your time when you are reading these questions you don't need to rush for anything better understand the question and then you're going to make or do the right thing okay so we're dealing with the data base section may or just go back to my folder here and just open the a rhythm notice okay so this is was given to the candidates on the day of examination and and in return notice like I said the other day in the elevator rather that it is simply just a notice that informs of some change that would be made on an already existing pepper for example if I produce a pepper or a memo or whatever and then suddenly have one to change something to that or update that will add something to the information is already existing then I would produce what is known as an erratum notice ok so in this question in this desk the rhythm notice was to do with just three databases and says that please add the following sentence to the instruction at the top of the page ok so the instruction at the top of the page is that if the euro the Euro sign is not available then we can use any other currency ok so if you go back to our question paper this is the section says you're going to we are now going to prepare some reports for the company make sure that all currency values display the Euro sign and are correct – two to zero decimal places ok so this is the part where the erratum notice come in if you don't have these some countries you may not have this because of the different document production package gta– use it wordpress impact is that you're using so in this case you guys would need to use that ok so let me just verify guys that I'm recording this video otherwise we find that you're not recording my computer seems to be acting up fast today so I was wondering if it was recording ok so what I'm gonna do now is to go straight into preparing importing the file the CSV file that by this time you guys are familiar with the concept of importing this fast ok so step 25 using a suitable database package import the file jet two to six yachts dot CSV use these field names and data types okay so these are the food names and then these are the data types now not of this part that says format to one decimal place and also this part that says currency okay so let's go ahead and open our axis open in black database doesn't matter where they give it a name or not okay but school practice to always give it names okay so what I'm gonna do is to import the file okay so I'm gonna browse to my desktop and go to paper to to in source files and I'm important above this one yet to do whatever yep let me just verify that it's a correct file otherwise we may okay so that's the farm it's correct so we go back to access okay and delimited okay so we have delimited text first row contains field names and I love to edit my field name properties from here you can edit your food name properties from here also it's very easy but maybe for a change let me edit them from here some of you guys I understand you are using you're editing your field names from here so let's go ahead and check what food names you have so yes ID is supposed to be text name text type text length number and should be one decimal place so let's go to our database so this one is short text this one is short text this one short text this one is double so it's a number double guys they let me just put this just for the sake of illustration Dabo allows you to have decimals okay or decimal points okay there's more points in your numeric value okay so you see the data that has got just most like this then you need to format this data as double but they are also other data types that you can use to format this okay so I'll show you also of course you've got real and float but you don't find those exist in in in in Access but you put also single and stuff okay so I just want you guys to take note that if you have a number or fuel that has got two decimal places it's always good to just go for demo so you can just check the other ones that are there like Zingo and stuff but that's not important now stick with double and then build up let's check the other ones build up is text and price is currency built here is number and so this one can be made to be integer even this one this one is double because it says four decimal places but these other ones can be made to be integers okay so let's go back here this one is build ID is text yeah should be text let me just close the array time notice the ID is text and then price is supposed to be currency bill day yet I will make it just integer this one also integer and this one also as integer so you notice that all these values have been edited in some way okay so it's important so this is the other min that you can use to set you of your field names okay if I go back here let me just go back and then go to advanced you notice that my data types have been changed so this is where we guys have been changing all these data types here but as I say there's also another way where you can change these data types just from this window and changing it from there okay so let's go ahead next and then choose primary key let's see if we've been told to choose some primary key of some kind you are set just ideal field as primary key and then we save the data so we said just ideas primary key and then go to next and finish and then close then we need to open that and save it okay so what matically you notice that there are some hashtags in some fields that shouldn't be an issue so what you just need to do is set click on this corner here and stick your cursor on one of the margins and just double click and what this does is that it sets all the fields to be visible and you see that all the pounds or the symbols there are there so the other thing guys that we were told is to change or rather to take a screenshot evidence showing the field names and data types used in the table and we set the screenshot into our evidence document okay so whoo-hoo so let's come back here and go to home and change our view to design view and we need just to set these fields as data type as to the data type that we have set now may I say that usually which one determines whether this part of the field imager said this part of the the screen should be part of this screen okay it should whether you're going to have something like this as your evidence or just something like this usually what examiner's want a guide to that is you supposed to come here and not which one shows this so the one that shows the formatting that has to do to one decimal place is the one that you're going to highlight here so the one that says length is the one that we are going to highlight because examiner's want you to they want to that you have actually set these property to to one decimal place also the format for this I'm going to set it as fixed I think yeah what I want to set it as oh the reason I want to set it as fixed is because I want to take advantage of the decimal places that are going to come from that so when you say it's fixed or its standard what axis is going to be doing is that it's going to allow the number of decimal places that you have set to and then they're rounding off is going to take place quite well and the number is going to be displayed in form of decimal points okay if you don't put this you find that your numbers are going to exist the same way your your your database is existing those are of what two decimal places would be two decimal places that I've got one decimal place and it will look as though you did set this field the length correct to one decimal place okay so like that I'm sure it should be able to do that so let's just before we take the screen shot is just verify that it's actually correct to one decimal place and you notice that length actually is Bin's has been set to one decimal place if you ignore if you ignore this section for fixed if you just leave it as blank then it may not set this field to one decimal place okay so I hope I've made that clear so I'm gonna just have to get a screenshot of this including this section because this is where I set the number of decimal places to be one okay so in some cases companies may know this pad but I just love to be sure that my examiner is not going to struggle less they're looking for a decimal places that the decimal places have set and so this is how I'm going to do that so let me get the screen shot of this and go back Marano our evidence document should be this one I last used it yesterday when I was recording when I was recording that so I don't know if it is this one I last used it doesn't seem to have stuff in it so I'm just thinking let me just go back guys to my forte I hope I did record my hope okay it should be this one otherwise should be this one no I probably didn't save I left this document open I left my computer on and probably because I left it on there was no power I found the computer went off so probably this stuff was not saved and then when I opened wait document to their oh wait Mac software today it was asking me to recover some files and I know that I said come on I do have these files already existing so I don't need that but I didn't know that I didn't save this because I was thinking I was going to record the second video but I had to go and attend to my wife course she was not feeling too well so I had left everything open and then I ended up just sleeping like that and woke up I found there was no power and the computer was off and since I'm using a desktop computer there was nothing like going into hibernation like a laptop dance so my apologies guys but you guys just you're able to see my videos documenting the other videos so I'll skip to the section that has to do with database few structure which is step 25 let me just verify that I have this is step 25 s and this is where we're going to paste our evidence screenshot okay so I'm just gonna paste it there so guys like I like I said please ignore the other part you can see my distraught men in the other video okay so let's continue so that we don't waste a lot of time let's go back to the question paper so we're done with step 25 we go to step 26 we created data entry form to include all fields from the ayaats table so let's go ahead and let's go to so let's go to create and then we are going to create a formerly that use it form with it we're going to design a form okay so we are going to use all the fields from the yacht's table and go to next and then I'll allow them to be just as columnar what this mean is that I'll have my labels on the left and my title on top and then I'll have the field names like that so the orange part is actually showing you that these are going to be the titles or the head does all the labels and the yellow pad shows you that these are going to be the check boxes and drop-down menus and and or top boxes rather combo boxes and so on and so forth so next and then go to modify design okay so again it's always good to give your forms appropriate title when you're working with these forms sometimes like J 2 to 6 yeah it's it's not so much a good title you guys you guys when you create forms one of the features of a good form is that it should have a meaningful title so that the users are able to understand what that form is is all about okay so please don't ignore that give us meaningful titles for your form okay so what I'm gonna do is just to give this form a proper title so let me try to see if I can find some good title what is this about okay so this is okay is this a company okay so this is the title okay so it says Dora has two brokers and that's a type two of the company so it to be just appropriate for me to just put this as the title for the company let me just say to ära if you want you can even put this title in capital letters okay because it's a heading of form so tower just Brokers okay it's always just good to have your form that so you can also edit that so you can preview your form and just see how it is looking okay so tip whatever is open badness the another kind of manipulated okay so we just need to close our table so don't forget to close this table just close from here and let's close the property sheet as well and let's try to view it again so there you go you have your title just here okay so you have all these fields and stuff the other thing that we just need to do is that we need to change the currency so that it reflects the required currency I know even if you left it like this because of the array table notice they said you could use any other currency so it to appear as though there was no Euro sign you know who carries a symbol but and forth into my computer I do have euros so I don't want to cheat I don't want to cheat guys so what I'm gonna do now the problem guys is that I've just remembered I needed to set the property for for the table in design view now I have the phone so which means I cannot change the property for the symbol for the price let me see if I can go to the property sheet for this one maybe I can let's see do I have currency here there's more format format is here let's see if I have Euro okay there you go Europe and let's try to preview it again okay okay there you go okay so I was getting concerned because because I think I was going to edit here again okay so now that we have this set okay it looks good let's go back to the question paper and find out what step so we've created a data entry form to include all fields guys may also say here that some of these questions are going to be asked in the theory paper I just brought this perhaps as just I think that I just need maybe to clarify you guys will be asked in your theory paper to draw up screens or input form or capped input capture forms with output script forms and stuff and this is basically what you are supposed to have a heading some labels and some text boxes and drop-down boxes if need be and some instructions on how to fill this form and some navigation buttons and stuff this is what actually makes up a good form and when you write that in the eschewing theory paper to draw up an input screen form you guys this is these are some of the features that you should be able to put up there guys may just do this so that you guys you you understand so suppose I draw an input screen form like this okay this is good let's put my title it has got my labels that would my thing but it lacks some navigation buttons like left right or not left previous/next then submit save delete all those things add new record all these buttons okay and you need just to have proper instruction now to fill this form please enter data like that and then this should be some sentences are not just just ID enter this and enter that at least there should be that so you guys don't forget this is very important on this section when it comes to do with your theory pepper okay so I want you guys to do very well like like I want you to do very well I have one of my subscribers he was telling me the other day that some of the lessons that I put up prior to his exam really helped him and he managed to come out with a Nesta from from his from his course and that was really reading courage in or my patters work cause some people were telling me Nisha my started recording this because some of the videos are very long and they've been long but I would love to really just come out and emphasize certain points I don't mind if it takes five hours for me to emphasize certain points and to you guys really understand and you go in the exam room with confidence and you approach the paper you open the paper you won't you know what you're supposed to do you know exactly what each concept is supposed to apply on every question that you ask for that is what makes me excited when I see the results they come out in School of Life for my school and then they are calling me that it management is calling me to tell me about the results your student did exceptionally well that's what that is what makes me excited them and for your own record guys last year I was teaching a computer science class okay and in my class top in the world student of in computer science in the November 2015 exams came from my class okay he came from my class so my class produced the top in the world student in computer science so don't don't take my lessons for granted I I know what I'm doing when I'm emphasizing selling things because I know when my when I'm marking certain papers students make silly silly mistakes and those if you add up let's say 15 silly mistakes and they each one of was costing you one Mac imagine you've lost 15 max out of a hundred mock exam and that leaves you around around rather 85 85 percent and at 5% may not be an Esther it may be an a and so imagine those silly mistakes if you capitalized on working on them you could get that so guys I encourage you to really really just take your time the lenda stuff and enjoy the videos also I tried to make them as interesting as possible with my funny laughter also somebody tell me that somebody was telling me laughs good I've got a an interesting laughed and I should they should use it in a cartoon ah you had the here Tom and Jerry laughing so I love Tom and Jerry by the way okay guys let's continue otherwise we may finish this video it's just those laughters okay so let's go ahead guys sorry if I've taken long and if you upset by my laugh tender so okay so created that entry form to include all fumes from the at Stevo and we've just done that now we are supposed to use the form to create in steps prints the one that we created step 26 to enter the record as it is okay so I'm gonna just bring up my it's like that and just probably minimize it like this so that I can see the record and then go on to new blank record and then enter this data as I see so M or one to six and remember guys like I said in the video yesterday that can bridge inspection to enter the data which is in bold like this one you see here it is in bored because it has to be exactly as it is put okay that there's one mark that is allocated for you to really just when you are accurate okay when you're accurate is one mark that is allocated for you just be to enter data accurately okay so this is and Alex or aunt Alex and Alex and then you have mortar is supposed to be kept oh so motor and then you have 26 and you have 155 if Al and you have 79 5,000 okay and you have 2011 six and three okay so just go ahead and verify that your data has been entered accurately okay so check your data for errors okay and save the data so I'll save this this is done okay and then Tech is screenshot evidence showing the form completed with this new record and place the screen into evidence so I'm gonna do is just to get this creation let me just enlarge this a bit and get this screenshot just get it as it is and paste it in my evidence document so let's go to my empties document okay there you go okay so I'm not gonna just waste them trying to arrange stuff here you've seen a way you're supposed to paste your evidence document and oh so let me go back to the question paper and okay so evaluate the design of your data entry form so you type the evaluation of your data entry form into your evidence document using no more than 50 words okay so guys like I said oftentimes you'll be asked to to make evaluations of things okay evaluation of a PowerPoint evaluation of and access for my evaluation of a document and stuff like that devaluation of a website web page evolution of your style sheet okay there will be many things that you'll be asked to evaluate now what you need to know is that when you talk about evaluation you're looking at both the good things that have worked out for that thing and also the bad side of that the limitations and the benefits okay the drawbacks of that thing okay so if you look at this form what are the drawbacks of this form just by looking at this form you'll be able to see some good things about the form and then you also look at some bad sides of this form also for example I can start with the bad sides and I can say well this form has known navigations okay we don't see any button here like go next and stuff like that it's you need to have proper navigations when how users are supposed to go to the left to fill in and stuff right it also has no instructions on how to complete the form you haven't seen any instruction here on how to complete this form it's not good if a good form is supposed to have instruction guys on how you're supposed to complete it okay we have the title is good okay that is good at least give the user some information to say hey this is what this is all about and then also we have appropriate or let me just say consistent layout okay you see how our layout is for the text boxes in fitness as Matt nice and stuff okay so we have consistent layout not layout layout okay oh you have constant layout and then your lab pose at least they should be I mean what is crew what is sleeps what is yeah you should have some descriptive sort of labels okay so have some descriptive labels okay so just let's just say use descriptive or should use descriptive level labels rather to just make sure okay so you can also use Kyle's collides do plain you can say okay I'm gonna make some color around you just to manipulate and just make it look good okay I'm gonna have to cut here on this one you can add some other things if you want things like some spacing they're appropriate spacing things like the deform occupies the the window nicely and stuff like that you need to just put them like that you can put up images and let me just close these guys otherwise it to close my my my my pages may just cancel it I need my web pages to remain open because I'm doing something also other than this okay so let's go back to our question so we've done an evaluation guys you can read in your in your text books and find out some good things some advantages of using forms and stuff like that and oh okay so step 29 import the file j2 to six builders dot CSV has a new tab go into your database set to build the ID as a primary key so what what we need just to do and then we create the one-to-many relationships as a link between the builder ID field in the build a stable and the builder ideal field in the yacht's table and we need to place them in there if this document so let's go ahead and import another foul so I'm gonna just maximize this again and I'm gonna close this okay I don't need it anymore I'm gonna close it and then go to external data and then import we're importing a file code builders dot CSV okay so this is the one here and go to okay and then next set first row and then next and then let's set the data I IDs or the data fit so they build the IDS short text okay and the builder name is also text and the address is text you can even tell that this is of a numeric data it has good numbers and texts then this is also texts this is text as you can see and then go to next we set the Builder ID as the primary key and then next and then finish okay now that we're done with this depose we have been asked now to create a relationship walking so creates a one-to-many relationship as a link between the Builder ID field in the builders demo and the Builder ID field in the yacht's table okay so we need to create this relationship so let's go ahead and go to database tools relationships and then select both tables okay just double click on them close it and then we are getting this builder ID to link it to that so we have that one reinforce integrity and try to create okay so Microsoft can't create this visit because this is a ship and enforcing fresh integrity so I'll explain what this actually means that you can't create this data so that they're calls in primary tempo exists for all data fields okay so what happens guys is that sometimes you in your primary table that is in the builders table which has got the primary key sometimes we may not have entries that are exist in there or we may have different data that is exists in there and the Refresh integrity thing will not work out for that okay it is also important to note that somebody was asking me the other day that what does the Refresh integrity do okay it makes sure that the data that you are trying to link the two fields that are trying to link in the two tables actually have consistent data okay data that is actually common data and then there are no blanks or empty spaces or empty records that I exist in or empty field data items in those in those records the Refresh integrity will reject that okay so just to make sure that you guys you really understand you try to build the relationship there just say refresh integrity enforce it and you'll see that it will say no so what I'm going to do is that ignore the aspect of refresher integrity but what you need to make sure is that you need to make sure that you have your one to many relationship visible here because this is what you are creating as a one-to-many relationship okay so the question says that we create a one-to-many relationship as a link between builder ID field in the builders table and then also build the ID field in the ass they wanna replace if it is didn't do it our evidence documents showing the relationship between the two tables okay so what we need to do now is to just get this screen shot just say create and you see that their relationship has been created but it doesn't show those parts weights is 1 to infinity and stuff like that but don't worry we also need just to edit that relationship so that we get the screenshot of this song when I just do this and then get a screenshot of this okay just get this so my relationship is this one which is showing that it's my one-to-many and it has been created okay so I'm just gonna go ahead and paste this into my updates document so this is between two tables and that's that one there okay so we go to the next question now steps 30 is about creating the database report and using tools from both the ads and they build a stable produce a report which one contains the food a new field called total Beth's which is calculated at run time so this field will calculate the total number of bits I didn't slips plus crew okay so only the records were billed the year where the build year was after 2010 and length is 40 or less and then shows on the fields yes name builder name and stuff like that and then in this order so this is the very part that I want them when I'm McLean I see two papers for more students I find out that then they don't care about the order in which these field names appear listen this is a one Mac which is like I said you cannot afford to lose because it can be that's that's one single Mac that you needed for you to go to the boundary or to go to an ester or a okay and just by missing and Esther by one mark guys it hurts it hurts it hurts a lot okay so you cannot afford to live in spawn and tend okay so just take it easy and that you need to make sure that these fields are actually appearing in the order in which they have been listed here and then you need to sort out in the ended in ascending order of type and then descending order of length and has a field name orientation landscape whatever and all these other things that we need to do so for the sake of time I'm just going to do the features here you guys can read them out if you want yeah I'm just going to do that and start working now what I encourage to do guys usually like for me when I'm building database reports I first of all create the query I know we have different ways of creating these these reports some they just go ahead and start with the report wizard under they go I didn't just design the report like that I love to do it with a query and then I can just import the query into my report I find that very easy okay so I'm going to select which tasks I can do with the report so the first task of a few which is supposed to calculate and run time can be done to report a query and this second one can also be done with the query this one can be done with a query this one can be done with a query okay so the first four can be done with a query okay so I'm gonna do the first four with a query so I'm going to close the relationships unless it tells me that just say okay unless it tells me I have just yes save it and then create this time a query with that and simple query I want to create a query based on some I some data so what I'm gonna do is to get the data that I want okay so what I want is these fields in this or damn last name build the name type length price beauty a country and total Beth in this order okay so I'm gonna go back here okay so the first one is supposed to be just named okay yes name it is in the other table in yes so yes name double click on it then build the name it is in the other table so build a name and then type it is in the other table so don't get tired switching in between tables so type is here and then length and then build here and then country is in the other table country let's say built country and we have total bits which is a a field that is calculated at one time so just go through the field names to know that they are in the order in which they have been asked for in exam so you have last name builder name type length built here as well as built country and then they are the fields that you are going to be using for sorting and for random and stuff can come later on okay for now these fields are enough for my query okay so I'll just go next and then next and then modify my query design and here I can set the title for my query okay so I can go back here and look at the words when the title for my report rather no I don't need to set the title for their query rather it's fine yeah it's fine I don't need that I was thinking it's on the report it's fine like this I'll just say finish and then you have these fields here so let me just remove the property sheet here and we're going to put a new field which is calculated at run time and this feud according to the question is calculated by the total number of bits is we add malessica clear the total number of bits by adding slips plus screw okay so we add the field or the values in slips we add them to crew okay so let's come here so this one is going to be square brackets like that and then you have slips okay plus square brackets crew like that okay just I've just written two things slips plus screw and I just click anywhere else and you'll see that automatically access knows what I'm trying to do so it puts expression 1 there and there on the expression 1 that's the one I can just give the name as toto and uh scroll bets okay and that is that so what I'm gonna do is to run this and just make sure that it is running so too much can you see that it is actually running and that should be fine okay so I'll go back to my design view okay so now we can go ahead and start working on some of the things that we've been asked we show only records where builder was after 2010 and list and length is less than forty or less or forty or less guys this is very important beauty is after 2010 don't put built here at equal to 2010 please bill dia is after 2010 which means and 2010 is a year and the year we set it as integer so anything that is after 20 must be greater than 2010 so you're going to put in your criteria as greater than 2010 and then the length is supposed to be 40 or less which means less or equal to 40 okay so this is very important guys that you supposed to do that so let's go ahead and put our build year and equity that's supposed to be greater than 2010 not equal to 2010 greater than 2010 and let's go ahead and run this query and you see that it leads all the years that are greater than 2010 okay so let's go back to the design view again and let's put up now lead length is supposed to be 40 or less so where it says length they were supposed to put less or equal to 40 and then let's go ahead and run it again and you see that the length is 24 and the maximum that you're supposed to get is 40 excellent okay so let's go back here so we're done with this one we done with this one we done with this one in the order in which they're supposed to be displayed and then the part that says in this order with data and Limbo's fully displayed okay so what this means is that if you come back here and you have some fields that are like this okay some fuels like this where the data is visible and in some section it's not visible okay so what you need to do is to select the tab book just combat it corner here you see a triangles here just click on it and take your cursor in on one of the boundaries okay one of the boundaries in the header and you see that all your data headings are going to be visible and all your data content is also going to be visible and that should be perfect okay so this tacos that okay so the last part is sort the data in two ascending order of type and then descending order of length okay so let's go back here I can sort that so let's go back to design view in ascending order of type first and then in descending order of length okay let's try to run the query and you see that type is in ascending order from M all the way to s and then in descending order okay and you notice that this is in descending order and it should be it should be good okay so the other thing that you can do if you don't want to sort it from here like if you don't want to sort it from here you can also sort it as you are creating or importing your query okay that is also perfect and it's going to work out just perfectly okay but I love to sort it out just like this from here that will give me some some items to work on and stuff okay so let me go ahead and yeah so we done pretty much with all this stuff we done with the first for the last six are supposed to be done on the report and so let's go ahead and save our query and let's go close it and then let's go create our report now I'm gonna use a report wizard don't get tired of using report Wizards guys this stuff is just going to work out perfectly so let me get all the items from my query and get them as they are in that order so you notice this is why I love to create this stuff in from the query because the order is maintained I just go next and then I don't need the anything from here how do you want to view data builders whatever I don't need anything from here it's just name do the name type lens whatever and just go to next and then do you want to add any up in levels I don't need any and then this is where the other part I was saying you guys where you needed to sort first of all by tape and then by length you can sort first of all by type in ascending order and then later on sort by name that is still going to work perfectly as well but I already did the sorting in the query so it should be fine so let's go next and I'm gonna use these columnar tabular and just fry it let me see which one I can use okay I'll go for tabular because I have my headings on top my tight on top and then all these data items then and then these are where you're going to set the other properties for that okay so you have highest page of rendition of landscape so let's set to landscape here and then also fits on a single page wide fits on a single page white is automatically ticked here adjust the field width so that all fields fit on a page now this is not the same as having a page fit on fit like one page fitting on our one I mean the report fitting on a single page this is different guys 15 on page white and the report fitting or when a single page is different white it means how from the left to the right it should occupy just one thing nothing should overflow onto the second page from the right let's say the column or the the records are cut here on the right edge here and then the other information overflows on to the next page no guys it's not supposed to be like that you are supposed to print or your dog let me just open this document maybe to just illustrate what I'm talking about guys let me just put a blank document and then at the blank document let me set another document another rather page black page so that I have to and then zoom out okay so what I'm saying guys is that I don't want you when you print your work occupying a single page means that from the left edge here information when they reach the margin here information should not be cut here on this margin information should not be cut on this imagine to overflow onto the right the right margin here and then to continues here no okay so when you add just you imagine to fit a single page white it everything compresses it only that it is going to be occupied under one page only okay but when you print to fit on a single page we're not saying we or information should not be I mean they should not occupy two pages but it should occupy only a single page everything should be compressed on to fit a single page have no hope guys that is clear enough for you to understand if it is not run the video and then you can do that so this is where we can edit the title of the report and the title of the report is supposed to be yeah it's less than six years that's gonna copy this you don't have this privilege of copying this I do because I'm using a soft copy question paper I'm just gonna paste it there okay so yeah it's less than six years old is a title of our report okay and this is why it's important to be thinking what you have done okay so I've done this I've done this and this okay so I'm yet to do calculate the length of what of that so this one and then finish it and then this is the report okay so you can go ahead and preview it and just see if your report is actually occupied so you see that the field name the head in here is not visible okay and then there's too many spaces here okay so this is not good so we need just to edit it we need to edit the type tab we need to reduce that the length also we can just drag it not like that just select this one I can just select this drag it there and then here select it and drag it there take note that the Year should be visible so you should make sure that the build year is visible enough okay build country just drag it there and I'm just gonna resize it a little bit countries don't think it's gonna be a long one bonus not bonus it just undo this one just dis elect these select that and then drag this this side okay that should be fine drag it here and let's try to preview it and see if it is going to that okay so pretty much good okay it's good it's so bad let's try to print preview when you print preview guys is very important it shows you the number of pages that this is going to occupy also as you can tell it says page one of page two okay so this is our also you can tell that your query is actually working that you've obstructed because this if you has your fifty four pieces I've seen some student printing as many as thirty four pages come on guys who'd asked you to print thirty four pages come on let's be serious let's not waste the school paper all right so this this looks good okay so I can go to page two and this looks good also on page two okay that should be fine let's go back to our close print preview and let's go back to design view we still remember we is to design in this report now what want to do go back to the question paper it says that it should calculate the average length positioned below length column and sorry percent below the length column and formatted to one decimal place okay okay so guys I'm gonna have to pause the video a little bit my wife is calling me let me just pause the video I'll come back shortly right so guys I'm back and let's continue on our program so I suppose we were supposed to do the average length and we're supposed to position this and their column and we need to format it to two to one decimal place so let's go back here let's preview it and let's see where we're supposed to go so for length we're supposed to find the average length at the last there record just below the last record let's go back to design view in the report footer that's what I'm going to put the average okay so let's go ahead and get the text box and put it somewhere here okay let's do that just take it just somewhere around there and this is supposed to get if average which is a VG rather begin with equal to VG and then you specify the field where you want to collect the average which is just length okay and then sorry so it's supposed to be average brackets and then at the end there because the square brackets they represent a field name okay or rather a column or a row not a pro a column okay an attribute okay so make sure just that your name is visible and then the label or the text that's supposed to be there it's supposed to be calculus average length percent below length has the label average length to the left of this number okay average length to the left of this number now remember when you guys did pepper to one they asked us to have the label at the right side of the calculation so what you need to do is to just select that and click where the grey part is and just move it around there and then you see that you can move so I can move it and take it to the right and so on okay so let's go ahead and preview this scroll down and it says average lynn's error so let's go back and find out where the era is coming from go to close this and go to design view and then let's scroll to the end there okay just find out where the era is coming from okay so lens it's coming from length so let's see capital e for length and let's go back and prove it again still shows era okay we need to sort out this one okay I'm gonna have to pause this a little bit let me just see where the air is coming from hello guys server that I've been trying to look around tried to find a way around the area that we encountered last time and the same formula that is supposed to be used as the formula would wear right when the formula I haven't used to found out the exact explanation why it had to do that but I had to go ahead and recreate the reports just to see if I encounter the same era nevertheless I did not encounter the same so what I did was I created a new database so to say but importing the same files and tested it again on a different set and this time around he did show as though it was going to work around so let's just see if when we we put in the formula for the average equal to average AVG and let's say it's a VG length and capital okay and the texts here contain syntax okay we're okay here let's remove that just do that and then the text is supposed to be average average length remember again the accuracy of the text is important and let's try to preview it okay so this time we get some value of some kind we're supposed to get this value last time we use the same formula remember and to get this so I'm just expecting that it could be just the software access we just acted up in some way I don't know because the same formula that we used has worked on this one nevertheless I hope you guys don't have that same error I'll try to find out why that era happened later on but for the sake of this video and time let's just stick to this one okay so you we have put the total that we need so the only thing that its remaining is for us to format the value that we get here to format it to the number of decimal places that are required of us so just select it and then go to right click and go to properties and then set the format format put it to fixed and I'll put the decimal places to one so let's view and you have thirty two point zero as your average okay so what I'm going to do let me see the let's get to the question paper okay so we've done this one we've done put the label down this one we need not to put this Center number name and candid number at the top of the report okay so let's go back here and put the label so let's say this is Casa Evans ZM five five six and then go to go to zero zero zero one okay preview it and you should have your details on top there okay so that should be fine the only thing that I need also it to adjust remember the data is supposed to be visible as well as the column headings so we can see that Pacific Asian Enterprise is not visible so let's go back to the DA view design view rather and just we're just going to push some things let's see this side first of all how it is okay there's some space here so we can just push this side this stuff this side so just reduce the size for this and max why is this one okay so view it again yeah there you go so you can now see the brackets at the end to close that so make sure that every field is visible before you print this so just do a print preview go to view and do print preview and this is how your document is going to appear at the end okay so we have not be asked to put the debt it's not important because by default access puts this so don't struggle to say we should remove it you want okay so that is what you are supposed to do guys on this one I'm sorry that we had to go this far anyway you didn't even see me going back through the process of working out what was causing that error but it did take me some time because I had to recreate the database again the same procedure that we followed everything was exactly the same and again that's why I say it should have been just the software error and I hope you guys don't encounter that otherwise I didn't change anything everything is just as it was in the previous pad okay so what I want to do now is to save the report and then print out make sure that you have entered your name Center Nandan amp and printout that and then we are going to place now if this document screenshot showing the formula to be used in the calculation of the average length so let's go back here and what we just need is a printout of this okay just get his screenshot of this and this is what you're going to put in your evidence document to show that you did calculate your average okay so that is that let's go back here so this is fine and you can go ahead and print preview it after that then you can go ahead and just print it you should have two pages this one showing you you have two pages and that should be fine all right so I'll close this one and go to the next step in the video which is the last step of acts rather data manipulation and we are also going to produce a report on this one in the other video we did produce an extract and this one we are required to produce a report so what I'm going to do again is to identify which sections I can do with a query and then the other ones I can just import them into the report okay so select only those records where built the name food contains just and then filled countries New Zealand or Australia and then the food names that are supposed to show only these and then the services in this order rather in this order and then we sought the price in a descending order and has a page orientation of portrait and so on and so forth so again we're going to go through the same process of creating a query for this one so let's go to create let's save this one first of all I think we're done with this one so there's no need for us to have it closed open brother so query wizard and simple query and we are going now to select the fields that we need ok so I did see that the fields were going to start in the old is name and then build the name let's see build a name then type so guys in case you're wondering which one is the important query and stuff this is the query that I just created nevertheless you can get them some of these things from the other table so I'm just getting from the previous table that I created most of the fields are there so just name build the type rather build the name build the names not there so build the name I'll just get it from here let's see do the name is there and then we have type it's not their type can be found moving in here so gets typed from there and then I'll get price get built country build country let's see they're built country and then services okay services okay so let's go through again that we have the correct format it's just named build an m-type price build country and services in this order so let's go back here just name the name type price build country and services okay in this order and then so now that we're done with this let's just go to next and then next and then modify the query and finish it so what I want in the query is to indicate that the build name contains the act so build name or build the name rather contains yes so criteria so it contains is to astrick signs and then I type yes okay remember they the astrick signs part of their world heads and you guys you're supposed to know how to use wildcards in in order to search for particular information for example quartz and things like brackets things like minus signs things like plus sign what exactly they mean you're supposed to know you guys okay so build country I'm gonna select or I'm gonna type New Zealand New Zealand now it's New Zealand or Australia so I'll put all Australia now you don't need to type to learn how to type like what do to matically access puts that for you so just click outside here and you will see that it puts New Zealand in quotes or Australia and then you can go ahead and preview this and see if it is working there you go so we have anything that has to do with yet so Alouettes fruits and stuff like that and this is just new zealand and australia and our query is working perfectly so if we come back to the equation paper we should see that we are done with this one and we are done with this one we are done with this one we just need to sort the price in descending order so let's go ahead and do that go back to design view and sort the price in descending order ran it the query and you see that the price has been sorted in descending order that's perfect let's go back to the design view again let's see what we can do okay so the other thing is supposed to be there on the report so I'll just go ahead and save my query and then guys remember again you need to save your query before you start to create your new report okay because if you don't save your report the query the report is going to be bail built based on the query that was last saved and you may find that you may have not have done descending on your Zealand and stuff and then you build the wrong way okay so in make sure that before you start to design you your report you you you make sure that you have saved your query okay so let's go ahead and create and let's close this first of all and go ahead and create our report and this report now is based on the last query which we created which is query two I think yeah and then to stick all the fields them and go to next and next and next and still next and then change the orientation let's verify the orientation orientation supposed to be portrait and should fit on a single page okay now guys this is very important this one says fits on a single page not on a single page white but the single page and this is where you guys is supposed to make sure that your your report is compressed onto your page before you print it I'm going to show you how to do that and then you guys can do that in the exam okay so how to adjust the fields to fit so that they fit on a single page white but I'll show you how to do it so that the page going down the page also fits there so let's go ahead and put the title so the title is or shiniest Brokers okay and just copy that and go to that and paste it there and finish okay so we do have that and let's go ahead and preview it okay so the hash tags like I said like I've always been saying the hash tags really just mean that this is going to be printed rather the the field size is is small okay so you need to increase the size of the field okay so what I'm going to do just go back to design view and just enlarge this so let's put that like that and then just enlarge this okay go back and preview it again and there you go you find that that is not there so the other thing again that you need to do is to make sure that all these fields are fully visible you can see here development is not visible and build is not visible so let's go ahead and drag this as well and to this other end and see how it's going to look okay so go back here let me just prove it so I see which ones okay so type there's so much space there so I can move type just do that move type this side then do that and this one this side as well let's go ahead and prove it and there you go builder is visible development is visible and I can still move type a bit this side just to make sure that the space is consistent okay okay just pull it a bit there and pull this one a bit there and also for build country just pull it a bit there and services as well I'm just going to do it beat there let's go ahead and preview okay looks fine but again we have cut some stuff here so we need just to make sure that so go back there this one just pull it a little bit there this one pull it a little bit there and this one okay just pull it a little bit there as well okay preview it again and again we just have some bit okay so let's go ahead again please you shouldn't get that doing this otherwise you get that also getting excellent results okay so let's let's increase this guy's size okay almost there guys almost there okay so let's do it again one more time just reduce the size of this just there and then drag this this side and yes this this should do it let's pivot okay so everything is visible now so this is perfect okay so we have managed to finish this part the sieve so we just need to put our name sent animal condom and at the bottom of the report so let's go back here at the bottom of the report we need to put that so you can either put this in the report footer or in the page footer but preferably you can put it in the report footer so just put first of all your name there just search because Evans comma z m5 5 6 comma 0 0 0 1 preview it and you see the name is there at the footer now what needs to happen guys also like I said the pages number of pages it's not necessary but I'm gonna just remove them so that this report looks professional okay then go ahead and preview that and that's that's good okay now on the second way you have to fit the page in a single white page let's go to print preview this page already fits on a single page but suppose we had a lot of text and a lot of Records and they had to go there so you go on your printer and under your printer properties I think let's see pages per sheet one this one doesn't give me that let me see page setup okay so close this one is come to page printer setup let's see if Britain tada okay this one does not giving me what I will want to see and there is a setting that you can change so that it fits on a single page but I don't get this set in here not even on the printer because it's supposed to place printer setup find out nothing supposed to be here okay but probably it's because the page is already fitted on one one page you notice for example in your in wait let me just show you when you're in word and then you want to do printing okay you can set up your print your page setup or just here where you you say that who is that customer in one page page sheet and then you can set let's see one page page yeah yeah that's that's that's the feature that you are supposed to use one page per sheet okay everything much looks the same oh good the feature and let's see so in Excel let me see if it's there as well cause you guys as well you're going to be producing reports in Excel and the feature for setting a fitting printout on a single page okay so scaling and this is where it says shrink sheet on one page so this is where this is the feature that allows you to print everything and to make it fit on one page so the the thin in axis like like I said we already have this page we just set it on the right side that it doesn't will flow to the next page but we don't have many records to allow it to go to to overflow so you go ahead and verify that all these actually are done and then you can go ahead and print this okay so guys thank you so much for watching this video and sorry that it does take a little bit longer than expected and mmm I'll see you in the next video let me just do a quick video on mail merge and then I don't know a quick video on from presentation and then I can call this a day I know I'm running out of time because I need to go home as well I'm still at the office and this is just nine nine almost 9:30 p.m. ok so right so let me stop this video and then let me continue recording in the other video

41 Comments

  1. Goffi uar said:

    Thank you sooo much for your explanation .Its helpful 😀😁

    May 23, 2019
    Reply
  2. pink eyed said:

    57:03 the same thing happened to me but I just adjusted the lengths and placed it right under the ''Price'' column and it worked 🙂

    May 23, 2019
    Reply
  3. Khadijah Badr said:

    there are exactly 37 records not 35

    May 23, 2019
    Reply
  4. yazan anbar said:

    For every guy in cbs watching this video
    Kos 2om tony

    May 23, 2019
    Reply
  5. Lil Broke boi said:

    When I print previewed my Report, I had like 7 pages. Does that mean I done something wrong? As he has 2 pages only

    May 23, 2019
    Reply
  6. mathaniii mdn. said:

    guys help, the #error in the report part is still happening to me

    May 23, 2019
    Reply
  7. Salman Arif Sohrawardi SSR said:

    Need the link for EVIDENCE 13 – Bullet animation screenshot

    May 23, 2019
    Reply
  8. Salman Arif Sohrawardi SSR said:

    Can i have the link please for step 25 evidence 6 Database field structure screenshot?

    May 23, 2019
    Reply
  9. Teaks said:

    very helpful!! Tanks!!

    May 23, 2019
    Reply
  10. Imperial Recker said:

    how do I fix the problem at 30:37 in libre base. Plz help me . I have the exams in 1 week time.

    May 23, 2019
    Reply
  11. BuilderLemon said:

    Thank you for these videos sir, they are very helpful, but please try to shorten them because most of the time I will not have that much time to watch a one hour video, even if I speed it up. Thank you.

    May 23, 2019
    Reply
  12. Buthina Aladwani said:

    Thanks my IGCSE exam is tomorrow it really helped a lot.

    May 23, 2019
    Reply
  13. fizshook said:

    sir, when i type in the new field name 'Total_berths' it gives me the parameter value. What do I do?

    May 23, 2019
    Reply
  14. Salome Singh said:

    I think the problem is you have to put the text box in the footer of the " report"
    "Yachts less than 6 years"

    May 23, 2019
    Reply
  15. kookies said:

    When do we use enforce referential integrity?

    May 23, 2019
    Reply
  16. Craig Craig said:

    I have the same error for the average

    May 23, 2019
    Reply
  17. Samyak Shah said:

    @evaan chikasa sir.. Sorry that we used ur comment section as a chat room ….but i would really likr to thank u for ur hardwork thats the reson we are able to get good marks in ict and sir 2019 feb paperb 2 and 3 were quite easy …

    May 23, 2019
    Reply
  18. the zodiac hut said:

    YOU ARE A BLESSING OMG. i just finished my exam one hour ago, it was feb march paper 21. and i remember, in the beginning of the year, i was on the borderline of failing ict and i was terrible at it because i could never understand anything during school lessons. but thanks to you, i've managed to do a much better paper today and during term reviews i scored above 70% due to your videos. thank you so so much. i hope i get a good grade 🙂

    May 23, 2019
    Reply
  19. Krish John said:

    47:07 what was that sound ?

    May 23, 2019
    Reply
  20. Danvirsinh Jadeja said:

    damn my paper was amzing beacuse i saw ur data manipulation video …it helped me alot….thank you

    May 23, 2019
    Reply
  21. Nebiyyu Yohannes said:

    Chikasa the man 🔥🔥🔥🔥🚀🚀🚀🚀👍👍👍👍

    May 23, 2019
    Reply
  22. Vedant Raval said:

    this dude is a GOD

    May 23, 2019
    Reply
  23. Mujtaba Hyder said:

    Thank You so much

    May 23, 2019
    Reply
  24. Manjunath LG said:

    MR-CHIKASA YOU FORGOT TO PUT THE PRICE COLUMN IN THE REPORT

    May 23, 2019
    Reply
  25. Ines Otero said:

    Evan, you are the best teacher in the world, I have my exam tomorrow and you teach me all the things I didn't know.

    May 23, 2019
    Reply
  26. Ahmed Sultan said:

    Thanks your a savoir

    May 23, 2019
    Reply
  27. Samyak Shah said:

    Sir,
    The error was shown because bymistake you wrote the formula in the page footer earlier….but then you corrected yourself and placed the formula in report footer.
    Thank you for the videos.
    You teach so well better than all i have met and the little informal and friendly language you use i love that the most …that realy helps in learning where the formal tone just irritates and feels boring.!!!!!!!

    May 23, 2019
    Reply
  28. RYAN KHAN said:

    Mr. Chikasa you had average length on the page footer but then you put it on the

    May 23, 2019
    Reply
  29. Silver said:

    When I solved the first query I had 37 records. Why?

    May 23, 2019
    Reply
  30. Ahmad Yehia said:

    to save time in the report instead of typing in the formula you can go to layout view then right click the column then go to
    Total(field name) and select average scroll down and you will find it then if u go to the design view you can see the formula and then you can add a label box next to it

    this way you will never get an error

    May 23, 2019
    Reply
  31. Yuen Lee Shark LAU said:

    igcse exam tomorrow

    May 23, 2019
    Reply
  32. Dua'a Saleem said:

    The error occurred because first you placed the text box in the page footer…. it was supposed to be put in the report footer. There was no other error.😊

    May 23, 2019
    Reply
  33. Hamood Abuzuhri said:

    ❤️❤️❤️❤️❤️❤️ You’re the best

    May 23, 2019
    Reply
  34. Banana Warrior said:

    dude you done something wrong cuz i also got this message!!! #Error

    May 23, 2019
    Reply
  35. Jasper Galton-Fenzi said:

    the error's u encounter save some of us from encountering them!!

    May 23, 2019
    Reply
  36. Mary Fernandes said:

    God bless you and your wife. You are so good. Thank you for your time.

    May 23, 2019
    Reply
  37. Allam Anas said:

    i am realy happy and i think i will past my igcse exams both paper 2 and 3 for 2018 enko bonanjo international school cameroon

    May 23, 2019
    Reply
  38. Tanvir Sharqi said:

    life saver on Tuesday igcse practical paper 22. XD

    May 23, 2019
    Reply
  39. sara 88 said:

    it was not a software error you placed the the box in page footer instead of report footer ,anyways thank you so much,, you're a life saver and a brilliant teacher you made me love something which made me cry for weeks .

    May 23, 2019
    Reply
  40. Dalya Hussein said:

    learning all of that before my ICT mock exam tomorrow lmaooo

    May 23, 2019
    Reply
  41. ĢÒĐ ĚŁĶØ said:

    Thanks Alot Daddy Your the best African in the whole wide world

    May 23, 2019
    Reply

Leave a Reply

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