DAY 21 MICROSOFT ACCESS CHAPTER 5 MICROSOFT ACCESS

DAY 21: MICROSOFT ACCESS – CHAPTER 5 MICROSOFT ACCESS – CHAPTER 6 MICROSOFT ACCESS – CHAPTER 7 Akhila Kondai akhila. kondai@mail. wvu. edu October 30, 2013

ANNOUNCEMENTS • Homework # 4 is due on 11/01/2013 • My. ITLab Lesson D is due on 11/04/2013 • Homework # 5 is now posted and available at http: //cs 101. wvu. edu/instructors/kondai/assignments/. It would be due for 11/08/2013 • Start working on them right away! • Email me or visit open lab for assistance in home works or My. ITLab.

• Data Validation Techniques • Importing and Exporting Data • Using SQL in Access

DATA VALIDATION TECHNIQUES • Data validation is a set of constraints or rules that help control how data is entered into a field. • Those are Field Properties. • Each Data type has its own properties. • Let us see some frequently used Constraints or properties.

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 )

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

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.

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.

USE INPUT MASK WIZARD. • Input Mask Wizard generates an input mask for a field based on your answer to a few questions. • Lets try: Delete all records • Switch to Design view: • Add a new field Phone Number: Text • Note: works with Text/Date fields only • Now click on Input Mask Wizard Option in Properties • Choose Phone Number and go on……. • Switch to Data Sheet view and try to add phone number and see what's happening.

• Data Validation Techniques • Importing and Exporting Data • Using SQL in Access

IMPORTING DATA INTO ACCESS • We can import data from Access / Excel / Text / XML file and more… • Note for TEXT file : comma to next column and enter to next row. ( similarly to EXCEL)

EXPORTING DATA FROM ACCESS • You can export the data from number of records in access to Excel / text / XML / PDF / Access files and more …… • Try those ……

• Data Validation Techniques • Importing and Exporting Data • Using SQL in Access

STRUCTURED QUERY LANGUAGE (SQL) • Standard computer language for retrieving and updating data within database systems. Database applications like MS Access, DB 2, MS SQL, Oracle, Sybase, My. SQL, etc. use SQL. • We have been using graphical tools to develop and manipulate database objects thus far…

SQL CONTINUED… • Access has written the SQL statements behind the scenes for us. • The SQL language can be separated into two sub sets: Data Manipulation Language (DML) and the Data Definition Language (DDL). • We will look mostly at DML aspects.

SQL KEYWORDS 16

SELECT STATEMENT • Retrieves all of the data in the fields specified from the specified database table. • Syntax is: SELECT <columns> FROM <tables> – <columns> is a comma separated list of column names to be returned – <tables> is the tables where the <columns> are located…

SELECT STATEMENT • The <columns> can be specified as *, which will return all columns that are in the <tables>. • <columns> can also be aggregate functions (i. e sum, count, etc) • Let’s try it!

LET’S TRY IT THE OLD WAY FIRST… Ø Obtain and open Customer_orders. accdb Ø Create a query in design view Ø Add the Customer table only Ø Add all fields Ø Run the query Ø Click “View” and change to “SQL View” to see what was done behind the scenes…

SELECT EXERCISE Ø Close the Query without saving changes Ø Create a new query in design view. Ø Don’t add any tables Ø Change to “SQL View” Ø Type SELECT * FROM customer Ø Run the query by clicking “!”… Ø Words in capital are “reserved words”

SELECT EXERCISE Now, let’s revise our SQL SELECT… Ø Go back to SQL View and make it read: SELECT first_name, last_name FROM customer Ø Run the Query We are shown only the fields we requested from the table we told it to use…

ONE MORE… Say we wanted to have it ALIAS a column for us which combined first and last name field values and separated them with a space… Ø SELECT (first_name + “ ” +last_name) AS [Full Name] FROM customer Ø Run it! We are presented a field that does not actually exist in the table as we gave it as alias.

WHERE CLAUSE FOR CRITERIA • This allows us to specify criteria at the command line so that only things matching will be returned. • The following are legal operators: = <> < > <= >= Between Like

WHERE CLAUSE Ø Let’s modify our last select to add criteria to it… SELECT (first_name + " " + last_name) AS [Full Name] FROM customer WHERE last_name=“doe” Ø Run it. Ø Have a look in regular Design View and widen out the first field. ØSee the alias name and the criteria

SQL CONTINUED • DML – Data Manipulation Language • DDL – Data Definition Language • JOIN – Forms relationships between tables

DATA MANIPULATION LANGUAGE (DML) • The DML is SQL Queries (or commands) that will manipulate the data within a database: – SELECT– extends or “selects” data from a database table(s) – UPDATE– changes or “updates” data from a database table – DELETE – removes or “deletes” row(s) from a database table – INSERT INTO – adds or “inserts” row(s) of data into a database table

DATA DEFINITION LANGUAGE (DDL) • The DDL are the SQL commands that define the structure of a database: – CREATE TABLE – creates a database table – DROP TABLE – deletes a database table – ALTER TABLE – modifies a database table

JOIN CLAUSE • Links tables together to form relationships through the tables’ primary and foreign keys • There are three main types of joins: – INNER JOIN – LEFT JOIN – RIGHT JOIN

INNER JOIN • Returns all rows from both tables where there is a match and will exclude the rows where a match is NOT made • We will now do an INNER JOIN for customer and address tables. • These tables are relational based on primary and foreign keys.

INNER JOIN EXERCISE • We will use the syntax as table. field to specify what tables to pull field values from. èLet’s look at the customer and address tables to see the fields called “address_id” in each. . . è “address_id” is a primary key to address table and foreign key to customer table.

INNER JOIN EXERCISE • Let’s say we wanted to join the customers with their addresses to make a multi-table query. . . • We would like the following fields – [Customer] table • first_name • last_name – [Address] table • Street • zipcode

INNER JOIN EXERCISE • We need to create an inner join on the customer and address tables on the address_id common field • When expressing the Join, the syntax will include “customer. address_id” and “address_id”. . .

CREATE THE SQL COMMAND • Create a query without adding any tables • Flip to SQL view and create this command: SELECT customer. first_name, customer. last_name, address. street, address. zipcode FROM customer INNER JOIN address ON Customer. address_id = address_id;

LEFT JOIN • Will return all the rows from the left, or first, table and only the matching rows from the right or second, table.

RIGHT JOIN • Returns all of the rows from the right, or second, table and only the matching rows from the left, or first, table.