MS Access 2002: Basic

1. MS Access 2002: Basic Instructor: Vicki Weidler

2. MS Access: Database Concepts

3. Overview • Become familiar with MS Access database terminology • Learn how to start MS Access, open a database & navigate the software interface • Understand database objects & their corresponding functions • Use standard design principles to plan a database • Explore relationships between tables • Use Help options to find information on MS Access topics • Learn how to close a database & MS Access

4. Databases • What is a database? • When do you use a database? • 3 main types of databases • Flat file • Relational • Object-oriented

5. Microsoft Access A relational database management program that allows you to store, organize, and retrieve information in an effective manner.

6. Planning a Database • What is the purpose of the database? • What do you need it to do? • Who will use it? • How many will use it? • When will they use it? (i.e. one at a time, simultaneously) • How will they use it? (i.e. desktop, server, web-interface) • Who will create it? • Who will maintain & modify it? • Who will do data entry? • Do you need to build in security measures? • Who will provide technical support for it? • What types of information need to go in it? • How will this be organized into tables? • How will these tables relate to one another? • What types of queries will you need to create? • Will you need to create any forms? How many? What kind? • Will you need to create any reports? How many? What kind? • Will you need to create any pages? How many? What kind?

7. Common Mistakes to Avoid • Don’t assume every problem can be solved with a database • Don’t leave too long between reviews • Don’t spend too much time on the prototype • Don’t develop the database until you fully understand what you need it to do • Don’t develop the database until it is clear where all responsibilities lie • Don’t aim for 100% functionality first time around; use 80-20% rule • Involve all users in the database planning, design & testing • Has anyone else solved the problem? Don’t reinvent the wheel • Plan on an iterative development process • Use consistent database standards and naming conventions • Use consistent, universal coding • Use consistent viewing standards • Keep sufficient documentation; build into the database when possible • Tech support, tech support, tech support………..

8. Database Development Life-Cycle Database Design Database Production Database Prototype Database Testing Database Development

9. Summary • Become familiar with MS Access database terminology • Learn how to start MS Access, open a database & navigate the software interface • Understand database objects & their corresponding functions • Use standard design principles to plan a database • Explore relationships between tables • Use Help options to find information on MS Access topics • Learn how to close a database & MS Access

10. Independent Practice Activity • Question #2: 3 types (tables, queries, reports) • Question #3: 5 fields and 10 records • Question #5: Database, Table, Data Value • Question #6: Table, Query, Report

11. MS Access: Access Basics

12. Overview • Explore the viewing options for MS • Access tables, forms, queries & • reports • Learn how to navigate in a table and • a form • Learn how to run a query

13. Views in MS Access • Design View - displays the structure of a table, query, form or report • Datasheet View – displays data in a tabular format containing rows & columns in tables, queries & forms; view & edit • PivotTable View – spreadsheet-like table used to analyze data dynamically in different ways in tables, queries & forms • PivotChart View – presents data in a chart form in tables, queries & forms • SQL View – allows you to create queries using SQL statements • Form View – allows you to view and edit data in a form • Layout Preview – allows you to check a report’s design; shows design elements & just enough records to verify the design is correct • Print Preview – allows you to view a report including moving around a single page, multiple pages, look at several pages at once & change magnification to view details

14. Using Queries to Retrieve Data Define the following: • The conditions that you want the data to meet • The fields that you want to see in the query result • The tables from which you’ll extract the fields • The means of extracting data

15. Summary • Explore the viewing options for MS • Access tables, forms, queries & • reports • Learn how to navigate in a table and • a form • Learn how to run a query

16. Independent Practice Activity • Datasheet View-shows data is a tabular format • Design View-gives control over table structure • Navigation Button-buttons at bottom of datasheet view used to move through records • Record Selector-small box to left of each record in a table that you can click to select a record

17. MS Access: Creating Databases

18. Overview • Learn how to create & save a database • Use the Table Wizard to create a table & set a primary key • Use Design view to create tables & enter records

19. Naming Rules A database, object or field name: • Can have any combination of letters, numbers, special characters, & embedded spaces • Cannot contain more than 64 characters & cannot start with a space • Cannot include a period (.), exclamation mark (!), an accent grace (‘), or brackets ([ ]) CAUTION: Good practice to use underscores (_) instead of embedded spaces because it is easier to refer to an object name & avoid potential issues

