Transcription
ICT IGCSEDatabases (Access)Open and Save (Create) Database3Chapter 18: Data Manipulation (Access)12Lawsons41) Open Microsoft Access2) Write the name of the database file3) Select the destination folder and choosethe location for the file.4) Select “OK” and then You can also right click on your mouseto create a new database.New Microsoft Access Database
ICT IGCSEDatabases (Access)Chapter 18: Data Manipulation (Access)In a typical database exam question you will be asked to import a CSV file into a suitabledatabase package (Microsoft Access).Tip:Double check the data types.They have to be correct or youmay get import errors.Primary Key: This will be the unique field for each record. The VIN will bethe primary key as each car will have a unique VIN.https://mrlawsonsclassroom.weebly.com/
ICT IGCSEDatabases (Access)Importing CSVChapter 18: Data Manipulation (Access)Tip: Always check the currency and the formatof the date. - Pounds01-Jan-2016Tip: Always check data types when importing CSV File. If you do not then you run the risk ofgetting import errors.Tip: If it asks for decimal placesthen during the import leave thedata type as double.https://mrlawsonsclassroom.weebly.com/Tip: You can then format the number in design view. If anumber is not showing to a certain decimal value thenyou can set the format to standard.
ICT IGCSEDatabases (Access)Importing CSVChapter 18: Data Manipulation (Access)12431) Click on External Data Tab and select Text File.2) Browse and Select the CSV File.3) Click OK4) Click on Advanced and check the format of the date so it is set to DMY. Then click Ok and Next.https://mrlawsonsclassroom.weebly.com/
ICT IGCSEDatabases (Access)Importing CSVChapter 18: Data Manipulation (Access)5) Click on First Row Contains FieldNames and then click on advanced.6) Double check all the data types.You would normally always have tochange the Boolean field.57) Select the Primary Key or letAccess choose. If you have a uniquedata for each record in a field thenselect this as your primary key.6https://mrlawsonsclassroom.weebly.com/8)Click on Next then Finnish toimport the CSV file.78
ICT IGCSEDatabases (Access)1Chapter 18: Data Manipulation (Access)In this exam question you have to first create the table(Including Fields & Data Types) and then import the CSV file.234When importingthe CSV file youhave to append acopy of the recordsto the table youcreated.https://mrlawsonsclassroom.weebly.com/
ICT IGCSEDatabases (Access)Print Screen EvidenceChapter 18: Data Manipulation (Access)Tip: When you print screen your data types ensure you also show the formats.Don’t crop the bottom part off.https://mrlawsonsclassroom.weebly.com/
ICT IGCSEDatabases (Access)Relationship DatabaseChapter 18: Data Manipulation (Access)A 'relational' database is one thatcontains two or more tables of data,connected by key fields.TeacherStudents(Primary Key)(Foreign Key)The 'One' side is usually the primary keyThe 'Many' side is usually the foreign keyA relational database has more than one table and the tables are linked using key fields.Advantages: Teacher details only need to be entered once into the database.Mistakes are less likely to happen when entering data if it already exists.Avoids duplicating data.Data can be accessed using key fields (Primary and Foreign Keys).Queries and reports can be created using data (fields) from a number of tables which have m/
ICT IGCSEDatabases (Access)Relationship DatabaseChapter 18: Data Manipulation k the Enforce Referential Integrity.Ensures no orphan records.Tip: The samedata shouldappear in bothfields
ICT IGCSEDatabases (Access)Relationship DatabaseChapter 18: Data Manipulation e aremore books
ICT IGCSEDatabases (Access)Chapter 18: Data Manipulation (Access)123You will be asked to either add, edit or deletedata. In this example you are asked to update3 records.The best way to update an existing record is touse the filter tool and search by the PrimaryKey (VIN).1) Select the VIN Field Text Filters Equals.2) Type in the Primary Key (377C15423003)and press Ok.3) Update the record according to the detailson the exam paper.Discount Applied: 10Date Sold: 06/05/2012Sales Person: AlgierSold: Yes4https://mrlawsonsclassroom.weebly.com/4) Clear the text filter and repeat the sameprocess with the next two records.
ICT IGCSEDatabases (Access)Chapter 18: Data Manipulation (Access)Tip: When you are creating a report ensure you have identified all query and reportstasks.Search Criteria: When creating the query onlyselect the fields stated.However you may have to insert additionalfields later to complete your search. Thesefields must not be shown.Report Title: Name the query as the reporttitle.QueryReport Selecting FieldsCalculated run-time field formattingSearch criteriaSort (1)https://mrlawsonsclassroom.weebly.com/Sort (2)Report header/footerCalculation (Count, Sum, Max & Min)Labels for formulasPage orientationShow labels in full
ICT IGCSEDatabases (Access)Annotating Exam PaperChapter 18: Data Manipulation (Access)https://mrlawsonsclassroom.weebly.com/
ICT IGCSEDatabases (Access)QueryChapter 18: Data Manipulation (Access)https://mrlawsonsclassroom.weebly.com/Tip: Extend thetables just in caseyou may need toselect and useanother field.Tip: You can searchby a field and thenhide it. Beforehiding it check tosee if the searchcriteria hasworked.Tip: Always check the number of records afteryou have typed in one search criteria.The number of records should reduce every timeyou type in a search criteria.
ICT IGCSEDatabases (Access)Calculated Run Time Formulas in the QueryChapter 18: Data Manipulation (Access)Enter New CalculatedRun Time Field hereCalculated Run time is used when we have to calculate specific valuesusing data from existing fields.New Stock Level:[Current Stock] 5Name of ired Field inSquare BracketsNumber ofBooks Addedto StockIn Design View usingthe Property Sheetthe formatting can beset for the new field.
ICT IGCSEDatabases (Access)Chapter 18: Data Manipulation (Access)Wild Card – Sometimes a field may contain more thanone word. To find something specific you need to write awildcard search.Like “*Channing Tatum *”BetweenBetween 01/01/2010 And 02/02/2012Between 100 And 150OrNorth Sea Or Irish SeaNotNot “Horror” 90Less than 90 90More than 90 90More than and equal to 9037*Fields starts with 37*37Fields Ends with 37https://mrlawsonsclassroom.weebly.com/Only field containingChanning Tatum areshown
ICT IGCSEDatabases (Access)ReportChapter 18: Data Manipulation (Access)1) Make sure Report title is the same as the Report Header2) Format the report in Layout View making sure al field names and data is visible.Page Footer/Header:Anything informationwill appear each printedpage either in the(header) or (footer) ofthe t Footer/Header:Anything informationwill appear on only thefirst (header) or lastpage (footer) of thereport.
ICT IGCSEDatabases (Access)Formulas in the ReportChapter 18: Data Manipulation (Access)We can use a number of formula in the report to calculatevalues from fields including: Sum, Average, Count, Max, Minetc. You need to ensure you are in layout view.Function FieldsIn Design View you can add a label for yourcalculation. Make sue formula is in Report footer.
ICT IGCSEDatabases (Access)LabelsChapter 18: Data Manipulation (Access)Create the query first for yourlabels using the specified fieldsand search criteria.1. Select the labels icon.2. Select the dimensions andthe number across (2).3. Write in the heading, Fieldnames and select the fielddata.4.In design view can youformat the label.312https://mrlawsonsclassroom.weebly.com/4
ICT IGCSEDatabases (Access)4Summery QueryChapter 18: Data Manipulation (Access)You can also enter search criteriainto the summery query1On most occasions a summeryquery will ask for calculationand a count. One of the fieldswill contain numerical valueswhich can be calculated.3Total Value of .com/CountNumber of Records
ICT IGCSEDatabases (Access)Extracting DataChapter 18: Data Manipulation (Access)https://mrlawsonsclassroom.weebly.com/Tip: To extract a queryor report you have toright click and select theexport option.You may be required toexport into Excel tocreate a graph.
Jan 01, 2016 · New Microsoft Access Database 1) Open Microsoft Access 2) Write the name of the database file 3) Select the destination folder and choose . Books Added to Stoc
