Ad Hoc Queries

Overview

The AdHoc Queries feature is located on the Advanced Tab in the forms manager.   All users have access to it.

An ad hoc query will pull a list of forms based on criteria specified by the query creator.   Nearly every field on a form may be queried.

The results come up in a report format that may be printed or may be exported to Excel, CSV, or PDF.

If you want your results to show in the Forms Manager instead of the report format, the Advanced Search should be used.

Creating & Sharing Ad Hoc Queries

For more information on creating ad hoc queries, see the How to Create an Ad Hoc Query page.

Once created, ad hoc queries may be shared between users.   If you have created an ad hoc query and want to share it with others at your agency, see the How to Share Queries page.

Available Pre-Built Ad Hoc Queries

There are several pre-built ad hoc queries available within TraCS.

All TraCS users may access them by going to the Advanced tab and then choosing AdHoc Queries.

Right click in the white space and click Import.

Select the ad hoc query that you wish to import and then press Open.

The query will open to the search criteria screen.

Press Finish and the query will appear on your list.

Repeat for any additional queries that you wish to use.

To run the query, double click on it or press View Results.

Boating Citations and Warnings

This query returns boating citations and warnings within a date range.   There are optional parameters for location, form number, violation, and officer name.

Forms must be in Accepted or Validated status.

CFS Location by Date

This query returns Calls for Service (CFS) within a date range.   There are optional parameters for street number and street name.

Speeding Citations With Posted Speed & Violator Speed

This query returns a list of speeding citations within a date range along with the posted speed and violator speed.

Citations must be in Accepted, Mailed, or Submitted status.

You may export the results to Excel and do calculations to determine the violator's MPH over the speed limit as well as which speed range it falls under (6-9, 10-14, etc.).

If you do not have someone at your agency who is proficient in Excel, send your results spreadsheet to TraCS Support and we will do the calculations for you.

Steps for Speeding Range Calculations

Export your results to Excel.

Highlight columns G & H and format them as Number with no decimal places.

Add two columns to the left of Column I. You will now have a blank Column I and a blank Column J.   Format them as Number with no decimal places.

Label Column I as MPH Over

Label Column J as Speed Range

In cell I2, enter this formula and copy it down -

=G2-H2

In cell J2, enter this formula and copy it down -

=IF(AND(I2>=1,I2<6),"1-5",IF(AND(I2>=6,I2<=9),"6-9",IF(AND(I2>9,I2<=14),"10-14",IF(AND(I2>14,I2<=19),"15-19",IF(AND(I2>19,I2<=29),"20-29",IF(AND(I2>29,I2<=500),"30 or more",IF(AND(I2=0),"No Speeds Indicated")))))))

Your results will look similar to the ones below.

Create a Pivot Table to count the speed ranges.