Computer Class Lecture Notes (Grade 7)

Welcome to our Computer Class GRADE 7 LEVEL UP! CREATE QUESRIES 6  WHAT IS DATABASE? & WHAT IS A QUERY? DATABASE ▪ It is an organized collection of data or information that is related to a particular subject or purpose. QUERY ▪ It is a database objec...

Welcome to our Computer Class GRADE 7 LEVEL UP! CREATE QUESRIES 6  WHAT IS DATABASE? & WHAT IS A QUERY? DATABASE ▪ It is an organized collection of data or information that is related to a particular subject or purpose. QUERY ▪ It is a database object that is used to view, change, and analyze data in different ways. ▪ It can be used to filter data, perform calculations, and summarize data. ▪ There are 2 ways to create queries in Access: Query Wizard and Query Design. 7  WHAT IS A QUERY? QUERY WIZARD ▪ It is used to create queries such as a Simple Query, Crosstab Query, Find Query, and Unmatched Query. QUERY DESIGN ▪ In this query, the Show table dialog box will allow you to choose the appropriate fields from the tables or queries in your database. 8  WHAT IS A QUERY? CROSSTAB QUERY FIND QUERY UNMATCHED QUERY QUERY SIMPLE WIZARD QUERY 9  WHAT IS A QUERY? QUERY DESIGN 10  TYPES OF QUERY 1. SELECT QUERY ▪ It is most commonly used type of query. ▪ It retrieves records from one or more tables and displays the results in the order you want in the datasheet view. 2. ACTION QUERY ▪ It is used if you want to make changes to any records or move a group records. ▪ It will show an exclamation point (!) along with a specific identifier icon in the Navigation Pane. 11  TYPES OF QUERY 3. CROSSTAB QUERY ▪ It is used to calculate and restructure data for easier analysis. ▪ By using it, you can calculate the sum, average, count, or other types of totals for data that is grouped by two types of information. 4. PARAMETER QUERY ▪ It is a query that when it runs, will display is own dialog box prompting for information, such as criteria for retrieving records or a value you want to insert in a field. 12  THE QUERY CRITERION QUERY CRITERION ▪ It is an expression that you type into the Criteria cell of the query or filter grid. ▪ It is also referred to as the Expression in Access. EXPRESSION ▪ It is a fundamental part of the Access operation. ▪ It is similar to a formula which is a combination of symbols, identifiers, operators, and values. 13  COMPONENTS OF AN EXPRESSION 1. LITERAL VALUES ▪ These are values such as numbers, characters, or dates that MS Access will evaluate exactly as written. ▪ For example: “Jeffrey”, 1435254, or #06/18/1981# 2. CONSTANT ▪ These are elements in an expression with values that do not change will automatically be defined by Access such as True, False, or Null. 14  COMPONENTS OF AN EXPRESSION 3. FUNCTIONS ▪ These return the value based on the results of the calculations or other operations. ▪ For example: Date(), Sum(), Avg() 4. IDENTIFIERS ▪ These refer to the value of the field, control, or property. ▪ For example: Forms![Students]![Last Name] ▪ This example refers to the value in the Last Name control on the Students form. 15  EXPRESSION EXPRESSION BUILDER ▪ It provides you with easy access to the names or fields, controls in your database, and built-in functions available in Access. EXPRESSION BOX ▪ This is the area where you can build your expression. ▪ You can use the Expression Elements section to locate the functions, fields, and operators of the expression to be created. 16  EXPRESSION EXPRESSION ELEMENTS ▪ It is the first or the left column that displays the folder with the list of tables, queries, forms, and reports in your database. EXPRESSION CATEGORIES ▪ It is the middle or second column that displays the specific fields or functions of the selected object on the left column. EXPRESSION VALUES ▪ It is the right or third column that displays the list of values, if any, 17 for the elements you selected in the first and second columns.  PARAMETER QUERY ▪ A query that when it runs, it will display its own dialog box prompting you for information, such as criteria for retrieving records or a value you want to insert in a field. ▪ In entering a parameter, instead of typing the criteria or expressions into the criteria cell, type a text string that would appear as a prompt for the dialog box. ▪ It could be in the form of a question or a simple text that will prompt you on what to type. ▪ You should enclose the text in square brackets ([]). 18  WHAT TO DO? Answer Worktext 3.2 “Check What You Have Learned!” (RN I) on COMPUTER7 p. 101. 19