‘Select query’ in access is basically used for creating subsets of data that you use to answer specific questions. Besides this it is also used to supply data to other database objects. In this topic we will discuss about how to create a simple query that searches the data in a single table. Also learn how to enhance the query by defining record criteria and adding calculated values.
In this the option of query design is basically the "Design View" for queries. It offers more options for creating queries than the query wizard. At the time of creating query you can either click the Query Wizard button or Query Design button. Clicking the query design button takes you to Design View. In design view one can easily build a query, test it, modify it, test it again and many more.
After understanding the basic building blocks of design view, you will also prefer design view for query creation instead of Query Wizard. So, let’s start knowing how it is to be created.
Go to the Create tab in the ribbon and click to the Query Design option here.
Select all the tables that you want in the query and then click Add to add it to the query.
After adding all the tables, click Close to close the dialog box.
So now you are in design view. The top pane is the Diagram Pane which contains the table that you have previously selected. These are the tables that are used for the query.
In the bottom pane, fields are kept that you will actually use in the query. As well as filtering criteria that we specify.
Make a double tap from a table in the top pane to make it appear in the bottom pane too. Repeat the same for each field that you would like to and in the query.
By default the show option is checked. This indicates that the field will be displayed when the query runs.
You can now run the query if you wanted. However mostly you probably want to add some filtering criteria to the query.
Once the table is configured make sure to save it. In any case, you will be prompted to save it when you try to switch to Datasheet View or close the table.
After adding the fields to include in the query, we can add certain against those fields.
To specify a sort order, click in the sort row for the fields you wants to sort by and do it in ascending or descending order.
Apply filtering criteria against a field add that criteria into criteria row for that field.
You can uncheck the show option to apply criteria against a field that is not actually displayed in the results.
Click Run in the Ribbon to run the query.
You can also run the query simply by switching to datasheet view using the view button on the ribbon or the datasheet view icon present in the bottom right corner of access.
The query will display the results in datasheet view. The entire field that have checked show option are been displayed.
If you want to run the query again later on then you need to save it first. Saving a query is like saving any other database object.
1. Make a right click over the query tab and select save from the contextual menu.
.png)
2. Assign a name for the query and click ok.
.png)
3. Query is now added to the left navigation pane, under a queries heading.
After saving you can go back and modify the query at any time by right clicking in the navigation pane and selecting Design view.
.png)