Office Access 2007 Lab 4ObjectivesSlide 3Concept PreviewOutlineSlide 6OutlineSlide 8Importing DataImporting an Access TableConcept 1Identify Source DataImport Objects Dialog BoxSaving Import StepsThe Find Unmatched Query WizardImporting Data from Another ApplicationSlide 17Specify Name for the New TableSlide 19Using a Saved ImportControlling Field InputChanging Field PropertiesConcept 2Number Field Size PropertyCreating an Input MaskConcept 3Characters for Input MasksInput Mask WizardStoring Data Entered with the Input MaskInput Mask for Social Security NumberTesting a Required FieldEstablishing RelationshipsOne-to-One and One-to-Many RelationshipsUsing Action QueriesAction QueriesUsing an Append QueryUsing a Delete QueryCreating a Select Query in Design ViewUsing an Update QueryUsing Calculations in QueriesSetting Up the QueryAdding a Calculated FieldConcept 4Using a Calculated FieldCreating a SubqueryConcept 5SQL StatementsSubquery PartsSubquery StatementSubquery Select StatementCreating a Top-Values QueryCreating a Crosstab QueryConcept 6Using the Crosstab Query WizardCrosstab CalculationsRefining the Query DesignKey TermsFAQSlide 59Discussion QuestionsSlide 61Slide 62Slide 63McGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.Office Access 2007Lab 4Importing and Querying TablesMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-2Objectives1. Import a table2. Import data from Excel3. Save important steps4. Create an input mask5. Make fields requiredMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-3Objectives6. Create and use action queries7. Add a calculated field to a query8. Create a subquery9. Create a Top-Values query10.Create a crosstab queryMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-4Concept Preview•Importing Data•Required Property•Input Mask •Calculated Field•SQL (Structured Query Language) •Crosstab QueryMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-5Outline•Importing Data–Importing an Access Table–Importing Data from another Application–Using a Saved Import•Controlling Field Input–Changing Field Properties–Creating an Input Mask–Testing the Field PropertiesMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-6Outline•Establishing Relationships•Using Action Queries–Using an Append Query–Using a Delete Query–Creating a Select Query in Design View–Using an Update QueryMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.Outline •Using Calculations in Queries–Setting Up the Query–Adding a Calculated Field•Creating a Subquery•Creating a Top-Values QueryMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-8Outline•Creating a Crosstab Query–Using the Crosstab Query Wizard–Refining the Query Design•Key Terms•FAQs•Discussion QuestionsMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-9Importing DataMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.Importing an Access TableMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-11Concept 1•Creates a copy of information from an external data source•Converts copied data into a format that can be used by Access•Inserts data in a new table•Source table or file is not alteredImporting DataMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.Identify Source DataMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.Import Objects Dialog BoxMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.Saving Import Steps •Save the import steps to rerun the import •Can reuse the import operation to import another tableMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.The Find Unmatched Query WizardMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-16Importing Data from Another ApplicationMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.Importing DataMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.Specify Name for the New TableMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.Saving Import StepsMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.Using a Saved ImportMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.Controlling Field InputMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-22Changing Field Properties •Use Design view –Add and remove fields–Change field properties•Name•Size•Data type–Add new properties•Input MaskMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-23Concept 2•Specifies whether a value is required in a field•Property can be set to yes or no•If yes, value cannot be blank•Can be set for any type of field except AutoNumberRequired PropertyMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.Number Field Size PropertyMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-25Creating an Input MaskMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-26Concept 3•Controls the following field properties–Where data is entered in a field–The kind of data –The number of allowable fields•Consists of …–Literal characters like hyphens and parentheses–Mask characters that define the characteristics of the input maskInput MaskMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-27Characters for Input MasksMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-28Input Mask WizardMcGraw-Hill Career Education © 2008 by the McGraw-Hill Companies, Inc. All Rights Reserved.4-29Storing Data Entered with the Input MaskMcGraw-Hill Career Education © 2008 by