Introduction to MS ACCESS

1. Introduction to MS ACCESS Presented by Arlene N. Baratang, Ph.D.

2. Objectives At the end of this training session you should be able to: • Understand the three types of relationships: one-to-many, many-to-many and one-to-one; • Create a new database; • Create tables for data entry in design view; • Use, understand, and set data types: Text, Memo, Number, Currency, • Date/Time, AutoNumber, Yes/No, Lookup wizard. • Create and delete a primary key; • Insert and delete a row.

3. Questions to Ask Before Designing a Database: What does the database need to do? • What functions need to be achieved? • Which objects (such as tables, forms) depend on each other? • What items are needed for the database? • Who will use the database? • How will the output data (report) be generated? • How will the database be organized?

4. AccessDatabase Objects

5. Table • The central framework of a database that stores data in fields (columns) and records (rows).

6. Query • Allows for table inquiries. A query can change, delete, add, arrange data in tables. Also aids gathering information for forms and reports.

7. Form • Displays and enters data in a fi tted format. Forms can also contain other nested forms (subforms).

8. Report • Allows for the printing and print preview of information such as labels, lists, form letters, invoices, summaries, display charts, etc. The user can personalize reports by adding a logo or picture, organizing headers, details, footers, and sorting columns.

9. Page • Also known as Data Access Page. Allows the publication of a web page and web access to a database. A page can be viewed and edited, and the information can be altered.

10. Macro • Allows for automating simple and common tasks such as opening and closing a form, exporting data, printing data in a report, and saving data.

11. Module • Modules are a collection of Visual Basic declarations and procedures that allow for the automation and customization of Access, giving the user more explicit control over actions.

12. Naming Conventions • The Leszynski Naming Convention (LNC), originally created by Stan Leszynski and Greg Reddick, developed guidelines called tags to assist the user to open, edit, and troubleshootwithout deciphering what is contained within the object itself. • File names can be more than one word; however, do not use underscores or spaces. Instead, capitalize the first letter of each word. e.g. tblEmpInfo

13. Naming Conventions tbl qry frm rpt mcr mdl dbf • Table • Query • Form • Report • Macro • Module • Database

14. Types of Relationships in Microsoft Access

15. One-to-many Table 1 1 Table 2 2 3

16. Many-to-many

17. One-to-one

18. Introduction to Database Concepts Organizing Data

19. Field • A field is a single characteristics or attribute of a person, place, object, event, or idea.

20. Table • A table is a collection of fields that describe a person, place, object, event, or idea.

21. FieldValue • The specific value, or content of a field is called the field value

22. Record • This set of field values is called a record

23. Databases and Relationships

24. Database • A collection of related tables is called a database, or a relationaldatabase. • You connect the records in the separate tables through a common field that appears in both tables.

25. PrimaryKey • A primary key is a field, or a collection of fields, whose values uniquely identify each record in a table.

26. ForeignKey • When you include the primary key from one table as a field in a second table to form a relationship between two tables, it is called foreign key in the second table .

27. Relational Database Management Systems

28. Database Management System (DBMS) • A database management system (DBMS) is a software program that lets you create databases and then manipulate data in them.

29. Relational Database Management System (RDBMS) • Data is organized as a collection of tables. A relationship between two tables in a relational DBMS is formed through a common field.

30. RDBMS • A relational DBMS controls the storage of databases on disk by carrying out data creation and manipulation requests.

31. Functions of RDBMS : • It allows you to create database structures containing fields, tables, and table relationships.

32. Functions of RDBMS : • It lets you easily add new records, change field values in existing records, and delete records.

33. Functions of RDBMS : • It contains a built-in query language, which lets you obtain immediate answers to the questions you ask about your data.

34. Functions of RDBMS : • It contains a built-in report generator, which lets you produce professional-looking formatted reports from your data.

35. Functions of RDBMS : • It provides protection of databases through security, control, and recovery facilities.

36. A company benefits from a relational DBMS because it allows several users working in different departments to share the same data. More than one user can enter data into a database, and more than one user can retrieve and analyze data that was entered by others.

37. Starting Access • Click the Start button on the taskbar then point to Programs. • Click Microsoft Access. The Access window appears. • To create a new Access database, select Blank Access database.

38. Starting Access (con’t) 4. In the Save in box, select the folder where you want your database to be located. 5. Type the filename of your database on File name box. 6. Then click Create button.

39. TheAccessand Database Windows

40. Access window title bar Database toolbar Database menu bar Database window List of tables in a database

41. Open Object Design Object Delete Object Create Object Object Display Properties Objects Bar Object List Pane Object Group

42. The Database Window Toolbar

43. object display properties

44. To create a table in a design view: • Select the Table object on the Objects bar. 2. Click the Design button on the Database window toolbar, or double-clickCreate Table in Design Viewon the list pane.

45. A BLANK TABLE IN A DESIGN VIEW Each row in the top half of the Table window represents a field. The bottom half of the dialogue box is split in to two sections: the right-hand pane displays a short description of the selected area of the dialogue box, while the left-hand side allows you to set additional properties for the field selected in the top half of the dialogue box.

46. To add a field to a table There are only two mandatory properties for defining a field: the Field Name and Data Type.

47. DATA TYPE The data type determines what field values you can enter for the field and what other properties the field will have.

48. DATA TYPES FOR FIELDS DATA TYPE: Text DESCRIPTION: Use for names, addresses, descriptions, and fields containing digits that are not in used in calculations. FIELD SIZE: 0 to 255 characters

49. DATA TYPES FOR FIELDS DATA TYPE: Memo DESCRIPTION: Use for long comments and explanations. FIELD SIZE: 1 to 64,000 characters

50. DATA TYPES FOR FIELDS DATA TYPE: Number DESCRIPTION: Allows positive and negative numbers as field values. FIELD SIZE: 1 to 15 digits