Daylight savings time begins this weekend

1. “I do not fear death. I had been dead for billions and billions of years before I was born, and had not suffered the slightest inconvenience from it.”  -Mark Twain

2. Daylight savings time begins this weekend Use this time (as well as when it ends) as a point when you change your smoke detector batteries.

3. Day 21: Access Chapters 5,6,& 7 Cody Cutright CS 101 October 30, 2013

4. Upcoming Dates • Homework IV • Friday November 1st, by 11:55pm • MyITLab Lesson D • Monday November 5th, by 11:55pm • Homework V • Friday November 8th by 11:55pm

5. Exam Dates • Section 8 (MW 11:30am) • November 13th • Section 10 (MW 12:30pm) • November 11th

6. Data Validation • Definition: A set of constraints or rules that help control how data is entered into a field. • Some data validation is done automatically • Access won’t allow you to enter text into a number field

7. Data Validation Properties • Required – Sets the Required property of a field to force data entry. i.e. LastName • Default Value – Specifies a value that is automatically entered into a field. • Validation Rule- Limits the type of data to be entered into a field. i.e. Course Numbers may need to be >100 • Validation Text: Provides an error message telling users what they did wrong, and how they can fix it.

9. SQL • Stands for Structured Query Language • Pronounced as  "sequel“ • Specially created programming language used to manage data in a relational database management system • Whether or not you realize it, you’ve been using SQL in Access all along!

10. What we see

11. WHAT ACCESS SEES

12. The Result

13. Why is this relevant? • On exams, and homework 5, we will give you SQL code and you need to copy and paste it into Access! • (We will do this as part of an In-Class Example) • You could, in theory, write these SQL statements from scratch… but that would be another course entirely.

14. In-Class Example - SQL

15. Importing Data from Excel • a. Open Access. In the File tab, click Save Database As, and then type LastName_FirstName_Import.Click Save. • b. Click the External Data tab, and click Excel in the Import & Link group to launch the Get External Data - Excel Spreadsheet feature. Select the Import the source data into a new table in the current database option. • c. Click Browse, and select the Data File. Click Open, and then click OK to open the Import Spreadsheet Wizard.

16. The top of the first window shows all of the worksheets in the workbook. This particular workbook contains only two worksheets: Customers and Accounts. The Customers worksheet is active, and a list of the data contained in the Customers worksheet displays in the Wizard. • e. Click Accounts, and then click Next. • f. Ensure that the First Row Contains Column Headings check box is checked to tell Access that column headings exist in the Excel file. The field names, AID, CID, BID, and Balance, will import from Excel along with the data stored in the rows in the worksheet. The field names will be modified later in Access. • g. Click Next. The AID (AccountID) will become the primary key in this table. It needs to be a uniqueidentifier, so we must change the properties to no duplicates.

17. h. Ensure that AID is displayed in the Field Name box in Field Options. Then click the Indexed arrow, and then select Yes (No Duplicates). Click Next. • i. Click the Choose my own primary key option. Make sure that the AID field is selected. Click Next. The final screen of the Import Spreadsheet Wizard asks you to name your table. The name of the Excel worksheet was Accounts, and Access defaults to the worksheet name. • j. Click Finish to accept the Accounts table name. A dialog box opens asking if you wish to save the steps of this import to use again. If this were sales data that was collected in Excel and updated to the database on a weekly basis, saving the import steps would save time. You do not need to save this example.

18. k. Click the Close button. The new table displays in the Navigation Pane and resides in the Bank database. • l. Close the table.

19. Once more! Now, we’ll import additional data…

20. a. Click the External Data tab, and then click Excel in the Import & Link group to launch the Get External Data - Excel Spreadsheet feature. Select the Import the source data into a new table in the current database option, if necessary. • b. Click Browse, and then go to the Data File. Select the a02h2bank workbook. Click Open, and then click OK to open the Import Spreadsheet Wizard. • c. Click Next. • d. Ensure that the First Row Contains Column Headings check box is checked to tell Access that column headings exist in the Excel file. Click Next. The CID will become the primary key in this table. It needs to be a unique identifier, so you change the properties to no duplicates.

21. e. Ensure that CID is displayed in the Field Name box in Field Options. Click the Indexed arrow, and then select Yes (No Duplicates). Click Next. • f. Click the Choose my own primary key option. Make sure that the CID field is selected. Click Next. Access defaults to the table name Customers. • g. Click Finish to accept the Customers table name. • h. Click Close on the Save Import Steps dialog box. The Navigation Pane contains two tables: Accounts and Customers.