Handy Hints

Managing Chem Codes

ESdat Handy Hint – Managing Chem Codes   EScIS (the producers of ESdat) provide a comprehensive list of unique ChemCodes with the installed version of ESDat to help clients manage the myriad of chemicals encountered in the Environmental and Earth Sciences.

While we try to keep with list as complete and up to date as possible with each ESdat release, clients will may require management of data associated compounds not previously included with ESdat or may wish to manage certain chemicals in a specific manner.

The Access version of ESdat allows clients to effectively maintain a 'master' copy of the zRef_Chemistry_Lookup table (where ChemCodes are stored) by several ways.

Copying from one project to another.

The first approach uses an existing project with an updated zRef_Chemistry_Lookup table that can be applied to new (other) projects.

Alternatively, zRef_Chemistry_Lookup data from the source project could be copied directly into an appropriate destination project import template.

This can be completed as follows:

From the source project, select the Import View and scroll down the All Data Tables list to zRef_Chemistry_Lookup table

From the new (destination) project, select the Import View and scroll down the All Data Tables list to zRef_Chemistry_Lookup table, creating a blank import template

A third method of managing ChemCodes is to develop a template project database that can be used to set up each new project.

New installations of ESdat use EScIS_Environmental_and_Hydrogeology.mdb and EScIS_Environmental_and_Hydrogeology.esd as the template database for all new projects. Both files (mdb & esd) are located within the installation folders.

Both files can be copied, renamed and saved within the Corporate Templates directory (which is generally located within the ESdat Network Directory) or in an Ad-Hoc Template Directory. (Avoid using the installation folder as template may be lost if ESdat updates are installed).

Once saved, any matching file pairs (i.e. matching .mdb and .esd files) can be selected as the basis for creating a new ESdat project.

To edit this database, open from within ESdat (File --> Open --> Other) and import/edit data as per a normal database. Typical edits may be to customise the Chem Group and output units in the zRef_Chemistry_Lookup table, or to import standards (i.e. guidelines) that will apply to all new projects.

It is important to avoid importing site data to the template database as it will be present in all new projects created with that template.

For guidance on importing data to ESdat projects:



For guidance on setting up project directories and templates:





With this handy hint, we hope some ESdat Access users can spend more time analysing and less time organising!

Managing Project Reference Documents Efficiently and Effectively

ESdat provides simple, accessible functionality to manage a variety of project documents.
Contaminated land projects (such as Phase 1 Site History Investigations) often require desktop review of a range of document types, such as:

Effective, centralised management of these documents can save valuable time.
ESdat provides the ability to manage the storage of these documents at a project level (i.e. unique to each project database).
The Access version of ESdat stores hyperlinks to locally saved documents in a single Data Table, together with relevant document metadata.
The SQL version of ESdat allows storage of documents either within the database or as a hyperlink, also with relevant document metadata.

Both versions allow documents to be opened directly from ESdat.
If the project proceeds to the next phase (for example a soil and groundwater investigation), your field and analytical data can then be retained in the same project within ESdat.
Spend less time looking FOR your documents and more time looking AT your documents!

Can you improve the appearance of your Chemistry Tables by just changing a few settings under More Options?

Are you producing Chemistry Tables that look as follows:
Chemistry Table Before

With a minor change in settings you can produce your tables in what might be a preferable output format:

Chemistry Table After

Changes include:

To achieve this you can use the following settings under “More Options” prior to producing the ChemTable.

You can also save these settings for others to use by selecting “Setup – User Settings” in the main ESdat form.

Zip your lab files:

When labs send their results they normally include the ESdat files in addition to other files, including certified PDF documents.  It can be desirable to be able to keep all this together and access all these files directly from within ESdat.
If files are stored in a zip file you can select that zip file directly from with ESdat, and ESdat will extract the data and present it for import in the normal manner.  It will also automatically move the zip file to an “imported” sub-folder of where it was previously stored.
This makes keeping track of which files are imported a lot easier.  It also makes managing original lab files easier as each lab report now only needs to have one zip file, in which all files (ESdat files and others) can all be consolidated.
If using Access versions the path to this zip file is stored in the database, and clicking on it in ESdat will automatically open the zip file.  If using the SQL Server version the zip file will be automatically uploaded into the database, or a system folder so it is always accessible even if project directories get archived.

