Creating Queries

When you create tables and forms for your application, you can add queries and reports to select and display data for your users. Your queries can be directed to a variety of destinations, so you can use them in the other components of your application. You also can run reports separately without using a query. This section highlights some of the ways you can use queries and views, add reports, and expose queries and reports to the user.

When you use a query or a view in your application, you are using a SELECT - SQL statement. This section describes how you can use a SELECT - SQL statement in your application whether you create it through a query defined in the Query designer, a view defined in the View designer, or code entered for an event or procedure.

With the Query Designer, you can search for records that meet the criteria you specify. In addition, you can order and group the records as required and create reports, tables, and graphs based on the results.

If you want a custom, updatable data set for your application, you can use views. Views combine the qualities of tables and queries: similar to a query, you can use a view to extract a set of data from one or more related tables; as with a table, you can use a view to update the information in the view and permanently store the results to disk. In addition, you can use views to take your data offline to collect or modify data away from your main system. For details, see Creating Views.

You may want to create queries for various reasons: to organize information for a report, to answer a quick question, or to see any relevant subset of your data. Whatever your aim, the basic process of creating a query is the same.

When you need answers quickly, use the Query Designer to retrieve the information stored in your tables and views. For example, you might need to know the answers to questions like:

• What customer name and address is associated with a particular ID number?
• How many orders are outstanding?
• How many customers have overdue balances?
• Which region or salesperson has the best sales record?

When you add queries to your application, you can combine a variety of data sources, finely filter records, manipulate data, and sort the results — all with the SELECT - SQL statement. By using the SQL statement, you have complete control over the results your query produces and where the results are stored.

A query is a SELECT - SQL statement

You can add SELECT - SQL statements to procedures or to event code. For more information about events, see Understanding the Event Model.

Creating SELECT - SQL Statements

To create a SELECT - SQL statement

• Use the Query Designer or the View Designer to build the statement, and then copy the contents of the SQL window into a code window.

  -or-

• In a code window, type the SELECT - SQL statement.

For example, you can select all the records from the Customer table in the TasTrade database where the country field contains the value "Canada":

SELECT * ;
   FROM tastrade!customer ;
   WHERE customer.country = "Canada"

To execute the command immediately, you can enter the statement in the Command window. If you want each clause to appear on a separate line within the window, end each line except the last one with a semicolon so that Visual FoxPro processes the command only after the last line.

Creating a Query Using the Query Designer

Using the Query Designer, you select the table or view you want information from, specify the criteria to extract records from the table or view, and then direct the results of your query to the type of output you want, such as a browse window, report, table, label, and so on. When you've created a query you want to keep, you can save it by giving it a name. Queries are stored as files with a .qpr extension.

After you have decided what information you need to find, and what table or view it is stored in, you'll go through the following general steps when creating queries.

To create a query with the Query Designer

1. Start creating the query with a query wizard or the Query Designer.
2. Select the table or view that holds the information you want to retrieve.
3. Define your query results by choosing which fields you want, the order you want the fields to appear, and the records you want.
4. Organize your query results by ordering and grouping the rows in the results.
5. Choose the type of output you want for the query: table, report, browse, and so on.
6. Run and verify the query.

Starting the Query Designer

If you don't want to use a query wizard, you can use the Query Designer to create your query. You can start the Query Designer from the Project Manager or from the File menu.

To start the Query Designer

1. In the Project Manager, select the Data tab.
2. Select Queries.
3. Choose New.
4. Choose New Query.

You can also start the Query Designer by choosing New from the File menu, selecting the Query option, and selecting New File.

When you create a new query, you will be prompted to select a table or view from the current database or from a free table.

After you select a table or view, Visual FoxPro displays the Query Designer window.

You can easily change to a different table or view.

To use a different table or view

1. Select the current table and choose the Remove Table button on the Query Designer toolbar.
2. From the Query Designer toolbar, choose the Add button, and then select the table or view you want.

Creating a Query Using a Query Wizard

If you want help creating a query quickly, use one of the Visual FoxPro query wizards. A query wizard asks you in which tables or views you want to search for information, and sets up a query based on your answers to a series of questions.

For more information about the query wizards, press F1 for Help while you are using the wizard.

To create a query with a wizard

1. In the Project Manager, select the Data tab and choose Queries.
2. Choose New.
3. Choose the Query Wizard button.
4. Select the type of query you want to create.
5. Follow the instructions on the wizard screens.

You can also choose Wizards on the Tools menu to start a wizard.

See Also

Defining Results | Querying Multiple Tables and Views | Query Designer | Organizing Results | Updating Data in a View | Creating Views