Introduction to Microsoft Access

1. Mark McDonald and Jason Dewitt Visual Information Systems Center School of Information Sciences University of Pittsburgh March 14, 2003 Introduction to Microsoft Access

2. Introduction to Microsoft Access • Databases are THE REASON why organizations have used computers • Many uses: • Corporate & organizational data • Sales • Inventory • Employee records • . . . • Personal databases • PC & Mac: Quicken • Palm: a collection of databases • Appointments • Addresses • To-do’s • World Wide Web • Increasing fraction of Web pages driven by databases • Allows pages customized for the user

3. Introduction to Microsoft Access • Introduction to database systems • What is a database? • Why are databases set up this way? • How to use Microsoft Access

4. Introduction to Microsoft Access • Introduction to database management systems • How to use Microsoft Access • Introduction to Access

5. Introduction to Microsoft Access • Introduction to database management systems • How to use Microsoft Access • Introduction to Access • Design of an Access database

6. Introduction to Microsoft Access • Introduction to database management systems • How to use Microsoft Access • Introduction to Access • Design of an Access database • Converting design into tables and queries

7. Introduction to Microsoft Access • Introduction to database management systems • How to use Microsoft Access • Introduction to Access • Design of an Access database • Converting design into tables and queries • Forms and reports

8. Introduction to Microsoft Access • Introduction to database management systems • How to use Microsoft Access • Introduction to Access • Design of an Access database • Converting design into tables and queries • Forms and reports • Modules, Programming, and the Web (no content)

9. Introduction to Microsoft Access • Introduction to database management systems • How to use Microsoft Access • Introduction to Access • Design of an Access database • Converting design into tables and queries • Forms and reports • Modules, Programming, and the Web (no content) • Basics of Macros • Security features in Access

10. Intro to Microsoft Access • Goals of Class: • Learn how to use Access • Develop understanding of database systems • Assumes you know something in Microsoft Office: • i.e., Word, Excel, PowerPoint, FrontPage • Prior experience with Access not needed

11. Introduction to Database Systems: A Very Brief History • First attempt: • Put paper records in computer-based files • One record in a paper form  One row (“record”) in computerized table of data • Worked OK for single application, e.g. • Write payroll checks • Maintain checking account balances • But . . . • Additional applications for data appeared • Inflexible- difficult to expand • Many redundant data entries needed for this system • Continuing problem- identifying and eliminating incorrect entries • Wasted storage space • Overwhelming complexity • Large effort to maintain this system

12. Problems with file systems- redundant entries

13. Problems with File Systems • Different views of data needed • Teacher – View data for each class taught • Class rosters • Input grades • Student- Personal data only • Administrator- needs multiple views • May need other views (e.g., counselors) • Different privileges for data access and modification • Teachers can add grades • … but only in classes he/she teaches, and only for current term • Can’t enroll students, add courses • Students can’t change anything • Administrative personnel can add courses, class sections, or teachers

14. Problems with file systems

15. Problems with File Systems • Different views of data needed • Teacher – View data for each class taught • Student- Personal data only • Course schedule • Final grades • Administrator- needs multiple views • May need other views (e.g., counselors) • Different privileges for data access and modification • Teachers can add grades • Students can’t change anything • Can only view his/her data • Administrative personnel can add courses, class sections, or teachers

16. Problems with file systems

17. Problems with File Systems • Different views of data needed • Teacher – View data for each class taught • Student- Personal data only • Administrator- needs multiple views • At start of semester: • ensure teacher for every class • sufficient student enrollment in class • At end of semester, ensure teachers have submitted grades • May need other views (e.g., counselors) • Different privileges for data access and modification • Teachers can add grades • Students can’t change anything • Administrative personnel can add courses, class sections, or teachers • But they can’t change grades

