1. Tip: Opening a single file in an email, or from the internet, can compromise your entire computer. http://en.wikipedia.org/wiki/Remote_administration_tool

2. Day 14:Access Chapter 2 Cody Cutright CS 101 February 23rd, 2014

3. About Computer science • My mission as a CS 101 Instructor is to give you all the tools you need to succeed in the business world in relation to Microsoft Office. That being said, I’m also available before class if you want to discuss anything technology related (even after class if you’re particularly interested some days). • “Knowledge is power, and knowing is half the battle.” - Every GI Joe PSA… ever

4. Upcoming due dates • 2/28: Midpoint of semester • 3/07: Last day to drop • 3/10-3/14: Spring Break • 3/17: MyITLab Lesson C

5. Chapter 2 Objectives • creating tables (Design View) • fields • keys • data types • lookup fields • indexing • relationships window • creating relationships • specifying relational integrity settings • importing data and tables • importing from XML files (content in Chapter 17) • importing from Excel spreadsheets (content in Chapter 17) • creating and modifying queries • creating using Query Wizard (brief overview) • using Design View • specifying tables • adding fields • sorting • specifying criteria

6. Table Views – Datasheet View The Datasheet view of an Access table resembles an Excel spreadsheet and displays data in a grid format—rows represent records, and columns represent fields.

7. Table Views – Design View Design view is used to create and modify the table structure by adding and editing fields and by setting the field properties.

8. Referential Integrity Why is referential integrity important? It makes sure that a foreign key in a table exists as a primary key in a related table.

9. Referential integrity

10. Input mask • Makes sure the data you enter is in a correct form • For example, (33)324-99 is not a valid phone number! • But a number of the form (999) 000-0000 is!

11. Creating Tables In Design View, we will create three tables: Customers, Orders, and Items

12. Customers Table: AccountNumber AccountType LastName FirstName RepName Phone Address City State Zip RepeatCust?

13. To do: Delete the ID Row Account Number Data Type Account Type Lookup Phone Input Mask ZipCode Input Mask RepeatCust? Yes/No Index by Address What’s missing? Now, save the table

14. Orders Table: OrderNum AccountNum RepName ItemSKU

15. To do: Delete the ID Row AccountNum -> What to do? ItemSKU ItemSKU Data Type? Save table changes

16. Items Table: ItemSKU ItemName Set Key Save Changes

17. Relationships: Close any open tables. Access simplifies creating relationships, what relationships should our tables have? Design -> Relationships

18. Creating Relationships: • Show all tables • Link from Orders to Customers with AccountNumber • Enforce referential integrity • Create • Link Orders and Items via ItemSKU • We will assume each order has only one item • Save Relationships

19. Data Validation • Data Validation is a formula that can be specified to help ensure incorrect data is not entered. • In our example, let’s assume we’ve started our order numbering at 1,000 after switching over from an old system (to preserve records). Let’s create a data validation rule and validation text.

20. More Practice! • http://cs101.wvu.edu/instructors/cutright/lecture-notes/