IGCSE ICT February March 2018 Paper 21 Data Manipulation P2



okay sigh everyone say once again and welcome to this tutorial in this tutorial I'm going to continue looking at the February March in 1880 CCI CT Peppadew one in the last video we end up step number 24 and in this video we start the step number 25 so step 25 says you are going to prepare a department Oh telephone directory to produce a new report from all the data which one includes in your field or calculated at run time with the title external phone and then displays the current rather the full external telephone number for the member of staff by picks the fix in the internal four-digit telephone numbers with plus 77536 for example if the internal telephone number was 9999 this number should display as plus seven five three six nine nine nine okay then the next one is includes and staff who work in the biology or chemistry departments so only stuff palate geochemistry Department and all this can be done in the in the query like I said you need to find which fields can be done in the query and which one can be done in the report so mine these ones can be done the in the query and then these ones should be done in the report you don't even need to waste time okay so this one should be done in the report you don't even need to waste time okay so working through this I remember some of the students that I take for online attrition I have shown them how to crack down this question by highlighting from the question paper you highlight which ones with which questions you can do with the query and which questions you can do with the report and when they just come across the question like this the first thing that they do is first in they sort out what can be done the query and what can be done the report and I see it it is just cuts the question the points into a half and they find it easy then to look at everything and you mixed what can I do now okay so it's very interesting okay and by the way I do run I still run the the online tuition I do get in touch me with me if you want it classes with me online at the fee of course the monthly fee right now the current students I have the pain one hundred and fifty dollars a month and that means they meet me 16 times in a month each time they meet me that's an hour so they have 16 hours where they work with me fully on both the practical papers as well as the theory papers so that just by the way thin in fact great an address and advertise me yeah I mean who does this is this is my channel this is my video so I paint off it to advertise in my videos alright guys so let's continue so with these breaking of this person into two parts and now this go ahead and do that so the first thing that we're going to do is to actually show these fields in this order okay so we start with this let me use pink so let's start with this one okay so we go to create and then let me just close up this one 70s and then let's go to query design we select all the three tables and add them now we need to start selecting the fields that we want so the fields that we want show let's see show only first name family name job description okay so first name family name so it's first name family name and job description okay in that order so then you have department then telephone and external phone so department then telephone and then there one is external phone which is a few tackle it at one time so this is the new field a cadet it at one time so let's put it there external one okay then folk a colon and then we have now the criteria for calculating this one so displays the full external telephone number of the member of staff by prefixing the internal four-digit number with it so this one guys prefix in which means before the existed number you add this code okay but exist the number is this one so before that we need to add this code in programming we call this as string concatenation this is the joining of two strings so you know is what they're asking is that join this string and this string and as this one now I'm calling them as strings you guys may not be conversant with the word string because you are guys doing ICT but the guys who are doing programming you know that string is the same thing as text so when I say string I'm just referring to the text okay so you know is we declared the telephone number as step and so this one should be also text so to join these two together they should be but this is not a number per se we need to convert it as text and to convert this one Aztecs we simply put it in double quotes then it will be regarded at okay so what we need to end and the eret and also that we need to do is that to join two numbers in join two numbers in in in in axis or in in in excel you can use the plus symbol so the plus symbol can be used to join two numbers or two not two numbers but two texts string or two strings or two texts okay so without wasting much of a time this is true that it will make more sense when you win win so the fitness plants seven seven and then five three six this just confirmed that we have the correct number seven seven five three six and then we need to put this into in double quotes so remember for accessing a field name we put in square brackets by this one we put it in double quotes so double quotes like that then we've converted that number into a text or into string and then when I say plus okay and then now I can get it text the other text now the other text is coming as a telephone number so we just came plus telephone okay so when you do this that means that you telephone is already text and you've converted a tomato text and then you are adding to text so when you add two texts or two strings the result should look something like this bingo so notice that it is this number that we've added at the end at the beginning and then the telephone number that was existed at the end this is the telephone number here and notice that we've added there okay so guys if you don't understand this please go ahead and just take your back a bit I mean back back back rewind it and it'll make much more sense I cannot overemphasize and string manipulation of string concatenation the joining of two strings together so remember that you are first of all changing that number that you had given with that with +7 that's why telephone number was initially declared as not declared was not initially assigned and the data type has ticked before now it is as it is so well that's good so this is done now we need to get the criteria for the staff who work in the biology of misery at MIT so now we need to do for Department right ecology or chemistry please don't use The Awl here some students can write polish here they write college here and then they write chemistry here know what this mean is that it goes through all work with Balaji so if you had another critters for example job description as maybe engineering and stuff like that it will go through all the biology with engineering and then you to come and go through all the chemistry again without ok this is very important so this all works with this entire criteria here okay so this criteria is the same criteria not the same but this is the alternative criteria so if you have one criteria here then you can have the alternative criteria and everything here else like ok so done with this one the next thing that we're supposed to do so you can prove it pick the query to see if it's running so if they go with biology and chemistry so it's running so then say that you need to do now is to go to your report okay so we direct the query the remaining part is in green and you see guys how easy it is actually to break it into so it shows you that you are done with this part the next now shift to the report okay let's save it I'll save it as query number 26 I don't know if you spend six or let me just verify 25 30 25 25 query okay just to give me to differentiate it from the other query close it up and then now we can go to create the report with it and then we are using query 20 25 all the data in there next and next next and then don't change anything here as usual and then go to orientation supposed to be orientation is supposed to be okay so orientation is portrait and supposed to be single 85 okay so our addition is portrait and single page white so it's already set that's fine and then did their report title you can add their potato here and the title department or telephone directory departmento de parte mentor so I don't know if it is capped or not so Department or telephone diary okay so that should be fine and modify their port okay so that is done so now the next thing just go ahead and view it in layout view so that use your information is fully visible so as it is the information is not fully visible so we are going to drag this one this side a little bit and then just to create some space and drag this one oops and do pick up this one alone and drag it so somebody was asking me how do you select all the the fields like how to select this one together with this one or how do you select this one and this one if you want to move them well the the thing that you need to do is to highlight hold ctrl key on your keyboard and do this one hold ctrl key on your keyboard and then you can click on the column that you want so I'm just resizing it okay and then drag it so just make sure that the fields the data is not cut in any way okay okay just do this and this one we need to enlarge it so drag it from this end drag it like that so I should be fine drag it and that should be good okay so your fields are fully visible the next thing that you need to do so they're fully visible then that they now let's do the sorting so the sorting we start with first of all and sort by department in ascending order so let's go here if you scroll if you go two ways it is this one go to you can go to layout view if you want and I can see sort at the bottom so if you can find sort just go to design okay and group and sort its here and then add the sort so we are sorting festival by department in ascending order so department it on top stroke and add another sort this time we are sorting by family name in ascending order so family name a on top that's fine and then add another sort because there are three sorts then this time sort by first name in ascending order first name in ascending order so first name a on top that's fine good so done with the sorting we're done with the sorting we can highlight this just to exclude it we done with this one then we need to include Center number enter the number on the report now this one is one the report so we are going to do that so guys the other thing that you can know whether to put it on the report footer or in the report footer or in the page footer is actually if you have multiple pages if you have what it is the name should appear on the other pages grow the other pages okay you can just put it on the report footer and you think yourself no you need to also put it on the on the page footer so that your work should appear there so here is Casa everyone's zm5 five six and zero one okay and that should be dead I don't need this one in with the date and time like that I don't need it not even the page number I don't need because I just have one page is no need for me to have that so that is good if you view it yeah whether in print preview what you should see your name appear in there and your port looks somewhat guilt and make sure that all these are fully visible and they're in the order that is given and that the work is good okay so that does it for that so that's your eight max and make sure that your printer has your name and center mankind number and they go with 30 max for data my reputation is well a lot of I emphasize this this is a free max case okay is the free maps we just need to know what to do at what time okay so thank you so much for watching this video and I'll see you in the next video when we look at marriage okay so thank you so much guys don't forget to subscribe to my channel comment like share whatever do whatever you want okay and I'll see you in the next video bye bye