18. Database systems (After twenty years and several intermediate stages of development… ) Separate into two functions: • Database management system(DBMS) • Keeps data in small, unique chunks • Efficient storage • Maintains “just enough” redundancy • The “relational database” • Principle focus: handling data • Handles physical details of storing data efficiently • Delivers & manipulates data for applications • Limits data access based on user’s privileges in system • Several “industrial-strength” DBMS: • Oracle 9i • IBM DB2 • Microsoft SQL Server • Application programs • Get data from DBMS • Standardized language to get data (SQL)

19. The Relational Database

20. The Relational Database

21. The Relational Database

22. The Relational Database Teachers ( Not shown )

23. Microsoft Access is Unique • Provides DBMS functions • Not “industrial-strength”, designed for: • Individuals • Small workgroups • External application programs work with Access • Can use SQL to get or manipulate data in Access database • Provides built-in tools for reporting and for application development • Forms • Reports • Visual Basic code modules • Provides flexibility • Small, simple all-in-one environment • Data can be easily transferred to full-fledged DBMS • i.e., if database, # of users gets too large • No need to decide on database size before you start • Allows natural growth

24. Introduction to Microsoft Access • Introduction to database management systems • How to use Microsoft Access • Introduction to Access • Design of an Access database • Converting design into tables and queries • Forms and Reports • Modules, Programming, and the Web (no content) • Basics of Macros • Security features in Access

25. Introduction to Access • Sample databases • School • Used with this presentation • See “Tutorials & Resources” on our web site • Northwind • Included with every version of Access since 2.0 • (Demonstration of Access environment)

26. Introduction to Microsoft Access • Introduction to database management systems • How to use Microsoft Access • Introduction to Access • Design of an Access database • Converting design into tables and queries • Forms and reports • Modules, Programming, and the Web • Macros • Security features in Access

27. Design of an Access database • First step: Eliminate the computer • Capture required features of school, business, or organization in model • Pencil & paper work; no computer needed • Translate to computer later • Identify the “entity types” • Add attributes (if desired) • Identify relationships between entities

28. Design of an Access database • First step: Eliminate the computer • Identify the “entity types” • Will become names for tables of data • Entity types should be real things • Can be physically real (e.g., Employees) • Can be abstract (e.g., Courses) • Examples: • Students • Teachers • Courses • Class Sections • Should be central to the work process of the school, business, or organization • Teachers- could be an entity type for a school • Crossing guards- not an entity type • Add attributes (if desired) • Identify relationships between entities

29. Identifying Entities

30. Design of an Access database • First step: Eliminate the computer • Identify the “entity types” • Add attributes (if desired) • Will be column headings in our table • Attributes are characteristics that “go with” entity • Example- A “Students” entity type would have these attributes: • First Name • Last Name • Street Address • City • Soc. Sec. No. • Identify relationships between entities

31. Adding Attributes • Always 1:1 with entity • Adding attributes: a mixed bag • Upside: You know where things go • Downside: Clutters up entity relationships

32. Design of an Access database • First step: Eliminate the computer • Identify the “entity types” • Add attributes (if desired) • Identify relationships between entity types • How many: How many • 1: 1 • 1: Many • [ “Many” = more than one ] • Many: Many • Remember: numbers refer to relationship, not to population of entity type • Is entity required in relationship? • Optional • (e.g., “ClassSection” may be optional to “Course”- a course may not be taught every semester) • Mandatory • (e.g., “Teacher” mandatory for “ClassSection”)

33. Identifying Relationships

34. Introduction to Microsoft Access • Introduction to database management systems • How to use Microsoft Access • Introduction to Access • Design of an Access database • Converting design into tables and queries • Forms and reports • Modules, Programming, and the Web • Macros • Security features in Access

35. Converting Design into Tables and Queries • Entities  database tables • Table Design View: establish or modify table structure • Attributes  fields in table • Make primary key • Data types and other table details • Table Wizard- not that useful • Datasheet View: view or enter data • Add relationships between tables • Make queries

36. Converting Design into Tables and Queries • (Demonstration- making tables and entering data) School1.mdb ????????

37. Converting Design into Tables and Queries • Entities  database tables • Add relationships between tables • Menu command: Tools / Relationships • Match primary key in one table with matching value in another table (“foreign key”) • Enforce referential integrity- keeps your data good! • Access uses “” to mean “Many” • Make queries

