MS Access: Database Concepts

1. MS Access: Database Concepts Instructor: Vicki Weidler

2. 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

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

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

5. 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?

6. 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 aid 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………..

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

8. 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

9. MS Access: Access Basics

10. 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

11. 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

12. 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

13. 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

14. MS Access: Creating Databases

15. 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

16. Naming Rules A database name or an object name: • Can have any combination of letters, numbers, special characters, & embedded spaces • Cannot contain more than 64 characters & cannot start with a space • Cannot incluce 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

17. 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

18. 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

19. MS Access: Working with Fields & Records

20. 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

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

22. 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

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

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

25. 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

26. MS Access: Querying Tables

27. 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

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

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

30. 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

31. 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

32. 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

33. 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

34. 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

35. MS Access: Creating & Using Forms

36. 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

37. 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

38. MS Access: Creating & Using Reports

39. 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

40. 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

41. Conclusion • Resources • Questions & Answers • Evaluations • Thank You!!!