Truck Inspection Database, 2007 Readme.txt Updated September 2008 ------------- Introduction ------------- The Truck Inspections Database is from the Federal Motor Carrier Safety Administration (FMCSA) Inspection Public File. The FMCSA is the division of the U.S. Department of Transportation responsible for monitoring and developing safety standards for commercial motor vehicles operating in interstate commerce. This database contains data from state and federal inspections involving motor carriers as well as shippers and transporters of hazardous materials operating in the United States. Inspection dates range from January 1, 2007, through December 31, 2007. Archived years are also available. The carriers inspected are from the United States as well as some U.S. territories and the Canadian province of Ontario. According to the FMCSA, the majority of the inspections were conducted at the roadside by state personnel under the Motor Carrier Safety Assistance Program (MCSAP). On January 1, 2000, the Federal Motor Carrier Safety Administration was established within the U.S. Department of Transportation under the Motor Carrier Safety Improvement Act of 1999. Previously, the FMCSA was a part of the Federal Highway Administration. Prevention of commercial motor vehicle-related fatalities and injuries is the mission of the FMCSA. The Motor Carrier Safety Assistance Program is a federal grant program of the FMCSA. It provides financial assistance to states for roadside inspections and other safety programs. Federal and state field enforcement staff perform inspections on interstate and intrastate motor carriers and shippers as well as transporters of hazardous materials. Severe violations of the Federal Motor Carrier Safety Regulations may result in a vehicle and/or driver being placed "out-of-service." The data collected from inspection activity is collected and stored in the FMCSA Motor Carrier Management Information System (MCMIS) Inspection data files. Due to privacy restrictions, driver information is not included in any inspection files released to the public. Carrier information in the "carrier" table may not match the carrier information contained in the FMCSA Census file. (The FMCSA Census file is the Truck Census Database, also available from NICAR, at (573)884-7711 or 884-7332) ------- Tables ------- There are six data tables in this database (Tables folder): INSPECT.dbf: The main inspection table that contains information including: the state, time, date and type of inspection; hazardous material information; number of inspection violations; whether the inspection was before or after a crash as well as alcohol and drug detection information. 3,139,366 records. CARRIER.dbf: This table provides details about carriers, including the name and address of the carrier, and the identification number assigned to the carrier. 3,139,366 records. STUDY.dbf: This is the Special Studies table. 194,102 records. VIOL_SHP.dbf: This is the Inspection Shipper Violation table, providing information about shippers in detail, including the name, address, etc. (Note: Some states do not have records in this table.) 434 records. VIOL.dbf: This is the Inspection Violation table, which gives sequence number and category of the violation. 7,332,466 records. UNIT.dbf: This table provides information about vehicle unit, such as sequence number and the type of vehicle unit. 5,369,524 records. -------------- Layout & Codes -------------- (In the Documents folder) Public_HM Free_Inspection March 2008: Original layouts and codes from the DOT. This file provides table structures as well as data element definitions and violation codes. NICAR added some date fields to update original truck inspection fields in character format. For example, INSPDATE is the converted field for CINSPDAT. ---------------- Importing Tables ---------------- The tables are all in Dbase IV format, which can be easily imported into any database manager program, including Microsoft Access. Here are the directions for Access: 1) Copy the tables from the CD to your hard drive. 2) Open a blank database in Access, name it and save it. 3) Inside the new database, in the File menu select "Get external data" Then select "Import." 4) An import wizard will ask you to locate the file(s). You will need to change the "file of type" to "Dbase IV". 5) Each table will need to be imported separately. While most tables can be imported into Access, the total size of all imported tables cannot exceed 2 GB in size. To work with large tables in Access, you will need to link them. You will be able to run queries on linked tables, but you will not be able to modify them. Here are the directions for linking tables in Access: 1) Copy the tables from the CD to your hard drive. 2) Open a blank database in Access, name it and save it. 3) Inside the new database, in the File menu select "Get external data" Then select "Link Tables." 4) An import wizard will ask you to locate the file(s). You will need to change the "file of type" to "Dbase IV". 5) Each table will need to be imported separately. -------------- Joining Tables -------------- A common, and consistent field can be used as a key to join two tables. ALL tables except for VIOL aND VIOL_CD can be linked using the INSP_ID field. You can link the VIOLSHIP table to the VIOL table (and then to all other tables) by using the VIOL_ID field. You can link the VIOL_CD table to the VIOL table using the VIOL_CD field. It is also possible to link the Truck Inspections database to tables in the Truck Accidents and Truck Census databases (both available from NICAR). To link these tables, use the census number for each vehicle, also known as the U.S. DOT number. (This field is named "CENSUS" in all of the Truck tables.) As a new carrier is added to the national census database of trucks, the next sequential number is assigned to that carrier for a census number. One word of caution if you do try to join the databases is the incomplete census numbering by the Truck Accidents database. Over half of the Truck Accidents, and many of the Truck Inspections file contain multiple carriers with census numbers that have all zeros, which is incorrect. But there are many records that do successfully join allowing you to discover more detailed carrier and accident information. If you plan on joining these tables, here are some things you might want to do to make it easier and faster: -index the CENSUS field (this will help your database manager join the tables more quickly) -select only those fields you absolutely want returned from your query (If you select all fields from both tables, there might be too many fields to return, resulting in an unsuccessful join or the freezing of your computer.) -If possible, create smaller tables to use for joining (for example, if you are interested in one state, create a new table with just that state, don't try joining the tables using all of the states. This will save you a lot of time.) -------------- Special Notes: -------------- INSPECT TABLE: In the shipper Name Field, there are some dirty data. For example, Schneider National Carriers, Inc. is entered as both: Scnneider National Carriers Inc and Schneider National Carriers, Inc. --------------------------- Stories and Uplink Articles --------------------------- These stories and uplink articles relating to the use of this database are available from the IRE Resource Center, and can be ordered by calling (573) 882-3364. STORIES Story # 22584 This investigation delved into the world of trucking in Alabama in 2005 where there are too few state troopers, and even fewer inspectors. This story compared the number of truck inspections done in Alabama to other states and the consequences. Story #21059 and #18812 Judy Thomas and Gregory Reeves of the Kansas City Star conducted several investigations in 2001 and 2003 into the safety of trucking companies in Missouri. They found that lax enforcement of regulations by the federal government as one of the main causes of safety problems. (See related Uplink article below.) Story #16152 A six-month Dateline investigation revealed an extraordinary number of eighteen wheelers driving the nation's highways with serious defects. We searched accident and inspection records of trucking companies throughout the country and spent several days at truck inspection stations and found forty percent of big rigs were so dangerous they were ordered off the road. Incredibly, the main defect we found was brake problems. (See related Uplink article below.) Story #14966 Duane Marsteller examined the safety records of the trucking industry in North Carolina in 1998 and published a series of stories in the Winston-Salem Journal. Most of the state's trucking companies were found to have "unsatisfactory" safety ratings from federal inspectors. Story #12140 Timothy M. Darragh at the Morning Call in Allentown, Pa. conducted a computer-assisted investigation in May 1995, finding that deaths from heavy truck crashes rose in 1993. Faulty equipment, tired drivers and unrealistic schedules contributed to lax safety. UPLINK ARTICLES Truck safety, May/June 2001 Andy Lehren gives a thorough account of how Dateline NBC investigated major problems in truck inspections and how records of inspections and accidents are kept. Dateline obtained databases from the National Transportation Safety Board, the Federal Motor Carrier Safety Administration and the U.S. Bureau of Transportation Statistics. TIP SHEET #2420 This tipsheet is a comprehensive guide to reporting on the trucking industry. It begins with a list of questions to ask at the beginning of an investigation, like, "Did the truck driver have a valid Commercial Drivers License?" Next, the tipsheet lists some pieces of information that reporters should be able to find before deadline, that could make their stories better. Then, the tipsheet lists possible follow-up investigations; these are more long-term projects and might make for good enterprise stories. There is a description of how to go about each investigation. Finally, the tipsheet ends with a list of contacts and government agencies that could be helpful for a reporter writing a story about the trucking industry. #2837 The authors list both sources and story ideas related to transportation. They include sources for stories about vehicle accidents, transportation infrastructure, and public transit. The authors also discuss some recent stories on this topic and include tips and advice for reporters pursuing similar investigations. Finally, they list some IRE resources about transportation.