38. Converting Design into Tables and Queries • (Demonstration on drawing relationships) School2.mdb ????????

39. Rules of Thumb: Creating Tables • Many:Many relationships • Require a “bridging” table • Converts Many:Many  two 1:Many • Bridging table is the “Many” side of the new relationships • Example: “Grades” table • 1:Many relationships • Typically, add a “foreign key” from “1” side in “Many” table • Example: “Advisor” field in “Students” table • Tables dangling on end of 1:1 relationship • Table on end may be unnecessary • Fields may belong in other table • Exceptions: • When main table is big • Certain fields aren’t used often • Example: “Emergency Contact Info” Entity

40. Rules of Thumb: Creating Tables • Primary key determination • Each value MUST be unique • Key “determines” all other fields in table- they all depend on value of primary key • Otherwise: a problem with design • When only a combination of fields is unique: • Add extra (otherwise meaningless) field with “Autonumber” data type as primary key • Any use of this data in another table should be of data type “Number”, Format = “Long Integer” • (If you choose Autonumber type again, Access won’t let you choose the number!) • Example: “Grades” table

41. Rules of Thumb: Creating Tables • Data types • Choose “Number” only if you want to do math on it • “Date/Time”, “Currency”: specialized number types • “Memo” type has no limits on length • Good for catch-all “Notes” field (misc. data that doesn’t have a “slot”) • Lookup Wizard- good for looking up real names for meaningless ID numbers • When in doubt, use “Text” • Field Size: • Default values assigned by Access usually OK • Unless you’re likely to run out of storage space • If you want to change from default, do it early! • Before data added • Before relationships between tables made

42. Rules of Thumb: Creating Tables • Format: • Extra level of formatting for Date/Time, Number, Currency • Input Mask: • Formatting for viewing, inputting Soc. Sec. No., Phone no., etc. • Wizard for masking common types • Caption: • Human-friendly name for field when used elsewhere in Access • Default value: • What filled in automatically if no entry in field

43. Rules of Thumb: Creating Tables • Validation Rule: • Mathematical expression- sets limits on data in field • Example: “Course_sections” table, “Year” field • Validation Text: • Text you want displayed when a Validation Rule is violated • Example: “Course_sections” table, “Year” field

44. Converting Design into Tables and Queries • Entities  database tables • Add relationships between tables • Make queries • Query assembles data we want • “Just enough” redundancy makes this work • Making a query in Access • Query Wizard- Let Access guide you • Query Design View- Make your query manually • Datasheet view- View, update, add, or delete data • Looks like data in a table • Any data changes result in changes in underlying tables • Filter data • By form • By selection • SQL view (You’ve been writing programming code!)

45. Converting Design into Tables and Queries • (Demonstration on making queries) School3.mdb ???????????

46. Introduction to Microsoft Access • Introduction to database management systems • How to use Microsoft Access • Introduction to Access • Design of an Access database • Converting design into tables and queries • Forms and reports • Modules, Programming, and the Web • Macros • Security features in Access

47. Forms and Reports • Make it easier to view, modify, add, and delete data • Forms – optimized for on-screen use • Reports- optimized for printout • Each based on a table or on a query • Creating Forms • Creating Reports

48. Forms and Reports • Make it easier to view, modify, add, and delete data • Creating Forms • Form Wizard • Form Design View • Controls in Toolbox allow customization • Wizards for many of them, e.g. Button • Use the View / Properties command to modify form or control properties • Datasheet View • Useful in sub-forms • Creating Reports

49. Forms and Reports • (Demonstration of forms) School4.mdb ???????????

50. Forms and Reports • Make it easier to view, modify, add, and delete data • Creating Forms • Creating Reports • Wizard- makes it easy • Design View • Very similar to Form Design View • Differences- more headers and footers • Report level • Page level • Data grouping level • Can be modified with “Sorting and Grouping” command on View menu • Page Preview • Similar to Page Preview in other Office applications