As the name implies, Query pulls out specific data from table fields included in database, thus, setting specific criterion in query helps you to filter down the table field data according to your needs. Through simple logical operators in Query Criteria you can set a criteria by using simple AND/OR operators, which lets you to confine the results of a query based upon the query criteria to check which table field data is to be included.
To begin with, launch Access 2010 and open the database on which you need to set criteria for table fields. For illustration, we have created a database record of employees salary in table fields; Employee_ID, Year Of Service and Salary.
We will start off with creating a query, navigate to Create tab and click Query Design.
Show Table dialog will appear, select the table you want to include and click Add. Once table is added, click Close.
Now double-click table fields which you want to include in Query Design fields.
Here we want to pull a specific record from the table, i.e, details of employees having salary ranges from 4000 to 6000. For this condition, we will be using AND logical operator and write the criteria as;
> 4000 And <6000
Now under Query Tools, switch to Design tab and click Run to execute the query.
It will pull out results that match with the applied criteria.
Now we would like to view employees according to Year of Service criteria, i.e, more than 2 years And less than 6 of service. For applying this condition, under Design Query window, we will write this condition under Year of Service field as;
>3 And <6
On executing Query, it will show employees details who rendered their services for more than 3 & less than 6 years.
For inserting an OR condition, just enter number of Year in OR field. For instance we also want to show employees who worked for 2 years. For this, we will simply add 2 in OR field.
On Query execution, it will show all the employees who have worked for more than 2 years and less than 6.
You can also check out our previously reviewed guides on How to assign Password Data Type To a Specific Field In Access 2010 & How to Create Calculated Fields In Access 2010.