Day 22: Access Chapters 5,6,& 7

1. Day 22: Access Chapters 5,6,& 7 NamanKohli November 5, 2013

2. Upcoming Dates • Homework 5 • Friday November 8th by 11:55pm • Exam Review Session • Friday November 8th at 12pm • EXAM 2 – Access • 12th November at normal class time

3. REPORT DESIGN • Did not cover design themes in reports last class. • The reports can have specific designs.

4. 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

5. Establishing data validation • Establish REQUIRED fields. • Set DEFAULT VALUE for fields. • Set VALIDATION RULE and generate VALIDATION TEXT. • Use INPUT MASK wizard. • Look up Fields. ( already done in homework's )

6. Establish REQUIRED fields • The required fields must not be left blank. • Lets try • Open blank table in design view • Field 1 -> Student ID : Number • Field 2 -> Student Name : Text and in Properties set “REQUIRED” to “YES” • Switch to data sheet view and try to enter a record with out giving Student Name, you will see an error

7. Set DEFAULT VALUE for fields. • Delete the records first • Switch to design view: • Field 3 -> Score : Number • Set DEFAULT VALUE property to 30 • Observe records.

8. Set VALIDATION RULE and generate VALIDATION TEXT. • Validation rule is designed to restrict the data values that can be entered into a field. • Validation text informs users that the validation rule has been broken, and how to correct it. • Lets try : Set validation rule and text for SCORE FIELD • Delete all the records first and remove the default value set. • Validation rule: [Score] <= 60 • Validation Text: You can’t enter a value greater than 60. Please enter correct value. • Now try to enter 90 in Score field for a record and observe the warning message.

10. 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!

11. What we see

12. WHAT ACCESS SEES

13. The Result

14. Why is this USEFUL? • On exams, and homework 5, SQL code will be given 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.

15. In-Class Example - SQL

16. 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.

17. 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.

18. 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.

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

20. HOMEWORK 5HINTS

21. HOMEWORK 5 Hints • Query 6E • States, FraudRankings and Fraud Types Table • Rank equal to 1 • Have to include rank (may not be immediately visible to some)

22. HOMEWORK 5 Hints • Query 6G • FraudRankings and Fraud Types Table • Rank equal to 1 • Use totals to count state abbreviation

23. Analysis Questions • If you run out of room to type responses, change the field data-type to memo.

24. EXAM 2

25. General hints FOR ACCESS EXAM • Read the instructions all of the way through • Things that future steps ask you to do may give you a hint how to complete earlier steps

26. General hints • Examine given tables for good ideas of what to name your fields Another good reason to read the whole assignment first!

27. General hints • Examine given tables to understand what the formatting should look like

28. General Hints FOR ACCESS EXAM • Close tables as you are done with them. • Remember, that Access is designed so multiple people can work on a database at once • This means that an open table is “locked” and nothing that references it can have changes made to it

29. General Hints FOR ACCESS EXAM • The MOST IMPORTANT THING you can do is be positive your tables are correctly designed • Improper table design will render your database unusable. • End of story.