EnviroInsite Database

 

 

EnviroInsite Database

 

Data can be contained in either a Microsoft Access database file or Microsoft Excel spreadsheet.  For many sites, Excel is adequate both with respect to the volume of data that can be stored (1,000,000 rows in the xlsx file) and the speed of data access.  It may be advantageous in some cases to use a database file format to make use of existing databases or for larger files where rapid data access is important.

The data is read as a relational database, so it’s important that the well, screen and constituent names are consistent in the different parts of the database file.  For example each Location in the Intervals worksheet must have at least one identical counterpart in the Locations worksheet.  Likewise, each Location – Interval pair in the Observation table must have their counterpart in the Intervals table.  The easiest way to start a new spreadsheet or database file is to find an existing file, like the ones contained in MyDocuments/My EnviroInsite Work.  

 

Click here to view a training video on the EnviroInsite database fundamentals.

 

There are nine data tables accessed by EnviroInsite:

 

Table

Fields

Locations

Name, location, surface/bottom elevations, class

Interval

Location/interval name and bounding elevation interval

Observations

Location/interval name, measured value, date, constituent, data flag and media

Constituents

Analyte, units, media, standard

Borings

Location ID, soil or boring log description, top/bottom elevation or depth

Stratigraphy

Location ID, strata, top/bottom elevation or depth

Point Values

Location ID, parameter, elevation, value

Fill

Description of material placed in annular space around well casing or screen

Well Construction

Casing or screen interval depth and diameter

In Microsoft Access, each table is actually an individual data table within either an mdb or accdb file.  In the Microsoft Excel data file, each table is contained on a separate Excel tab sheet with the field names specified in the header row.  The header names and data field names must not be changed arbitrarily, although as you read on you will notice that there are options for fields that can store either elevation or depth values depending on the field name.

 

 

Using Excel for Data Storage

It can be very convenient to use Excel to store data, but there are a few things you need to keep in mind.  Well ids, screen ids, class and media are all text fields.  If you’re using an Excel file and absolutely need to enter a numeric value for one of these fields (like naming screens as 1, 2, etc.) make sure to put a single apostrophe before the value to indicate to Excel that the value is a number stored as text. Likewise, numeric values are numbers and should have only numerical characters in the field.  Do not leave any numeric fields blank and only the Data Flag or Formatted Value text fields should be left blank.  If you don't use or don't want to use screen ids simply enter a single hyphen character and the program will interpret this as a null value.

When you delete rows, make sure that you delete the cell formatting in addition to the cell values.  The best way to do that is to select the row number(s) on the left hand side of the sheet and then select edit->delete (Alt -> E -> D) from the menu.  This deletes both the value and any hidden formatting codes.

Using Access for Data Storage

A sample Access database file is provided with the EnviroInsite installation files for users that would like to store their data in an Access database.  The file contains five linked tables, along with forms for data entry.  Users can enter the data by hand or import the data from another data file. 

Locations and Intervals – Up through EnviroInsite 2014, the EnviroInsite database structure contained a Wells tab and a Screens tab.  We still support the old database structure, but have moved to make the table and field ids more generic so that users are not under the impression that only wells and well screens may be stored in the database.   Instead we are using the more generic term of Location to refer to any point at which some value is measured and Interval to refer to the depth or elevation of the measurement.