Quick Query

Overview

Quick Query is a data acquisition companion for Quick Reports 3. It's purpose is to provide visual tools for building an SQL query. The user doesn't need to know how to write SQL commands. Quick Query contains options for selecting fields from multiple tables and linking the tables with SQL commands. A user can thereby make a table based on this query. This feature is particularly useful to Quick Reports 3 users because the results of the mutli-table query can be used for a Quick Reports 3 report.

Path

The Path page is used to locate and select the path to a database. Sometimes a database is a collection of files stored in a folder on your hard drive or on a file server; other times a database is a database server which has no path in the local file system.

Quick Query provides support for both types of databases. When your tables are stored on a local hard drive you can browse to select the folder where your database is stored.

In some cases your dBASE software has a shortcut that points to the folder containing the database. This is called a database alias. If you know the name of the database alias it can be selected from the "Alias" combo box. When you choose this option the path to your tables will be automatically entered in the "Data Path" entry field.

If your tables are stored in a database server (like Oracle, SQL Server or Interbase), or if they are stored in a Microsoft Access database, you must use the "Alias" combo box. There is no path to these tables, so, in this case you can not use the "Data Path" entry field or its pushbuttons.

Use the "Tables" button on the page navigation bar when you have completed your Path selection.

Tables

The Tables page is used to select the tables for your query. Tables are stored in a database.

After selecting the database where your tables are stored, Quick Query will provide a list of the available tables in the database. To create a SQL query, you must add one or more tables to the "Selected Tables" list box.

Use the "Fields" button on the page navigation bar when you have completed your table selection.

Fields

The Fields page is used to select the fields for the query. The fields are displayed for whichever table is current in the Tables combo box. Use this combo box to switch among the selected tables. And use the pushbuttons to add or removes fields from your query.

The "Remove Duplicates" option can be used to produce a query with distinct values. Note that SQL considers the entire row when determining whether the data is distinct. This option is not based on any one field.

Quick Query does not support calculated fields, like Price * Quantity or FirstName || LastName. However, if you know a little SQL, you can use the Custom Query page to add calculated fields to your SQL command.

Join

When you have two or more tables in your query you must create a "Join" that links the tables. SQL Joins enable your query to retrieve data from two or more tables and merge the data into a single results table. Sometimes this results table is referred to as a "flat file."

Joins are created between two tables by using a common field. These are called key fields.

SQL supports two kinds of joins. The most common join is an "Inner Join" which returns only those rows that match the join condition. For example if you join a Customers table to an Orders table and John Smith does not have any rows in the Orders table, then the customer John Smith will not be included in the results table.

Conditions

The Conditions page is where you tell your query to retrieve only those rows that meet specific conditions.

Quick Query provides support for comparison operators (<, >, <=, >=, =, <> and Like) and logical operators (and & or). Quick Query does not, however, support Between, Containing, In, Is Null, Starting With, and Not operators.

When you use the "is Like" operator, your condition can use standard SQL pattern matching characters. Use a percent sign (%) to match zero or more characters. Use an underscore (_) to match a single character. For example, to select names that end in "an", you would enter "%an" (without the quotes) in the entry field. To select names that begin with "M", have exactly two more characters and then a "D", followed by anything else in the remainder of the name, you would enter "M__D%" (without the quotes).

Sort

The tables in your database do not store the rows in any particular order. So when you create a query you will often want to specify the order in which to sort your rows. The field (or fields) used to sort your query must be included in the results table (that is, they must be in the Selected Fields list).

SQL queries do not support dBASE indexes, even when the queries are based on dBASE tables. For this reason, when you create a temporary table for use in Quick Reports 3 you should sort the data in the order that will be needed in the report.

When you sort your results table by two or more fields, the first field is the primary sort order, and the second field is the secondary sort order. Thus if you want your report to group by customer and then by date (e.g. like a monthly account statement), then the customer should be the primary sort field and the date should be the secondary sort field. On the other hand, if your report is a daily list of customer activity, the date should be the primary sort field and the customer name should be the secondary sort field.

Quick Query does not currently support descending sort orders (DESC).

Group

SQL provides aggregate functions that calculate a single value from a group of values. Aggregate functions can be used to summarize the rows contained in your query. The SQL Grouping function should not be confused with the groups that you would use in a Quick Reports 3 report. If you plan to use groups in your report, you do not need to use the SQL Grouping function. However, if you want a quick summary of your data, the SQL Grouping function is the tool to use.

When you run a query that has both aggregate and non-aggregate fields, you MUST use the Group By function with each non-aggregate field. All the fields in the "Selected Fields" list box must be added to the "Group and Summary Fields" list box. If there is a field that you do not want summarized, use the Group By function.

Custom

The Custom page is used to view the actual SQL SELECT command that is built by Quick Query. It can also be used to edit the SQL command. Any SQL keyword and/or operator supported by dBASE can be used in this editor control. Use the insert button to build the SQL command from your Quick Query choices. Then edit the statements as needed. It goes without saying that this is not a task for novice.

When you edit an SQL command, the Quick Query output buttons will use the modified text contained in the custom editor. However, if you save a custom SQL command, Quick Query may not be able to reload that command.

Reload

In addition to saving the results table, Quick Query provides the ability to save the SQL file that is used to create the results table. Use the Reload button to reopen a saved SQL file.