Creating Queries  «Prev  Next»
Lesson 6 Creating a query in Design view
Objective Create a query in Design view.

Creating Query in Design View

Now that you have been introduced to the Query Design view, it is time to try creating a query using Design view.
Generally you will use Query Design view to create a query that combines fields from different tables. You may also add criteria to the query so that you see a subset of all records. However, as I mentioned earlier, if you want to calculate summary data, you will find that the Simple Query Wizard is the best way to go--at least until you learn about grouping in Design view. After you create a query in the Design view, you will view it in the query datasheet.
Creating a query in Design view is similar to using the Advanced Filter/Sort feature. To create a simple query there are only two steps:

Using Query Datasheet

The query datasheet not only looks like the table datasheet, it is like the table datasheet. Just about anything you can do with a table datasheet you can do with a query datasheet. That includes:
  1. Entering data: When you enter data in the query datasheet, the data appears in the underlying table.
  2. Editing data: When you edit data in the query datasheet, the data is changed in the underlying table.
  3. Navigating: You can navigate a query datasheet using all the techniques you learned with the table datasheet.
  4. Printing: Print the datasheet by using the Print button.
  5. Filtering data: You can filter the query datasheet by selection, exclusion, or by form. Of course, it is more permanent to make theBnecessary changes to the query definition by using the query design.
  6. Sorting: Use the Sort Ascending and Sort Descending buttons to sort the data using the field the cursor is in.B
When using a form in Datasheet view, you automatically have the option to sort on any column using the column header (exactly like an Excel spreadsheet).

Specifying how to view the Form

Access uses several properties to determine how a form is viewed. The Default View property determines how the data is displayed when the form is initially opened:
  1. Single Form: Displays one record at a time. Single Form is the default and displays one record per form page, regardless of the form's size.
  2. Continuous Forms: Shows more than one record at a time. Continuous Forms tells Access to display as many detail records as will fit onscreen.
    Figure 3-6 shows a continuous form displaying five records.
  3. Datasheet: Row and column view like a spreadsheet or the standard query Datasheet view.
  4. Split Form: Provides two views of the data at the same time, letting you select a record from a datasheet in the upper section and edit the information in the lower section of the split form.

There are three separate properties to allow the developer to determine if the user can change the default view. These include Allow Form View, Allow Datasheet View, and Allow Layout View. The default setting is Yes for Allow Form View and Allow Layout View and No for Allow Datasheet View. If you set the Allow Datasheet View property to Yes, the Datasheet view commands (in the Views group of the Ribbon, the form's View Shortcuts, and right-click pop-up menu) will be available and the data can be viewed as a datasheet. If you set the Allow Form View property to No, the Form view commands won’t be available.

Removing the Record Selector

The Record Selectors property determines whether the Record Selector (the vertical bar shown in Figure 3-6: on the left side of a form) is displayed. The Record Selector is important in multiple-record forms or datasheets because it points to the current record. A right arrow in the Record Selector indicates the current record, but changes to a pencil icon when the record is being edited. Though the Record Selector is important for datasheets, you probably will not want it for a single record form. To remove the Record Selector, change the form's Record Selectors property to No.

The Continuous Forms setting of the Default view property shows multiple records at once.
Figure 3-6: The Continuous Forms setting of the Default view property shows multiple records at once.

  1. Add the tables you need to the query.
  2. Put the fields you want to see in the design grid.

This Simulation takes you through the creation of a simple query:

Creating Access Query in Design View

  1. To create a new query in Design view, display the Queries view in the database window and double click the
    “Create query in Design view” option.
  2. What you see here is the query Design view, with the Show Table dialog box open on top of it. The Show Table dialog box is what you use to add tables to the query. This query will contain fields from the Clients and Projects table, you will need to add them both. Click Add to add the Clients table (which is already selected) to the query. Double-click the Projects table to add it to the query also.
    Then click the Close button to close the Show Table dialog box.
  3. Here you see the query Design view with the table boxes in the top pane and the blank design grid in the bottom pane. Notice that the relationship between the tables is marked with a familiar line. If the tables are not linked with a relationship, the query results will not be what you expect. To create a query, you need to add fields to the design grid. The easiest way to add fields is to double-click the field names in the table boxes in the top pane. Add the following fields to the query: First Name, Last Name and Project Description.
  4. You have now defined a simple query that will combine data from two different tables. To see the results of the query, click the View button (the first button on the toolbar).
  5. Here are the results of the query in a datasheet. To return to the Design view, you can click the view button again, just like moving between a table datasheet and the table design.
Design View designer allows the user to build forms in a grid-based layout, where all of the sections of the form are broken up to show as separate parts.
Both designers have their pros and cons, but you will probably find yourself using both to build forms in Access, depending on the specific task at hand.

Learn the different ways to save a query design.

Creating Query in Design View - Exercise

Try your hand at creating a query in Design view.
Creating Query in Design View - Exercise