Day 21: MICROSOFT ACCESS – CHAPTER 5 Microsoft access – chapter 6 Microsoft access – chapter 7

Day 21:MICROSOFT ACCESS – CHAPTER 5Microsoft access – chapter 6Microsoft access – chapter 7 AkhilaKondai akhila.kondai@mail.wvu.edu October 30, 2013

ANNOUNCEMENTs • Homework # 4 is due on 11/01/2013 • MyITLab Lesson D is due on 11/04/2013 • Homework # 5 is now posted and available at http://cs101.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 MyITLab.

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.

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 ……

Structured Query Language (SQL) • Standard computer language for retrieving and updating data within database systems. Database applications like MS Access, DB2, MS SQL, Oracle, Sybase, MySQL, 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

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 Customertable 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_idcommon field • When expressing the Join, the syntax will include “customer.address_id” and “address.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.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.

Questions ?

Day 21: MICROSOFT ACCESS – CHAPTER 5 Microsoft access – chapter 6 Microsoft access – chapter 7