Introduction

This tool is to allow you to perform custom queries on the database.

Lookups

Many fields in the database have "lookups". A lookup is a list of valid values, sometimes with a code and sometimes with additional informational data.

For example, there is a lookup that contains the list of all countries. Wherever a country could be entered, there will be a magnifying glass, allowing the country to be selected.

Introduction to SQL

3 or 4 paras.

How To Use

You can only select a single top level table. This is because any related tables to the initial one will be shown underneath it. The system knows how these tables are related, and is able to perform the appopriate joins.

When you begin to compose a query in the AQT, you should start with the "driver" table that holds the data most relevant to your query. Choosing the appropriate table can take some experience, and you should experiment to find the best queries for your specific needs.

Once you have chosen your driver table, Any related tables will be displayed. On the right hand side you are able to add additional constraints that reduce the amount of data returned, or "filter" it.

If you require your driver table to be joined with related information, you should check the box next to the related table. You will have the following options for how to manage this relationship:

Join

There must be matching related data in the related table, or you will see no records.

Optional Join

Will show all data from the driver table, and include related data where it is available. Blank values will be used for data from the related table if it is not available.

Must Have

Only returns records from the driver table if corresponding records are found in the related table. This does not return any data from the related data, it merely checks for it's existence. This therefore allows you to create a join to another related table.

Must Not Have

Only returns records from the driver table if corresponding records are not found in the related table. For example, "All people without a default contact address".

Fields

You can add constraints based on any fields in any of the tables. If the field is a lookup, the constraint options will be different from non-lookup fields.

If the field is a number or a date, there will be additional comparison options available, such as later and earlier.