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.
Crashes by Location
This query returns long and short form crashes within a date range along with the location and latitude & longitude where they occurred.
Forms must be in Submitted status.
Crashes With Approving Supervisor
This query returns a list of all crashes within a date range along with the approving supervisor and the date the crash was accepted.
Forms may be in any status.
Crashes With Bicyclists and Pedestrians
This query returns a list of long and short form crashes within a date range that involve a bicyclist/cyclist or pedestrian, including persons in a wheelchair, in a building, on skates, etc.
Forms must be in Submitted status.
Crashes With Citations Issued
This query returns a list of long and short form crashes within a date range where at least one citation was issued.
This is a count of crashes; it is not a count of citations issued for crashes. Use the UTC's Written for Crashes query to get that information.
Crashes With Motorcycles
This query returns a list of long and short form crashes within a date range that involve a motorcycle.
Forms must be in Submitted status.
Crashes With Suspected Alcohol or Drug Use
This query returns a list of long and short form crashes where Suspected Alcohol Use and/or Suspected Drug Use are marked Yes.
Forms must be in Submitted status.
You may also use the FDOT Safety Grant - Crashes with Suspected Alcohol/Drug Use analysis report to get these statistics.
FIBRS Crime Stats
This query returns a list of FIBRS reports within a date range that are grouped by FDLE Offense Category.
Forms must be in Accepted or Validated status.
FIBRS Incident Count - Location & Zone
This query returns a list of FIBRS reports within a date range along with the location of the incident. The results are grouped by Incident Description.
Forms must be in Accepted or Validated status.
FIR With Location & Subject Information - Coming in Version 16
This query will be available in Version 16. If you would like to use the query now, contact TraCS Support.
This query returns a list of FIR's within a date range along with the location of the incident, the officer first and last name, and the name, DOB, race, and gender of the subject(s).
Forms must be in Accepted or Validated status.
Parking Citations and Warnings
This query returns parking citations and warnings within a date range. There are optional parameters for location, form number, ordinance type , tag number, violation, and officer name.
Forms must be in Accepted, Paid, Submitted, or Validated status.
Speeding Citations With Posted Speed & Violator Speed - Coming in Version 16
This query will be available in Version 16. If you would like to use the query now, contact TraCS Support.
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.
We are happy to do these calculations for you. Contact TraCS Support for assistance.
Traffic Warning Search with DOB
This query returns a list of traffic warnings within a date range along with the violator's DOB. From there you can calculate the violator's age at the time the warning was issued.
Forms must be in Accepted or Validated status.
Steps for DOB Calculations
Export your results to Excel.
Add two columns to the left of Column D. You will now have a blank Column E and a blank Column F. Format Column E as Number with no decimal places.
Label Column E as Age at Time of Citation
Label Column J as Adult or Juvenile
In Cell E2, enter this formula and copy it down -
=DATEDIF(D2,A2,"y")
In Cell F2, enter this formula and copy it down -
=IF(E2<18, "Juvenile", "Adult")
Your results will look similar to the ones below. These results are from UTC data; the warning results will show Accepted or Validated for the Status instead of Mailed or Submitted.
Create a Pivot Table to count citations issued to Adults vs. Juveniles. Add the Form Type desired to break out the type of violation (Moving, Non-moving, etc.).
We are happy to do these calculations for you. Contact TraCS Support for assistance.
Trespass Warnings Name and Address Search
This query returns a list of trespass warnings within a date range along with the name and DOB of the person trespassed and the name and address of the trespass location.
Forms must be in Accepted or Validated status.
UAA Search With Age
This query returns a list of UAA forms within a date range along with the charge description, arrestee or suspect age, and whether the person is an adult or juvenile.
Forms must be in Accepted, Submitted, or Validated status.
UTC by Location
This query returns a list of citations within a date range along with the location of the violation.
Citations must be in Accepted, Mailed, or Submitted status.
UTC Crashes & Aggressive Driving
This query returns a list of citations within a date range where the Aggressive and Crash fields are both marked Yes.
Citations must be in Accepted, Mailed, or Submitted status.
UTC Search with DOB
This query returns a list of UTC's within a date range along with the violator's DOB. From there you can calculate the violator's age at the time the warning was issued.
Citations must be in Accepted, Mailed, or Submitted status.
Steps for DOB Calculations
Export your results to Excel.
Add two columns to the left of Column D. You will now have a blank Column E and a blank Column F. Format Column E as Number with no decimal places.
Label Column E as Age at Time of Citation
Label Column J as Adult or Juvenile
In Cell E2, enter this formula and copy it down -
=DATEDIF(D2,A2,"y")
In Cell F2, enter this formula and copy it down -
=IF(E2<18, "Juvenile", "Adult")
Your results will look similar to the ones below.
Create a Pivot Table to count citations issued to Adults vs. Juveniles. Add the Form Type desired to break out the type of violation (Moving, Non-moving, etc.).
We are happy to do these calculations for you. Contact TraCS Support for assistance.
UTC Seatbelt Violations Written for Crash Reports
This query returns a list of citations within a date range where the Crash field is marked Yes and the violation written is for FSS 316.614, the Florida Seat Belt law.
Citations must be in Accepted, Mailed, or Submitted status.
UTC Speeding in School Zone
This query returns a list of citations within a date range written for speeding violations within a school zone.
Citations must be in Accepted, Mailed, or Submitted status.
UTC Top Violations
This query returns a list of all citations within a date range. The results are grouped by violation type and sorted in descending order.
Citations must be in Accepted, Mailed, or Submitted status.
UTC's Written for Crashes
This query returns a list of citations within a date range that were issued in connection with crashes.
This is a count of citations; it is not a count of crashes. Use the Crashes With Citations Issued query to get that information.
Citations must be in Accepted, Mailed, or Submitted status.
Voided & Deleted Citations With Officer Name & Violator Name
This query returns a list of voided and deleted citations within a date range along with the officer name and violator name.
Warnings With Race, Gender, and Officer Name
This query returns a list of warnings within a date range along with the first and last name of the officer, the violation, the race and gender of the violator, and the location of the violation.
Warnings must be in Accepted or Validated status.
Warnings by Location
This query returns a list of warnings within a date range along with the location of the violation.
Warnings must be in Accepted or Validated status.