Use the Jet Analytics Query Tool

Overview

The Query Tool is a powerful supplement to the Preview Table feature of the Jet Data Manager that gives the user more flexibility in exploring the content of a table. This allows the user to execute any SQL query to see the data the way that they wish to see it.

As the Query Tool can be used with ANY DATABASE, extreme care should be taken to ensure proper connection to the desired data source.

Process

1. Opening the Query Tool

   You can open the Query Tool in three different ways:

   • Right click a table, click Preview Table and click Query Tool in the Table Preview window.
   • Right click a table, navigate to Advanced and click Query Tool
   • Press F8 on your keyboard.

   

   The Query Tool opens with a query that selects the content of the currently selected table, similar to the query that is executed to get the content for the Preview Table window.

2. Executing Queries

   To execute a query, follow the steps below.

   1. Open the Query Tool using one of the options described above.

   2. If available, choose the Source and Account you wish to query. Account is only displayed when using an adapter with multiple possible accounts.

   3. Enter your query in the top text box of the Query Tool window. You can enter multiple queries that will be executed in sequence by the JDM.

   4. Adjust Max no. of rows to the maximum number of rows to be returned.

   5. Click Execute.

      or

      Select F5 on your keyboard.

   6. To terminate the query before it completes click the Stop button.

   When they query is complete the results are displayed in the Result tab. If multiple queries have been entered the user can select the query result to see in Result setIf the query resulted in a message, for example a syntax error, the Message tab will display this message.

3. Drag-and-Drop and the Query Tool Window

   The Query Tool supports drag and drop of tables and fields.

   • The user can drag a table or a field from the project tree into the query. This places the table name in the query.

   • If a table is dragged to an empty query the default query is generated. The default query fetches everything in the table.

   • If the user drags a table from another source into the window, they will be asked if they want to change connection and generate a default query. If the user answers No the name is simply added to the query.

4. Sorting and Filtering Data

   The Query Tool enables the user to sort and filter the results.

   Note: Only the rows returned by the query are available for sorting and filtering in the Results tab. If the user wishes to sort or filter all rows in a table, the most efficient way is to include the conditions in the query, e.g. by using "order by" or "where" clauses. Fetching thousands of rows and sorting them using the tools provided in the Results tab can be very slow.

   To sort the data follow the steps below:

   1. Open the Query Tool and execute a query as described above.

   2. In the Result tab, click a column heading to sort the rows on the value in that column. Click again to switch between ascending or descending order.

      To apply a filter, follow the steps below.

      1. Open the Query Tool and execute a query as described above.

      2. In the Result tab, click the Filter icon next to the name of the column to filter on. There are five filtering options:

         • (All) is equal to no filtering.

         • (Custom) opens the Custom Filter window, where the user can add conditions for filtering.

           

           Each condition evaluates the value of the row field compared to the possible values in the column. The comparison can be made on Equals, Does not equal, Less than, Less than or equals to, Greater than and Greater than or equal toClick Add to add an additional filter and click Delete to remove the currently selected condition.  The user can choose to filter on Any or All conditions, i.e. stringing the conditions together with "or" or "and". Click OK to activate the filter.

         • (Blanks) shows rows where the column in question is blank, i.e. empty.

         • (NonBlanks) shows rows where the column in question is not blank.

         • A specific value. All unique values in the column is listed and can be chosen as a filter.