Partager via


Query Wizard

With the Query wizard, you can select a group of records based on some rules you specify, For example, if you have tables that contain large amounts of information in one or more tables, you can use a carefully designed query to collect or display just a few records that satisfy some numeric or logical value.

To access the Query wizard

  1. From Tools menu, choose Wizards, and then click Query.

  2. In the Wizard Selection dialog box, select Query Wizard.

Step 1 – Select Fields

In this step, you can choose free tables or tables within a database as the source for your query. You can select fields from one or more tables or views.

To select the fields for your query

  1. Use the Databases and Tables controls to locate and select the tables or views you want to use.

  2. In the Available fields window, select one or more fields you want to use from the selected table, and use the arrow buttons to move them to the Selected fields window.

Repeat this process to add fields from other tables or views.

Step 2 – Relate Tables

This step displays only if you select fields from more than one table or view. You can specify which fields in each table or view contain the same information and can therefore govern the relationship between the tables or views, thereby determining the included records.

Select the desired fields from the two drop-down list boxes, and then choose Add. If you use multiple tables in your view, then you must relate the tables by indicating which fields contain matching data in each table.

Step 2a – Include Records

This step displays only if you select fields from more than one table or view. You can specify which records from the selected tables will be made available to the Query wizard.

If you are using more than one table, you can specify a join condition. For more information, see How to: Create a Multitable View. For example, if you specify only matching rows in Step 2a, you can further refine that choice in Step 3 by specifying a particular value for a field.

  • Only matching rows
    Returns only records from both tables that match the comparison condition set between the two fields in the join condition. This is called an inner join.

  • All rows from this table
    Returns all the rows from one of the listed tables or views. This makes it possible for you to create a left or right outer join.

  • All rows from both tables
    Returns matching and non-matching records from both tables. This is called an outer join.

By default, only matching records are included.

Step 3 – Filter Records

This step appears immediately after Step 1 if you have chosen only one table or view as source for your query. You can specify a filter condition, so only certain of the records you have chosen to make available are used in the query.

To determine the filter condition

  1. Select a field from the Field drop-down list.

  2. Specify an operator, such as equals or contains, in the Operator drop-down box.

  3. Specify a value in the Value textbox.

    For example, if you wanted to filter on a specific city, such as Helena, you might choose city from the Field drop-down list, choose equals in the Operator drop-down list, and enter Helena in the Value textbox.

You can reduce the number of records by creating expressions that filter records from the selected tables or views. You can create two expressions and connect them with And, which returns only records meeting both specified criteria, or Or, which returns records meeting either criteria.

You can see the result by clicking the Preview button.

Step 4 - Sort Records

In this step, you can specify the sort order of your query records. For example, if your query will be used to assess orders by region, then you might sort by state or zip code.

Choose up to three fields or an index tag that already exists in the database to determine the order in which your view results will be sorted. Select Ascending to sort the view in ascending order or Descending to sort the view in descending order.

Step 4a – Limit Records

This step appears only if you have specified one or more sort fields in Step 4. You can further limit the number of records in the view, based on either a percent of the records returned or an actual number of records.

To see a percentage of the available records, choose the Percent of records radio button, and then specify the percentage in the Portion value edit box. See all records by selecting Number of records and then selecting All records. As shown in the following procedures, you can choose a portion of records from the beginning or the end of the available records.

To see the first 10 items

  1. Choose the Number of records radio button

  2. Enter 10 in the Portion value box.

To see the last 10 records.

  • In Step 4, change the sort order to Descending.

Note

This changes the sort order to last record = first read.

  1. In Step 4a, select the Number of records radio button.

  2. Enter 10 in the Portion value box.

Step 5 - Finish

In this step, you can choose how to save your query.

  • Save query
    Makes it possible for you to save the query for later use.

  • Save query and run it
    Makes it possible for you to save the query and run it immediately

  • Save query and modify it in Query Designer
    Makes it possible for you to save the query and then use the Query designer to enhance or modify it.

See Also

Tasks

How to: Define Query Results

How to: Create Persistent Relationships Between Tables

Reference

Local View Wizard

Remote View Wizard

Other Resources

Wizards (Visual FoxPro)

Query and View Designers