Data Editing in ESdat

Quickly and easily edit your data via the “Data” panel in ESdat. On the “Data Views” screen the “Data” panel is located alongside the “Map” panel and “Filter” panel.

To make changes to your data, simply place ESdat in edit mode by clicking the pencil in the left toolbar of the “Data” panel. The pencil will turn red to indicate ESdat is in edit mode.

You can make changes to a whole column of data by selecting the column header prior to editing, or you can select multiple cells in the same column.

To exit edit mode, simply click the pencil again. The pencil will return to grey to indicate that ESdat is no longer in edit mode.

Changing the Order and Grouping of Compounds in the Chemistry Output Table

In the Chemistry Output Table both the grouping and the compound order can be customised to suit your reporting requirements.

For more information see this forum topic: www.esdat.net/forum/default.aspx?g=posts&t=23
You can also change the ordering of ChemGroups by editing or adding values to the “zRef_Chemistry_Lookup_ Chem_Groups” table.

If you are using the Access version any changes you make will only apply to the database you are working with. More information is available via the User Forum. (In ESdat select Help - Help Portal).

LNAPL density corrections

Do you need to plot LNAPL corrected water levels? Are you aware that ESdat can calculate the corrected water level for you?

ESdat has a “NAPL” output under “Water Levels”. ESdat will output the water depth, the NAPL thickness, the corrected water level and the relative density used in the correction.

By default ESdat uses a relative density value of 0.76, however this can be modified in the “Site Properties” table to get a single value across the site. You can even have a different value in each Well (Piezometer) by entering a Chem Result with a ChemCode of “LNAPL_Rel_Density”. See the help file for further information.

Result Ranges ESdat

Result ranges

Do you sometimes find that ESdat will output results in the Chemistry Output Table export as a range (ie 1– 3)?

There are two reasons this can occur:

  1. There may be two samples collected at BH01 on 28-Jan-10. This can be resolved by including additional sample details in the table (ie Sample Type if one is a duplicate). Alternatively including Field ID may ensure each sample is reported independently (if the Field ID values are different.) Including SampleCode will always differentiate them as it is impossible for two samples to have the same SampleCode.
  2. There may be two results for Ethylbenzene delivered for the one sample, and the results are grouped by “Chem_Group”.
    This can be resolved by grouping the results by either Method Type or Method Name, which will be unique for each of the different results.

Setting up new projects

For population of new ESdat databases (Access or SQL Server) the most effective way reported by users is the use of an ESdat Interchange File (EDIF).

You can take an existing project and export the reference or required data to an EDIF. Then when you start a new project you can import that EDIF to immediately have the environmental standards, combined compounds or other startup data that you require on a recurring basis.

You can create a whole selection of EDIFs for different kinds of projects.

Depth based guidelines

Some local soil guidelines (Canadian CCME, proposed Australian NEPM) require different trigger values be applied to different depth ranges or soil types.

As ESdat facilitates filtering by any parameter prior to output table generation it is a simple matter to filter for all data above or below a particular depth and produce specific chemistry output exceedance tables or maps for each specified interval. Similarly it is simple to filter by different soil types, whether you define these by different areas of the site, or on the basis of the sampling matrix.

Ensuring your data has the correct soil depth specified is easy. If you include it on your COC it will be reported with the laboratory data in ESdat format. If you don’t, but include the depth as part of the sample Field ID then ESdat will automatically extract the depth component, so long as one of the “recognized formats” is used. See the help file for details. Of course, if you are using PLog/LSpecs for electronic COC then it all happens automatically for you.

Basemaps for mapping – download street maps direct from the web

ESdat supports all common basemap formats (ie bmp, jpg, ecw, dxf, shp etc.), and many ESdat users are lucky enough to be in organizations that have an in-house supply of basemaps. For those who don’t have such resources, access to geo-referenced imagery can be difficult.

