The All-in-One Database Software Solution
To create/modify/delete queries you should use Query Editor.
To open the Query Editor window, select the Queries > Add/Modify/Delete Query... menu item in the main menu:
The Query Editor window consists of two panes:
- The left pane provides the list of queries available in the database, and the buttons to add and delete a query. The queries are grouped by types of records, the current record is marked with the blue color;
- In the right pane you can edit the current query.
Creating and Deleting Queries
- To create a new query, click the New Query button in the left upper
part of the window and select the record type, with which the new
query will work. By default, a query returning all records of the selected
type is created. To modify a query, use the right pane of
- To delete the current query, click the Delete Query button in the
left upper part of the window.
Modifying the Current Query
The right part of the windowis used to edit the selected query. It contains 4 tabs:
Query, Appearance, Actions and Misc.
The contents of this tab can be divided into three parts:
- Part 1 - the records source (i.e., the folder, from which the records should be taken);
- Part 2 - set of rules, according to which the records and the
queries will be checked for consistency;
- Part 3 - in this part you can specify
fields the records should be sorted by.
Using unique field you can specify the field that has to be unique in the query results.
For example (for a database of books), to get a list of all genres, indicate genre as distinct.
Now the query will return only one book for each genre, and
we will get a list of all genres. Otherwise the resulting list will
contain all books and, consequently, duplicating genres.
When creating a new query, the set of rules is empty. This means that all records from the specified folder (and its subfolders, if the corresponding check box is selected) will be returned.
At the left of the window there are 3 buttons that can be used to create, delete or edit rules.
If a ruleset contains more than one rule, you should use the And/Or
button to specify whether a record to be returned should satisfy all the rules (and), or one of the rules (or).
- Get a list of the books published in 2000, and those the number of pages of which is greater than 100;
- Get a list of the books, which were either published in 2000, or the number of pages of which is greater than 100;
You can also use bracket "(" and ")" to create complex rules.
Clicking either Edit Rule or New Rule opens the window for rule editing,
where you can set up the rule.
- Table Style - click this button to define how query results are to be output (fields, colors, grouping,
subtotals, etc.) Learn more...
- Show query results in - this option defines where query results are to be displayed:
- Small List - query results are to be displayed in a
special window to the left, under the folder list;
- Main Table - query results will be displayed where
the record list normally appears; at the same time, the record list in the
current folder will be hidden.
- When the check box is cleared, the window with query results will not be displayed.
- Configure totals - this button opens the window in which you can specify the fields by which it is necessary to display the sum, minimum and maximum value in the query results. In the report it will look in the following way:
- Add Toolbar, Toolbar...
- select this check box to add a toolbar that will be displayed over the query results.
example, there you can place buttons for printing results or for mass editing
of selected records:
- Show the 'Refresh' button - select this check box to display the Refresh button over the query results. On clicking this button, the query will be executed once again (updated).
- Restart query... If none of the records meets query parameters, the user will be offered to repeat the request with different parameters. This option is performed only if any parameters in the ruleset are specified as "variable" (see above).
- Automatically select the first record from query results. After a request is accomplished, the first record in the query results will be selected.
- Print query results. After a request is accomplished, you will be offered to
print out its results.
- Export query results using an export/send template.
After a request is accomplished, you will be offered to export its results
by using Export/Send templatesexport to txt, send records by
e-mail, etc). Learn more...
- Execute a script for each record.
If this check box is selected, the specified script will be executed for every record in the query results in turn. For example, if you include in the script the line incrementing the field value by 1, the field value of all records in the query results will be changed.
- Execute a script one time after the query is complete. In contrast to the previous script,
this script will be executed only once,
no matter how many records are in the query results.
You can, for example, display a message about the query execution, offer
the user to print records from the query and so on.
- Execute a script before running a query. In this script you can init data for the query or ask user for a confirmation.
You can also cancel the query execution from this script. To do it set the [$cancel] variable to "1".
- Don't show the query in the "Queries" menu - select this check box to hide
the option for execution of the query in the queries menu.
- Don't show the "no records were found" message - select this check box
if you do not want the program to show the message in case no records have been found.
- Limit number of records - this option allows you to specify the maximum number of records returned by the query.