Q.4. Build a query

To create a new query, click to expand the Woodview node in the left-hand frame of the Data Source Manager. Two sub-nodes will appear, one called Queries and one called Data Views. Select the Queries node and click Add. A dialog will appear prompting you to specify a name for the query, and to select whether to launch the Query Builder, or enter an SQL statement.

Enter any name you would like, select Open query builder, and click on Ok. The Query Builder will then launch. You will see a separate window containing all of the tables for Woodview sitting over top of the main Query Builder window.

Click to view larger image

From the Tables tab add the Categories, Products, Customers, Order Details, and Orders tables to the query by selecting each and clicking the Add button. As the tables are added they will appear in the top half of the query builder window. The tables will be auto-joined as indicated by the black lines connecting them. Once you have added the tables, click the Close button on the tables window.

Click to view larger image

To add fields to the query you can either double-click the field from within the table window, or you can double-click on the Table and Field fields in the lower-half or QBE (query by example) portions to make selections from a drop-down menu. Using either method, add the following fields to the query: CategoryName from the Categories table, Region from the Customers table, and Quantity from the Order_Details table.

Once you have added the fields, double-click in the Aggregation field under the CategoryName column. Select Group by as the aggregation type. When you click elsewhere in the QBE frame all the other columns will default to Group by as the aggregation. Double-click the Aggregation field under the Quantity column and change the aggregation to Sum.

Click to view larger image

Once you have entered the fields, you can preview the results of the query by clicking on the Datasheet View tab. The query will run and you will see the total sales volume broken down by product category and sales region.

Click to view larger image

Once you have finished building the query, select FileDone. The Query Builder will close and the Data Source Manager will come back up. There is now a node under Queries for the newly created query.