"MODIFYING A QUERY" > Page 1, 2, 3
III. Removing Fields p>Often, you'll need to remove unnecessary information from a query. If the field in question is not a component of any criteria or sort orders that we wish to maintain, the best option is to simply remove it from the query altogether. This reduces the amount of overhead involved in performing the query and maintains the readability of our query design.
1. Click on the field name. Single click on the name of the field you wish to remove in the query table. In our example, we want to remove the CompanyName field from the Suppliers table.
2. Open the Edit menu and select Delete Columns. Upon completion of this step the CompanyName column will disappear from the query table.
IV. Adding Criteria
We often desire to filter the information produced by a database query based upon the value of one or more database fields. For example, recall that our purchasing department was only interested in those products with a small inventory and no products currently on order. In order to include this filtering information, we can add criteria to our query in the Design View.
1. Select the criteria field of interest. Locate the field that you would like to use as the basis for the filter and single click inside the criteria box for that field. In our example, we'd first like to limit the query based upon the UnitsInStock field of the Products table.
2. Type the selection criteria. For a complete look at the allowable criteria, see these examples of criteria expressions from Microsoft. Continuing with our example, we want to limit our results to those products with less than ten items in inventory. To accomplish this, enter the mathematical expression "< 10" in the criteria field.
3. Repeat steps 1 and 2 for additional criteria. We'd also like to limit our results to those instances where the UnitsOnOrder field is equal to zero. Repeat the steps above to include this filter as well.
V. Hiding Fields
Sometimes we'll create a filter based upon a database field but won't want to show this field as part of the query results. In our example, the purchasing department wanted to filter the query results based upon the inventory levels but did not want these levels to appear. We can't remove the fields from the query because that would also remove the criteria. To accomplish this, we need to hide the field.
1. Uncheck the appropriate Show box. It's that simple! Just locate the field in the query table and uncheck the Show box by single clicking on it. If you later decide to include that field in the results just single click on it again so that the box is checked.
Next page > Sorting the Results > Page 1, 2, 3
