Introduction to Access

1. Introduction to Access • Busn 111 - Intro to Business Computing • Edward Mitchell

2. What is Access? • Access is a “data base” program for storing and retrieving information • While you can store data in Excel lists and tables, Access is better for storing large quantities of data • Access can store many types of information more efficiently - meaning less storage, less duplication of data

3. Other kinds of databases • Access is a “relational database” - which for now we will leave as a “fancy name” - but implies a certain kind of flexibility and power unavailable other forms of data storage • Flat-file database - old fashioned library card catalogs, a simple list of information in Excel

4. Flat File + and - • Flat files are easy to understand and setup • Flat files can be inefficient at storing information (narration covers example of a customer order at an online store) • Duplicated data leads to errors

5. Relational databases • A relational database stores information in tables • Properly structured, is more efficient and eliminates duplication of data

6. Relational Example • We have a table containing the customer’s order • Rather than storing the customer information with the order, we store only a unique identifier for the customer (for simplicity, let’s say the phone #) • In a separate Customer table, we store all of other customer information

7. Relational Example 2 • When the customer places an order, we store the customer’s phone # in the order table with the order. • We use the phone # to cross reference to the Customer table. • For each order, we have only one copy of the Customer contact information

8. Relational Example 3 • By storing only one copy of the customer data, rather than storing with each order, we use up less disk storage • If the customer moves, we only need to update a single entry in one table. • We do not need to find all previous orders and change those too.

9. Access “Tables” • Information is stored in “tables” • Think of a table as containing a “record” of related data such as name, address, city, zip, telephone and email address. • A “customer table” then contains a collection of data records, each record having information about one customer

10. Tables and “records” • The data stored in each “record” is actually contained in “fields”. • For example, “Name”, “Address”, “City”, “State”, “Zip” and “Telephone” are six separate fields to hold each value.

11. Relationship Concepts • The Customer_Order table • The Customer_Info table • Linked or “related” by a relationship. • Orders are made-by customers • Thus, each order is linked to a customer - in our example, through the phone #.

12. Multiple Tables • Each access database can contain multiple tables • We could store the Customer_Orders and Customer_Info tables inside the same Access database file

13. Retrieving Data • We search for data in the tables (database) using a “Query” • A query is - in some ways - similar to the “criteria” we used in Adv Filter in Excel. We use the query to select records that match our query criteria.

14. Creating a new database • Creating a new database means defining the tables, and the field within each table • It also means defining the “type” of the data that goes in each field • This can require some tedious, detail oriented work to define each field in your table

15. Using “Wizards” • For many applications, you can use one of Microsoft’s database creation wizards • There are many types of “standard” database designs that you can choose from, and customize • The wizard creates the tables, fields, data definitions for each field and more

16. Entering and Editing Tables • We can enter and edit data directly in a table • A table “view” looks very much like an Excel worksheet of columns of data

17. Forms • We can create a “form” for data entry and editing • A “form” is like a dialog box

18. Selecting a Template

19. Using the template Wizard

20. Selecting Fields for Records in Table

21. Order Information Table

22. Various options

23. Naming database

24. Finish and Build

25. Initializing database

28. Using Table Entry

29. Intro to Access • This concludes the basic introduction to Access and databases • Next time we will begin some specific exercises to practice creating tables • Homework: Read pages 1259 to 1275 in Chapter 46.