Microsoft Access

1. Microsoft Access Database Creation and Management

2. Overview of Access Database • One of database management systems software. • Access, SQL Server, Oracle, DB 2 • MS Access is a relational database. • a collection of tables that are related to one another based on a common field.

3. A schematic diagram of a relational database (a) and a sample part of a relational database showing different tables (b) Relational Database

4. Open an existing database • Create a folder by your last name in My documents oron Desktop • Download“Restaurant1”database intothe folder by your last name then, open the “Restaurant1”database • To open an existing database, you must first start Access • When Access is launched you will see the Access window, with the task pane on the right side of the window. • From the task pane, you can open an existing database. • Or simply double-click the existing database to open.

5. How Access creates and saves a new database • Create a new database • Your first activity (before question #1) for the midterm is creating a new database. • Database name: your last name + first initial of first name • When you press the Save button in Access, you are saving the design of the Access objects and NOT the database itself! • The Save function in Access differs from the Save function in other Windows programs.

6. Characteristics of Relational DB • In a relational database, each record (row) in a table must be uniquely identified. • Using Primary Key • A relational database is a collection of tables that are related to one another based on a common field. • Using Foreign Key (& Primary Key)

7. Primary Key (PK) & Foreign Key (FK) • PK: A field thatuniquely identifies each record in a table. • SS#, Student ID • It does not have to be first field. • FK: A field that connects one table logically with another table • Rule of Thumb: PK = FK (value) – see next slide

8. Relating tables using PK and FK The primary key in the Employer table (EmployerID) is the common field that relates this table to the Position table. PositionID is the primary key in the Position table. The EmployerID field is a foreign key in this table. Primary keys can only have one occurrence in a table. Foreign keys may have multiple occurrences.

9. Valle Coffee’s Restaurant DB • Valle is a small distributor of inexpensive coffee beans to various restaurants. Barbara Hennessey, the Director of CRM, and her staff use Access to maintain company data such as customer orders and billing, coffee supplier orders and payments, and advertising placements and payments. Barbara has a recently developed database named Restaurant 1to track the company’s restaurant customers, their orders, and related data such as the products they order. She asks for your help in completing and maintaining this database.

10. Descriptions of Restaurant DB • Valle coffee’s Restaurant 1 database will contain five tables: • Customer table, which Barbara already has. • Order table, which you will create soon. • Product and Order Detail tables, which you will import from FineFood database. • Billing Address table that is in Excel format and you will import it, and then convert to Access table.

11. Creating a Order table • Barbara wants to track information about each order placed by each restaurant customer. This information includes the order’s billing date and invoice amount. Barbara asks you to create a second table in the Restaurant database, named Order, in which to store the order data.

12. Creating a Order table • Use Design view • Case sensitive • NUMBER vs. number • Exact match of each name • Use tap key to enter data • Especially, when you enter data in datasheet view.

13. Access data (field) type Make certain the field type you select matches the data to be held in that field.

14. Access data (field) type (con’t) Additional Access field types.

15. Creating the Order table

16. OrderNum CustomerNum BillingDate PlacedBy InvoiceAmt 323 624 02/15/2001 Mike Smith $1,986.00 201 107 01/15/2001 Matt Davis $854.00 Adding Records to a Table • Enter data sequentially in Datasheet view • Do not jump from field to field • Use tap key to enter data

17. Modifying a Table • From the Order table • Delete the PlacedBy field • Move the BillingDate field to the end of the table • Insert the Paid as a new field between CustomerNum and InvoiceAmt (position) fields • data type: Yes/No • default value: No (means “unpaid”) • Add following data to each filed: 211, 201, paid (mark the check box using space bar), $703.50, 01/15/2001

18. Practice: Creating a DB • Barbara needs a database to track the coffee products offered by Valle Coffee. She asks you to create the database by completing the following: • In the initial Microsoft Access dialog box, click the blank Access database option button, and then click OK button. Click the Create button the new database. • YOUR LAST NAME DB file name • Display the Table window in Design view (if necessary), and then create a table using the table design shown in the next slide.

19. Practice: Creating a Table • Specify ProductCode as the primary key, and then save the table as Product. • Add the product records shown in next slide table to the Product table. (Hint: You must type the decimal point when entering the Price field values.)

20. ProductCode CoffeeCode Price Decaf BackOrdered 2316 JRUM 8.99 Yes 9754 HAZL 40.00 D Yes 9309 COCO 9.99 D No Practice: Modifyinga Table • Add a new field between the CoffeeCode and Price fields, using these properties; • Field Name: WeightCode • Data Type: Text • Description: foreign key • Field Size: 1 • Move the Decaf field so that it appears between the WeightCode and Price fields.

21. Practice: Updating aTable • Enter these WeightCode values for the three records: A for ProductCode 2316, A for ProductCode 9309, and E for ProductCode 9754. •  Add a record to the Product datasheet with these field values: • ProductCode: 9729 • CoffeeCode: COLS • WeightCode: E • Decaf: D • Price: 39.75 • BackOrdered: Yes

22. Data Integrity (PK Rule) • No “null” value can be allowed. • No two records can have the same primary key. • No two CSUB students can have same ID number. • A PK can be “composite key” • Example on class web page • “Composite PK Example” • More than one field can be used as a PK (composite) • CSUB: student ID + SS#

23. Referential Integrity (FK Rule) • A field that connects one table logically with another table. • Exception example on class web page • “PK as FK” there are two tables… • Related table: Shipping Address table • Primary table: customer table

24. Importing External Access Table and Excel Worksheet • Barbara also wants you to include the Product and Order Detail tables from the FineFood database in the Restaurant database. • Download and Review design view of FineFood DB first

25. Importing External Access Table and Excel Worksheet (con’t) • And she wants you to include the Billing Address Excel worksheet as a Access table in the Restaurant database. • Before try to import the excel file, review it first • Use Excel column headings for Access table • PK:CustomerNum • Specify in the description area of Design View that CutomerNum is not only primary key of BillingAddress table but also a foreign key of Customer table.

26. Access is a relational database • Access allows you to form relationships between the tables; that’s why it’s called a relational database • The simplest way to create a relationship • Look for identical field names between tables. • Tables can be joined in three ways; one-to-one, one-to-many, and many-to-many.

27. An example of a one-to-many relationship The Employer table is related to the Position table via the common field EmployerID. The Employer table has one record for EmployerID value 10126. The Position table has two records.

28. A one-to-one relationship • A one-to-one relationship exists when one table has one record associated with only one record of another table. • As we saw from PK as FK database • Shipping Address table is an related table. • Primary table: customer table • Primary key is also foreign key: only Access

29. Using referential integrity • Referential integrity allows you to maintain the integrity and consistency between related tables. • If you choose to enforce referential integrity, you can insure that you will not have records that have no matching record in the primary table. • The rules associated with referential integrity specify that when you update or delete a record (PK) in the primary table, a matching record (FK) in the related record must be updated or deleted.

30. Use cascade update and cascade delete • In referential integrity, there are two options. • If you choose cascaded updates, making a change in a field that is common to two related tables will cause the update to be made in both tables. • If you delete a field that is common to two tables, the deletion will take place in both tables. • Enforcing Access Referential integrity Rules • TryReferential Integrity Example DB on the class web page.

31. Creating Relationships • DownloadRestaurant2 file from the class web • Create relationships using 5 table • In terms of creating a relationship between Customer and BillingAddress, start from the Customer table. • Enforce both cascade options • Primary key of the Order Detail table • Combination of OrderNum and ProductCode • Otherwise, a duplication of the quantity field in both the Order and Product tables.

32. Practice: Creating Relationship 1 • Create a blank database (use any name you like) And then, import the three Excel Worksheets (Course, Instructor, and Membership) from the class web site into your Access database. • Define each imported table’s primary key using information below: • Course table: Class_Number • Instructor table: Employee_Number • Membership table: Member_Number

33. Practice: Creating Relationships 2 • Establish relationship based on common fields. • And enforce referential integrity (apply both options) among three imported tables

34. What is an Access query? • You can create a query when you need ONLYa portion of the dataform tables (or existing queries). • For example, you may only need to see customers who live in CA. The response would be to display only the records whose state field matches with CA. • Multiple tables or queries can be used. • Restrictions can be used • Comparison operators • The design view is used to specify the fields and records you want to see.

35. The Query Design view window Fields used in the query are selected from the field list and added to the design grid. You can run the query at any time by clicking the Run button.

36. Creating a Sample Query • Kim Carpenter, the director of marketing at the Valle, wants a list of all restaurant customers so that her staff can call customers to check on their satisfaction with Valle coffee’s services and products. She wants to have only followings; CustomerName, City, State, OwnerName, and Phone. • Use restaurant 3 database • Save the query as First Customer List.

37. OrderNum ProductCode CustomerNum OrderNum CustomerNum ProductCode CoffeeName CustomerName CustomerNum BillingName Qty Street Weight/Size Paid Price City InvoiceAmt Decaf State Street ZipCode BillingDate OwnerName City Phone State FirstContact Zip 2301 201 000 2834 Colombian Aged Crop Choi 11 COB 105 1 lb pkg 7.99 CSUB CA 93311 Scott Choi 5348 09/20/2001 129 201 107 Sandy Lookout Restaurant No 854.00 PO Box 2800 01/15/2001 Grandville MI 49468 Billing Address Table Customer Table Order Table Order Detail Table Product Table

38. More about a Query • When you use the query design window, you use Query By Example (QBE). • Do not include any unnecessary tables or queries in Design View of the query. • Otherwise, you have to deal with unexpected problems. • In your midterm, you will be penalized if you include unnecessary tables or queries. • Always use a primary table. • CustomerNum: Customer vs. BillingAddress • Restrictions can be used as well.

39. Creating Queries using Multiple Tables • Since there have been some major changes in the Restaurant database, Barbara wants to make sure that the database is reliable. In order to test the reliability of database query function, She wants you to generate a query. The query must include following fields: Order Number, Coffee Name, Quantity, Price, and Weight/Size. • Save as Test • Should select OrderNum from the Order table (primary table)

40. Querying and Sorting of multiple Tables • Barbara wants to have following information for the upcoming quarterly report: CustomerName, City, State, BillingDate, InvoiceAmt, Paid, and First Contact. • At same time, Barbara wants to sort the records by the InvoiceAmt field in ascending order. • Barbara also wants to move the Paid field between the State and BillingDate field. • Save as Customer Orders

41. Expressions Words begin or end with a:LIKE a* or LIKE *a Find between values: (NOT) BETWEEN 45000 AND 78000 Find exact match value:1/10/2005

42. Find an Exact Match • Barbara would like to have a list of all orders billed on 01/15/2001. • The list must include following fields; OrderNum, Paid, InvoiceAmt, BillingDate, CustomerName, State, OwnerName, and Phone • Save as Jan Orders

43. Using Like a* • Barbara wants to know a list of all Customer Names that begins with M. The list must include following fields; Customer Name, Order Number, First Contact and Billing Date. In addition, the Order Number must be sorted in ascending order. • Save as Customer Name Begins With M

44. Using Between ** and ** • Barbara wants to know a list of all Product Code between 2465 and 2763. The list must include following fields; Product Code, Coffee Name and Price. • There should a space between number and word • Save as Product Code Between 2465 And 2763.

45. Using Greater than (>) • Barbara wants to know those records whose InvoiceAmt exceeds $2,000. • Use the Customer Orders query. • The query must include only following fields; Customer Name, Invoice Amount, and Billing Date. • Save as High Invoice Amounts

46. UsingAnd and OrOperators The And condition. The Or condition.

47. Creating And and Or conditions in the design grid And conditions must be specified on the same line. Or conditions must be specified on different lines.

48. Using AND • Leonard asks Barbara for a list of orders billed on 01/15/2001 that are still unpaid. He wants to know which customers are slow in paying their invoices. • Use the Customer Orders query again and make sure that this query includes following fields; CustomerName, Paid, BillingDate, and InvoiceAmt. • 01/15/2001 AND unpaid (No) • Save as Unpaid Jan Orders

49. Using OR • Leonard wants to determine which restaurant customers are most valuable to Valle Coffee. Specifically, he wants to see a list of those customers who have been placing orders for many years (specifically, first contact date - earlier than 01/01/1994: <=01/01/1994 ) OR who place orders for a substantial amount of money (greater than $2,000: >2000), so that he can call the customers personally and thank them for their business.

50. Using OR • Make sure that this query includes following fields; CustomerName, InvoiceAmt, and First Contact. • He also wants to have a sorted output in ascending order based on the customer name. • If you want, you can use the Customer Orders query again • Save as Top Customers