Query Grid

To enter field details in the grid, the easiest method is to double-click on the required field in the Tables View window one at a time. This will automatically fill (populate) the criteria columns from left to right. Alternatively, you can drag the required field name from the top section onto the required criteria column.

• Field - automatically displays the selected field name, eg: Surname
• Table - automatically displays the selected table name, eg: Patient
• Database - automatically displays the selected database name, eg: CCare
• Sort - (Optional) double-click to determine if you want the results sorted in ascending or descending order for this field, eg: A - Z or Z - A
• Condition & OR - the grid includes two lines for entering conditions. The first line is labelled Condition; and the second line is labelled OR, allowing you to enter an alternative without needing to enter complex formulas. When you double-click on one of the cells in this line, the Where Definition window is displayed.
• Aggregate Function - double-click on the cell to display the list of applicable functions. Aggregate functions are only accepted in certain cases.
• Field Name - automatically displays the selected field name, eg: Surname
• Visible - (Optional) double-click to determine if you want this field's data to be displayed in the final results, eg: DOB is the criteria but for confidentiality reasons this is not to be shown in resulting report.
• Group & Group Condition - to enter a group, double-click in the desired cell. The word GROUP is displayed, followed by an order number. This number defines the field grouping order. This is the same principle used for sorts. You can add a HAVING clause by entering it in the Group's Condition line (or by adding it to the global HAVING clause). A detailed explanation of SQL is beyond the scope of this document. We will simply discuss the effects, limits and constraints of the GROUP BY command.
• Local Alias - automatically displays the database alias name for the selected table and field (System-defined)
• Global Where - the query definition allows you to enter a filter condition for each field. This is normally sufficient, but in some cases you may need to add global filtering conditions at will. This option allows you to enter a WHERE clause that is added automatically to the query conditions. It also allows you to create a basic filter that is independent of the conditions defined for each field. For example, you can limit a query by default to all patients in a specific community whatever the search conditions used for the fields.
• Global Having - the HAVING clause is used with the GROUP BY clause. It acts a bit like a WHERE clause (see your server's SQL manual to understand the exact effect of HAVING). This clause is very similar to the Global Where clause described above.
• Distinct Mode- eliminates duplicate names in the query result. To activate Distinct Mode, select SQL > Distinct Mode.
• Initialise Query - This option clears the query space. Any calculated fields that may have been defined are not deleted (this must be performed manually, unless you load another model). This makes it easy to recognize a new query without needing to redefine the calculated fields. To activate Init Query, select SQL > Init Query.