1 / 36

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

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 MyITLab Lesson D is due on 11/04/2013

anakin
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

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

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

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

  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.

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

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

  11. 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)

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

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

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

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

  16. SQL KEYWORDS

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

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

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

  20. 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”

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

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

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

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

  25. SQL Continued • DML – Data Manipulation Language • DDL – Data Definition Language • JOIN – Forms relationships between tables

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

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

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

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

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

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

  32. 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” . . .

  33. 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;

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

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

  36. Questions ?

More Related