21 Comments

  1. Osama Alrazi said:

    THANKKK YYOOOUUUUUUUUU

    May 22, 2019
    Reply
  2. sabrina shrmin said:

    Thank you every much. It was absolutely helpful

    May 22, 2019
    Reply
  3. Musab Qaiser said:

    Thank you Evans Chickasa

    May 22, 2019
    Reply
  4. Dan a said:

    MR APPY- Grasses can hear human voices and respond to them

    May 22, 2019
    Reply
  5. Areeshah Hassan said:

    why dont you do ascending and descending order in report wizard?

    May 22, 2019
    Reply
  6. Cyclone King said:

    hey thx bro

    May 22, 2019
    Reply
  7. Sahil Morey said:

    Can some one tell me how to add prefix in access. I tried the same way as evans did but in mine rather than using it as a prefix it is adding the +77536 with the telephone please help
    @Evans Chikasa

    May 22, 2019
    Reply
  8. Meme Lord said:

    All the best to everyone for feb March and may june 2019 exams

    May 22, 2019
    Reply
  9. Allan S 2003 said:

    thank you so much for teaching me this
    your videos are really helping me and my friends

    May 22, 2019
    Reply
  10. Rishabh singhvi said:

    Dear Sir,
    Can you please tell me how to automatically get width/height size in tr tags, in mark scheme they have shown that width size or height size must be there in tr tags, but i have tried many times but it only comes in td tags. Please help sir.

    May 22, 2019
    Reply
  11. Sanjog Chavan said:

    Love u thanks for teaching me this

    May 22, 2019
    Reply
  12. mustafa maher said:

    God bless you

    May 22, 2019
    Reply
  13. Luqman Hakim said:

    how to find out which questions you can do in query and which questions you can do in report?

    May 22, 2019
    Reply
  14. Sultan Saeed said:

    you're the best

    May 22, 2019
    Reply
  15. Abdullah Asif said:

    Wakanda forever

    May 22, 2019
    Reply
  16. Prince Jack said:

    Immensely helpful video

    May 22, 2019
    Reply
  17. Mavesh Damodaran said:

    hey press alt and
    tab to change tabs

    May 22, 2019
    Reply
  18. Adam Hazahar said:

    cant watch data manipulation P1 because it says private huhu pls help cuz ur vids are literally helping me

    May 22, 2019
    Reply
  19. Lee Jian Hui said:

    is there any merch? put merch link in bio and video description pls pls pls

    May 22, 2019
    Reply
  20. qwe qwe said:

    Sir, for the sorting section, it says the Department(Chemistry & Biology)should be in an ascending order, if we apply this sorting, then the name&family name wouldnt be in an ascending order anymore (although the question ask you to do so), does it mean the 'department' section is the prioritized one? pls help anyone

    May 22, 2019
    Reply
  21. Eithar Alshbail said:

    advertising for his own private tuitions,,hahahahahaaha,this person is amazing.btw,sir,,you have taught this past month more than mt ICT teacher ever did in the past years

    May 22, 2019
    Reply

Leave a Reply

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