Blog#14 : Creating a simple query for a given or required Criteria/Value

In the previous blog we learned to create a simple query using the Query Wizard, where in we created a simple query to list all records from the Pincode_Data table for only two columns/ fields of our choice.

Here in this blog we will try to learn a create query that lists records from the “Pincode_data” table for a given criteria, i.e to have a list of records for a given input.

We will use the sample example of the previous query to understand this point. In the Query that was created (Query1), we only requested to list all records from the “Pincode_ data” having columns/fields of our choice (we chose to list the field “Regionname” and “Pincode”). Now suppose we want the list of records with the above columns/fields but only for the city “Mumbai” from the “regionname” field/column. How do we create a query that lists the fields “regionname” and “Pincode” pertaining to the city “Mumbai”. the city “Mumbai” is the criteria for filtering the data.

Following Steps are to followed to create an Query for the above output.

Open the database “Learning Access“.
Select the table “Pincode_data” with a single click from the left Window (Do not Open the Table)
Click on “Create” using the Menu.
Click on “Query Wizard“.
Select “Simple Query Wizard” from the dialogue box and then click “OK“.
Select the fields/columns named “Officename“,”Pincode” and “regionname” and click on the “>” symbol. The selected column moves from the left to the right side of the list.
Once all the required columns/fields are selected and moved from the “Available Field” to “Selected Field“, click on “Next“.
The last section of the wizard help you name the Query (by default it suggests a Name), we will rename it to “Query1 with Criteria“.
Select the option “Modify the Query design” using the radio button and click on “Finish“.
The Query is opened in the design Mode for you to modify as shown below.
As shown above you can see three fields/column that have been selected.
You need to enter the Criteria “Mumbai” in the cell below the “Tick Mark i.e you need to Type “Mumbai” in the row named “Criteria” and under the column named “regionname” as we need to filter the records for “Mumbai” under the regionname column/field.
Save the Query using the Save option.
View the results of the Query using the “Run” Option under the Menu and the results are displayed.
The created query (Query1) cane been seen on the left pane under the heading queries.
The query would also show the column “regionname” in the output. In case you do not want the field/column “regionname” to be displayed in the output, simply untick the tick mark below the column name “regionname” at the time of creation of Query. This can be done by opening the Query in Design View as shown in the attached Video.
The saved query can also be viewed in “Design View” by right clicking the query and selecting the “Design View” Option.