ICT IGCSE Paper 2 May June 22 2020 Data Manipulation Access Part 2

hello welcome back to my youtube channel my name is yasur ahmed so in this video we're going to continue on with paper two from the cie ict igcc course we've started a may june 2020 paper variant 22 so this was the paper that was cancelled by covert if you check out the previous video i've completed the document production part and now we're going to be doing a data manipulation which is a database part so guys remember paper two is a practical paper which is 2 hours and 30 minutes long so let's go down to page i think it's page 7 yep task 3 databases i've got the evidence documents open as well from the previous part of the exam paper as you can see we've taken some screenshots from the word task and we're now ready to start the database task so you are now going to prepare some reports dates are to be imported in the format of day month and year so using a suitable database package import the following files so before we can do anything let's just make a new database file so we can just right click our mouse go to new and select microsoft access database or you could just simply open up access and do it that way as well okay it doesn't really matter what you call it so let's just call it one two one t and open up the database file so we are going to be importing this file here j220results.csv uh if you look at your source folder it's going to be this csv file here so let's just have a quick look it's not necessary to have a look at the csv file uh but if you want to have a quick look to get familiar with data uh you can do so okay that's fine just normal data we've got fields and then records and this is the boolean field it's going to be yes or no i can tell because it's got minus one and zero um yep that's fine so remember close it down don't make any save changes before you import okay to import the csv file you need to click on external data new data source from file and text file you may be using a different version of access so if you click on external data text file may be up here i'm just going to pause the video whilst i browse and locate the file the csv okay okay so i've now found the location of the file so if we just press cancel remember you click on browse go to source folder and the file that we need to import is going to be this one here ending in results double check yep double click press ok then we need to click on next select the first row contains field names because these are field names along the top and then go to advanced so the first thing we need to check is it's a date in the correct format yes it is and now let's check the data types so the first four should be text so short text yep that's fine birth date it's going to be date and time that's fine then we have some number fields we've got one two three four five fields which should be numbers um so one two three four five what was penalty and penalty is going to be shown as a boolean field so we did see a double so this is where we have decimal places that's fine penalty let's change to yes or no okay and the next four fields should be text okay sure yeah that's fine okay so as you can see the double normally refers to the field which contin contains decimal values okay so we need to store and display to two decimal places which we can do later on press ok next next do we have a cramming key so we need to set the eraser id as the primary key so remember the primary key is a unique piece of data for each record so we can choose our own primary key and racer id yeah that's fine next and finish [Applause] open up the table and the first thing we need to do is to set this to two decimal places so this field here okay so we can go to home design view and click on here you can see the format is double decimal places we want to set to two let's go to view okay that's fine that's been set to two let me just see if i go back to here and if we click on standard actually that's better like that so to get two decimal places what i had to do was i went to design view um i changed this to two and we change the format to standard okay the next thing that we need to do is to show this as yes or no so remember this is a penalty field and is to be displayed as yes or no so again we go to design view uh click on penalty and change the format to yes or no click on view and yeah we're good to go that's done for evidence five so let me just quickly save the data for evidence five we need to place in the evidence document a screenshot showing a parameter key field names and data types used in this table so this is a typical print screen we're doing databases so we go to design view and basically need to print screen this okay um i may need to take a couple of print screens so we can get everything in one go okay again i've mentioned in previous videos if you want to use a snipping tool it's up to you i just prefer to use this method now since i've been using it for a long time okay so i'm going to have to take this screenshot and two separate goals i think we'll go just above the boolean field okay so we can see the field names the data types um yeah let's take the next print screen obviously now we want to show this part okay go back to my evidence document so it's okay with this part if you do take um additional print screens again crop off the parts which you don't need um and i just want to show from um the yes or no field the boolean field okay let me just see if i can make that all fit uh let's make this a little bit smaller perhaps make this a little bit bigger we see the format below that okay i'm happy with that um i'm just going to crop up this part here yeah and for this one i'm just going to crop off the bottom part there we go yeah we've got all the fields the different data types and we've got this extra um information piece of information here to show that this is going to be shown as yes or no the format has been changed okay let me just zoom on to this page okay that's better and we can move this one down now to the next page okay so that's that part done now we need to import the second csv file and this one ends in club.csv okay as a new table in your database so again we can close this down we go to external data new data source from file text file or you may click up here to get a text file or you can click there as well so the csv file ended in clubs again press ok next the first row contains field names and let's just have a look at the data we have so it seems like it's all text um yeah and this these are just points here so let's go to advanced um yeah we've got integer at the end and the club rank is also an integer that's fine let's go forward do we have a primary key uh set the club ids primary key so we can choose our own primary key and i'm going to choose the club id next finish now we need to create a one-to-many relationship between club id in a clubs table so from this table to the club code in the results table okay so we don't need to change anything here so we need to basically link the club id which is going to be this one here this fields to club code in the results table okay so to create your relationship make sure you close down the tables you go to database tools and you click on relationships so we click on this and then this and you can increase the size of your tables so it's going to be a club id from the clubs table to club code uh i'm just going to click on create then what i'm going to do is click on this line here and show this as a print screen because normally we have to print screen the evidence yep and what we have is a one to many relationship okay so let's take this print screen and i'll explain the once many relationship in a moment as well so this was evidence six again crop of the parts which are not necessary you want to make sure your print screen is nice and clear showing the relationship between the two key fields and obviously it's a one-to-many relationship so what does that one-to-many mean let me just click on okay close this save the changes so each record probably refers to a club okay and each club has a unique id so one club has many races i think these are yet many members of many races okay so this club has 35 you can see the number at the bottom here this club has 23 so the example classic example is one teacher many students in one class okay so we've done a relationship and now question 21 we need to enter the following details as a new record in the results table and we click on here so this is the results table sometimes they may ask you to create a data entry form but in this paper we're just going to add the new record along the bottom here so in the exam you won't have it electronic so you basically will have to copy out this information so make sure you do type it exactly as it appears here also including capital letters when shown okay so what i'm going to do i'm just going to pause the video whilst i finish writing or copying and pasting in in in the fields okay so now i've entered all of the data so before we move on let's check the date your data for any errors okay so we can check four fields at a time so this is a code let's get this highlighted pen on again let me double check set x c o two 35 olsen janet and an mtp c10 yep 12 of the second 1991 28 [Applause] yeah 28 and 13 67 and six done 105 1550 yep then the next one's going to be so penalty was yes and i haven't changed this so this needs to be yes so it's good thing i did check then got senior 19 to 29 yeah and an expert and female yeah so make sure you do check um luckily i did check and notice there was a mistake with penalty so now we can move on save the changes close on tables and this is now where we create a query and a report so the question says using fields from both tables produce a tabular report okay so remember before we can make the reports so the report is how the data is going to be represented or formatted we need to create something called a query and in a query is where we select the fields we enter some search criteria and then once we've filtered down the data then we can basically create a report based on that query so always um if you look at this question and then if you look at the next question that we're going to do we always go with selecting the fields first okay so if i go to access you click on create query wizard press ok so we've got two tables and we need to select fields from both tables so it's going to be first name last name club name so first name last name the club names probably in clubs yeah uh category gender so let's go looking over table category gender and event rank okay so that one there next next and when when you get to this point i always use or i always like to include the title that's going to be appearing at the top of the report so query 2 actually wait not that's not the title so i just realized that was a mistake um so what i'm going to do is change my annotation so i'm going to just move that for now and then this we need to move up to here okay and this is also a report task which will come to so the title is going to be at the top of the room of the report i always i like to also name a query with the same title and you'll see why later on in a moment so the title will be results by category let's put that title in finish so we're starting off with 774 records as you can see if you go straight to the report you're going to be printing for days um so what we need to do next is to enter the search criteria so the event rank is going to be five or less so you can basically use this criteria here to enter the criteria we go to home design view underneath event rank um so see here criteria below the tick we can enter the criteria so less than or equal to five always enter one search criteria one at a time so we're down to 69 records and you can see these numbers seem to be less than five yep five or less and ability does not include novice so there's two ways of doing this you can either use the greater than and less than sign novice or just right not novice doesn't really make a difference so what is that what field was that again so ability so you can see here we don't have ability as a field but if you go to design view we can add ability and what we basically want to show is or say is not novice so i'm going to use the greater than and less than click on run and we are down to 59 records so i'm just going to click here can we see novice anywhere no so we could have also um wrote it like this as well so i'm going to show you you can do this do that as well if you want to doesn't really make a difference so not and we're still down to 59 records and you can see we don't have novice here okay so we've done that now the search criteria is finished we are now ready to move on to the report task okay so save the changes to the query go to actually before we move on are we meant to show ability uh nope so what we can do is we can hide this field okay click on run and you can see still we have 59 records so you can still search by field and then hide it after if you want to now we're going to move on to create a report so you click on create a report wizard just save the changes select all of the fields next next when you get to this point here you can see we have an r which now stands for report task so sorts of data into descending order of category so category is going to be descending and then ascending order of event rank so yeah so because we have done the source these fields will now appear at the front but obviously we need to put the fields back in the same order shown here and we can do that in a moment okay so we've done that has a portrait has a page orientation or portrait so we can choose that next and we're going to enter the title again so includes only the text results by category as a title displayed in a larger font so we can change that in a moment in the report finish okay so this is a report and there's a few things that we need to do the reason i like to name a query with the same name as the report is now i know this query is for this report because both both have the same name right what we need to do first of all is to display the fields in this order and make sure all of the data is shown and making sure all the data fits one page wide so what we're going to do is click on close change the view to layout view and these are the fields we've sorted on so what i need to do first of all is just to move this across so to select the field name and the data you need to press down on control on your keyboard selecting both at the same time so again select the field name press control select the field data and the first thing i'm going to do is just move fields back into the right order so the first thing that we need to have was first name last name and then club name first name last name let's just move these across okay and i'm going to move this across again just to give myself a little bit more space to work with and just move this across slightly and then what we need to do is make sure all the data is shown in full so can we see all the names yes but obviously you can see this name is slightly cut off so what we need to do is just move this slightly to the right and then we want to make sure these names are shown in full as well yeah we can see that name is now shown in full uh we've got a club name is that all shown in full obviously this is slightly cut off so let's just increase the size of that yeah and we can move this to the end slightly move this across and then finally okay so i just want to make sure all of the data is fully visible yeah perfect well done to myself right and it fits on a single page wide okay so that's worked so what you don't want to do is let's say for example if i go to design view and let's say i went slightly over like this i want to show you what happens now if we go to print preview you can see some of the data has come on to the next page which we don't we're not data but this part the report has come onto the next page and that's not what we want we need to make sure everything fits to one page so let's just move this back to where it was uh check the print preview yes all of the data fits one page going across okay so that's that done now we need to increase the size of the title so we need to display title and a large font fully visible at the top of the page so we can it's already in a large font but we can make it even larger so go to design view we can go to home and we can increase the size of this font here if you want to increase the size of this you can do okay so let's make a little bit bigger actually so it's obvious that we have changed the size okay so let's have a look yep that's done and what we need to do next is to calculate the number of races and places under the gender column so if you go to layout view remember each record each person is a racer so we just need to count and we can count any field it doesn't really make a difference because we're going to get 59 so click on any field come up into here so make sure you're in layout viewed go to totals and click on count records and he should have the number 59 yep that's what we had from the query so we've done that it's formatted as an integer so yeah but i don't need to change anything here um because it's already shown as an integer if you wanted to show format it uh you can use the property sheet and you can change the formatting up here but it's not necessary so that's done and has a label number of races to the left of this value so when you are entering this label make sure you need you include capital letters when shown make sure you don't make any spelling mistakes as well so go to design view go to design and then click on this icon here to add a label so just draw a box number phrases again you can go to print preview and go to second page we can go to the bottom and this is to the left of this yeah that's fine what you can do is just to move this maybe across slightly and then this as well across slightly and then let's go to print preview so second page yeah so now this is yeah underneath this column a little bit more if you wanted to be a bit more specific make it underneath the gender columns we can move it across a little bit more let's see what that number is yeah so it's bang in the middle of the gender column and you got this label to the left okay has only your name center number and candidate number in the footer of the report so it appears on every page so if you go back to design view you got report header and report footer if you put your name here in a report header it will only appear on the first page if you have your name in a report footer it's only going to appear on the last page we want to include our details including the center number candidate number in a footer on every page and we don't want to show anything else so has only your name so what we need to do first of all is get rid of these things in a page footer so if you want to put something in the page header you can create a text box here but we're going to insert the information here in the page footer so i'll type my name out the center number i'll use five six seven eight and then candidate number one two three four and if i go to print preview again hopefully you should see my name here on the first page my name here on the second page okay so save and print your report oops and when you receive your report from the printer the first things you should be checking for is all of the data shown okay are the fields in the right order um it's a formatting correct and if you're happy then obviously you can go forward so evidence seven placing the evidence document a screenshot of the formula used in a database to calculate the number of races so what we do is we basically take a print screen of this or if you want to use a snipping tool you can do that as well so this is evidence seven increase the size of your screenshot and here's the formula okay done okay question 23 again we're going to be creating a report so using fields from both tables produce a tablet report this question is slightly more complicated but it's pretty much the same thing the only thing different here is we have to create a new calculated field and you can see i've already annotated a question again so you got to cues for query ask for reports okay so again we're going to select the fields and yeah and then we're going to label a query with the same title that's going to appear at the top of the report so again we can go ahead save the changes you click on create query wizard make sure you select your fields from the two tables not the query from the previous um task uh we want to select the fields from the tables so let's see what we need to do we need to select first name let me just click on the highlight a pen first name last name club name again so first name last name and club name again location category where's category here we are uh event rank penalty and then the average miles per hour and then this is going to be the field that we create in this question here so that's it for now we can move forward next next and then like i said before let's use the same title that's going to be at the top of the report so obviously when you're doing this you would have to enter the exact details shown on the paper so capital letters make sure you make no spelling mistakes finish so again we're starting off with 774 records so we've done that part now we have three search criteria here so location includes the text east so let's have a look at location okay so what we're going to do is let me just see yeah we need to include these fields which include east at a start um let me see what else we have yeah and we have east here as well eastern so we need to include any data where or any if we go back to the question paper any text that contains east so what we need to do is wrap east in two stars and what what i will do is basically find anything that contains east so if you go to home and design view location star east star click on run so by doing that we basically find anything that contains east including eastern and let's see what else we have south east north east so we're down to 134 records the category is going to be senior so that's quite straightforward we can just basically go to category in design view underneath the tick typing senior yeah we're just down to 75 records now and the penalty is going to be yes so this is going to be yes so again enter your search criteria one at a time you can see if it's worked or not and we're down to now 20 records so location includes east category is senior and penalty is yes so all this is done now so the only tricky part about this was maybe um anything that contains a particular piece of text you put stars on both sides okay right now we need to create this new field called revised underscore nph when you do create a new field it's normally followed by a colon okay it's going to be calculated at runtime using the data from the average miles per hour field so you you know this is a field because it's italic or you can also check here uh yeah it's a field um so using the data from the average miles per hour field and reducing this by five percent so we need to create a run time or create a calculated field which will basically reduce this by five percent so we can do that by multiplying it by 0.95 so the field goes into square brackets as you can see you need to write it exactly as it appears you can't make any spelling mistakes uh if there's an underscore you need to include the underscore and if you want to take five percent away you need to multiply it by 0.95 so let me see if i can copy this to create this field you go to design view and you come to the end here and you enter a new field here so remember you first of all write the name of the new field which is here or not there yeah then you need to write down any fields which is part of this calculation so remember after the name you need to include colon so you can see the colon here okay then the field that's going to be used and if you are using an existing field you need to place in square brackets and since we want to take five percent away from this we need to multiply by 0.95 okay format this field to display two decimal places okay to display two decimal places just click on the field come up to the property sheet remember you can click up here to show the property sheet and change the format to standard and then select two decimal places click on run yeah happy that so save the changes and then what we can do is yep we've finished all of that okay and i think we are now ready to move on to the reports part of the task okay so before we move on make sure you have the correct fields shown here okay we've got the new fields we've entered the correct search criteria which we checked before once you're happy then let's go to the report wizard to make the report so select all of the fields from this query here click on next next next and we need to sort the data into descending order of average miles per hour so descending on this field here remember this field calls the beginning so we need to move it back to this position in a moment is it going to be landscaped yes it is and let's include the title again and then we can click on finish and what we need to do obviously we zoom out you can see not all of the data is shown in full so we need to make sure all of the data is showing okay and fits to a single page so let me just highlight these tasks as complete first of all and let's start by putting the fields back into the right order so if we go to layout view i'm going to move this field just towards the end here and let's start moving these fields across now making sure they all fit in okay so that's the first name let's move the last name across yep i'm just going down to make sure all of the information is shown in full yeah that's shown in full okay yep happy with that so after category is going to be event rank and we can double check in a second then it's going to be penalty then is the average miles per hour and then it's actual new feel that we created so let's just move this across slightly there we go so we have a little bit of extra space here so we can move some of this information across and you can see we have now fitted the data to one page wide okay so done okay all of the labels are displayed in full there's nothing grouped fits on a single page for the title we need to change the format so this title here if we go to actually just realize that r is slightly cut off uh is everything else fine just let me check the fields first of all um if they're in the right order first name last name club name yep location category advanced rank yep penalty average miles per hour revised yeah that's done so let's now format the title so if i go to design view highlight the title and we need to format this title so it's going to be black 30 points and in a server font so we can go to home we can change the color to black we can change the size to 30 and we can choose a serif font so we can go for times new roman and you may need to increase the size of this box so we can show the full title okay done make sure all the letters are fully visible has your name center number and candidate number on the report so it doesn't say where it can be top or bottom head over footer okay so let's just include the name here at the top so again if you go to design click on the label tool or icon and we need to include sent your name center number candidate number so let's click inside the box for center number i'll go with one two oh five six seven eight and then candidate number one two three four okay go to print preview yep the title has been formatted the name is at the top is only one page you want is all of the data shown in full yes so we can go ahead and print and make sure this is to two decimal places which is is yep we can print this off okay so that's that done and we can save the changes and we can close this down so what we have here is basically two queries that we created and two reports that we created and you can see using the same name for the query in report makes sense now because this is obviously linked to this one and then this query is linked to this report okay we have to export this report into your work area and we have to save it as an or export it as a rtf file so if we right click we can click on export select word rtf file choose your source folder and save your name or save the name of the file as spa press ok okay so if i now go to the evidence documents um or to my source folder we need to take a screenshot of the exported files saved into evidence documents and we need to make sure we show the file type as well okay so and i think that's the last part yeah that's the last part of databases so evidence eight here's the file there's a file type let me take the print screen evidence eight and yeah that's it we're done so we've come to the end of the database section um i think it was relatively straightforward we didn't have to create a form um yeah it was straightforward pretty much the only maybe tricky part was uh the calculated field where we have to reduce it by five percent but you just need to apply your maths knowledge um the search criteria where the text contains east so if there's more than one word and you want to find something that contains east just put two stars one at the front and one at the end to find that particular criteria and yeah this question was quite straightforward okay guys we've come to the end of this video please drop your comments below good luck if you have exams coming up in october november hopefully the next set of videos i will complete this paper including mail merge and powerpoint thank you for your time and again good luck in your exams bye

Channel: ICT YAHMAD

Views: 15,989

Rating: 4.9322033 out of 5

Keywords: ict igcse, ict yahmad, yahmad, ict igcse paper 2 2019, paper 2 2019, query, report, data manipulation 2019, cie ict igcse, microsoft, access, october, november, ict, igcse, mail merge, presentations, INFORMATION AND COMMUNICATION TECHNOLOGY, Paper 2 Document Production, Data Manipulation and Presentations, ict igcse paper 3, paper 3, web authoring, spreadsheets, Data Analysis, web expression

Id: 5LKfesN1UO8

Length: 43min 17sec (2597 seconds)

Published: Sat Sep 26 2020