How To: Add a Query

The first step to creating a Query is knowing what type of Query needs to be created.  (Example: to build a Query to find all checks over $30.00, a Tender Query needs to be created.  If needing to know all water Payments, then a Payment Query needs to be created.)

An example of how to create a Tender Query to locate all Checks over $30.00 is as follows.

Know what type of Query needs to be created and Select that Heading.

(For this example: Tenders)

Click

Step 1: Name the query and set fields to view

Give the Query a Name and Description. 

Choose the data Query Fields to be displayed in the results.

For this example:

Name is Checks over $30.00

Description is A Query to locate all Checks given a date range over $30.00

Query Fields chosen are: Amount, Batch Date, Batch Number, Batch Status, Check Account Number, Check Name, Check Number, Check routing Number, Owner Name, Routing Check Digit, Tender Code, Tender Name, and Transaction Number

Once all the information is correct, Click Continue to Step 2

Step 2: Criteria to limit the query results

Create Criteria to limit the Query results. A data Query Field can be Added, Edited, and/or Deleted.

To Add a data Query Field to set Criteria.

Click .

(Example of a Add Query Criterion Dialog Box)

Choose a data Query Field from the dropdown list of available data fields.

(For this example: Batch Date)

Choose the Comparison Operator from the drop down list. Comparison Operators are Equals, Does Not Equal, Greater Than, Less Than, Greater Than Or Equal, or Less Than Or Equal. Only available operators will be displayed.

(For this example: Greater Than or Equal)

Choose a Comparison Value either from a drop down list or type into the empty field.  If available, the drop down list will show all available options. If needing to enter information into the empty field, ensure to enter the correct information.

(For this example: Prompt User)

The Criteria has been added to the Criterion List.

For this example:

Criteria one: will be a start date

Data Field is Batch Date

Comparison Operator is Greater Than Or Equal

Value is Prompt User

Criteria two: will be an end date

Data Field is Batch Date

Comparison Operator is Less Than Or Equal

Value is Prompt User

Criteria three: will ensure that only checks will be returned

Data Field is Tender Code

Comparison Operator is Equal

Value is CHECK

Criteria four: will return all amounts (in this case checks) over 30.00

Data Field is Amount

Comparison Operator is Greater Than

Value is 30.00

Continue to add Criteria until all needed Criteria has been added,

Once all the information is correct, Click Continue to Step 3.

Step 3: Organize the layout of the query information

Choose how to sort the retrieved information. Up to three columns to Sort by, either in ascending (ASC) or descending (DESC) order.

For the First Sort Column: use the first drop down arrow to choose the data field to sort the retrieved information. Then use the next drop down arrow and choose the ASC to sort the information in ascending order or DEC to sort the information in descending order. If wanting, do the same for the Second Sort Column: and the Third Sort Column:.

Choose the Column Order. The columns of fields from the top to the bottom are displayed from the left to the right.

Next to column order all of the chosen data fields will be displayed. Highlight a data field (column). To move the selected data field (column) up in the list (to the left) Click .
To move the selected data field (column) down (to the right) in the list, Click .

For this example:

First Sort Colum: Batch Date, ASC

Second Sort Column: Batch Number, ASC

Third Sort Column: Transaction number, ASC

Column Order: Batch Date, Batch Number, Transaction Number, Owner Name, Batch Status, Check Name, Check Account Number, Check Routing number, Touting Check Digit, Check Number, Tender Code, Tender Name, Amount

Once all the information is correct, Click Save, Save and Set Security, or Save and Run

Save will save the Query to be ran at a later time.

Save and Set Security will save the Query and take the user to the Set Security page. Security can be set at any point, not just when creating the Query.

Save and Run will save the Query and Run the Query.  Allows the user to see what they have created.

For this example: Save and Run was selected to ensure that the Query is correct.

Some Columns may not be needed, like the Tender Name and Tender Code.  The Criteria is set so that the only Tender will be CHECK, as such the results may not need to be in the Query Results. No Query will be perfect the first time. Edit the Query and make changes as needed until the Query is correct.

As with Run Query, the Query can be Exported To PDF, Excel, XML, and ASCII.

Security

Security works just as Permissions, by user or group.  If a user or group is visible on the Query Security then that user or group has Permission to that Query.

How To: Set Security

Highlight the Query.

Click .

(Example of the Tender Query: Checks over $30.00)

Put a check in the box under Selected next to the user or group to give access.

If a user or group does not have access, they will not be able to view or use the Query.  If wanting to limit a user from being able to Edit a Query, ensure that the box under Read Only is also checked for that user or group. If Read Only is checked, the user or group will be able to Run the Query (given permissions), Edit and Run the Query, but not able to Configure (even if they have that permissions).

Related Topics

Query