Setting Criteria In A Query

Setting Criteria In A Query:
Sorting fields and setting criteria are what makes a query valuable. If you can sort a field in ascending, descending, or in combination, there are limits to these actions. The criteria setting allows you be even more specific.

1. Make sure your qryEmployees query is in Design View. To see only the people who were hired before 1995, in the Criteria row for the DateHired field, type <#1/1/96#. This means "show me the people whose DateHired field is less than January 1st, 1996", which means anybody hired before 1996. Then run the query.
2. Switch back to Design View and delete the criteria set in DateHired.
3. To find out which employees live Maryland, in the Criteria field for State, type ="MD". Run the query.
4. To find out which ones of the employees don't live in MD, change ="MD" to <>"MD". Run the query.
5. To get the list of employees whose salaries range from $10 to $15, in the Criteria for Salary, type >=10 And <=15. Run the query.
6. Switch to Design View and delete the Criteria set for Salary.
7. To run the same query, in the Criteria for Salary, type Between 10 And 15. Run the query, then switch back to Design View, and delete the Criteria under Salary.
8. To get a list of married employees (and invite them to the Employees Marriage Conference of the Greater Washington DC(?!?)), in the Criteria box for MaritalStatus, type True and run the query.
9. Switch back to Design View. To run the same query, replace True with On.
10. To print a list of married employees, on the Standard toolbar, click the Print button.
11. To get a list of the employees who hold a function in the company, switch the query to Design View. In the Criteria for the Function field, type Is Not Null and run the query. Switch back to Design View. To get a list of employees who don't hold any function in the company, replace Is Not Null with Is Null and run the query. Switch back to Design View and delete the criteria.
12. Switch to Design View and delete the Criteria set for MaritalStatus.

Combining Sort And Criteria:
The most useful thing about a query is to get the right information and efficiently. This comes from running a query whose sort and criteria fields have been set appropriately. Just like you can combine Sort retrieval, you can combine criteria fields, sort fields, and combinations of lots of them. Sometimes, the purpose is to narrow your search as much as necessary.

The first thing you need to know about combining fields is that they use Boolean algebra. Let's review it quickly. If I say, "the dog is in the house", that condition is TRUE whenever the dog is in the house. If I say, "the dog is in the house and the door is closed"; that statement is true only if the dog is in the house AND the door is closed; if the dog is in the house and the door is opened (NOT closed), the statement is FALSE, the same goes if the dog is outside but the door is closed. If I say, "the dog is in the house or the door is closed"; if one of these two situations is TRUE even if the other situation is false, the statement is true. All of that was plain English, wasn't it? The tricky part lies somewhere else. Mathematicians have had the (silly) idea of starting some sentences negatively. If in English I say, "the dog is not in the house and the door is closed"; if the dog is NOT in the house and the door is closed, the statement is TRUE, but the dog has to be NOT in the house. In Boolean Algebra, that would be written, "NOT the dog is in the house and the door is closed". I know you understand it because somebody is just plying with our... mind.

1. To get a list of employees whose salary is greater than $10 AND live in MD, in the Criteria for Salary, type >=10; then in the Criteria for State, type =MD (the query will change it to ="MD"). Run the query.
2. Switch back to Design View and delete both criteria.
3. To get a list of the company's employees who earn more than $11 AND are married AND don't live in Maryland, in the Criteria, for the Salary field, type >=11, for the MaritalStatus, type True, and for State, type <>"MD". Run the query.
4. Switch to Design View and delete all of the preceding criteria.
5. To get a list of employees who live in Maryland OR in Virginia, for the State field, in the Criteria row, type MD Or VA, then run the query.
6. Switch to Design View. To run the same query, in the Criteria under State, type MD, press the down arrow key (that should take you to the OR field of the Criteria for State), then type VA. Run the query.
7. Switch to Design View and delete the preceding criteria. Finally, to see an Ascending order list of employees who live either in Maryland or in Arlington (VA), set the Sort order of LastName to Ascending; in the OR field for City, type "Arlington"; and in the Criteria field for State, type "MD". Run the query.
8. Switch back to Design View. delete all of the Sort, Criteria, and OR fields. Run the query. Make sure you have all data.
9. Save the qryEmployees  query and close the database.

Getting Help In Microsoft Access:

1. Click the Office Assistant and type Query.
2. Click Create a query.