20. Practice • Inventory_ _ Control • Yes, although one underscore is the accepted standard • .Access.Database 1 • No, because the name contains periods & spaces should be avoided • finanCIAL$Transaction • Yes, although try to use names that are as simple & descriptive as possible • Contact [Client] Address • No, because the name contains brackets & spaces should be avoided • SalesPerson3 • Yes, it is fine to run words together & use different cases

21. Summary • Learn how to create & save a database • Use the Table Wizard to create a table & set a primary key • Use Design view to create tables & enter records

22. Independent Practice Activity

23. MS Access: Working with Fields & Records

24. Overview • Learn how to modify a table’s design by editing fields & setting field properties • Learn how to add & delete records • Use the Find feature to find & replace records • Use the Spell Checker to correct mistakes in a table • Learn how to sort & filter records

25. Effective Field Names • Meaningful • Descriptive • Self-Explanatory • Purposeful • Understandable • Readable • Consistent

26. Text Formatting Characters @ at least one letter or space must be entered in the field < the letters entered in the field will be converted to lowercase letters > the letters entered in the field will be converted to uppercase letters & letters cannot be entered in the field

27. Sorting & Filtering Sorting Organize field data values in a particular sequence (i.e. ascending, descending) Filtering Temporarily isolate a subset of records to analyze

28. Sorting Multiple Fields • Fields must be adjacent to each other in Datasheet view • Sorts from left to right

29. Summary • Learn how to modify a table’s design by editing fields & setting field properties • Learn how to add & delete records • Use the Find feature to find & replace records • Use the Spell Checker to correct mistakes in a table • Learn how to sort & filter records

30. Independent Practice Activity

31. MS Access: Querying Tables Instructor: Vicki Weidler Assistant: Joaquin Obieta

32. Overview • Learn how to create, run, print, and save queries • Use queries to sort data and filter query results • Learn how to modify query results • Understand how to modify queries by adding/ removing fields and by using comparison operators • Learn how to use AND and OR conditions • Learn how to find records with empty fields • Perform calculations in queries by using expressions and aggregate functions

33. Comparison Operators > Greater than < Less than = Equal to <= Less than or equal to >= Greater than or equal to <> Not equal to

34. OR Conditions Used to specify two conditions in the criteria Example: Unit_price is greater than 2 OR Qty_Available is equal to 700

35. AND Conditions Used to show only the records that satisfy all the specified conditions Example: Unit_price is greater than 1.4 AND less than 1.9

36. Wildcard Operators Used to locate text values when you can’t remember the exact text or word; placeholder ? Used to substitute for a single character * Used to substitute for any number of characters

37. Empty Fields • Unknown (empty) values in fields are referred to as null values • Null values cannot be used in primary key fields or in calculations • Can display or remove null values to avoid/prevent problems

38. Calculations in Queries • When calculations are built into queries (and not stored in the source tables) it ensures the most up-to-date information • Calculations are performed each time the query is run • Perform calculations on single records or on a group of records • Examples: Totals, Averages, Counts

39. Summary • Learn how to create, run, print, and save queries • Use queries to sort data and filter query results • Learn how to modify query results • Understand how to modify queries by adding/ removing fields and by using comparison operators • Learn how to use AND and OR conditions • Learn how to find records with empty fields • Perform calculations in queries by using expressions and aggregate functions

40. Independent Practice Activity

41. MS Access: Creating & Using Forms

42. Overview • Use the AutoForm feature to create forms • Use the Form Wizard to create forms • Understand how to modify forms in Design view • Use forms to find, sort, and filter records

43. Summary • Use the AutoForm feature to create forms • Use the Form Wizard to create forms • Understand how to modify forms in Design view • Use forms to find, sort, and filter records

44. Independent Practice Activity

45. MS Access: Creating & Using Reports

46. Overview • Use the Report Wizard to create reports • Learn how to group records in a report • Learn how to summarize information in a report • Understand how to change the report layout • Learn how to print a report

47. Summary • Use the Report Wizard to create reports • Learn how to group records in a report • Learn how to summarize information in a report • Understand how to change the report layout • Learn how to print a report

48. Independent Practice Activity

49. MS Access: Importing, Exporting, & Linking Objects

50. Overview • Learn how to import MS Access objects from a different database • Learn how to export objects to a different database • Understand how to export and import XML documents • Understand how to link objects from one database to another & update those links