CLOSE WINDOW

Developing and Managing an Environmental Sample Database

Working for an engineering firm providing operational management and oversight on a large and active rail yard, Destiny provided database design/development services, custom programming, data auditing, and data reporting services.

On winning the site management contract, the engineering firm inherited a sample database, but none of the needed reporting tools. The initial scope focused on identifying needs, designing the appropriate database and data structures, and replacing custom reporting tools matched to client, state, and EPA requirements. The database included sample and monitoring data tracking various contaminants in soil, sludge, surface water, and groundwater. Samples were tracked from field collection to validation and reporting spanning nearly ten years.

Conducting a Detailed Data Audit / Correcting Legacy Data Problems
While examining the inherited database, Destiny specialists identified a number of problems including duplicate records, mis-labeled and mis-reported samples, data reported in the wrong matrix, and data loaded in the wrong units of measure. Destiny proposed a detailed data audit to identify and address suspect data.

Conducting the audit, Destiny provided custom programming to automate the identification and flagging of suspect data, and to analyze the findings to identify trends and patterns. The information was utilized to automate quality control and reporting on data loading, and to establish new standards for field data collection, data handling, and formatting. This included adding a unique identification number identifying each sample, sample event, and location.

Based on the client’s research, Destiny corrected the suspect data, adding an audit record for reference. This included the reason for the correction, the source documents used to validate correction, and name of the person responsible for the research and for final determinations.

Database Design and Custom Programming to Automate Data Loading and Reporting
Destiny examined several existing databases, reporting tools, sample data sets, and reporting requirements. Selecting and integrating the best components, Destiny added additional tables and features, and created a new Access database template for use as a company standard. Destiny also provided Visual Basic programming to automate the following:

Data Stewardship Services Managing the Database and Loading and Reporting Data
Accommodating and streamlining the simultaneous completion of database development, custom programming, and data auditing, Destiny stewarded the database through the development period. This included loading weekly and monthly sample data, providing QC services to avoid the range of problems identified in the audit, and reporting and formatting to complete various deliverables to the site owner, state agencies, and EPA. The project concluded with the archiving and documentation of data transfer and auditing, the migration of valid and corrected data into the new database template, documenting the processes and audit trails, developing new standards and documentation, providing training, and assisting through on-call help as the database gained broad use.