HearAid Version 5.0

Query

Query

Previous topic Next topic  

Query

Previous topic Next topic  

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)!

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:

QueryToolbar

 

Use Execute SQL to execute the SQL statement in the 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

 

ExportTable

 

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 5.0

QueryMailMergeMenu

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!

 

Note: The menu will include any custom templates you create which use HearAid's standard format

 

 

 

The Work Area:

 

QueryWorkArea

 

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