In version 4.2 the ability to download street maps directly into ESdat was added. There is no cost, and you already have the software. You simply need to ensure you have specified the correct co-ordinate system (Project table in Access versions, Site Table in SQL Server versions), and click the |+Web| button on the map layers toolbar, then “Get OpenStreetMaps” and you have your data points shown relative to streets.

Finding free geo-referenced imagery (as opposed to street outlines) can be a time consuming and frustrating exercise. Nearmap Hypertiles covers many urban areas and provides georeferenced and free (conditions apply) imagery which is very useful. For other areas DigitalGlobe has a good collection of high resolution images for a modest cost.

If you have found a great imagery resource that is free or low cost, please let us know and we will promote it to other users.

Do you use Post-Import Calcs? /running 95th Percentile

Post Import Calcs are an incredibly powerful way to get ESdat to run pretty much any calculation you want and store the result in any ESdat table. The calcs need to be written in the format that your database recognizes (ie Access or SQL Server compatible Structured Query Language). While being incredibly flexible, this can be a barrier to many users developing their own calcs, however your IT or ESdat support should be able to add others for you. A standard library is available in your installation directory.

A standard usage of such calcs is to convert results between “Nitrate as N” and “Nitrate as Nitrate” and similar situations. These situations occur because of different labs reporting the result differently, or because guidelines require a particular style.

A different usage of post-import calcs is to maintain a running 95th Percentile for each compound. The post-import calc inserts the 95th Percentile as a new “Environmental Standard”, which is output along with others (such as Drinking Water standards) on tables, graphs or maps.

*The calculation assumes a normal result distribution, and as such the 95th Percentile calculated may differ from packages that may use other distributions, suchas ProUCL.

Timevariant TOC (Top of Casing)

At a practical level in ESdat Top of Casing (TOC) refers to the reference point which groundwater dips are measured from. Generally TOC is considered to be a constant value, and so only one value needs to be used, and is entered in ESdat in the Wells table. Waterlevel Elevations are then automatically calculated from depths by deducting depths from the TOC value.

Occasionally it is necessary to accommodate a varying value for TOC, either through ground subsidence, or the top parts of the Wells to being “excavated”, and new headworks created.

ESdat manages this through the “Timevariant TOC” data table, which allows users to enter the survey date, the TOC, and an Effective Date (useful if an excavation occurs and the survey is taken some months later). The TOC values are then interpolated between these values and the water depth to elevation calculations processed. This is all managed in a database query, which you are welcome to review if you open the database in Access or SQL Server.

For further information about Timevariant TOC search for “TOC” in the help file.

How do you manage Logger or continuous time-series data?

We have seen some examples where users have imported logger data into ESdat using the “Field” or “Historical/Other” Chemistry Templates.

Using this approach results in a large number of samples (one for each time interval) to which the results are attached.

Notwithstanding that the Sample – Result concept isn’t applicable to logger data this is also an inefficient way to store logger data and is likely to impact negatively on speed of data retrieval as well as database size.

Users who are importing logger data should use the specific logger functionality. Users may be more aware of the logger option with the new import form available in version 4.2 but it’s also a case of familiarizing yourself with the other option for chemistry, beyond field / historical / lab.

Result Ranges ESdat

Do you use the Well Code to associate samples with Piezometers? Do you associate Piezometers with Aquifers?


The diagram to the right shows a single Location (“BH01”) with two screened intervals or Wells (“A” and “B”).

It can be seen that it is possible to have multiple wells at a single location.

Location information (ie co-ordinates, site sub-areas/zones etc.) is entered into the “Location” table, and Well information into the “Wells” table.

The Wells table also allows you to indicate the “Monitoring Unit” that the particular well is monitoring. Typically this would be populated with the aquifer name. Other information, such as screened interval, may also be entered.

When importing chemistry data associated with a particular “Well” the correct Location and Well should be specified. When this is done the outputs through ESdat will enable you to filter data for your aquifer unit, screened depth etc. prior to producing outputs such as Chemistry Tables or Charts.