Transcription
Microsoft Application SeriesAccess 2003IntroductionBest STL Courses never cancelled: guaranteed Last minute rescheduling 24 months access to Microsoft trainers 12 months schedule UK wide deliverywww.microsofttraining.netVersion 1
Your Best STL Learning ToolsWelcome to your Best STL training course.As part of your training, we provide you with the following tools and resourcesto support and enhance your learning experience.Thank you for choosing Best STL.1In-coursehandbookTo guide you through yourtraining while you are on thecourse.2Referencematerial312 monthsaccess to MicrosofttrainersAvailable online throughyour delegate account.Available through onlinesupport forum.Contains unit objectives,exercises and space towrite notes.Comprehensive referencematerial with 100 pages,containing step-by-stepinstructions.Need help? Our team ofMicrosoft qualified trainersare on hand to offer adviceand support.456DelegateaccountYour delegate accountgives you access to: Reference material Course exercise files Advice & support forum Rewards programme Promotions & NewslettersTrainer hintsand tipsHints and tips availableonline from our Microsoftqualified trainers for: All MS Office applications VBA MS Project MS Visio moreSave withPromotionsSave on further trainingcourses you book withPromotions. 30% off list price(time limited) 50 off list price(blue card discount)E&OEBest Training reserves the right to revise this publication and make changesfrom time to time in its content without notice. Best STL 2013www.microsofttraining.netTel: 0207 987 3777
Quick reference: Access shortcut keysCommandKeystrokeAdd new recordCtrl BuilderCtrl-F2Check/uncheck box or option buttonspacebarCloseCtrl-WCopyCtrl-CCutCtrl-XCut current line and copy to ClipboardCtrl-YCycle through sectionsF6/Shift-F6Cycle through tab of each object's type (toggle)Ctrl-Tab/Shift-Ctrl-TabDatabase windowF11Delete current recordCtrl -Edit/Navigation mode (toggle)F2Exit subform and move to next/previous field in next recordCtrl-Tab/Shift-TabExtend selection to next/previous recordShift-Dn/UpFile/Save AsF12FindCtrl-FFind NextShift-F4Find PreviousShift-F3GoToCtrl-GInsert current dateCtrl ;Insert current timeCtrl :Insert default valueCtrl-Alt-spacebarInsert new lineCtrl-EnterInsert value from same field in previous recordCtrl 'Menu barF10Move to beginning/end of multiple-line fieldCtrl-Home/EndMove to current field in first/last record (Navigation mode)Ctrl-Up/DnMove to first field in first record (Navigation mode)Ctrl-HomeMove to first/last field in current record (Navigation mode)Home/EndMove to last field in last record (Navigation mode)Ctrl-EndMove to left edge of pageHome or Ctrl-LeftMove to page number/record number boxF5Move to right edge of pageEnd or Ctrl-RightNext windowCtrl-F6Open combo boxF4Open in Design viewCtrl-Enter Best STL 2013www.microsofttraining.netTel: 0207 987 3777
Access IntrodutionCourse Overview1.2.3.4.5.6.7.8.www.microsofttraining.net Best STL 2013Database conceptsAccess basicsCreating databasesWorking with fields and recordsQuerying tablesCreating and using formsCreating and using reportsImporting, exporting, and linking objectsTel: 0207 987 3777Page 1
Access IntrodutionDatabase conceptsUnit 1 objectives Use database terminology to become familiar withAccess Start Access, learn about its environment, open adatabase, and learn about database objects Plan and design a database to ensure that no datais missing or redundant, and explore relationshipsbetween tables Use Help options to get information on Accesstopics Close a database and AccessYour notes: Unit 1www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 2
Visual summary: Unit 1 objectivesUse database terminology to become familiar with AccessTableUsed to store data (e.g. all company employees)QueryUsed to retrieve specific data (e.g. most sold product in a month)FormsUsed to enter data. Can also be used to modify data.ReportsUsed to display and print data in an easy-to-read format.Field 1Field 2Field 3Field 4Field 5Field 6Record 1Data valueLearn about the Access environment and database objectsTitle barMenu barDatabase toolbarTask paneStatus barDatabasewindow toolbarObjects barwww.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 3
Visual summary: Unit 1 objectivesPlan and design a database and explore relationshipsPlanning a Database – FactorsPurpose of the databaseNumber of tables and type of information they will containFields that will be in each tableType of queries to perform on the databaseForms that you’ll needTypes of reports that you’ll need Product ID field is used inthe Transaction table toindicate the product beingpurchased and in doing dolinks the tablesUse Help options to get information on Access topics Help menuPress F1 keyAsk a Question boxOffice Assistantwww.microsofttraining.net Best STL 2013 Google searchAsk an expert or colleagueVisit database forums onlineTel: 0207 987 3777Page 4
Unit 1 Practice Activity1. Start Access.2. In the Access window, identify the toolbar, menu bar and title bar.3. Open Concepts.mdb. How many and what types of database objects arethere in this database?4. Open the Transaction table. How many fields and records are there in thistable?5. Close Concepts.mdb and Access.6. Write down the appropriate database term for each of the following:a. A set of related data valuesb. A collection of recordsc. An Item of data7. Suppose you are working with a database containing information about thesalespersons in Outlander Spices. Write down the name of the object youwould use in each of the following situations:a. You want to enter information for a new Salespersonb. You want to know which departments have earnings more than 80,000.c. You want to print all the values from the table.8. Plan and design a database for storing information about customers whoplace orders for different products. The database should have a minimumof two tables.Answers to 3, 4, 6 and 7 on the following page.www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 5
Access IntroductionAnswers to Unit 1 Practice ActivityAnswer to 3.There are three types of database objects: Tables, Queries, and ReportsAnswer to 4.5 fields and 11 recordsAnswer to 6. a to c, in order:Database, table, data valueAnswer to 7. a to c, in order:Table, query, reportOnline support forum and knowledge basehttp://www.microsofttraining.net/forumVisit our forum to have your questions answered by our Microsoft qualified trainers.www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 6
Access IntrodutionAccess basicsUnit 2 objectives Explore a table in Datasheet view and Design view,navigate in a table and a form, and explore a formin Design view Examine a query in Design view and run it View a report and explore it in Design viewYour notes: Unit 2www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 7
Visual summary: Unit 2 objectivesExplore a table in datasheet and design viewsOpens a tablein datasheetviewOpens a tablein design viewTable datasheet view with navigation buttonsTable design viewExplore a form in normal and design viewOpens a formin normal viewOpens a formin design viewA form in normal view with navigation buttonsForm in design viewwww.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 8
Visual summary: Unit 2 objectivesExamine a query in design view and run itRun Query buttonDesign view buttonQuery in design viewView a report and explore it in design viewReport in printable viewReport in design viewwww.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 9
Unit 2 Practice Activity1. Open Product data.mdb.2. Examine the Retailer table in Datasheet and in Design view.3. Identify the primary key.4. Navigate the datasheet view of the table by using the navigation buttons.5. In the same database, examine and navigate the Retailer form.6. Examine Product query in Design view and run the query.7. View Retailer report. Examine this report in Design view.8. Close Product data.mdb.9. Match each of the following terms with the statement that describes it.TermDescriptionDatasheetviewSmall box to the left of each record in a table thatyou can click to select a record.Design viewShows data in tabular format.NavigationButtonGives you complete control over the structure ofthe table.RecordselectorButtons located at the bottom of the Datasheetview window that you can use to move throughvarious records in the datasheet view.Answers to 9 on the following page.www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 10
Access IntroductionAnswers to Unit 2 Practice ActivityTermDescriptionDatasheet viewShows data in tabular format.Design viewGives you complete control over the structure ofthe table.NavigationButtonButtons located at the bottom of the Datasheetview window that you can use to move throughvarious records in the datasheet view.Record selectorSmall box to the left of each record in a table thatyou can click to select a record.Online support forum and knowledge basehttp://www.microsofttraining.net/forumVisit our forum to have your questions answered by our Microsoft qualified trainers.www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 11
Access IntrodutionCreating databasesUnit 3 objectives Create and save a database Create a table and set the primary key by using theTable Wizard Create and work with tables in Design view andenter records in tablesYour notes: Unit 3www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 12
Visual summary: Unit 3 objectivesCreate and save a databaseNaming Rules Any combination of letters, numbers, specialcharacters, and embedded spaces 64 or fewer characters Cannot start with a space No periods (.), exclamation marks (!), accentsgrave ( ), or brackets ( [ ] )Create a table and set the primary key by using the Table Wizardwww.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 13
Visual summary: Unit 3 objectivesCreate and work with tables in design viewDescription of Data typesText(Default) Text or combinations of text and numbers, as well as numbers thatdon't require calculations, such as phone numbers.MemoLengthy text or combinations of text and numbers.NumberNumeric data used in mathematical calculations. See the next page for theNumber Field Size propertiesDate/TimeDate and time values for the years 100 through 9999.CurrencyCurrency values and numeric data used in mathematical calculations involvingdata with one to four decimal places. See the next page for the Currency SizepropertiesAutoNumberA unique sequential (incremented by 1) number or random number assignedby Microsoft Access whenever a new record is added to a table. AutoNumberfields can't be updatedYes/NoYes and No values and fields that contain only one of two values (Yes/No,True/False, or On/Off).OLE ObjectAn object (such as a Microsoft Excel spreadsheet, a Microsoft Word document,graphics, sounds, or other binary data) linked to or embedded in a MicrosoftAccess table.HyperlinkText or combinations of text and numbers stored as text and used as ahyperlink address. A hyperlink address can have up to three parts:text to display — the text that appears in a field or control.address — the path to a file (UNC path) or page (URL).subaddress — a location within the file or page.screentip — the text displayed as a tool tip.The easiest way to insert a hyperlink address in a field or control is to clickHyperlink on the Insert menu.LookupWizardCreates a field that allows you to choose a value from another table or from alist of values by using a list boxwww.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 14
Visual summary: Unit 3 objectivesNumber and Currency Field Size propertiesData typeStorage sizeRangeByte1 byte integer0 to 255Integer2 bytes-32,768 to 32,767Long Integer4 byte integer-2,147,483,648 to 2,147,483,647Single4 byte floating pointApproximate range -3.40 x 10 to 3.40 x3810Double8 byte floating point-1.79769313486231E308 to-4.94065645841247E-324 for negativevalues;384.94065645841247E-324 to1.79769313486232E308 for positive valuesCurrency8 bytes fixed point-922,337,203,685,477.5808 to922,337,203,685,477.5807Decimal12 byte28 places to the right of the decimal;smallest non-zero number is /-0.0000000000000000000000000001(Only used within aVariant)www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 15
Unit 3 Practice Activity1. Create a new blank database with a name of your choice.2. Start the Table Wizard to create a table.3. Select a sample table of your choice.4. Select sample fields and set a primary key.5. Click Finish to close the Table Wizard.6. Create a table in Design view, set the primary key and save the table asCustomer order.7. Enter data in Customer order as shown in the example below.8. Save and close the table.9. Close the database.Online support forum and knowledge basehttp://www.microsofttraining.net/forumVisit our forum to have your questions answered by our Microsoft qualified trainers.www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 16
Access IntrodutionWorking with fields and recordsUnit 4 objectives Modify a table’s design by editing fields and settingfield properties Add and delete records Find and replace values in records by using theFind feature and use the spelling checker to correctspelling mistakes in a table Sort and filter recordsYour notes: Unit 4www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 17
Visual summary: Unit 4 objectivesEdit fields and set field propertiesNaming fields Always give thefields easilyunderstood andrelevant names Always give adescription of thefield’s dataText formatting characters for input masking@At least one letter or space Letters will be converted to lowercase Letters will be converted to uppercase&Letters cannot be entered in this fieldEg L000 – A capital letter followed by 3 numbers (C001)For more characters type Input mask Syntax in the “Type a question for help” boxUsing the Find feature and the Spelling Checker in a tablewww.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 18
Unit 4 Practice Activity1. Open Modifying database.mdb.2. Open the table New retailer in Design view.3. Observe the table design.4. Open the Retailer table in Design view.5. Observe the design of Retailer and compare it to the design ofNew retailer. You might want to tile the windows to see both names at thesame time.6. Modify the design of the Retailer table so it matches the New retailertable. Save the design changes.7. Close the Retailer table and maximise the New retailer table.8. Sort the records based on ascending order of the fieldContract first name.9. Update and close the New retailer table.10. Close the database.Online support forum and knowledge basehttp://www.microsofttraining.net/forumVisit our forum to have your questions answered by our Microsoft qualified trainers.www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 19
Access IntrodutionQuerying tablesUnit 5 objectives Create, run, print, and save queries, use queries tosort data, and filter query results Modify query results; modify queries by adding andremoving fields and by using comparisonoperators; use AND and OR conditions in queries;and find records with empty fields Perform calculations in queries by usingexpressions and aggregate functionsYour notes: Unit 5www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 20
Your notes: Unit 5www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 21
Visual summary: Unit 5 objectivesCreate, and run queries, sort data, and filter query resultsTo run a query from design view,Click the Run Query buttonTo return to the design view clickQuery in designviewModify queries by adding and removing fields and by using comparisonoperators; use AND & OR conditions in queries; and find records with emptyfieldsComparison Operators Greater thanLess ThanEqual toLess than or equal toGreater than or equal toNot Equal towww.microsofttraining.net Best STL 2013Using conditions in queriesOR1. Enter the condition in theappropriate cell(s) of the Or row2. Run the queryAND1. Enter the AND condition in theappropriate cell of the Criteria row2. Run the queryTel: 0207 987 3777Page 22
Unit 5 Practise Summary1. Open Orders.mdb.2. Create a query in design view based on the Product table, that displays allthe records where the Product begins with Ce.3. Delete the previous criterion and display all the products having asUnit price between 1.00 and 2.00. Close the query without saving it.4. Create another query named Summary based on the Order detail table,that displays the SumOfQty sold to each customer, as shown in theexample below.5. Save and close the query.6. Close the database.Online support forum and knowledge basehttp://www.microsofttraining.net/forumVisit our forum to have your questionsanswered by our Microsoft qualified trainers.www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 23
Access IntrodutionCreating and using formsUnit 6 objectives Create forms by using the AutoForm feature Create forms by using the Form Wizard Modify forms in Design view Find, sort, and filter records by using formsYour notes: Unit 6www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 24
Visual summary: Unit 6 objectivesThe Navigation BarCreate forms by using the Form WizardModify forms in Design viewTo access this:www.microsofttraining.net Best STL 2013Tel: 0207 987 3777 Double click the Form Header Right-click / PropertiesPage 25
Visual summary: Unit 6 objectivesFind, sort, and filter records by using forms1.Click2.Select the field on which to set filter3.Select the value for the filterClickwww.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 26
Unit 6 Practice Activity1. Open Employee.mdb.2. Create a Columnar form based on the Employee information table byusing the AutoForm feature.3. Enter a new record in the form.4. Save the form as Employee details.5. Close Employee details.6. Using the Form Wizard, create a form based on the Employee informationtable.7. Select the Employee code, First name, Last name, Region, Department,and Earnings field to create a tabular form based on the Industrial style.8. Save the Form as Employee form.9. Enter a new record for Megan Reid as follows:S028, Megan, Reid, East, Human Resources, 75,00010. Close Employee form.11. Select the Quarterly sales analysis table to create a Form in design view.12. Enter Sales Analysis as the title. The size and font of the title should be14pt and Arial.13. Set the background colour to green and the border colour to yellow.14. Drag Fields from the fields list to create the form as shown in the examplebelow.15. Sort the records in ascending order by Quarter1 sales.16. Filter the records based on the field Quarter1 sales for values greater than10,000.17. Save the form as Sales analysis.18. Close the form and the database.www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 27
Access IntrodutionCreating and using reportsUnit 7 objectives Create reports by using the Report Wizard andqueries Group records in a report, summarise informationin a report, modify the appearance of a report bychanging the report layout, and print a reportYour notes: Unit 7www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 28
Visual summary: Unit 7 objectivesCreate reports by using the Report Wizard and queriesGroup records in a report, summarize information in a reportGroup ByProduct name Click Next ClickSelect theSummaryvalueswww.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 29
Visual summary: Unit 7 objectivesChange the report layout, and print a reportThe Print dialogboxwww.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 30
Unit 7 Practice Activity1. Open Emp.mdb.2. Create a query based on the Quarterly sales analysis table displaying theSalesperson name and Quarter1 sales fields, whereQuarter1 sales 10000.3. Save the Query as Sales query and create a columnar report based onthis query.4. Save the report as Sales report.5. Close the report and query.6. Using the report wizard, create a report based on theEmployee information table with the following settings: Display all the fields of the table.Group the report by Department.Sort Earnings in ascending order.Summarise by calculating the average (Avg) of Earnings.Use the Outline1 layout.Use the Soft Gray style for the title background colour.Specify Employee Details as the title.7. Change the background colour of the detail section (select any colour ofyour choice) and set the Special Effect property to Raised.8. Preview the report.9. Update and close the Report.10. Close the database.Online support forum and knowledge basehttp://www.microsofttraining.net/forumVisit our forum to have your questions answered by our Microsoft qualifiedtrainers.www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 31
Access IntrodutionImporting, exporting, and linking objectsUnit 8 objectives Import Access objects into the active databasefrom a different database Export objects from the active database to adifferent Access database Link objects from one database to another andupdate the linksYour notes: Unit 8www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 32
Visual summary: Unit 8 objectivesImport Access objects from a different databaseSelect File, Get External Data, ImportThe ImportObjects dialog boxExport objects to a different Access databaseSelect File, ExportThe Export Table dialogboxLink objects between Databases and update the linksSelect File, Get External Data, Link TablesRetailer is alinked tablewww.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 33
Visual summary: Unit 8 objectivesLinked Table mangerSelect Tools, Database Utilities, Linked Table ManagerSelect linkto updatewww.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 34
Unit 8 Practice Activity1. Open Import practice.mdb.2. Import the following objects from the Spices.mdb database: The Suppliers tableThe Invoices QueryThe Categories formThe Catalogue report3. From the Import practice database, export the Employee informationtable to the Spices database.4. Close Import practice.mdb.5. Open Spices to verify that the table was successfully exported.6. Close Spices.mdb.7. Open Links practice.mdb.8. Link to the table Categories from the Spices database.9. Close Links practice.mdb.10. Close Access.Online support forum and knowledge basehttp://www.microsofttraining.net/forumVisit our forum to have your questions answered by our Microsoft qualified trainers.www.microsofttraining.net Best STL 2013Tel: 0207 987 3777Page 35
1. Start Access. 2. In the Access window, identify the toolbar, menu bar and title bar. 3. Open Concepts.mdb. How many and what types of database objects are there in this database? 4. Open the Transaction table. How many fields and records are there in this table? 5. Close Concepts.mdb and Access. 6.
