Creating Queries

Article
11/14/2006

The basic process for creating a query is generally the same regardless of the method you use. The following general process describes creating a query from a single table. You can also create queries from multiple tables.

To create a query

Determine the kind of information you need to find and the table or view from which you need to retrieve the data.
Select the table or view and the corresponding fields that you want data from.
Specify any additional criteria for extracting records from the tables or views.
Generate the query and direct the results to an output source.

When you have created a query you want to keep, you can save it by giving it a name. Queries are stored as files with the .qpr extension.

You can use different methods to create queries:

Use the Query Wizard to guide you through the steps of creating queries.
Use the Query Designer to create and customize queries quickly.
Use SQL SELECT statements, which offer more powerful and flexible ways to generate queries using code.

Creating Queries Using the Query Wizard

If you want help with creating queries, use the Query Wizard. The wizard prompts you to select the tables or views that you want information from and creates a query based on your answers to a series of questions. For more information about the wizard, press the F1 key for Help while you are using the wizard or see Query Wizard.

To create a query using the Query Wizard

Open a free table, or in Project Manager, open a database or a database table.
On the Tools menu, point to Wizards, and click Query.
Select Query Wizard and click OK.
Follow the instructions on the wizard screens.

You can also begin creating a query in the Query Wizard, save the query, and then use the Query Designer to modify it.

Creating Queries Using the Query Designer

If you do not need help with creating your query, you can use the Query Designer. The Query Designer provides a way for you to search for records that meet specified criteria for retrieving information stored in tables and views. You can also organize and group the records as needed and create reports, tables, and graphs based on the results. For more information, see Query and View Designers.

Selecting Tables for Queries

If you are creating queries from free tables or views, you can select the tables or views after starting the Query Designer. However, if you want to select tables or views from a database, open the database before starting the Query Designer. If your tables, databases, or views are in a project, open the Project Manager before starting the Query Designer. For more information about using multiple tables and views, see Querying Multiple Tables and Views.

When adding more than one table to your query, you can specify join conditions to modify the scope of the results returned. Visual FoxPro displays the Join Condition dialog box for you to specify the join condition for each additional table you add to your query. For more information specifying join conditions, see Controlling Record Selection with Joins, Join Conditions for Tables, Queries, and Views, and Join Condition Dialog Box.

To select a free table for a query

From the File menu, click New.
In the New dialog box, click Query, and then New File.
In the Add Table or View dialog box, click Other.
Select a table, and click Close.

To select a database table for a query

Open either a stand-alone database, or in Project Manager, select a database.
On the File menu, click New.
In the New dialog box, click Query, and then New File.
In the Add Table or View dialog box, select a database table, and click Add.
When you are finished selecting tables, choose Close.

The tables appear on the Query Designer surface.

You can also create queries by using the CREATE QUERY command to open the Query Designer. For more information, see CREATE QUERY Command.

Instead of using the automatically generated alias when you add a table or view, you can specify an alias for each table you use by typing it in the Alias box that appears in the Add Table or View dialog box.

Removing and Adding Other Tables

You can remove tables from and add other tables to your query.

To remove a table from and add another table to a query

In the Query Designer, click the table you want to remove.
On the Query Designer toolbar, click Remove Table to remove the table and then Add Table to select another table or view you want.

Building Queries in the Query Designer

After selecting the tables you want to use for your query, you can begin building queries in the Query Designer. You can also select tables and create a query in the Query Wizard, save the query, and then use the Query Designer to modify the query.

To build a query using the Query Designer

In the Query Designer, click the Fields tab.
In the Available fields list, select a field, and click Add.

To select multiple fields, press the SHIFT key while selecting fields, and then click Add.
To specify the order in which fields appear in output, click and hold the vertical double arrow button appearing to the left of the field you want to move in the Selected fields list. Move your mouse to position the field the way you want.

You can customize or define your query further in the Query Designer by performing the following tasks:

Change the join condition of the selected tables in the Join tab.
Filter results by field in the Filter tab.
Organize results by field in the Order By tab.
Group results by field in the Group By tab.
Select a different type of output in the Miscellaneous tab.

For more information about defining your query, see Defining Query Results. For more information about customizing your query and using the tabs in the Query Designer, see Customizing Queries and Query and View Designers. For more information about organizing the results of your query, see Organizing Query Results.

Adding Comments to Queries

You can also add comments or notes about the query to describe its purpose.

To add a comment to a query

Click the Query Designer surface to bring it into the foreground.
On the Query menu, click Comments.
In the Comment box, type comments you want to make about the query.

Comments that you add to a query are stored with the SQL SELECT statement generated by the Query Designer. You can view comments by clicking the View SQL button on the Query Designer toolbar to open the SQL window. Comments that you insert using the Comment box appear above the SQL SELECT statement and are preceded by an asterisk (*).

Note When you modify comments using the Comment box, changes are made only to the first comment and do not affect any other comments you might manually insert into the SQL window.

For more information about the SQL SELECT statement generated by a query, see SELECT - SQL Command. For more information about customizing queries in the Query Designer and using the SQL SELECT command in the SQL window, see Customizing Queries and Customizing Queries Using SQL SELECT Statements.

Share via