microsoft access

1. Microsoft Access Advanced

3. Your Expectations/Experience?

4. Class Objectives Overview of some basic database design principles Create a sample MS Access database from scratch Modify database objects Learn how to import data from other programs We are going to show you some of the more advanced features of MS Access. In order to do this, we will give you a brief overview of how databases are designed. We are going to show you some of the more advanced features of MS Access. In order to do this, we will give you a brief overview of how databases are designed.

5. Definitions MS Access - software used for creating databases Data are just information Database Collection of data/information Related to a particular topic or project

6. Basic Database Design Principles Avoid Duplication Tables, fields, records Enforce Data Integrity Data are referenced throughout the entire database so that changes made in one table affect other tables Ensure Data Accuracy Formats (mm-dd-yyyy), pick lists, controlled vocabulary

7. Main Parts of Database Design Mission Statement Tables Fields Identifying Relationships Enforcing Integrity

8. Sample Database Scenario A VERY simple example A small business owner with three staff members needs to contact customers by phone.

9. Mission Statement A mission statement clearly explains the purpose of the database Sample mission statement: The contact management database will keep track of customers, the calls made, and by whom.

10. Mission Statement >>Tables The mission statement is used to develop a list of tables. �The contact management database will keep track of customers, the calls made, and by whom.� Each table represents a single subject, object or event. Avoid duplication

11. Tables Customers �Keep track of customers� Calls �the calls made� Employees �and by whom� Avoid duplication! Avoid duplication!

12. Tables >>Fields Tables are made up of fields that represent a characteristic of the subject, object or event For example, each person has: First name Last name Phone number Avoid duplication by specific naming

13. Fields in the Calls Table These fields represent characteristics of the Calls: Call ID Customer ID Employee ID Call Date Call Time Call Subject Call Notes

14. Required Fields Each table must have a Primary Key field to uniquely identify a record to establish relationships between tables Typically, it is an auto-generated number Foreign Key fields are required only: to facilitate relationships between tables to enforce data integrity Primary Key; foreign keyPrimary Key; foreign key

15. Fields in the Calls Table: Primary Key These fields represent characteristics of the Calls: Call ID Customer ID Employee ID Call Date Call Time Call Subject Call Notes

16. Fields in the Calls Table: Foreign Keys These fields represent characteristics of the Calls Table: Call ID Customer ID Employee ID Call Date Call Time Call Subject Call Notes

17. Main Parts of Database Design Mission Statement Tables Fields Identifying Relationships Enforcing Integrity

18. What You Don�t Know � Can Cause You Problems Database design requires: Thinking through what you want and need out of your database Assessing whether or not you have the skills to create that database Recognizing when you need database programming expertise

19. MS Access and its Templates Can Help You Test your database design ideas Determine if your database is simple or complex Create a simple database from scratch Create a more complex database using templates Identify when you need a database programmer Help you explain what you want to a database programmer, so the results meet your needs

20. Questions

21. Now for the Hands-On Practice! MS Access Tutorial