CCNC/CCNC Module 5/Retrieving data with queries/Query Operations

---

Designing a new query

Outcomes

Upon completion of this tutorial the learner will be able to: Open a new query in design Add more than one table to the query design Set the fields for the query Set the sort order for the query

Creating Queries

Figure 1. Database Queries

Creating queries in design mode allows the most amount of flexibility when building a query. In design mode the query is built from scratch. The designer selects the data source [table(s)] and creates joins between tables (if necessary), adds fields to the query, sets sort order and criteria, adds aliases for column names and other design features.

Starting a new design

Figure 2. Query Design

To create a new query in design mode select the "Create Query in Design View..." task.

Setting up the query

Figure 3. Adding tables

The first step in designing a new query is to add the tables that the query will be based upon.

Figure 4. Adding Fields

Once the tables have been added to the query designer the fields need to be selected. Adding the fields is done by selecting them from the available drop-down menu. If more than one table is a part of the query it is also a good idea to review if the table relationship(s) have become a part of the query. Confirm the relationships can be done by looking for a line joining the two tables. If no line exists one of the two fields that create the join can be dragged and dropped on top of the other. This should create the join.

Figure 5. Choosing the sort order

Once all the fields have been added to the query, sort orders can be applied the fields of the query. Its a good idea to limit the number of sorts to three. After that performance of the query will increase as data in the base table increases.

Test your knowledge

Designing a new query

1. A query can include more than one table? TRUE → Absolutely Correct! FALSE → Incorrect, a query can include many tables, these tables should be joined by a relationship. Click here to review the topic. 2. Joins can be created while in the query designer? TRUE → Absolutely Correct! FALSE → Incorrect, joins can be created by dragging and dropping the required fields. Click here to review the topic. Your score is 0 / 0