Access Chapter 3: Creating and Using Queries

Comparison Operator: Between

- Between these two values - Example: Between 10 and 20

Comparison Operator: In

- Contained in this list of values - In(1,5,10)

Criteria for Short Text and Long Text Data Types

- Enclosed within quotation marks. - Access ignores the case of the text in the criterion when making an evaluation.

Comparison Operator: =

- Equal to - Example: ="CA"

Query

- Finds data in your database by allowing you to ask a question. - For example, in a music database you can create a simple query that shows all the albums that you own.

Comparison Operator: >

- Greater than - Example: >#1/12014#

Comparison Operator: >=

- Greater than or equal to >="B24"

Comparison Operator: <

- Less than - Example: <5

Comparison Operator: <=

- Less than or equal to - Example: <=100

Comparison Operator: <>

- Not Equal to - Example: <>0

Most Common way to make a Query

- Query Design

OR Logical Operator

- Requires a record to meet at least one of the individual conditions specified in order to be included in the results. - With the OR operator you can specify multiple criterion in one field or you can use compound criteria across several fields.

AND Logical Operator

- Requires that a record meet all of the individual conditions specified in order to be included in the results. - An AND condition can be used in one field or across multiple fields.

Comparison Operator: Like

- Similar to this value - Like "C??"

Criteria for Yes/No Data Types

- Use either Yes or No. - Use Yes to find records where the check box is selected and use. - No to find records where the check box is not selected.

Comparison Operator

- Used to compare two values in an expression. - Usually the comparison operator appears at the beginning of the expression, although that depends on the exact structure of the expression.

Single Criterion

- You only enter the criteria value. Access evaluates this criterion as an "equal to" comparison. - In other words, it will find all data that is "equal to" the criterion.

Parameter Rules

- Your parameter is the specific phrase that you want to display in the dialog box, for example "Enter the City". Be sure to enter a phrase that gives clear instructions about what kind of information to enter into the dialog box, along with any formatting instructions. - Enter your parameter into the Criteria row cell for the desired field. - Enclose the parameter within square brackets. - You can include a comparison operator, like < or >=, before the parameter.

Constants

A value used in a formula.

Compound Criteria

Combine more than one criterion using the AND or OR logical operators and allow you to ask questions using multiple conditions.

Criteria for Date/Time Data Types

Enclosed within pound (#) symbols.

Calculated Field

Field that gets its value from a calculation instead of a value that is entered into the field.

Aggregate Function

Function that performs calculations on a group of records such as Avg , COUNT, and SUM.

Criteria for Number, Currency, and AutoNumber Data Types

Include the actual number value not surrounded by any special symbols.

Unique Values

Query property that directs Access to display only records that do not include duplicated values in the fields included in the query.

Unique Records

Query property that directs Access to display only records that do not include duplicated values; checks all fields in the table, whether or not they have been included in the query.

Summary Query

Query that analyzes a set of records and displays summarized results.

Parameter Query

Query that asks a user to enter a specific criterion value.

Select Query

Query that locates, or selects, data in a database.

Wildcard Characters

Symbol used in an expression in a query to find records that match, or in a validation rule to require data being entered to match a specific character, number, or pattern.

Total

Table Style option showing color and text emphasis; in Access, row added in Design view of query that specifies the aggregate function to perform.

Simple Wizard Query

Tool that guides you through the steps to create a query.

IntelliSense

Tool that suggests a list of possible values when you enter an expression.

Expression

Type of formula used to write validation rules, perform calculations, create query criteria, control macros, and group and sort data in a report.