Question

Describe in steps the design view method to create a query in MS Access?

Answer 1

Design Method In order to create a query by design method, the following steps are required
(i) Select Queries from Objects list given in LHS of database window, Double click at Create Query by Using Design View given on the RHS.
(ii) Access responds by displaying a Select Query and Show Tables Window. The Select query window is vertically divided into two panes, upper pane and lower pane. The upper pane is meant to display data sources (Tables or Existing Queries) and the lower pane, which is also called Query By Example (QBE) grid, has one column each for field to be included in query being created.
The row of this grid shows field name, table (or query), sort order, whether the selected field is shown in the query results or not and also the criteria that have been applied to the field or fields to restrict the query results. The Show Table Window is meant to add tables, queries or both to the upper pane of Select Query Window. If closed, the Show Table Window can be recalled by a right click at upper pane % show table.
(iii) Click at View item of Menu bar % Total and then % Table Names.
(iv) Click at field row of first column of QBE grid to select the fields to be included in the query. The process is repeated for second and subsequent columns of grid to include more fields in the query. This process of selection constitutes the data items to be displayed by SELECT clause of SQL statement.
(v) The name of table or query is displayed, in accordance with selection of fields. Such tables or queries constitute the data sources shown after FROM clause of SQL statement. However, the initial selection of a table/ query in the second row of QBE grid restricts the choice of fields to the selected table/ query only.
(vi) Click at row of grid to specify the Group by clause and aggregate functions so that a summary query is created.
(vii) Click at row of grid to specify the sort order (Ascending or descending) on field(s). The selected fields for sort order are shown after ORDER BY clause of SQL statement in which ascending order is the choice by default.
(viii) Click at row check for the selected field to be displayed in the query result. The field(s) may be selected only for the purpose of specifying the sort order or criteria.
(ix) Click at row of the grid to specify the criteria to limit the records to be displayed by the query being created. The specified criteria result in a conditional expression, which is shown after the WHERE clause of SQL statement.
(x) Click File % Save (or Press Ctrl $+$ S) to save a query. A dialog box prompts the user to specify the name of the query being created. By default a generic name appears which can be accepted or rewritten with a desired name.