SIMSCounty.accdb - Instructions|
The self-extracting archive files identified by Illinois county names
contain the Microsoft Access® databases designated "Countyna.accdb" ("Countyna"
= first eight characters of the County name) and "SIMSLink.accdb".
Countyna.accdb contains the information, extracted from the Illinois
Structure Information System (ISIS), for Local Agency structures located in
each county. It is intended to operate as a read-only informational
database. The data should only be revised through the established ISIS
procedures as described in the IDOT Structure Information and Procedure
Manual. Revisions to the ISIS data is loaded in the SIMSCounty database
files on a weekly basis. SIMSLink.accdb, on the other hand, is a working
database file in which custom queries, reports and forms based on the ISIS
data from Countyna.accdb may be created.
Linking SIMSLINK.accdb To
After you have extracted the above named files and placed them in a
folder, you should open SIMSLink.accdb in MS Access (2007 or greater). When
the "SIMSLink: Database" window opens, select the Macros tab, then
doubleclick the "AttachTables" macro.
Provide the requested information in the two dialogue boxes.
This procedure allows you to use SIMSLink.accdb as a working file in
which to create custom queries, reports and forms based on the ISIS data
that is available in the Countyna.accdb database. This has been done that
when you bring in a new Countyna.accdb file (available weekly), your custom
queries, reports and forms will be unaffected.
Included are some queries, a report and a substructure inventory table
and form in SIMSLink that you may use, examine, revise, etc. as you wish.
There are several "Decode" queries that you can use to add to your
queries to output the description for codes used in ISIS. For example,
instead of just outputting a code "4" for the Main Span Material of a
structure, by using the “Decode: Material – 43A & 44A” Query in your custom
queries you can display "STEEL CONTINUOUS" which is the description for a
"4" code. An example of the use of Decode Queries is in the query called
"Decoding Example Query" included in SIMSLink.
A few things to keep in mind when doing queries on the ISIS data:
- You should normally have a field called "KRLinkOn" with criteria set
to "Y" in your queries. (This insures that all the information output in
a query is for structures carrying a motor vehicle road. While
structures carrying railroads, etc. over roads are included in the
database, only those carrying a road require NBIS inspection or may be
replaced using BRRP or TBP funds.)
- When a query includes a table that may have more than one record per
structure (i.e. - Const Reconstruction, Fracture Critical..., Inspection
Appraisals, Key Route..., Microfilm History, Scour Critical Inspections,
Underwater Inspections) a field called "SequenceNbr" may need to be
used. “SequenceNbr” coded as "1" indicates the most recent occurrence,
"2" just prior to most recent, and so on. For example: If you want
information for a structure related only to the most recent NBIS
inspection and not all of the previous inspections performed, the query
will include "SequenceNbr" as one of the query fields and set its
"Criteria" as "1". (Note: A variation on the usual 1-2-3-… sequencing is
in the Construction / Reconstruction information. The original
construction record always has SequenceNbr = "0" (zero) and the
subsequent Reconstruction records are numbered from "1" (most recent).
Minor maintenance (code "M") records are numbered from "51".)
- Be careful of linked tables where some of the records of one are not
matched in the other. Access reads the missing record as a "null" and,
depending how you have the links configured, may not display any data.
Sometimes you can get around this by setting the criteria for a field to
"Is Null" or "Is Not Null".