HCL query

The HCL query is used by the High Crash Location report to query all of the records with in a date range. This query is used to retrieve all records between the first crash date entered and the last date entered by the user. It requires the parameters of  :FirstDate AND :LastDate. The HCL query must contain all of the fields listed in your Default query to operate correctly.

This query is normally a copy of your Default query less any references to the primary street and cross street in the where clause. If your Default query look like this:

Select (list of fields)

FROM

    ( Dbo.Crashes Veh_1

      LEFT JOIN Dbo.Crashes Veh_2 ON ((Veh_2.Accident_Number=Veh_1.Accident_Number) AND (Veh_2.Vehicle_Number=2)))

      LEFT JOIN Dbo.Crashes Veh_3 ON ((Veh_3.Accident_Number=Veh_1.Accident_Number) AND (Veh_3.Vehicle_Number=3))

WHERE 

(Veh_1.Vehicle_Number = 1)

AND((( Veh_1.Primary_Road = :PrimaryStreet) AND 

( Veh_1.Secondary_Road = :CrossStreet )) OR

(( Veh_1.Primary_Road = :PrimaryStreet2 ) AND 

( Veh_1.Secondary_Road = :CrossStreet2 )))

AND((Veh_1.Collision_Date BETWEEN :FirstDate AND :LastDate))

The Default query can be copied and renamed HCL. Then remove the AND section of the where clause that contains athe

Select (list of fields)

FROM

    ( Dbo.Crashes Veh_1

      LEFT JOIN Dbo.Crashes Veh_2 ON ((Veh_2.Accident_Number=Veh_1.Accident_Number) AND (Veh_2.Vehicle_Number=2)))

      LEFT JOIN Dbo.Crashes Veh_3 ON ((Veh_3.Accident_Number=Veh_1.Accident_Number) AND (Veh_3.Vehicle_Number=3))

WHERE 

(Veh_1.Vehicle_Number = 1)

AND((Veh_1.Collision_Date BETWEEN :FirstDate AND :LastDate))