|
<< Click to Display Table of Contents >> Query |
  
|
|
<< Click to Display Table of Contents >> Query |
|
The Query window is an extremely powerful tool for accessing the HearAid database. It can be used not only to display "views" or subsets of your data, it also has the capacity to modify the database itself using SQL, although System Administrator rights are required for database modification operations.
You should take very great care using anything other than SELECT queries (i.e. INSERT, UPDATE, DELETE and Data Definition (DDL) statements)! Only SYSDBA can execute anything other than SELECT queries.
All of HearAid's Quick Reports, Label, Mail-Merge, e-Claim processing and data validation rules are based on SQL queries and the Explorer report wizard uses SQL as the basic data access tool for preparing formatted reports.
SQL queries with Firebird are usually very fast and allow very precise control over the data selected for display in the result set. The HearAid query-builder is quite easy to use but it does require some skill and knowledge of SQL to use effectively! It is outside the scope of this help file to teach a novice user the intricacies of SQL, but there are numerous excellent books available from on-line bookstores on the Internet and there is a brief primer in the SQL topic.
The Query interface will also make use of any formatting information stored in the script, as used in the Quick Reports scripts. If you prefer to have the data presented in a fancier format, use the Report Builder wizard in Explorer.
The Toolbar:
•Use Execute SQL to execute the SQL statement in the editor. This button has a sub-menu item Ignore Script Errors and Continue which can be very useful for debugging long scripts such as HSP Fee Updates.
When this option is selected and one or more errors occur in a script the errors will be displayed in a pop-up panel at the bottom of the script editor
•Use New Query to clear the editor
•Use Load SQL to load SQL from a text (.sql) file
•Use Save SQL to save the SQL statement in the editor into a text file
|
•Use the Export Table menu to export the result of the query (ie the data in the table) into a variety of formats: Binary, Excel, HTML, Text/CSV of XML. Excel, HTML and CSV are perfect for use as data sources for merge documents in Microsoft Word. CSV files are a standard format which can be opened directly in spreadsheets such as Microsoft Excel, or in a text editor such as Notepad, or as a data source for importing into other databases, or as a data source for such things as mail-merging. Binary format is the most compact but can only be used for export/import within HearAid |
|
•Use the Mail Merge menu to use the query result as a data source for a mail merge using HearAid's built-in merge functions. For this to work properly you must make sure that the query includes the same data fields used in the merge template (typically, "Address_Name", "Title_Name", "Address_Line_1", "Address_Line_2", "City" and "Postcode" fields) For further information on preparing a Mail Merge template, see the Mail-Merge topic. You can also use a suitable query result to generate mailing labels.
This function provides users with an almost unlimited scope for selecting target groups of clients for mail-outs! The Mail Merge function can also be used to send out emails and SMS messages, as well as to print mailing labels Note: The menu will include any custom templates you create which use HearAid's standard format
|
The Work Area:
The work-area is divided into two sections, accessed by the SQL, and Data tabs. The SQL tab contains the Editor, Table List and Field List panes and is where the query is composed and executed. The Data tab displays the result of the SQL executed from the editor.
Table and field names can be inserted into code by either
•Double-clicking on the table or field in the list, or
•Dragging and dropping the name into the editor
Code Completion is a tool to help enter table and field names. Pressing Ctrl+Space Bar will display a drop-down option list containing table, view and field names that begin with text that has been entered so far
