Creating Criteria

Creating Criteria. Query criteria helps you to retrieve specific items from an access database. If an item matches with all the criteria you enter, it appears in query results. When you want to limit the results of a query based on the values in a field you use query criteria..

Simple example of criteria in access. First open your Access database and then go to the Create tab and click on Query Design..

In the Tables tab on Show Table dialog, double-click on the Employees table and then close the dialog box..

Let us now add some field to the query grid such as Employee ID, FirstName, Last Name, Job Title and Email as shown in the following picture..

Let us now run your query and you will see only these fields as query result..

If you want to see only those whose Job Title are Marketing Coordinator then you will need to add the criteria for that. Let’s go to the Query Design again and in Criteria row of Job Title enter Marketing Coordinator..

Let us now run your query again and you will see that only Job title of Marketing Coordinators are retrieved..

If you want to add criteria for multiple fields, just add the criteria in multiple fields. Let us say we want to retrieve data only for “Marketing Coordinator” and “Accounting Assistant”; we can specify the OR row operator as shown in the screenshot below.

Let us now run your query again and you will see the following results..

If you need to use the functionality of the AND operator, then you have to specify the other condition in the Criteria row. Let us say we want to retrieve all Accounting Assistants but only those Marketing Coordinator titles with “Pollard” as last name..

Let us now run your query again and you will see the following results..

DIFFERENT TYPES OF QUERIES.

Select Query. A select query is a database object that shows information in Datasheet view. A query does not store data, it displays data that is stored in tables. A query can show data from one or more tables, from other queries, or from a combination of the two..

Basic steps to create select query You can create a select query by using the Query Wizard or by working in Design view. Some design elements are not available when you use the wizard, but you can add these elements later by using Design view. Although the two methods are somewhat different from each other, the basic steps are essentially the same: Choose the tables or queries that you want to use as sources of data. Specify the fields that you want to include from the data sources. Optionally, specify criteria to limit the records that the query returns..

Query Wizard to create Select Query On the Create tab, in the Queries group, click Query Wizard. The Queries group in the Access ribbon displays two options: Query Wizard and Query Design..

For each field, perform these two steps: Under Tables/Queries, click the table or query that contains the field. Under Available Fields, double-click the field to add it to the Selected Fields list. If you want to add all fields to your query, click the button with the double right arrows (>>). When you have added all the fields that you want, click Next..

4. If you did not add any number fields (fields that contain numeric data), skip ahead to step 9. If you added any number fields, the wizard asks whether you want the query to return details or summary data. Do one of the following: If you want to see individual records, click Detail, and then click Next. Skip ahead to step 9. If you want to see summarized numeric data, such as averages, click Summary, and then click Summary Options..

5. In the Summary Options dialog box, specify which fields you want to summarize, and how you want to summarize the data. Only number fields are listed. For each number field, choose one of the following functions: Sum The query returns the sum of all the values of the field. Avg The query returns the average of the values of the field. Min The query returns the smallest value of the field. Max The query returns the largest value of the field..

6. If you want the query results to include a count of the records in a data source, select the appropriate Count records in data source name check box. 7. Click OK to close the Summary Options dialog box..

Parameter Query. In Microsoft Access, a parameter query works with other types of queries to get whatever results you are after. It can either be a value or a condition and will essentially tell the other query specifically what you want it to do.

A prompt in a parameter query. When you’re running parameter queries, search terms act as variable criteria, which are query criteria that change each time you run the query..

To create and run a parameter query:. Running a parameter criteria.

Tips for writing parameter queries. Ideally, the prompt you create for your query should make it clear what type of information the search term should be, and what format it should be entered in..

Crosstab Query. Most tables in Access, including ones generated by queries, have records down the side and field names across the top. Crosstab queries produce tables with the values from one field down the side and values from another field across the top of the table. A crosstab query performs a calculation - it sums, averages, or counts data that is categorized in two ways, as defined by the row and column labels..

A select query and a crosstab query displaying the same data.

Diagram of a crosstab query. 1. One, two, or three columns on this side contain row headings. 2. The row headings appear here. 3. The columns on this side contain column headings and summary values. 4. Summary values appear here.

THANK YOU FOR LISTENING.