Home

MICROSOFT ACCESS STEP BY STEP GUIDE

1. Make field inserted into the label after the field name we added 3 Press enter to move the cursor to the line below Page 41 Section 11 Data Manipulation Mark Nicholls ICT Lounge 4 Add the other necessary fields and field names in the same way Model Size Price Wind Condition Your label window should look like this Label Wizard What would you like on your mailing label Construct your label on the right by choosing fields from the left You may also type text that you would like to see on every label right onto the prototype C C k N ext Prototype label Make Make Model Model Size Size Price Price Wind Condition Wind condition Separate Lines Cancel z Back Next gt Finish Sorting Size field into ascending order Task 5 needs you to sort the Size field into Ascending Order Ascending means From low to high This means that the data will be sorted from the lowest kite size to the highest NOTE Descending Order is the opposite From high to low 1 Click on the Size field and use the single arrow to move it into the Sort By window By default Access will sort the field into Ascending Order Which fields would you like to sort by Which fields would you like to sort by Sort by Available fields Sort by Skill level Wind condition Use Click Next Page 42 Section 11 Data Manipulation 2 Choose a sui
2. Tables Queri Tables Queri Query Kites Order Query Query Kites Order Query Available Fields Selected Fields Available Fields Use the arrows to move the fields from the Available Fields window into the Selected Fields window Press Next and the Next again Page 23 Section 11 Data Manipulation Mark Nicholls ICT Lounge Sorting data into ascending order of make 3 Inthe Sort Order section use the drop down box to select the Make Field What sort order do you want for your records Make sure that it is set to Ascending Low to High You can sort records by up to fourg ascending or descending ges Click Next Landscape page orientation How would you like to lay out your report 4 Set the Page Orientation to Landscape Wide and leave Layout as Tabular ell Click Next Adjust the field width so all fields fit on a page What style would you like 5 Choose a Design for your report chose None option as it is easier to re design later Title a Control from Detail Click Next Page 24 Section 11 Data Manipulation Mark Nicholls ICT Lounge 6 Choose a report name which suits the task I chose Kites Order Report Select the Modify the Query Design option This lets us is
3. 1 15 2012 1 38 PM File folder m 5 Creating Labels 1 12 2012 845 AM File folder al Music HE Pictures H Videos m 6 TED 1 12 2012 845 AM File folder Computer Click OK I Save as type Rich Text Format La Hide Folders 5 Page 51 Section 11 Data Manipulation Mark Nicholls ICT Lounge Your exported data in RTF format will appear File Home Insert Page Layout References Mailings Review View Add Ins Design Layout de Cut er l a iz u aF F A Find B Calibri 11 A a Aay 3 EE 2h 4 AaBbccoe AaBb AaBbC AaBb AaBbCcl x A ee ac EB B 2 ab SEE un i i i Change gt F Format Painter ZU abe X x 4 A a ied T Normal 1NoSpaci Headingi Heading 2 Title Subtitle styles N sints Clipboard F Font F Paragraph Ts Styles F Editing Peter Lynn Hornet 114 95 Flexifoil Rage 150 00 Ozone Cult 178 95 Flexifoil Rage 180 00 Ozone Cult 204 95 Er EEE 5 5 u Be a Importing the data into the Kites Word document How to do it This task is very easy All you need to do is copy and paste the extracted data from the RTF file into your Kites Word document NOTE Make sure you paste the table into the correct location Read the instructions in the question carefully croso Table Tools Home Insert Page Layout References i Add Ins Design Layout i Cut m E i a A fi I zn 2 92738 22 2 1 papbcenc aaBbccoe AaBb AaBbG AaBbC AaBbCc
4. What label size would you like 1 Product number Dimensions 11 2 x 21 2 rg 121 the number across is 2 The task asks you to fit two labels side by side so select an option where Unit of Measure English Metric eee Label Type option should be Sheet Click Next 2 The next screen allows you choose font styles and sizes but the task does not specify any formatting so it would be advisable to leave this alone fa A What font and color would you like your text to be Text appearance Font name Font size 32 l 8 e Font weight Text color Light el E E Italic E Underline Click Next Page 40 Section 11 Data Manipulation Mark Nicholls ICT Lounge Showing the correct fields and field names Tasks 3 and 4 require you to do 2 things e Only show the fields Make Model Size Price and Wind Condition each ona separate line e Show the field names as well as the actual data 1 Type the field name Make into the Prototype Label window with a space at the end of the letter e 2 Click on the Make field and use the arrow to move it into the label What would you likefon your mailing label Construct your lab l on the right by choosing fields from the left You may also type text that yoy would like to see on every label right onto the prototype Prototype label Make Make
5. 130 Ozone Instinct Light 651 00 Intermediate Medium Kite Surf 137 Peter Lynn Vortex 561 99 Intermediate High Kite Surf 138 Ozone Instinct Light 5561 00 i High Kite Surf New 1 2 2 2 1 2 2 2 Skill Level field does not Use field contains the Stock Item contain Beginner words Kite Surf Yes Creating the Labels How to do it The problem Produce labels from all the data which 1 Fit two side by side on the page 3 Shows only the fields Make Model Size Price and Wind Condition each on a separate line 4 Displays the field name as well as the data 5 Are sorted into ascending order of Size Labels are a special kind of report They are designed to provide a short summary of information and you are usually required to fit two labels side by side Page 39 Section 11 Data Manipulation Mark Nicholls ICT Lounge First steps in creating labels 1 Click on the Labels Query without opening it Create External Data Database Tools gt PivotChart c a c E Blank Form ae Form Split Multiple Form Form Items More Forms Design Forms Table Table ShargPoint Table Templates Li sr Design Tables All Tables x thiKites FS tbikites Table Kites Order Query Kites Order Report Labels Labels Que 2 Click Create and then Labels Fitting 2 labels side by side This wizard creates standard labels or custom labels
6. 2 Save the query with the name Extract Query and then open it in design view That s all the information the wizard needs to create your query Do you want to open the query or modify the query s design Open the query to view information Stock item 18 Make Model Size Price Skill level Wind condition Use Number tblKites tbiKites tbiKites tblKites tblKites tbIKites tblKites tblKites tbIKites tblKites v W v v v Adding criteria to select small kites and those suitable for beginners 1 The question stated that kites of less than size 3 should be queried so find the criteria section of the Size field and type lt 3 Stock item Make Model Size Price Skill level Wind condition Use Number tblKites tblK tes tblK tes tblK tes tblK tes tblK tes tblK tes tblK tes tblK tes Fj Ei w Fi E E E wi Page 47 Section 11 Data Manipulation Mark Nicholls ICT Lounge 2 Kites which are suitable for beginners should be queried so you should find the criteria section of the Skill Level field and type Beginner Not Beginners Make Model Size Price Skill level Wind condition Use Number Stock item Table tbIKites tblK tes tblK tes tblKi tes tblKi tes tblK tes tblK tes tblK tes tblK tes tblKi tes Fa J 7 Fj J NOTE To make sure you are typing the correct words in the query always check th
7. Dates Times and Numbers following changes Size field needs changing to Long Integer Wind condition Price field needs changing to Currency Use Number Long Integer Yes Duplicates OK Stock item Long Integer No Stock Item needs changing to Boolean Yes No File Format Delimited Field Delimiter 9 To make these changes click on the Data Type ae English cell for each of the fields and use the drop down Western European 005 Mes Times and Numbers Order DMY x Four Digit Years Date D amp imiter E Leading Zeros in Dates list to select the correct options as described in the list above Time Deli jter Decimal Symbol Wind condition Use Number Stock item File Format Delimited Code Page Western European DOS Dates Times and Numbers Date Order DM Four Digit Years Date Delimiter J Leading Zeros in Dates Time Delimiter Field Information Indexed Use Number Long Integer Yes Duplicates OK Stock item Yes No En y Z Page 7 Section 11 Data Manipulation Mark Nicholls ICT Lounge When all of the changes have been made click on o 10 Select twice 11 On the screen where Access is asking you about a Primary Key you should ensure that you select the option Let Access add primary key M
8. Gridlines Gridlimes Page 46 Page Setup fel a Logo ay i 3 alos a Fields Kites Jat tabase Access 2007 Wiicrosot t ACCES abl ES Ob SS k A HOMA ee WE amp Add Existing Property Sheet Ea Controls Tools offer for kite surfers Make Slingshot Model Turbo 2 Size 9 Price 779 Wind Condition Medium Special offer for kite surfers Make Ozone Model Instinct Edge Size 11 Price 977 Wind Condition Medium Name Centre Number Candidate Number ial offer for kite surfers Make Flexifoil Model lon Ill Size 16 Price 1136 95 Wind Condition Low Section 11 Data Manipulation Mark Nicholls ICT Lounge Q42 Produce an extract from all the data which 1 Selects only o Small kites of less than size 3 o Those suitable for beginners 2 Shows only the fields Make Model Size and Price 3 Sorts the kites into ascending order of price The first thing to note here is that the question does not require you to produce a report An extract of information is purely a query Creating the query How to do it NOTE The question clearly requires you to create the extract based on all the data so you should ensure that the query is sourcing tblkites and not your Order query 1 Create a query in the usual way making sure that you use the tblkites table as the source and not one of your queries a p What title do you want for your query Extract Query
9. However before you can make the report you must create something called a query What is a query A query is a way to search through the data stored in the database to produce meaningful results In this question the query needs to search the database in 2 ways 1 Search the information where Number is less than 2 AND Stock Item is Yes 2 Includes a field called Order which is calculated at run time and multiplies the Price field by 3 What is a report A report is a method we use to display our information in the clearest way possible Reports are essentially the output of the database The report in this question only wants us to display information that meets certain criteria and therefore we also need to create a query Page 15 Section 11 Data Manipulation Mark Nicholls ICT Lounge NOTE You MUST be able to determine the difference between a query and a report in this task In short Any tasks which sound like you need to search for information are tackled within the query Any tasks which sound like they are talking about the display of the information should be tackled within the report Creating the query How to do it 1 Click Create and then Query Wizard Ciad os Home Create External Data Database Tools PivotChart E3 es E Labels E Blank Form u _ Blank Report Table Table SharePoint Table it Multiple Form Report Templates Lists Design Items TA More Forms Design Kal Re
10. Produce a report which 8 Calculate the total value of kites to be ordered and o Shows this total at the bottom of the Order column o Formats this total value to currency with no decimal places o Has the label Total order value for the total value To add a total to the report you will insert a text box into the Report Footer section By default the Report Footer section is hidden from view and so you have to create some room for the text box Creating some room in the Report Footer 1 Position your cursor to the bottom of the Report Footer bar so that the cursor changes to an arrow F Page amp Page amp of amp E _ Report Footer 2 Hold the left mouse button down and drag the Report Footer row down a little bit to make some room ie Page Footer Page amp Page amp of Report Footer 3 Inthe Design section click the Text Box option Pia 7 Report Design Tools Ciad A O 7 Design Arrange Page Setup 1 ut Home Create External Data Database Tools r m Group amp Sort HH 5 ab E SC dbs B Zz UI Be amp totals OO S Ara J iS Hide Details it an 3 a E i gt I Views Font Grouping amp Totals Gridlines Controls Page 28 Section 11 Data Manipulation Mark Nicholls ICT Lounge Calculating the total value of the kites to be ordered 1 Draw the text box into the Report Footer underneath
11. Surf Land Board Buggy Land Board Buggy Land Board Kite Surf Kite Surf All stock items are Yes 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 0 1 1 Order _ 2 937 00 5 2 097 00 3 537 00 5 53 416 85 2 937 00 l 92 696 85 2 264 85 5 2 099 97 1 227 00 1 443 00 5428 85 l 52 337 00 es 52 997 00 NOTE Allrecords that do not match our criteria are omitted from the search result Page 22 Section 11 Data Manipulation Mark Nicholls ICT Lounge Creating the report using the Kite Order Query How to do it The Problem Produce a report which 4 Shows all the fields and their labels in full 5 Fits on a single page 6 Has a page orientation of landscape 7 Sorts the data into ascending order of Make with Airush at the top Once we have our completed query we can take this information and display it in the form of a report 1 Click Create and then Report Wizard Kites Database Access 2007 icrosoft Acc E55 Home Create External Data Database Tools FE mi se jar r7 i PivotChart E Labels BN feast a fe ees F Blank Form E Blank Repot re Table Table SharePoint Table it Multiple Form Report Query Query Macro Templates Listsr Design Items i More Forms 7 Design a sort Wizard Design Wizard Design a Tables Forms 5 Other Which fields do you want on your report You can choose from more than one table or query
12. Wind Use Number Stock Item Condition Airush Vapour 16 199 Beginner low Kite Surf Kite Surf Flow 15 699 Beginner High Kite Surf Save the data How to do it To save the new records in the table simply press the Save button which you can find to the right of the Office Button top left of the screen External Data Database Tools ds Calibri 111 7 a A Paste BZIU ATS Views Clipboard Font Page 14 Section 11 Data Manipulation Mark Nicholls ICT Lounge Q38 Produce a report which 1 Contains a new field called Order which is calculated at run time This field will calculate the Price multiplied by 3 Has the Order field set as currency with 2 decimal places Shows only the records where Number is less than 2 and Stock item is Yes Shows all the fields and their labels in full Fits on a single page Has a page orientation of landscape Sorts the data into ascending order of Make with Airush at the top Calculate the total value of kites to be ordered and o Shows this total value at the bottom of the Order column o Formats this total value to currency with no decimal places o Hasthe label Total order value for the total value 9 Include the heading Kites we need to restock at the top of the page 10 Has your Name Centre Number and Candidate Number on the left footer of each page This task is the most difficult of them all It requires you to produce a report
13. You can choose from more than one table or query Tables Queries p Table tbikites Available Fields 4 Select the Detail show every field of every record option then press Next If the task required a summary of data then you would choose Summary 6 Choose a name which suits the task suggest Labels Query Simple Query Wizard What title do you want for your query Labels Query Select the Modify the Query Design option This lets us create our searches That s all the information the wizard needs to create your query Do you want to open the query or modify the query s design Click Finish C Open the query to view information Section 11 Data Manipulation Mark Nicholls ICT Lounge This takes us to the Query Design screen and from here we can tell Access which data we would like to search for ID Make Model Size Price Skill level Wind condition i Query Design Number Stock item 4 uw Field ID m Make Model Size Price Skill level Wind condition Use Number Stock item Table tbikKites tbIKites tbIKites tbIKites tbIKites tbIKites tbIKites tbIKites tbIKites tbIKites Sort Show Criteria or Specifying the query criteria How to do it The problem Produce labels from all the data which 2 Show only the records where Use field contains Kite Surf the Stock item field is
14. each Kite Make appears in the database The completed summary query should look like this Sum Of Price Count OftblKites 10 057 50 12 8 570 00 Flexifoil 13 470 45 Flysurfer 15 658 96 Ozone 26 835 45 Peter Lynn 8 983 35 Slingshot 13 713 00 END OF GUIDE Page 55
15. field and displays Page 19 Order 3 537 00 3 416 85 2 937 00 3 176 85 2 781 00 2 742 00 2 696 85 2 456 85 2 997 00 2 264 85 2 249 85 Section 11 Data Manipulation Mark Nicholls ICT Lounge Setting the Order field to currency and 2 decimal places How to do it The Problem Produce a report which 2 Has the Order field set as currency with 2 decimal places All Tables x tblKites tbikites Table 1 Open the Kites Order Query in Design gt gt View To do this right click the query and BEE Kites Order Quen then select Design View Export 2 Highlight the Order field then right click F Collect and Update Data via E mail and select Properties Totals Table Names 3 In the Properties Sheet change the Format Property sheet Selection type Field Prope to Currency by using the drop down list Description 4 Run the query in Datasheet View and check Format i f Input Mask Long Date to make sure that your Order field is set to m aaa Smart Tags Short Date Text Format Long Time Medium Time Wind conditior Use Number Stock iten short Time 7 Medium Land Board 3 537 00 General Numbe Low Kite Surf 3 416 85 High Land Board 2 937 00 Low Kite Surf 3 176 85 Currency NOTE Currency fields should be set to 2 decimal places by default Page 20 Section 11 Data Manipulation Mark
16. is happening here This tells Access what to name the new field This tells Access which mathematical calculation to use This tells Access which current field to use in the calculation Page 18 Section 11 Data Manipulation NOTE It is essential that you follow the correct syntax For example e Correct order Mark Nicholls ICT Lounge e Using a colon after the new field name e Using square brackets to surround the current field s name e Using the correct mathematical symbol see below Mathematical symbols are as follows Make Flysurfer 2 Flexifoil 3 Flysurfer 4 Flexifoil gt Ozone 6 Peter Lynn 7 Airush 8 Airush 9 Ozone 10 Ozone 11 Ozone 1 Peter Lynn Model Size Speed 2 lon IH Speed lon II Instinct Edge Synergy Flow Flow Instinct Sport Frenzy Fx Manta Il synergy Price 1 179 00 1 138 95 979 00 1 058 95 927 00 5914 00 898 95 818 95 5754 95 5749 95 5724 00 Skill level Intermediate Intermediate Intermediate Intermediate Experienced Intermediate Beginner Beginner Intermediate Experienced Intermediate 4 Run the query by clicking the Datasheet View button Wind conditior Use Medium Low High Low Medium Low Low Medium Low Low Land Board Kite Surf Land Board Kite Surf Kite Surf Land Board Kite Surf Kite Surf Kite Surf Snowkite Number Stock iten Order field multiplies the contents of the Price
17. the Get External Data window like this oa a a Imo ee 4 Get External Data Text File Use the Browse button to find the file NI0EKS CSV Specify the source of the data File name CES Work Key Stage 4 IGCSE Lessons Section 11 Data Manipulation 2010 Past Paper Walkthrough NIOEKS cSV Specify how and where you want to store the data in the current database Import the source data into a new table in the current database If the specified table does not exist Access will create it If the specified table already exists Access might overwrite its contents with the imported data Changes made to the source data will not be reflected in the database ea ec ee I button is selected This ensures If the specified table exists Access will add the records to the table If the table does not exist Access will create it Changes made to the source data will not be reflected in the database NOTE Ensure the top option the data is saved in a new table C Link to the data source by creating a linked table Access will create a table that will maintain a link to the source data You cannot change or delete data that is linked to a text file However you can add new records Click on humaine IMPORTANT NOTE A large number of students perform poorly in this section of the exam because they select the bottom option instead of the top one Page 5 Section 1
18. the Order field _ Report Header ra Footer Report Footer 2 Inthe Unbound text box type SUM Order This adds up all of the values stored in the Order field SUM means ADD TOGETHER Breakdown of what is happening here This tells Access that we want to add something or total NOTE It is essential that you follow the correct syntax For example e Correct order e Using an sign in front of the calculation e Using the SUM function if the task requires you to total something e Using curved AND square brackets to surround the field s name Page 29 Section 11 Data Manipulation Mark Nicholls ICT Lounge NOTE In your exam you will be asked to use one of several possible types of calculation within your report SUM Used if the question requires you to total a field COUNT Used if the question requires you to count the number of records in a field AVG Used to calculate the average value of a field MAX Used to find the highest number within a field MIN Used to find the lowest number within a field Formatting the total value to currency with no decimal places 1 Make sure the report is open in Design View 2 Click the text box which contains the calculation 3 Inthe Design section click Property Sheet Create External Data Database Tools Design Arrange Page Setup t Group amp Sort ha Ej abl EB SC 2 dh SS E Totals a N A
19. wizard make sure that the Comma option is selected using the option buttons the appropriate delimiter and see how your text is affected in the preview below 3 Import Text Wizara Examine the first row of the data and What delimiter separates your fields Sc decide if it contains the fieldnames that Choose the delimiter that separates Tab semicolon Space Other you need or if it contains the first row of data First Row Contains Field Names Text Qualifier none Land Board 7 If the first row contains the fieldnames click on the First Row Contains Field Names tick box As you tick the box the first row changes from this to this What delimiter separates your fields Select the appropriate delimiter and see how your text is affected in the preview below Choose the delimiter that separates your fields Other Text Qualifier none v Snowkite Land Board Buggy Racing Snowkite Section 11 Data Manipulation Mark Nicholls ICT Lounge 8 Click on to open the Import Field Delimiter L xl x Specification window See se an E Language English x Check that all fieldnames and data types match j Sif wester European coos u those specified in task 35 In this case the Size Price aeore om I Four iat Yeas Date Delimiter E Leading Zeros in Dates and Stock Item fields are not correct Make the rmen un
20. 1 Data Manipulation Mark Nicholls ICT Lounge 4 The Import Text Wizard window will Your data seems to be in a Delimited format F it isn t choose the format that more correctly describes your data open Delimited Characters such as comma or tab separate each field _ Faxed Width Fields are aligned in columns with spaces between each field 5 Select the Delimited option This sss scswosuersraesecsevessoisseenou oira uamuanonzno rist pre WAUTIROUSHNIORS C ke Model Size Price Skill level Wind condition Use Number Stock item 2 Elysurfer Speed 2 12 1179 Intermediate Medium Land Board 1 1 e e e lexifoil Ion III 16 5 1138 95 Intermediate Low Kite Surf 1 1 option is for data that is separated by a gegen run lexifoil Ion III 14 5 1058_95 Intermediate Low Kite Surf 0 0 Dzone Instinct Edge 11 927 Experienced Medium Kite Surf 2 1 comma as is the case in csv files peter urn Sera 15 514 ineereaiote tow tan Bard 3s 1 gt ae Flow 15 898 95 edicts Low Kite Surf 0 irush Flow 10 818 95 Beginner Medium Kite Surf 0 0 10pzone Instinct Sport 15 999 Intermediate Low pais Surf 0 0 11bzone Frenzy FX 13 754 95 Experienced Low Snowkite 1 1 CI ick on Next gt B 12pzone Manta II 10 749 95 Intermediate Medium Land Board Snowkite 2 1 Peter Lynn Syneray 8 724 Intermmediate High Land Board 3 1 6 For the next part of the
21. Kites Table J Kites Order Query 3 Labels Query Your label should be switched into Design View Kites Labels 2 Open Kites Order Rep Layout View Export Rename _ Wind Condition amp Wind condition Bereni a _ Page Footer 2 You should see 3 sections to the label Page Header Detail and Page Footer Page Header For information that you only want to display ONCE at the top of each page Page Footer W For information that you only want to display ONCE at the bottom of each page Detail For information that you want to display over and over For example every piece of information should be displayed one after the other until we have nothing left to show Because our heading needs to be displayed at the top of EACH label we need to insert a label into the Detail section Page Header Detail each label we need to create some room a 3 Before we can insert a heading at the top of Click and drag around each of the fields to select them Page Footer _ Page 44 Section 11 Data Manipulation External Data J k Conditional Database Tools k I gt Bk amp Font Group amp Sort Totals 3 Hide Details Grouping amp Totals Mark Nicholls ICT Lounge 4 Drag the fields down a little bit to create some space at the top of the label NOTE You can make the Detail section bigger by dragging the top of the Page Footer bar dow
22. Nicholls ICT Lounge Creating some query search criteria How to do it The Problem Produce a report which 3 Shows only the records where Number is less than 2 and Stock item is Yes 1 Open the Kites Order Query in Design View EH tbiKites Table BEE Kites Order Quer 2 Click in the Criteria section of Number field Stock item thlKites Number Order Price 3 tblkites Wind condition tblKites Skill level tblKites Price tblkites Size tblkites Make tblKites Model tblkites Table thiKites Sort Show a Breakdown of what is happening here This Operator Symbol This tells Access which stands for less than number to be less than NOTE Operator symbols in the green cells can only be used on Number Fields or Currency Fields Other operator symbols include OR Low OR Medium OR High for example LIKE wordscontaining LIKE av starting witha LIKE a ending with a Page 21 Section 11 Data Manipulation Mark Nicholls ICT Lounge 4 Click in the Criteria section of the Stock Item field Make Model Size Price Skill level Wind condition Stock item Table thiKites tblKites thlKites tblkites tblKites tblKites tblkites i tblKites Order Price 3 Criteria asses 5 The question wants us to search for records where Stock Item is Yes To do this simply typ
23. Oe oe j 5 Logo iS Hide Details i a od sk O Grouping amp Totals Gridlines Controls 4 The Property Sheet for the text box will open Selection type Text Box You should make sure that you are in the All section A Format Data Event Other All En Source a E 5 Find the Format setting and use the drop down box to pecimairiace nn Visible E select Currency er Short Date Width er ee Height ed um iim Top General Nur 6 Use the Decimal Places setting to Sunorden 2x SW 5 Format Curren acx oor select 0 Decimal Places Border Style Standard Border Width Percent Visible Text Format Datasheet Caption Section 11 Data Manipulation Mark Nicholls ICT Lounge Adding the label Total order value 1 Close the Property Sheet and click in the label for your calculated field Page Header 3 Runthe report in Report View and scroll to the bottom Your calculated field and label should appear Notice how the Order Total has no decimal places Beginner High Buggy Land Yes 428 85 Board Intermediate Medium Kite Surt Yes 2 337 00 Total order value 33 926 Total Value Label Total Value Calculation 0 decimal places Page 31 Section 11 Data Manipulation Mark Nicholls ICT Lounge Changing the Report Heading to Kites we need to restock How to do it The Problem Produce a report which 9 Includes the heading K
24. REport ceeseceesesssceeeeseees Page 32 34 Task 40 CO GANS ee ee seen Page 35 Creating the Second QUETY cccscsssseceecesssseecescsssseaeecescseeeeceeceseeeaeecesesssaeeeees Page 35 37 Second Query Search Criteria cccscccccesssssseceecsssseceeceseseeecescesseeseeceseseeeeeeeseees Page 37 39 Setting up Labels of the Second QueryY ueeeeesssesesensssnnsnennnenenesnnnnnnnnnnennnenenennn Page 39 43 Adding Header and Footer information to Labels c ce ceecesssteeecesesrseeees Page 43 46 Task 42 details ee RE Page 47 Creating the Thira QUEL series aena ee Page 47 Third Query Search Criteria cccsssseccccesesssecescssseceeceseseaeecesceseeeeecesessaeetes ens Page 47 48 Hiding Fields within a QUETY cc ccccessseseecsssseceseseseecesseecescesaeeceseeseseeseaeseeeees Page 48 Sorting information within a QUETY cccccesssecessssssecessseceecesseecescseaeecesessaeess Page 49 Task 43 and 44 detaills cccccccssssscecesssecescssseecescenaeecessseeeecesseesessseaeeceseaees Page 51 Exporting Data from a QUerVv u een Page 51 53 Importing Exported Data into a Word DOCUMENL ccccssseceecessseceesssseeeesees Page 52 Extra Info Summarising Dita uni Page 54 56 Page 2 Section 11 Data Manipulation Mark Nicholls ICT Lounge 2010 Database Task Walkthrough 035 Using a suitable database package import the file NIOEKS CSV Assign the following data t
25. Section 11 Data Manipulation Mark Nicholls ICT Lounge IGCSE ICT SECTION 11 DATA MANIPULATION MICROSOFT ACCESS STEP BY STEP GUIDE ark Nicholls CT Lounge Page 1 Section 11 Data Manipulation Mark Nicholls ICT Lounge Contents Task 35 ACTAS onair canted egos i tossed ae Page 3 Opening a new Database nee Page 4 Importing csv file into the Database uuususersenesenennennnenennnennnennnnnnnnenenennnenn Page 5 9 Amending Field Properties vaccsisctsercccccavceusccantoaxssdesnwadiecanwstaceasspactepanaapisamcavartetacees Page 10 11 Taking Screenshot Evidente ee Page 12 Task 36 CCUG near re plain ee erneuern Page 13 Inserting and Checking New RECOMS ccssscceecesssccecesessseeceecesseeaeecesesseeeeees Page 13 14 Task 38 CORN Sas nee ee Page 15 Identifying Query Tasks and Report TaskS ueeeeesenesesenenennnsnnnnnenenennnnnennnnnnnnnn ens Page 15 Creatine the First QUEEN en Page 16 17 Creating a Calculated Field within the Query eeeesssesessessssenenenennnnennnnnnne Page 18 19 Changing Format of a Calculated Field 00 0222000000202nsenennnenesnnnnnnnenen Page 20 First Query Search Chiterld nne een Page21 22 Setting up a Report of the First Query uneeeesssesenensnennsnsnnnnnenenennnnnennnnnnnnnnenenn Page23 27 Performing Calculations within REports ccccccccscsssseceecessseeecescssseeceeceseseaees Page 28 31 Adding Header and Footer information to a
26. Yes and the Skill level is not Beginner Records containing the words Kite Surf 1 Open the Labels Query in Design View 2 Click in the Criteria section of the Use field 3 Type Like Kite Surf into the criteria field Field ID Make Model Size Price Skill level Wind condition Use Number Stock item Table tbikites tbIKites tbIKites tbIKites tbIKites tbIKites tbIKites tbIKites tbiKites tbiKites Sort Show J J J J J W J J J Criteria Like Kite Surf or NOTE The Like criteria tells Access that you are performing a wildcard search Any record containing the words Kite Surf will be returned Wildcard searches should be used as the criteria for any search questions that ask you to find records that contain specific words Page 37 Section 11 Data Manipulation Records where Stock Item field is Yes 1 Inthe Labels Query click in the Criteria section of the Stock Item field 2 Type Yes into the Stock Item criteria field Mark Nicholls ICT Lounge Field ID Make Model Size Price Skill level Wind condition Use Number Stock tm Table tbIKites tbIKites tbIKites tbIKites tbIKites tbIKites tbIKites tbIKites tbIKites tbIKit Sort Show J J J J J J J J Criteria Like Kite Surf or Records where Skill Level Item field is Not Beginner 1 Inthe Labels Query click in the Criteria section of the Skill Level field 2 Typ
27. e Not Beginner into the Skill Level criteria field Field ID Make Model Size Price Skill level Wind condition Use Number Stock item Table tbIKites tbiKites tbiKites tbiKites tbiKites tbiKites tbiKites tbiKites tbiKites tbiKites Sort Show J W J J J W J W J Criteria Not Beginner Like Kite Surf or NOTE The Not criteria tell Access that you are looking for every other record apart from the word included within the Not criteria In this example there were 3 types of record held in the Skill Level field e Beginner e Intermediate e Experienced Including Beginner within the Not criteria excludes it from the search and Access will only look for records containing Intermediate and Experienced Page 38 Section 11 Data Manipulation Mark Nicholls ICT Lounge 3 Runthe query by clicking the Datasheet View button Home Create Results Access will now search for the records that match all 3 of the search criteria that we have created Your results should look like this Make Model Size Price l Skill level Wind condition Use Number Stock item Flexifoil lon Ill 1 138 95 Intermediate Low Kite Surf 5 Ozone Instinct Edge 927 00 Experienced Medium Kite Surf 70 Best H P Nemesis 13 1 069 00 Experienced Low Kite Surf 91 Slingshot Rev 5849 00 Intermediate Medium Kite Surf 109 Slingshot Turbo 2 779 00 Intermediate Medium Kite Surf
28. e Yes into the criteria box Stock item tblkites Number tblkites 4 Yes 6 Run the query in Datasheet View and check to make sure that the database has only returned records that match our criteria Number less than 2 and Stock Item of Yes F Ki P iD Make 145 Best 146 Airush 1 Flysurfer 2 Flexifoil 3 Flysurfer 7 Airush 10 Ozone 16 Flysurfer 31 Flexifoil 37 Flexifoil 42 Flexifoil 49 Ozone 50 Ozone 53 Ozone 60 Peter Lynn 62 Peter Lynn 109 Slingshot 144 Airush New Model Size Price Nemesis Flow Speed 2 lon Ill Speed Flow Frenzy Fx Pulse 2 Blade IV Rage Blurr Yakuza Instinct Light Cult Hornet Hornet Turbo 2 Vapour 12 5979 00 5 5699 00 1 1 179 00 16 51 138 95 15 5898 95 13 5754 95 6 5699 99 2 180 00 3 299 00 2 5234 95 3 5481 00 3 5229 95 6 5224 95 3 5142 95 9 5779 00 16 999 00 Skilllevel Beginner Beginner Intermediate Intermediate Intermediate Beginner Experienced Intermediate Experienced Beginner Intermediate Experienced Beginner Beginner Beginner Beginner Intermediate Beginner Wind conditior Medium High Medium Low High Low Low High Low High Medium High High Medium Low High Medium Low All numbers are less than 2 Use Kite Surf Kite Surf Land Board Kite Surf Land Board Kite Surf Snowkite Land Board Freestyle Buggy Land Bugey Land Board Buggy Buggy Racing Kite
29. e data in the table to see how it is spelt or worded etc For example If we typed Beginners into our query it would fail because there are no records named this in the Skill Level field If we checked the table we would see that the records in this field are called Beginner Showing only the Make Model Size and Price fields 1 The question stated that only the Make Model Size and Price fields should be displayed in the query so we need to hide the rest Make 2 Look for the Show section of query Design View You Table tbikites _ tblkites will see check boxes with ticks in them Bade Show Criteria Tick Show the field when the query is run NoTick Do not show the field when the query is run 3 To hide the fields we do not want to show just un tick the boxes in the Show section Make sure you leave ticks in the fields we need to show Make Model Size Price Skill level Wind condition Use Number Stock item Table tblKites tblKites tblK tes tblKites tblK tes tblKites tblKites tblK tes tblKites tblKites vf Beginner Removing ticks from the fields we do not want to show Page 48 Section 11 Data Manipulation Mark Nicholls ICT Lounge NOTE Even though we have hidden the Skill Level field the query criteria will still play its part and affect the results We just won t display the contents of the Skill Level field Sorting the query into ascend
30. ft Office Access 2007 Databases Hide Folders Table Tools Create External Data Database Tools Datasheet wa im er ome of g Delete Format Formatting Is Required View New Add Existing Lookup Relationships Object Field Fields Column 2 Rename 9 9 98 388 Dependencies Views Fields amp Columns Data Type amp Formatting Relationships E Tablet Table Page 4 Section 11 Data Manipulation Mark Nicholls ICT Lounge Importing the N10EKS How to do it 1 Copy the 2010 Past Paper Walkthrough folder into your Data Manipulation folder 2 Select the External Data tab then click on the Import Text File icon eate External Data Database Tools Datasheet Eaa Fa Access gt J Text File ne fx gt Excel i gt ee an a 2 Discard Changes E F gt f am bd pe fe ae oe ix Excel XML File it SharePoint List _ gt 34 Cache List Data ed Create Manage Work Synchronize _ Move to Saved Re Sav nee Imports IM SharePoint List we Exports PDForXPS E mail Replies Online a nn SharePoint Import Export Collect Data SharePoint Lists All Tables Import text file Tablel Import data from or link to data in Fa Table1 Tabl a delimited or fixed width text file able1 Table Press F1 for more help IMPORTANT NOTE Files saved in csv format are considered text files Each data item is separated from the next by a comma 3 This icon opens up
31. icrosoft Access recommends that you define a primary key for your new table A primary key is used to uniquely identify each record in your table It allows you to retrieve data more quickly This adds a new field called ID to the table NOTE Primary Keys ensure that each record can be uniquely identified 12 Click on ie That s all the information the wizard needs to import your data 13 In the Import to Table box enter tblKites Import to Table itblKites NOTE This is a meaningful table name The tbl shows you that it is a table and the Kites gives an idea of what kind of data is being held 14 Click on to import the data and then to close the wizard Page 8 Section 11 Data Manipulation Mark Nicholls ICT Lounge 15 Double click on tblKites to display the imported information which should look like this External Data Database Tools Ei Ei New Totals ome Y Vf Selection a 4 ER Save 7 Spelling iis YJ Advanced 7 efres ilter Allr X Delete HA More 9 Vf Toggle Filter Sort amp Filter Records Use Land Board Wind condition Intermediate Medium Make 1 Flysurfer Speed 2 1 179 00 2 Flexifoil lon Il 1 138 95 Inter Mdiate Low Kite Surt Came 3 Flysurfer Speed 8 5979 00 Interme Land Board tblKites containing 4 Flexifoil lon Ill 14 1 058 95 Inter
32. ields 2 a f CE ila New gt Totals amp F Selection FA 2 Replace 3 I a T A save F Spelling F YH Advanced Go To Vi Past A 3 ab Refresh S Filt Find jew aste A s at res ilter ind 5 F B zw jA EZ 5 Eo pon Ally X Delete More 7 W Toggle Filter Le Select Views Clipboard Font Rich Text Records Sort amp Filter Find Some labels fields are not large enough to hold the data Make ID Model Price Skill level Wind condition Use Airust 146 Flow 699 00 Beginner High Kite Surf Airust 7 Flow 898 95 Beginner Low Kite Surf Airust 144 Vapour 999 00 Beginner Low Kite Surf Best 145 Nemesis 979 00 Beginner Medium Kite Surf Click the drop down arrow on the View option and select Design View This lets us make the required changes Click on the Make field and drag the handles out to create more space for the information hold all of the information It looks okay ID Model Airush 146 Flow 7 Flow Continue doing this until all of the fields are just the right size to hold the information You may need to reduce the size of some of them if they are too large NOTE Fields can also be resized to make them higher instead of wider This is useful to force longer pieces of data onto a new line and therefore take up less room widthways Page Header Freesty le Buggy f Eai Seal ind condition Land Board Price Skill
33. ing order of price 1 The question stated that the kites should be sorted into ascending order of price To do this you need to find the Sort section of the Price field 2 Click in the Sort section Field 1D Make Modell Si a Skill level Wind condition Table tbIKites tblKites tblKites tblKites cites tblkites tblKites Show E Ascendinc E E dro p d own box to select Criteria lt 3 ending Beginner or of Price and use the the Ascending option Your completed query should look like this All fields hidden apart from Price is sorted in Ascending order Make Model Size and Price Id 1D Make Model _ Size Price tbiKites tblKites tblKites tbIKi al Wind condition Use tblKites a Number tbIKites a Stock tem tblKites a ae thlKites F Skill Level is Beginner Make Model Size and Price fields Model Showing data from sizes Hornet i 2 114 95 less than 3 and prices __ Flexifoil Rage 1 5150 00 sorted from low to high B Ozone Cult 2 5178 95 Flexifoil Rage 2 180 00 E Ozone Cult 5204 95 Page 49 Section 11 Data Manipulation Mark Nicholls ICT Lounge Q43 Save this data in a form which can be imported into the document that you saved in step 34 Import this data into your document as a table after the paragraph which ends Here are some small kites sui
34. ites we need to restock at the top of the page 1 Make sure the report is open in Design View 2 Move the cursor into the Report Header section _ Report Header De aa Skill level Wind condition Zn Stock item Order Page Footer to restock Page 32 Section 11 Data Manipulation 5 Check in Report View that the new heading is e Atthetop ofthe page e The EXACT words given in the question e Spelt correctly It should look like this Kites we need to restock Make ID Mode Size Price Airush 5699 00 Airush I 5898 95 Airush Vapour 5999 00 Skill level Beginner Beginner Beginner Wind condition High Low Low Mark Nicholls ICT Lounge Use Number Stock item Kite Surf Kite Surf Kite Surf Order 2 097 00 2 696 85 2 997 00 Adding Name Centre Number and Candidate Number How to do it The Problem Produce a report which 10 Has your name Centre Number and Candidate number on the left footer of each page 1 Make sure the report is open in Design View 2 Move the cursor into the Page Footer section m Report Footer Page 33 Section 11 Data Manipulation 3 Delete the text box containing the formula Now 4 In the Design section click the Label tool External Data Database Tools i i t Group amp Sort HH Totals m ne Gridlines ES Hide Details Grouping amp T
35. l level Mark Nicholls ICT Lounge 2 The task instructed you to set the Size field to 1 decimal place You can check this by clicking the left mouse button in the Size field and the number of Decimal Places in the General tab at the bottom of the window As you can see this is not set to 1 decimal place but set to Auto Field Name ID AutoNumber Make Text Model Text Size Number Currency Text Wind condition General Lookup Format Decimal Places Input Mask Caption Default Value Validation Rule Text Align Section 11 Data Manipulation Mark Nicholls ICT Lounge 4 To change the Boolean field so that it displays Yes or No click in the Stock AutoNumber Text Item field and in the General tab select Text the Format cell Size Number Price Currency Skill level Text ff Wind condition Text Use Text Number Number General Format Caption Default Value True False True Yes No 5 Use the drop down list to select the Yes No option N The displa defined form Page 11 Section 11 Data Manipulation Mark Nicholls ICT Lounge Taking a screenshot how to do it 1 5 Open your Kites Table in Design View the keyboard u Open up an empty Microsoft Word document and fm A then click Paste Add your Name Centre Numbe
36. level Wind condition Page Footer Page 26 Section 11 Data Manipulation Mark Nicholls ICT Lounge r Fields all large enough to hold the information Deren Make ID Model Size Price Skill level Wind condition Use Number Stock item Airush 146 Flow 5 699 00 Beginner High Kite Surf 1 Yes Airush 7 Flow 15 898 95 Beginner Low Kite Surf 0 Yes Airush 144 Vapour 16 999 00 Beginner Low Kite Surf 1 Yes Aligning Fields 1 ID Model Price and Skill Level fields could benefit from being further apart This will make them easier to read 2 Move the cursor into the Page Header section and click on the ID label Page Footer Create External Data Database Tools J e F7 Conditional Group E amp Sort Font Group 3 Centre the label using the Centre Alignment tool in the Design tab 4 Repeat this for the ID field in the Detail section 5 Use the same techniques to re align the other problem areas in the report Kites Order Report Muchineaterlayout Make Model Size Price Skilllevel Wind condition Use Number Stock item Order Airush Flow 5 5699 00 Beginner High Kite Surf 1 Yes 52 097 00 Airush Flow 15 5398 95 Beginner Low Kite Surf 0 Yes 2 696 85 Airush Vapour 16 5999 00 Beginner Low Kite Surf 1 Yes 2 997 00 Page 27 Section 11 Data Manipulation Mark Nicholls ICT Lounge Calculating total value of kites on the report How to do it The Problem
37. licates Query Wizard Find Unmatched Query Wizard This wizard creates a select query from the fields you pick Page 53 Section 11 Data Manipulation Mark Nicholls ICT Lounge Simple Query Wizard 8 Make sure that you are sourcing the data Which fields do you want in your query from the kites table i E nee ey Tables Queries Table tblKites 9 The question required the summary to be avaiable Fields performed on only the Make and Price fields so move these into the Selected Fields window Click Next 10 Select Summary from the two options and then click Summary Options Simple Query Wizard Would you like a detail or summary query Detail shows every field of every record NOTE For ALL other normal queries you would be sure to select the Detail option Summary option is only used where question needs you to summarise data gn es Ten E Summary Options What summary values would you like calculated 6 To count each kite make click the Count records in tblkites tick box 7 To calculate the sum of the Price field tick the Sum tick box 8 Click Ok then Next 9 Give the Summary Query a name Summary will do just fine 10 Click Finish Page 54 Section 11 Data Manipulation Mark Nicholls ICT Lounge The Query will then summarise all the different Kite Makes with totals Sums for Price The Query will also Count how many times
38. me Imported Kite Surf the imported csv data 5 Ozone Instinct Edge 927 00 records Kite Surf Page 9 Section 11 Data Manipulation Amending Field Properties how to do it Kite NILES za Database Access 2007 Table Tools Database Tools Design AM 87 l View Primary Builder Test Validation Property Indexes a Lookup Column Sheet Create External Data Fa Insert Rows 3 Delete Rows Show Hide Field Name ES tblKites Table Text Currency Skill level Text Wind condition General Lookup Field Size Format Decimal Places Input Mask Caption Default Value Validation Rule Validation Text Required No Indexed No Smart Tags Text Align General AutoNumber Microsoft Description Field Properties The data type determines the kind of values that users can store in the field Press F1 for help on data types Design view F6 Switch panes F1 Help Field Name AutoNumber Skill level Wind condition Field Size Format Decimal Places Input Mask Caption Default Value Validation Rule Validation Text Required Indexed Smart Tags Text Align 3 Click on the viewing cell containing Auto and use the drop down list to set this to 1 decimal place Use the same method the Price field currency data type decimal places Page 10 to set which is to 2 _ Price Skil
39. nwards Page Setup Na ol BN 2 dS TER ER 2 at El 5 Lal O Contrals H Gridlines Logo Gridlines NOTE Don t confuse the Label Az Text Box Draw your label in the Detail section just above the fields Type your heading into the label Special offer for kite surfers Repeat this underneath the label with your Name Centre Candidate number and number tool with the Text Box tool Label Tool a Ba Ba BE BE ee ee BE er BE BE DE BE EB BEE Br ee E Page Header Detail UHEREIEIEIEIEREIEIEIETETERFIETENEN Ree ee ee Special offer for kite surfers Pe Make amp Make Model amp Model Size amp Size Page Footer Page 45 Section 11 Data Manipulation Mark Nicholls ICT Lounge 8 Expand the View menu and select Print Preview External Data Be t Group amp Sort EH Totals JB Hide Details Grouping amp Totals Conditional ial offer for kite surfers Make Ozone Model Instinct Light Size 9 Price 651 Wind Condition Medium Name Centre Number Candidate Number acial offer for kite surfers Make Slingshot Model Rev Size 11 Price 549 Wind Condition Medium Name Centre Number Candidate Number ial offer for kite surfers Make Best Model H P Nemesis 13 Size 14 Price 1069 Wind Condition Low Name Centre Number Candidate Number Save and Print the Labels Database Tools Design Arrange
40. omerate customise our report That s all the information the wizard needs to create your report Do you want to preview the report or modify the report s design Preview the report Click Finish Modify the report s design Showing all fields and labels and making sure the report fits onto a single page 1 You should be in Report Design View It looks like this Os CEM B i Report Design Tools Kites Database A 2007 Microsof 1 es X te Home Create External Data Database Tools Design Arrange Page Setup 7 A i Group amp Sort HH ba EEFJE ee L I PA f in MM ips u an Z 1 eee hae eee ar ee aaa aN Se Og En y E gt 5 Fede Deak Gridlines r Logo E a E re ia 0 r E ee a A Views Font Grouping amp Totals Gridlines Controls Tools a ree TOTEE AE Some labels fields are not rare BMA Sere KEN N es 1 large enough to hold the data BEE ion Pane Feds a 3 e rrr Clearly this report needs some work for the following reasons Report Footer e Itis messy e The labels and fields are not large enough to display the information e The report does not fit onto a page Home Create Exte ke T E E 3 y If I ran this report in Report View you will see what mean Page 25 Section 11 Data Manipulation Mark Nicholls ICT Lounge Report View Resizing F
41. otals Gridlines Mark Nicholls ICT Lounge Kites Dar tabase Acces 2007 Page Setup ba a EN 4 ode SS zia E O A a Controls 6 Type your name Centre Number and Candidate number into the label Page Footer Name Centre Number Candidate Number 7 Use Report View and scroll to the bottom of the page to check that the required information is fully visible It should look something like this Ozone Frenzy FX 13 Feter Lynn Hornet 6 Peter Lynn Hornet 3 Slingshot Turbo 2 Save and Print this report 754 95 224 95 779 00 Experienced Low Beginner 142 95 Begings High Intermediate Medium Page 34 Snowkite 2 264 85 Buggy Land 5674 85 Board Buggy Land Yes Board Kite Surf Yes 2 337 00 Total order value 533 926 Fage 1of1 Section 11 Data Manipulation Mark Nicholls ICT Lounge Q40 Produce labels from all the data which 1 Fit two side by side on the page 2 Show only the records where Use field contains Kite Surf the Stock item field is Yes and the Skill level is not Beginner Shows only the fields Make Model Size Price and Wind Condition each on a separate line Displays the field name as well as the data Are sorted into ascending order of Size Include the heading Special offer for kite surfers at the top of each label Has your Name Centre Number and Candidate Number at the bottom of each label This task again re
42. port Wizard Design Tables Forms Reports 2 Select Simple Query Wizard then click OK Crosstab Query Wizard Find Duplicates Query Wizard Find Unmatched Query Wizard 3 On the next screen you should make sure that tblKites option is selected Use the This wizard creates a select query from the fields you pick arrows to move the fields from the Available Fields window into the Selected Fields window Which fields do you want in your query Which fields do you want in your query You can choose from more than one table or query You can choose from more than one table or query Tables Queries Simple Query Wizard Tables Queries Table tblKites x Table tblKites Selected Selected Fields Available Fields Fields Arrows gt gt lt Price Skill level Wind condition Use Available Fields Section 11 Data Manipulation Mark Nicholls ICT Lounge 4 Select the Detail show every field of simple Query wizara N every record option then press Next If the task required a summary of data then you would choose Summary Simple Query Wizard Select the Modify the Query Design option This lets us Kites Order Query What title do you want for your query create our searches That s all the information the wizard needs to create your Click Finish ages SeS Do you want to open the query or modify the query s design Open the que
43. quires you to produce a query that searches for the required records described in task 2 of Q39 The rest of Q39 can be tackled in the process of making the labels Creating the query How to do it NOTE The question clearly requires you to make the labels based on all the data so you should ensure that the query is sourcing tblkites and not your Order query 1 Click Create and then Query Wizard CO iN Sy ae om J v Kites Database Access 2007 Microsoft Access Home Create External Data Database Tools ZEE FE TH SEs Paf i4 PivotChart 4 F Labels ao reat per a Blank Fom f Blank Repot 8 om Table Table SharePoint Table Form Split Multiple Form Report Report Query Query Macro Templates Lists Design Form Items E More Forms Design zl Report Wizard Design Wizard Design 5z Tables Forms Reports Other All Tables i Query Wizard 2 Select Simple Query Wizard then click OK C Find Duplicates Query Wizard Find Unmatched Query Wizard This wizard creates a select query from the fields you pick Page 35 Section 11 Data Manipulation Mark Nicholls ICT Lounge 3 On the next screen you should make sure wer that tblKites option is selected Use the arrows to move the fields from the E Available Fields window into the Selected Fields Fields window Simple Query Wizard Which fields do you want in your query
44. r and Candidate Number to the Footer Your finished screenshot should look something like this DLO Database tas e We een Mecrosed TR Table Posts Kites Database Access 2007 gt Mecrosoft Acces AutoNumber Text Text Number Currency Test Text Text Number Yes No tong Integer Increment Tei No Dupin sten A field name can be up to 44 characters long General inchyding poirt Preis Mi for help on feta ne nme EA Mark Nicholls Cen Num 001101 Can Num 1100 Print a copy of your screenshot Page 12 Section 11 Data Manipulation Mark Nicholls ICT Lounge Q36 Insert the following three records Make Size Price SkillLevel Wind Use Number Stock Item Condition Kite Surf 9 Kite Surf jAirush Flow 5 699 Beginner High Kite Surf Check your data entry for errors Q37 Save the data Inserting new records How to do it 1 Double click on tblKites to view the records z TECE z able Tools Cites Database Access 2007 Microsoft A Home Create External Data Database Tools Datasheet n a CE A Y ion i ne Rat gl A A z mie 3 TT as EA Save 7 Spelling l YJ Advanced i ste 3 i S ab i en Be F Biu ES 2 i ir x Delete More gt 2 oe Y Toggle Filter o Views Clipboard Rich Text Records Ii Sort amp Filter All Tables tblKites Model Size Price Skilllevel Wind condition 1 Flysu
45. rfer Speed 12 1 179 00 Intermediate i 2 Flexifoil lon II 6 1 138 95 Interggediat 3 Flysurfer Speed 8 5979 00 Intermediat 4 Flexifoil lon Il 1 058 95 Intermediat Records 5 Ozone Instinct Edge 927 00 Experienced ite Surf Database 2 Scroll to the bottom of the table and look for the row which is marked with an asterix The asterix indicates that this row is where new records are input 141 Ozone Instinct Light 7 611 00 Reniicies Medium Kite Surt 142 Peter Lynn Hornet 8 224 95 Intermediate Low Buggy Land Board 143 Peter Lynn Reactor 4 5219 95 Intermediate Medium Buggy New records inserted here 1D Make r Model Size Price Skilllevel Wind condition Number Stock item z 143 Peter Lynn Reactor 4 5219 35 Intermediate Medium No E 144 Airush Vapour i 5999 00 Beginner Low Kite Surf i 145 Best Nemesis 2 979 00 Beginner Medium Kite Surf u 146 Airush Flow Beginner High Kite Surf Page 13 Section 11 Data Manipulation Mark Nicholls ICT Lounge Checking data entry How to do it All this requires you to do is to read through the new records that you have entered and double check that they match those stated in task 36 It is vital that your data entry is EXACTLY the same as the information stated in the question or you will run into problems when you come to search the database later in the exam Remember task 36 required you to add the following records Make Size Price SkillLevel
46. ry to view information This takes us to the Query Design screen and from here we can tell Access which data we would like to search for tbIKites ID Make Model Size Price Skill level Wind condition Use Number Stock item Price Wind condition Number Stock item tbIKites i tbIKites tbIKites tbIKites Page 17 Section 11 Data Manipulation Mark Nicholls ICT Lounge Creating the Calculated Field Order How to do it The problem Produce a report which 1 Contains a new field called Order which is calculated at run time This field will calculate the Price multiplied by 3 Calculated fields only work during run time This means that the calculation is made as the query is activated or ran NOTE It is important to understand that Calculated fields are ALWAYS created within queries nowhere else 1 In query design view find the Stock Item field 2 Click the mouse cursor into the empty field to the right of Stock Item 4 ww Field Model Size Price Skill level Wind condition Use Number Stock item Table tbikites tblKites tblkites tbIKites tblKites thiKites tblKites tblkites Sort Show Fi Fi Fd 7 E Fi Fl E Criteria or 3 Inthe empty field type Order Price 3 Stock item Order Price 3 This creates a new field called Order tbIkites The Order field will store the result of the El Price fieldmultiplied by 3 Breakdown of what
47. t pete F Format Painter B Z Ur M y fn z v we T Normal 1NoSpaci Headingi Heading 2 Title Subtitle Clipboard F F Styles Paste Options E 5 8 A Insert Ed Delete Table fi Merge Cells Page 52 Section 11 Data Manipulation Mark Nicholls ICT Lounge Extra Info Summarising Data Although this exam paper question did not ask you to summarise data it is something that does appear in exams from time to time To practice this have added an extra question to the 2010 exam paper Q45 Produce a new report from all the data which Shows a summary of only the Make and Price fields Performs a count of each kite make Calculates the sum of the Price field for each kite make Sorts the Make field into ascending order Summarising the data How to do it NOTE Summaries are a form of query Like all queries you should ensure that you source the table data and not one of your other queries 6 Click Create and then Query Wizard Home Create External Data Database Tools m mam is PivotChart c E labels Be i C Blank Form _ f Blank Report Table Table SharePoint Table Form Split Multiple Form Report Report r Kal Report Wizard Design Templates Lists Design Form Items E More Forms Design Tables Forms Ii Reports All Tables Query Wizard 7 Select Simple Query Wizard then click OK Crosstab Query Wizard Find Dup
48. table for a beginner some of which will still be suitable as you progress to intermediate skills Make sure that all required fields and their labels are fully visible and that there is one blank line above and below the table Question 43 is asking you to save the data so that it can be used within a Word document Saving data for use in another document is known as Exporting data Exporting the data How to do it To export the extract of data created in Q42 you must Right Click the query All Tables 3 Extract Query tbiKites A sasaa A BR E From the menu choose GH tbikites Table Peter Lynn Hornet Fad Extrat Query Flexifoil Rage Export i Open i BEE Kites Order Query sal li Defign View Rage BEE Labels Query Ex i Excel Kites Order Repor EEE Xp Exce H Collect and Update Data via E mai Tit SharePoint List labels Labels Que _ As the data is going to end 4 ename a Hide in this Group i gt PDF or XPS up in a Word document the Delete AP Access n best format to export the Cut o Go XML File data to is a Word RTF file me ODBC Database HTML Document dBASE File NOTE dEr kl Word Merge ue Object Properties i Check Web Compatibility You can export to other formats excel HTML etc by selecting one of the other options in the list Page 50 Section 11 Data Manipulation Mark Nicholls ICT Lounge Click the check box which says Open the des
49. table name for the label I suggest Kites Labels 3 Select the option labels as printed and click Finish they will Label Wizard See the look Your labels should look something like this Make Ozone Model Instinct Light olze 5 Price 561 Wind Condition High Make Ozone Model Instinct Light Size 9 Price 651 Wind Condition Medum Make Slingshot Model Rev Size 11 Price 849 Wind Condition Medum Field names as well asthe data Each field on a separate line All records sorted into ascending order of size Mark Nicholls ICT Lounge What name would you like for your report Kites Labels That s all the information the wizard needs to create your labels What do you want to do See the labels as they will look printed Make Peter Lynn Model Vortex Size Price 561 99 Wind Condition High Make Slingshot Model Turbo 2 Size 9 Price 779 Wind Condition Medium Make Ozone Model Instinct Edge Size 11 Price 927 Wind Condition Medium Ammending the label s heading and footer How to do it The problem Produce labels from all the data which 6 Include the heading Special offer for kite surfers at the top of each label 7 Have your name Centre number and Candidate number at the bottom Page 43 Section 11 Data Manipulation Mark Nicholls ICT Lounge tblKites 1 Right click your Kites Labels Report and select PETE Design View tbI
50. tination file after the export operation is complete This lets you view the file when export is complete Specify the destination file name and format File name ZAM Nicholls ICT Organised Stuff CES Work Key Stage 4 IGCSE Lessons Section 11 Data Manipule Specify export options J Export data with formatting and layout Select this option to preserve most formatting and layout information when exporting a table query form or report Open the destination file after the export operation is complete Select this option to view the results of the export operation This option is available only when you export formatted data _ Export only the selected records Select this option to export only the selected records This option is only available when you export formatted data and have rederds selected Click Browse to select a location to save the exported data and make sure to give it a descriptive name Search R Organize Mew folder Ensure that the file type is set E Recent Places Mame Date modified to Rich Text Format t h en we m 1 Opening a database and Importing Re 1 15 2012 1 38 PM File folder he IB 2 Ammending Field Properties 1 15 2012 1 38 PM File folder click Save Documents ER m 3 Queries and Calculated Fields 1 15 2012 1 39 PM File folder Mm 4 Creating and Ammending Database Re
51. ypes to the fields Make Text Model Text Size Numeric 1 decimal place Price Currency 2 decimal places Skill Level Text Wind Condition Text Use Text Number Numeric Integer Stock Item Boolean Logical Make sure that you use these field names You may add another field as a primary key field if your software requires this Save a screen shot showing the field names and data types used Print a copy of this screen shot Make sure that your name Centre number and candidate number are included on this printout The solution to task 35 will be detailed over the pages 2 10 Page 3 Section 11 Data Manipulation Mark Nicholls ICT Lounge Opening a Database How to do it 1 Open Microsoft Access by clicking e Start Button e All Programs e Microsoft Office e Microsoft Access 2 Click the Office Button followed by New to open the Blank Database pane on the right hand side in the window Blank Database Create a Microsoft Office Access database that does not i H tai isting data or objects 3 Enter a meaningful File Name for the database N ER ae File Name For example Kites would make sense as this is Kites C Users Mark Nicholls Documents the type of information that the database will hold 4 Click on the Browse button yellow folder and choose where you would like to save your database Data Manipulation folder iv Unmarenm Press e Save as type Microso

MICROSOFT ACCESS STEP BY STEP GUIDE

Contents

Download Pdf Manuals

Related Search

Related Contents