iFAQ Search

Using Enviro Data

Crosstab Export

How can i save a parameter's result that does not have a numerical value like color or pass-fail?

Why am I getting a Run-Time Error 1004 when creating a Crosstab Export?

What causes a 'Record To Large' error when creating a crosstab?

How can I use a Crosstab Export Templates to create a custom header and footer on my tables?

Crosstab Export Templates

The crosstab export now permits selection from multiple customizable crosstab templates that can display site name and address and station name information. Custom logos can also be printed on the Excel reports. Templates features and examples including their output, are provided below and allow you to use a custom headers and footers with the crosstab. How to set templates up and modify them is outlined below along with samples of each templated provided. Note that while you must select an Export Template, you can choose a blank one from the Select Export Template dropdown shown below. If none are shown, click Locate to select one.

Crosstab Wizard Grouping Formatting tab select template

The crosstab templates are saved in the Enviro Data C:\Enviro\EData8\Templates folder. Because each template file contains coded instruction on the format to be created, Geotech strongly recommends that the first time you open one, promptly do a Save As and save it with a different name in the C:\Enviro\EData8\Templates folder and add it to the Datasheet. This way you will have your own copy that can be used to create new custom formats. A several example templates are provided with Enviro Data.

crosstab example using template 8d

Note: for informatoin on the Version 8D feature adding NL to reg. limits or NM to results please see the FAQ on How can I show in a crosstab that no measurement taken for a sample or that there was no Reg. Limit for a specific parameter?

Crosstab templates are saved in the Enviro Data C:\Enviro\EData8\Templates\CrossTabTemplates folder shown below. Because each template file contains coded instruction on the format to be created, Geotech strongly recommends that the first time you open one, promptly do a Save As and save it with a different name in the C:\Enviro\EData8\Templates\CrossTabTemplates folder and add it to the list of available templates as shown below. This way you will have an original copy that can be used to create new custom formats. A several example templates are provided with Enviro Data. Instructions for modifying these templates can be found in the User Documentation.

crosstab template files 8d

The Crosstab Templates from is used to select and locate the crosstab templates. You can also add a friendly Description to these files and if needed you can also delete any existing template files that are not needed. Any new template (XLSM) files should be copied into your C:\Enviro\EData8\Templates\CrossTabTemplates folder or a shared location on a server.

crosstab template selection form 8d

This is the Datasheet form of the Crosstab Templates table.

For more information on creating, editing and using crosstab templates please see the Crosstab Export Template section of the Documentation.

Permalink Print

How can I display multiple regulatory limits in my crosstab report?

General Questions

Why are my undetected results now reporting the wrong Value after upgrading to Version 8D?

Why am I getting an error "Can't connect to" GCSLICENSE.MDE?

What are Detection Limits and Limit Types and how should they be stored and used?

Non-Detected results are common in environmental data and Enviro Data provides a complete set of tools for handling them. Using these allow you to specify exactly how you want the results displayed with options such as < .01, .01u, ND, and so on. By using detection limits along with the other features of Enviro Data, you can even specify a numeric factor or value, such as zero or half the limit, to report for the result, for special situations like contouring, statistics, or risk analysis.

The detection limit and the type of limit is provided by the lab. For example, Detect might be 0.01 and the LimitType could be “RL” and it is not unusual for a lab to provide multiple detection limits. However, the limit’s reporting units (not duplicated in the EDD) must be the same as the analytical value’s units.

The limits and limit types for each result should be in the electronic data deliverables or EDD file during the importing of data provided by the laboratory. See screenshot below. During the import, they are stored in the Detect and LimitType fields in the Analyses table. Detect is the value of the detection limit and the limit type is the type (usually an abbreviation) of the limit for that specific limit. For example. Detect might be 0.01 and the LimitType could be “RL”. The limit’s reporting units (not duplicated in the EDD) must be the same as the analytical value’s units. You can store up to five detection limits for each result in these paired fields: Detect / LimitType, Detect2 / LimitType2, etc. to Detect5 / LimitType5. The screen shot below is from the DTSFormat8.xlsx import electronic data deliverables (EDD) file showing the fields that the limits are imported from. While there are only three limits available in the DTSFormat8 EDD, you can import up to five (total) with the User-Defined Import.

detection limits in edd

Users have the ability to choose which of the five available columns to use to store their various detection limits. Typically, the “Detect” and “LimitType” columns are used for the RL or PQL limits, and the “Detect2” and “LimitType2” columns are used for the MDLs. There’s no set rule, but obviously if you only have one limit you need to use Detect / LimitType. Also, it’s imperative to be consistent. If the detection limit columns in your databases contain a mixture of different detection limit types, Geotech can help you run a few queries to get each limit type consistently assigned to the desired column.

You can select which value you wish to display in the Non-detect Options section of the Display/Graphing Options form as shown below.

select detect linit option

You can also select multiple detection limits in the Crosstab Wizard Export.

detect linit crosstab

Most users, however, will choose to display (whichever limit they need) for non-detected results in the Value and Flag field selected in conjunction with the Analytical Flag codes.

Permalink Print

Why are my Calculated Parameters not giving the same results as in Version 2012 or 8x?

What should I do before contacting support?

Before you request support please read ALL of the following,

Have you checked the Documentation for a solution to your problem? the documentation can be downloaded using the Download button in both the Editor and Viewer.

Have you checked the Frequently Asked Questions on our website for a solution to your problem? We have a large set of short articles on the most frequently encountered and asked about issues from our clients.

Note that both the User Documentation and FAQs are updated regularly so check back whenever you have a problem or need more information.

When asking for help, please do not send your request to a specific individual but contact us at support@geotech.com. This way, if a specific individual is not available, everyone can see it and someone else can jump in. Also, even though one person maybe handling your request, another might have a suggestion or solution that could solve it. Also, please use Reply All when responding.

We are here to help you but please, provide us sufficient information to help us understand the issue you are having problems with. This needs to include:

• What version of Enviro Data are you having the problem with? This information can be found on the main menu of both the Editor and Viewer and may be different for each. Examples would be Ver. 8 A 1.73 or Ver. 8B 1.3.
• Is the problem with the Editor or Viewer?
• What specifically are you trying to do? This might include something like: “I was trying to create a Crosstab Export and had selected a Reg. Limit Group, but no Reg. Limits showed up in the export. Or “I was trying to import new records from my lab using the DTS8 format and Excel and I got an error saying _____.”
• Are you not getting any results from your selections?
• What were you expecting the output to look like?
• Did you get an error message(s)? If so, send a screen shot.
• Is this happening on multiple computers?
• Is this a new problem or one that has been occurring for a while?
• Is your Enviro Data database local, on a network or in SQL?
• Are you using a shared Library File for lookups? (If you do not know what this is, you probably do not have one.)
• What version of Microsoft Office are you using and is it 32-bit or 64-bit?
• Screen shots are always helpful. These may be of your Select Data or Display Options forms, any error messages you receive, a copy of any output or anything that might be relevant. You can paste screen shots into your message or attached them to the message in JPG or PNG format. Just make sure they are large enough to be legible.
• Let us know if your issue is urgent or routine.

If you have more than one issue/question, please separate them clearly in your message. Solving an issue efficiently depends primarily on good communication. If you have multiple issues that need to be addressed, please make each clear and separate them in your message. Using numbers or bullets would be best.

Have you already contacted Geotech about this issue? If so, please provide a copy of that response. This should be obvious but Use Reply All when replying to any support message on the same topic so there is a history of what has transpired.

If this had been working last time, please describe anything new that has been changed since it worked.

If you did not knowingly make any changes that you expected to affect the results, please say so. Do not go into great detail; just mention what has changed. If the change might be relevant to the issue, we will ask for more details.

Have you made any changes, hardware or software, including but not limited to?

• Installed Enviro Data on a new computer.
• Upgraded to a new version of Microsoft Office.
• Switched from one version of Enviro Data, including updating either the Editor or Viewer alone, include that information in your message.
• If you are on a network, or using SQL Server, have any changes been made to that setup?
• Has your system crashed recently?
• Are you using a new/different/older database?

Have you had this same problem in the past? If so, what was done to fix it then?

Describe the output you expected and the output you received. People sometimes assume that the desired outcome is obvious. It often isn't, especially to someone who is not in the same room with you. Screen shots are always helpful. It's surprising how often people report that they tried something and "it didn't work", but do not describe what happened. If you expected a specific output or result, but there was none, please state that explicitly. Screen shots are always helpful.

If we ask you any questions, please read them carefully and then respond to each of them. We often add numbered steps or questions to our responses. It is very important to follow and/or answer each question. The relevance of our questions may not be apparent to you, but remember, we know how the program is supposed to work.

If you do not respond to our specific questions, you may delay finding your solution. If we ask you to choose a single data record or group of records and answer one or more questions using it, please do exactly that.

General Comments

Support time is deducted from your prepaid support account however, if we determine if the problem is a bug in the software, it will be fixed at no charge.

In many cases we may ask you to provide us with a subset of your database, your Settings file and/or any import files you are working with. You can find out how to create a subset here. Note all Access database you send must be zipped up in order be allowed into our email system.

If necessary, we will setup an online conference using Microsoft Teams. (This may require that your IT department gives permission for you to do this.) Voice communication can either by using your computer’s microphone and speakers (or a headset) or by a phone call, whichever is most convenient. This way we can see what is happening on your system to get a better idea what the problem is and how to resolve it. If needed, we may also ask for control of your computer to diagnose or fix the issue. There is no cost for this conference. Before the conference begins we recommend that you check out ZDNet's Ten golden rules for newbies and pros, especially if you have not done this before.

With your help, will attempt to address your issue as soon as possible. However, it may be necessary to assign priorities based on our mutual schedules.

If we determine that your issue is a bug or is a new feature that requires a change to the code, this could take additional time to find a solution. New features are generally preformed under a billed project once we agree on a scope of what is needed, and authorization is obtained. If you have any questions, please feel free to contact us.

Permalink Print

Why does the Quick List and List buttons on the Select Data form display the data different?

How are Duplicates and Supersedes handled in Enviro Data?

For quality assurance and quality control (QA/QC) purposes, environmental projects generate duplicated data in a variety of ways. Care needs to be taken with this duplicated data at both the samples and analyses levels. Enviro Data can handle duplicated data at both the samples and analyses levels. To import this duplicated data, the system uses two fields, DuplicateSample and Superseded, which are numeric fields in the Samples and Analyses tables respectively.

Duplicate Samples.

Duplicate sample are one or more additional samples taken from the same station at the same time to check the quality of the sampling, shipping, and analysis processes. This information is stored in the DuplicateSample field in the Samples table.

At the sample level, this data could be QA/QC data associated with an original sample such as a field duplicate. As an example, an original sample assigned a DuplicateSample number of zero might have a field duplicate taken on the same day, which would be assigned a DuplicateSample number of 1. The QCSampleCode in the Samples table is used to explain the meaning of the two samples, with perhaps a code of “Original” for the first sample, and a code of “Duplicate” for the second. Additional related samples are given higher DuplicateSample numbers. In some cases, the additional sample might be a second original sample instead of a QC sample. In this case, the QCSampleCode might be the same for both samples, however, in most cases the LabSampleID number would be different.

Enviro Data identifies unique samples based on the station, sample date, sample matrix, sample top, duplicate sample, sample bottom, filtered sample, field sample ID, QC sample code and alternate sample ID fields as seen in Figure 134 below. This allows the data administrator to import multiple samples related to one sampling event or make multiple imports of analyses from a given sampling event.

Fields used to define the Unique Index in the Samples Table

Figure 134 - Fields used to define the Unique Index in the Samples Table

Superseded Analyses

A superseded analysis is a duplicated result from one or more a re-analysis of the same sample for a single or group of parameters for QA/QC purposes. This results in duplicated or superseded results at the analyses level. In the industry these results can be represented in two ways, either as the original result plus the re-analysis, or as a superseded (replaced) original result plus the new unsuperseded result. This information is stored in the Superseded field in the Analyses table.

During import Enviro Data provides a way to supersede the original result value as this is more useful for selection purposes. You can choose to supersede or replace (overwrite) a superseded result by checking or unchecking the Supersede Values If Present checkbox in the IMPORT WIZARD – OPTIONS AFTER CHECKING After Successful Check section. If this box is checked, a new analysis will supersede the old one. Otherwise, if it is unchecked, the new analyses will replace (overwrite) the old one. Either way, in the when selecting data, you can easily choose to see just the most current (unsuperseded) data. Selecting the re-analyzed results is not as helpful because not all the parameters may have been re-analyzed.

Enviro Data identifies unique analyses based on sample number, parameter number, superseded, analytic method, leach method, report units, basis, filtered analyses code, QC analysis code and lab sample id fields as seen below in the figure below. This allows the data administrator to import multiple analyses related to one sample or make multiple imports of analyses from a given sampling event. Re-analyses or analyses delivered at separate times are matched to the correct sample event by the import code using the unique fields listed above.

Fields used to define the Unique Index in the Analyses table

Figure 135 – Fields used to define the Unique Index in the Analyses table.

Discussion of Duplicates and Supersedes

Examples of data at these two levels, and the various fields that can be involved in the duplications at each level, are shown below.

Diagram of duplicates and supersedes

Figure 136 - Diagram of duplicates and supersedes

In the Sample Level example in Figure 136 above, there are three samples from the same well on the same date: an original sample, a field dup, and a split. These are represented with three different DuplicateSample numbers, and three different LabSampleID numbers.

In the Analysis Level there are superseded results for two different reasons.
In the field four different pH values were taken, most likely to confirm that the well chemistry had stabilized prior to taking a sample. The results have increasing superseded values from 0 to 3, with the 0 value being the most recent and presumably most representative value.

For naphthalene, multiple results were reported possibly due to matrix interference. The dilution factor for each result is shown. The original sample is reported with a superseded of zero, and the dilutions with higher superseded values. The ReportableResult field has been used to override which result will be reported. An alternative would have been for the data administrator to adjust the superseded values so the value for the result at a 50:1 dilution had a superseded value of 0 and it would be displayed by selecting a superseded value of 0 on the SELECT DATA form.

At the analyses level, the Superseded field is incremented when different analyses apply to the same sample and parameter. For example, multiple dilution analyses can be stored with incremented superseded values of 0, 1, 2, etc. Likewise, a re-analysis can be stored with a superseded value of zero (most recent data), while the original value remains in the database with a superseded value of 1. Note that this use of superseded values is the opposite of the more traditional view of new data for the same parameter as being a “re-analysis.” Experience has shown that while both the superseded and re-analysis view of the data validly describe what happened, the superseded approach is more useful for selecting data, since you can select the “unsuperseded” data (Superseded = 0) to suppress all but the most representative data. This is the default on the SELECT DATA form in the VIEWER program, so for the most part people get what they expect without much effort.

While you can manually change or delete the values in the Duplicate or Superseded Field Criteria boxes in the Select Data form, this form now had a dropdown control that will toggle them between a value of 0 (zero) and nothing (Null). This control can be found near the left side the Action Buttons Bar. “Display 0” is the default setting and how Enviro Data has historically opened the Select Data form. Caution: This should only be used to show the specific duplicated and/or superseded information when outputting data for calculations, exports, and reports.

Default Duplicates and Superseded in Select Data form

The default is to leave this control set to “Display 0”. This means that both the Duplicated or Superseded Fields Criteria boxes will contain a 0 and duplicated or superseded data will NOT be provided in the dataset. If this control is changed to “Nothing”, the default of 0 is removed from both the Duplicate and the Superseded Fields Criteria boxes. The resulting dataset WILL contain every duplicated sample and every superseded result.

The ValueCode field provides a reason for the superseded value, such as re-analyzed, dilution, etc. however, the ValueCode field is not used to order the superseded sequence. The software sets Superseded values if one of the Duplicates and Supersededs options on the IMPORT WIZARD form, shown below, is selected as described in Duplicates and Supersedes in the User Documentation.

Figure 137 - Duplicates & Superseded settings in IMPORT WIZARD form

Correctly assigning QCSampleCode is crucial to identifying original vs. QC data as the QCSampleCode field is used to correctly identify original data vs. QC data for reporting. If you are importing data with QC sample information, you must populate the DuplicateOrder field in the QCCodes lookup table before importing as the Import Wizard uses this field to set your duplicate values based on the duplicate order.

Understanding duplicate and superseded values is very important for understanding QC and other data in Enviro Data. Selecting data based on duplicated and superseded values is discussed in Select Data section of the documentation.

Permalink Print

Why am I getting the error message saying that "not all unit conversions were successful"?

Why are trailing zeros not showing up / decimal places not displayed as imported?

There are multiple reasons why trailing zeros are lost and/or decimals places are not displayed correctly. Options for decimal places can be found in the EDD that is imported, the import setting and in Display Options when the data is reported or exported. (See screen shots below.)

1. Our standard EDD has a NumberDecimals field that the laboratory can populate when importing data. If this field is used, Enviro Data stores that information in that field in the Analyses table for use when the result is displayed.

2. If the NumberDecimals field in the EDD is not populated Enviro Data calculates the number of decimals from the Value field and stores that number. If this is the case, you can choose to use the decimal places from either the Value or the Detect field in the EDD.

Number of Decimal option in the Import Wizard

Options for # of Decimals in the Import Wizard in the Editor

3. When preparing reports or exporting data, there is the Number of Decimals option in Display/Graphing Options that can determine how the decimal place to be displayed. The options are Exact Value or Auto. Setting this option to Auto will use the NumberOfDecimals value stored in the Analyses table to add back trailing zeros if necessary. Choosing Exact Value turns this feature off and uses the Value field as it is stored The Auto option is necessary because Microsoft Access (and Excel for that matter) do not normally store or display trailing zeros. Loss of these zeros can be a problem because they contain information about the precision with which the result was measured.

Number of Decimals option in Display Options

Options for Number of Decimals in the Display/Graphing Options form in the Viewer

4. Once you have the correct decimals places stored and output, the Viewer outputs the Value of an analysis with the correct decimal places based on the option chosen.

Your results will vary depending on the output chosen. For the Crosstab Export, Value and Flag is always stored as text and this is recommended. However, the Value (only) field is numeric. If you are depending on the Value field only, this can be problematic because Excel does not store or display trailing zeros in a numeric field.

Note that if you are having a problem with decimal places and/or significant figures, you should check with Support to see if there is a newer version available. Also, the screen shots shown above may vary slightly depending on your version of Enviro Data.

Trailing zeros are NOT supported in regulatory limits as they are meaningless.

Permalink Print

How does Enviro Data use Analytical Flag Codes?

Note that most of this section also applies to Validation Flags that use a similar form and table structure.

Flag Codes are not required and generally are not checked to see if they present are in the Analytic Flags or ValidationFlags tables and Flags not in the Analytic Flags table may be ignored in ValueAndFlag formatting. Flags used in the Analyses.FlagCode field that are not in the Analytic Flags table can cause a problem when moving to a newer version of Enviro Data. This means you need to be extra careful when using them.

Analytical Flag Codes

A FlagCode is an analytical flag that describes the analysis.

It is important to understand that in Enviro Data there are two similar but different FlagCode fields, one in the AnalyticFlags lookup table and a different but similar one in the Analyses data table. The FlagCode field in the Analyses table contains one or more FlagCodes from the AnalyticFlags table. When multiple codes are used in the Analyses table, they must be separated by either a comma or a space.

AnalyticFlag in the Lookup Table

The FlagCode in AnalyticFlags table is a lookup that stores a single code of up to four characters and cannot contain any spaces or commas. The AnalyticFlags table stores the following:

• FlagCode is the code for a ONE Analytic Flag. It can contain multiple characters but cannot contain a space or comma.
• AnalyticFlag is description of the code.
• ReportingFactor is used as a multiplier for statistical analysis. Note that this can be impacted by selections in Non-Detect Options on the DISPLAY OPTIONS form.
• ReportingBasis indicates how display the output. See examples in Reporting Basis.

NOTE: Use Analytic Flags Table must be selected in the Detected Value Options and Same as Detected Values in the Non-detect Option on the Display/Graphing Options form as shown below for the Value and Flag field to be displayed in the ReportingBasis format in Reports or Exports.

Use Analytic Flags Table selected in Detected Value and Non-detect Options in DISPLAY OPTIONS form.

Fig. 18 - Settings for Detected Value and Non-detect Options in Display/Graphing Options form.

• ReportingPriority determines the flag’s priority in formatting the value for output. The flag with the lowest number will determine the format. Note that ReportingPriority is an integer and only accepts whole numbers. See examples of this in Figure 21 and Figure 22.

Note the field Indicate Non-Detect was replaced by the DetectedResult field in the Analyses table after Version 2012.

To view the flag codes, open the ANALYTIC FLAGS form. This can be done either from the Edit Analytic Flags button on the Display Options form in the Viewer (shown on the screen shot above) or from Manage Lookups Analytic Flags button in the Editor. This form can be seen in Figure 19.

You can scroll through the Flag Codes by using the page control at the bottom left. You select how each Flag Codes displays using the ReportingFactor, Reporting Basis, and/or ReportingPriority options. (Note: Setting a flag as a detected or non-detected is no longer an option on this form. Use the DetectedResult field instead.)

analytic flag form

Fig.19 - ANALYTIC FLAGS form in Form View

This table can also be edited in Datasheet View as shown in Figure 20, below, by clicking the Datasheet button.

AnalyticFlags

Fig. 20 - ANALYTIC FLAGS form in Datasheet View

Note that the FlagCode or “Y”, and its description AnalyticFlag of “Calculate Value” is a required value because it is used in some calculations. Even if you are not doing any calculations, if this flag is not present, the ValueAndFlag field may not be displayed correctly.

Examples for Reporting Basis

Reporting Basis Examples

FlagCode in the Analyses Table

The FlagCode in the Analyses data table can contain one of more analytic flags that are separated by spaces or commas. These are usually imported with your analytic results. They are not checked against the AnalyticFlag lookup table during an import.

• The order of the flags doesn’t matter, when being output to a report or export the program is only looking for the lowest ReportingPriority.
• You can have any number and combination of FlagCodes, up to the 25-character limit in the Analyses table.
• Commas or spaces always indicate that multiple FlagCodes are present.
• If the analysis is considered a usable value, and would not otherwise have a qualifier, this field can contain the code for Detected Value (usually a “v”).
• If the analytical flag is unknown, it may contain a “z” but this not required.
• The FlagCode field may be left blank if desired.

Example of Flag Code Usage

One example is if data is imported with FlagCode “U,B” which should indicatesa that the result is a not-detect and there was a problem with a blank. Because of the comma, these are two distinct qualifiers (flag codes). The AnalyticFlags table should have two records: one with FlagCode = “B”, and another record with FlagCode = “U”. It should NOT have a record for FlagCode = “U,B”. (Ignore all quote marks.)

Controlling How Data is Displayed

The formatting decisions that you make as described in this section are used for most types of output, so you should see the same result whether you are printing a report, exporting to Excel, or posting the data on a GIS map.
For reporting, Enviro Data uses the Reporting Factor and Reporting Basis entered below to format the display of the result. Where multiple Flag Codes are reported for a result, the Reporting Basis of the Flag Code with the lowest Reporting Priority is used to determine the format.

Here’s what happens when Enviro Data formats ValueAndFlag in SelectedData during an export.

1. The Value and Flag handling parses FlagCode from the SelectedData table (retrieved from Analyses) into its two separate flags (“B” and “U”) and sorts them by first ReportingPriority assigned to the code in the AnalyticFlags table, then alphabetically. If a code is not found in the AnalyticFlags table, that code is ignored.
2. The flag with the lowest ReportingPriority wins, and its ReportingBasis is used to format the result.
3. If both FlagCodes in the AnalyticFlag table have the same ReportingPriority, the code that appears first in the alphabet wins.

For example, you could display your non-detects with a "<" sign and the detection limit. Or you could display them as "ND" or " - ". But for results with a "J" flag, you could display the value followed by the flag. See examples in Table 1.

Note: The FlagCode “v” for “no code required” is generally automatically stripped out of exports and reports.

The Value and Flag function uses the Reporting Basis shown below in Table 1 for each character of the analysis FlagCode. It also finds the Reporting Factor from the AnalyticFlags table based on the first character of the analysis FlagCode. This factor is used to calculate the number displayed for non-detects, if that option is selected on the DISPLAY OPTIONS form.

Examples of Value and Flag Formats

Table 1- Examples of Value and Flag Formats

If there are multiple flags, i.e. b j, and you wish to print j in reports, use the Reporting Priority on the ANALYTIC FLAGS form to set the hierarchy determining which flag is used in the display. Again, if two or more flags are encountered, the flag having lowest Reporting Priority value is used. If two or more flags have the same Reporting Priority, then the priority is determined alphabetically.

An example of this is a analytical result with the FlagCode of “u,d” for undetected and diluted respectively. If the ReportingBasis for the “u” was “< and Detection Limit (Detect1) or Value” you would expect the Value and Flag field to be something like “< 0.06”.

However, if both have a ReportingPriority value of 1 in the AnalyticalFlag table, then the program would use the ReportingBasis of the “d” flag which does not contain a “<” so the results would not be formatted as a non-detect. See the examples in Figure 21 and Figure 22 below. Value/Flag format where Reporting Priority for flag codes "u" and "d" are the same.

Fig. 21 - Value/Flag format where Reporting Priority for flag codes "u" and "d" are the same.

Value/Flag format where Reporting Priority of flag code "u" is 0 and for "d" is 1.

Fig. 22 - Value/Flag format where Reporting Priority of flag code "u" is 0 and for "d" is 1.

Note: If the Value/Flag is not displaying correctly after checking all of the above, check Parameter Units to see if it is set correctly for the parameter(s) in question.

Permalink Print

What are Lookup tables and how does Enviro Data use them?

What are Parameter Units and why do I need to set them?

Why were no records returned when I selected data?

What happened to the Enviro Data internal map?

How do I store geological data?

How can I view or list my data?

How can I use the NEW Enviro Data Lab Master Services Agreement Tool?

How can I set the File Open dialog to open in my C:\Enviro\EData8 folder?

How do you create an Enviro Data Subset?

My computer with Enviro Data is not running right. What can I do to fix it?

Many times, a problem with Enviro Data can be traced to a problem with the computer itself. Below are some of the solutions to try when something does not seem right with your computer. While these may not solve your problem, they will not harm your computer. They only take a few minutes to run and rely on tools built into Office and Windows. We recommend you run them in the order shown. While these steps apply to all versions of Windows, the terminology may vary slightly from one version to another.

Note that we are assuming you are running an up-to-date antivirus and/or antimalware application.

If you are having a specific problem with Enviro Data, try these steps first.

Restarting usually solves most issues. Before restarting, make sure you have saved any settings or files you need. Note we are not talking about turning your computer's power off.
Click on Maintain Database and then click Clear Temporary Tables.
Close Enviro Data and then and open the database file you were attached to in Access then run "Compact and Repair*. This should only take a few minutes. Note that this step does not apply to databases on SQL Server.
Open both the EDITOR and VIEWER programs and run Compact and Repair in each from Access’ Database Tools menu.
Check to see if you are still having the problem. If so, continue below.
Check your hard drive to see how much free space you have available. It is recommended to have at least 10% free space. You can check this by opening the Windows Explorer in My Computer view (this will vary depending on the Windows version) and check to see how much space you have free on your hard drive(s). If you see a red bar, you need to move or delete some files to free up space. (If you cannot delete or move any files you may need to get a larger drive.) To help free up space run the Windows Disk Cleanup (CTRL-R and type in disk cleanup).
While still in Windows Explorer, right click on the C:\ drive and choose Properties then use the following steps. Click on the Tools tab. Click on Error checking and then click the Check button. You may see a warning; "You don’t need to scan this drive.” Do it anyway. This may only take a few minutes unless there are serious errors on your drive. (Repeat this for other drives as appropriate.)

We typically do not recommend reinstalling Enviro Data unless directed to by our Support staff.

If you are still having problems with Enviro Data after going through the above steps, send our Support section message and we will setup a Teams meetngto assist you.

* See Compact and Repair a database at Microsoft.com

Permalink Print

Are there any characters or symbols that will cause problems if I use them in my data?

Why do my forms take up the whole page or are in tabs in Access?

Importing Data

I am having trouble importing an EDD. How can I troubleshoot this Import?

On occasion, an import file will not import either producing an error, showing many duplicate / superseded results, or having some other problem. Sometimes these issues can be hard to track down. The most common issues are "Import Problem" errors/problems, bad dates, missing lookup data, and occasionally a corrupted Excel file.

If you get to the EDIT IMPORT FILE form and see errors of problems in the Import Problems form see the FAQ on How does Enviro Data use Analytical Flag Codes?

The rest of this section will provide a few steps that might help identify and eliminate other types of problems.

If the EDD is not in the correct format, you may see the error message below. There are many available import formats so first and foremost, make sure you have selected the appropriate format in the File Type and Format box on the IMPORT WIZARD form that matches your lab's file. While Version 8 will import an EDD in the DTS2012 format, you still have to select that format.

ImportErrorPopup

When the import file does not precisely match one of the supplied formats, you may be able to use one of the two User-Defined formats might do the job. These are Columnar and Crosstab. The former can be an Excel or delimited file while the latter is an Excel file.

We recommend that an Excel EDD file only contain a single worksheet unless multiple worksheets are specifically supported for the chosen import format. If the file you are attempting to import has multiple worksheets and/or the first worksheet in that file is not in a recognized import format you can see the error below.

field too small import error

Setting up your EDD file in order to check it for problems

If your file is in one of the supported formats but you are still having problems, you can use the steps below to organize the EDD in Excel to help identify the problem(s).

Since most of these steps require some knowledge of Excel, we have prepared a 5:30 min YouTube video. troubleshooting_edd_setup.mp4, to assist you in performing the steps below. (Note that the video does not follow the exact steps sone. Also, when recording the video, each time the video was paused, Excel lost focus and we had to re-select the entire sheet.) If you are familiar with Excel, you likely can skip the video.

Open the DTS Import file that you are checking in Excel.
Make a note of the bottom-right or last cell in this spreadsheet. This would be the bottom row number and the far-right column number.
Click inside the spreadsheet and using the keyboard press the CTRL and Home keys together to place the cursor in cell A1.
Now press CTRL and END keys together. This should take you to the cell identified in Step 2 above. If it does not, the spreadsheet needs to be fixed to remove these "extra empty" data cells before importing. These cells could be extra rows and/or extra columns.
Insert a blank row into row 1.
Open the import example file for the import format being used. This might be C:\Enviro\Edata8\DTSFormat2012.xls or C:\Enviro\Edata8\DTSFormat8.xlsx depending on your import format.
Highlight the top row with the field header names in the example file and Copy it.
Back in your import file, in the blank top row you created above, Paste the example DTS header you just copied.
This will give you two rows of headers, a correct set in Row 1 and the original ones in Row 2.
Click in the upper left-hand corner of the spreadsheet to select all records.
All records must be selected for the following steps through 14 to complete correctly.
Right click in any cell and select Format Cells then in the Alignment tab make sure Wrap text is not checked.
Right click in any one of the columns (A, B, C, etc.) and choose Unhide so all columns are displayed. (If all records are not still selected go to step 8.)
Right click in any one of the rows (1, 2, 3, etc.) to display any hidden rows. (If all records are not still selected go to step 8.)
Change the font and font size to something easy to read. We personally like Calibri 11 or 12.
Turn on Filter. (All columns should now show a Filter dropdown.)
Double click one of the dividing lines in the header between columns to resize each column so all contents are displayed (without text wrapping)
Compare EACH columns header in Row 1 to the original header in Row 2 and correct any errors.
Note, if you have selected the wrong example format header or, your import format is non-standard, you will need to take extra steps to resolve this.
Check to make sure each required field identified by RED font is populated.
Using Filtering and Sorting to compare the data in the file to make sure it matches the required format.
If all the header names are present and correct, but they are not in the correct order or you are missing some, you may have to use the User-Defined format.

Correct Headers - If the header row has incorrect fields due to spelling, you really should contact your lab to have them supply the EDD in the correct format. However, if you need the file imported in a hurry, the User-Defined Columnar Excel/CSV import will allow you to map the incorrect fields to ignore the order and/or misspellings.

Formatting of Columns -All the data in each column must be the same data type. You can check this by highlighting a column and right-clicking in the header cell and choosing Format Cells. This will tell you what the data type is for that column. If the column should be Date, that should be the format shown. If not, you may need to change the format. You can sometimes see a formatting problem simply by looking at the data in the column. For example, if there are mixed number and text values, you will see this by the justification of the content. See the example below.

edd bad format in columns

To correct this error, highlight the entire AnalyticMethod column then right-click the AnalyticMethod column and select Format Cells. On the Number tab of the Format Cells form change the Category from “Number” to “Text”, and then click OK and make sure you save your file.

Special Characters - Some characters or symbols can cause problems when used in your data. The issue is that Access may treat them a reserved program code. The primary character that causes problems is the single quote symbol that can also represent a foot symbol, but there are others. See the FAQ Are there any characters or symbols that will cause problems if I use them in my data?

Dates – Date fields require special attention because Access can be problematic when importing dates from Excel. Dates in incorrect formats need to be corrected prior to import. This can be done with your import file in Excel. First click once above the SampleDate_D (or other date field) header to select the entire column. Next, right-clicking on one of the dates and choose Format Cells. In the Format Cells form you can then select Date in the Category window and the type of date in the Type window.

Null (Blank) Dates -- All normal samples should contain the sample date in the SampleDate_D field. Many times, the lab will not include a Sample Date for lab QC samples. There are two ways to work around this. If the sample in questions is truly a lab QC sample just check the Allow Null Dates (shown below) in the Samples section of the Import Wizard - Data Checking Options form, The other way is to copy one of the lab dates (LabRecvDate_D or AnalDate_D) into the SampleDate_D field.

allow null dates

Filtering – You can use Excel's Filter function to see what data is in each column and also filter and/or sort it. To set up filtering, click in the upper left-hand portion of the spreadsheet (above the 1 for rows and to the left of A for columns) to select all cells in the worksheet. Next, on the Home ribbon, click on Sort & Filter and choose Filter. By applying filters to each column missing or incorrect data can be identified. Once a column is filtered, use the dropdown arrow in the column you wish to exam to view the contents. If Blanks are shown, uncheck Select All and then check blanks to see if data is missing, or if this is just an empty row of data.

Sorting – Once a Filter is set, you can sort the columns individually, both in ascending and descending order, by clicking on the drop down at the top-right of each column. By sorting, you can verify that the data range is accurate, there are no alphabetic characters in date or numeric field and that all (required) fields are populated correctly. Blanks should also come to the top when a column is sorted in ascending order.

Duplicate / Superseded issues - We recommend checking to see if the FieldSampleID, the LabSampleID or AltSampleID fields are populated. If they are, choose the most appropriate one of those other fields in the first Import Wizard form for instead of using Auto.

dupes Supersedes

Glitches in the File – At times, there can be a problem in the data that cannot be seen visually. These are usually caused by some data corruption in the file. Saving the file in a comma delimited CSV format and importing it in that format may correct these glitches.

Copying to Another File – As a last resort, you can copy the data into a new spreadsheet, taking care to only select the cell range that only contain your results.

User-Defined Import - Many times an import file may have missing fields in the header, or one or more field names is spelled wrong. When this happens, you can use the User-Defined import with either an Excel or CSV file.

If you are unable to import your file after going through the above checks, Geotech Support can assist you in resolving the import issue(s).

Permalink Print

When importing data why am I getting the error "The default Analytic Flag or some other field is invalid"?

How do I determine what constitutes a unique sample and/or a unique analysis?

To use as an example to answer this question, a client asked: We report multiple different units and filtered analysis…e.g. the analysis is Chlorophyll, the filter is lab 0.22, the units are mg/L and then for the same station I also have a field 0.45 and units are mg/L for reporting OrthoP for the same station. How is that handled in Enviro Data.

In this scenario, a sample taken at a site and filtered in the field with a 0.45u filter, all these analyses would have the FilteredSampleCode of F45U. When the lab reports Chlorophyll back they would use a FilteredAnalysisCode of L22U (for Lab 0.22u). The OrthoP result would have its own FilteredAnalysisCode of TOT. The units for both would be whatever the lab reports. (See Filtered table below.) However, if the OrthoP sample was not filtered, it would be a different sample even though it was filtered in the lab. They obviously would not be the same Analysis since the Parameter is different.

filtered table

Filtered lookup table.

Also, if these two samples did not all have the same criteria as shown in the unique index for the Samples, they would not be the same sample.

Criteria for a Unique Sample

Enviro Data identifies unique samples based on the station, sample date, sample matrix, sample top, duplicate sample, sample bottom, filtered sample, field sample ID, QC sample code and alternate sample ID fields as seen below. This allows the data administrator to import multiple samples related to one sampling event or make multiple imports of analyses from a given sampling event.

unique sample table

Fields used to define the Unique Index in the Samples Table.

Criteria for a Unique Analyses

Enviro Data identifies unique analyses based on sample number, parameter number, superseded, analytic method, leach method, report units, basis, filtered analyses code, QC analysis code and lab sample id fields as seen below. This allows the data administrator to import multiple analyses related to one sample or make multiple imports of analyses from a given sampling event. Re-analyses or analyses delivered at separate times are matched to the correct sample event by the import code using the unique fields listed above.

unique analysis table

Fields used to define the Unique Index in the Analyses table.

Permalink Print

How do I fix an "ImportProblem" error/problem during import?

I imported a file that produced many Duplicate/Superseded errors. How can I fix this?

If you have imported an EDD and received a large number of Duplicated or Superseded records, you probably should undo the import file and learn more about how Duplicates and Superseded results are handled in Enviro Data before trying to import them again.

Undoing an import is simple and can be done by going to the Import Wizard form and clicking on Undo A Previous Import. This will display a list of EDDs imported where you can select the problem file. Before clicking the Undo Selected Import button be very careful that you select the correct file and that you have a copy of the file being deleted so you can re-import it later. Typically, all you need to do is to select a different choice from the Duplicates & Supersedes radio buttons for a new import.

An explanation of how Duplicates and Supersedes are handled follows.

Enviro Data allows duplicated data at both the sample and analysis levels. The Duplicates and Supersedes section of the Import Wizard - Data Checking Options form provides several options for handling of duplicated data. If you do not have the Duplicate and Superseded fields correctly populated in your import file, you MUST select one of the following options, which uses additional fields to determine duplicate samples.

Options for Duplicates

Auto - This option looks for unique samples based on StationName, SampleDate_D (without time), SampleMatrixCode, SampleTop, DuplicateSample, SampleBottom, FilteredSampleCode and QCSampleCode or LabSampleID (if QCSampleCode is ‘Unknown’). If all of these are the same for the next sample, then the DuplicateSample field is incremented.

The recommended import format contains a QCSampleCode field. The import logic utilizes this field for ordering and assigning the DuplicateSample number. Enviro Data groups samples by the unique fields, orders the records based on the DuplicateOrder field in the QCCodes table, then increments the DuplicateSample number by comparing the QCSampleCode of each sample. These codes can be entered by the lab or by the data administrator before import. If the QCSampleCode is not known or is left blank, it defaults to “Unknown”. The code then compares the LabSampleID numbers of each sample to set the DuplicateSample number.

Field ID - This option looks for unique samples based on StationName, SampleDate_D (with time), SampleMatrixCode, SampleTop, SampleBottom, DuplicateSample, FilteredSampleCode, QCSampleCode and FieldSampleID. If QCSampleCode is ‘Unknown’ then it just looks at FieldSampleID. If all of these are the same, the DuplicateSample field is incremented.

Alt ID - This option looks for unique samples based on StationName, SampleDate_D (with time), SampleMatrixCode, SampleTop, SampleBottom, DuplicateSample, FilteredSampleCode, QCSampleCode and AltSampleID. If QCSampleCode is ‘Unknown’ then it just looks at AltSampleID. If all of these are the same, the DuplicateSample field is incremented.

For these import options, correctly assigning QCSampleCode is crucial to identifying original vs. QC data as the QCSampleCode field is used to correctly identify original data vs. QC data.

Clients who are dealing with large amounts of QC and multiple samples per day may want to consider removing the Duplicate = 0 and Superseded = 0 defaults on the SELECT DATA screen. If you need to remove this, be aware that this will be changed back to the default with any new program updates. Call Geotech for more information about changing this program feature.

If you are importing data with QC sample information, you must populate the DuplicateOrder field in the QCCodes lookup table before importing. The Import Wizard uses this field to set your duplicate values based on the duplicate order. For example, if the duplicate order for your QC codes were Original = 1, Duplicate = 2, and Split = 3, then for samples with those codes you would end up with duplicate sample numbers of 0, 1, 2 respectively.

EDD - The next option uses the duplicate and superseded values as they are contained in the import file. Use this option if whoever is providing you with the data sets the values correctly in the EDD.

Lab ID - The final option uses the laboratory identification number from the import file to determine duplicates and superseded values.

Set Initial Dup/Sup to Zero - This option sets both the Duplicate and Superseded fields to zero prior to import.

Options for Superseded Analyses

Superseded values for are set using the unique key of SampleNumber, ParameterNumber, Superseded, AnalyticMethod, LeachMethodCode, ReportUnitsCode, Basis, FilteredAnalCode, QCAnalysisCode and LabSampleID. Unless the EDD option is checked, when a new analysis is imported matching this unique key is imported, the Superseded field for the original analyses is incremented.

Permalink Print

What Should I Do About Missing Parameters During Import?

During an import, the wizard checks to see if all the parameters are found in the Parameters table. If a match for each parameter was not found using the options shown below, Enviro Data then displays the parameter match screen to assist with assigning valid parameter names. This form allows you to specify alternate spellings, or aliases, for parameter names for use during import and output. These values are specified the same way as other lookup values, using the Parameter Aliases button on the MANAGE LOOKUPS form. The intention of this feature is to handle situations where the laboratory reports the parameter using a different spelling from the one in the Parameters table. An example would be an alias of 1,2-Benzanthracene for Benz(a)anthracene. It is not intended to intercept misspellings, although it is sometimes used this way. Entries in the ParameterAliases table can either have a site associated with them, in which case the alias is used only for that specific site, or can have a site number of zero, in which case it is used for all sites.

This import check is Accomplished by checking the spelling or CAS number (if the Use CAS Numbers option has been selected). Just be careful using the CAS number option as the CAS number may not be unique or standard for a specific parameter. You can also import by Other Parameter ID (such as STORET) if you are using this field.

If some parameters could not be found in your database, you will be presented with a list of these. You are given the option to either match the "new" parameter to an existing one, add the parameter, or create an alias for an existing parameter.

It may be best at this point to take a screen shot of this list and then cancel out of the import. Using this list, you can research to determine if each is already in the Parameters table under a different name. If so, you can then create an alias for that parameter either during your import or using the PARAMETER ALIAS form accessed from Manage Lookups.

Parameter Alias Form

If you find new parameter that is not in your database, it needs to be added either manually or during the import.

Permalink Print

My EDD wont import or I get errors when importing. How can I troubleshoot an Import?

On occasion, an import file will not import either producing an error, showing many duplicate / superseded results, or having some other problem. Sometimes these issues can be hard to track down. The most common issues are "ImportProblem" error/problem, bad dates, missing lookup data, and occasionally a corrupted Excel file.

If you get to the EDIT IMPORT FILE form and see errors of problems in the ImportProblems form see the FAQ on How do I fix an "Import Problem" error/problem during import.

The rest of this section will provide a few steps that might help identify and eliminate other types of problems.

ImportErrorPopup

field too small import error

Setting up your EDD file in order to check it for problems

If your file is in one of the supported formats but you are still having problems, you can use the steps below to organize the EDD in Excel to help identify the problem(s).

Since most of these steps require some knowledge of Excel, we have prepared a 5:30 min YouTube video. troubleshooting_edd_setup.mp4, to assist you in performing the steps below. (Note that the video does not follow the exact steps sone. Also, when recording the video, each time Ithe video was paused, Excel lost focus and we had to re-select the entire sheet.) If you are familiar with Excel, you likely can skip the video.

Open the DTS Import file that you are checking in Excel.
Make a note of the bottom-right or last cell in this spreadsheet. This would be the bottom row number and the far right column number.
Click inside the spreadsheet and using the keyboard press the CTRL and Home keys together to place the cursor in cell A1.
Now press CTRL and END keys together. This should take you to the cell identified in Step 2 above. If it does not, the spreadsheet needs to be fixed to remove these "extra empty" data cells before importing. These cells could be extra rows and/or extra columns.
Insert a blank row into row 1.
Open the import example file for the import format being used. This might be C:\Enviro\Edata8\DTSFormat2012.xls or C:\Enviro\Edata8\DTSFormat8.xlsx depending on your import format.
Highlight the top row with the field header names in the example file and Copy it.
Back in your import file, in the blank top row you created above, Paste the example DTS header you just copied.
This will give you two rows of headers, a correct set in Row 1 and the original ones in Row 2.
Click in the upper left-hand corner of the spreadsheet to select all records.
All records must be selected for the following steps through 14 to complete correctly.
Right click in any cell and select Format Cells then in the Alignment tab make sure Wrap text is not checked.
Right click in any one of the columns (A, B, C, etc.) and choose Unhide so all columns are displayed. (If all records are not still selected go to step 8.)
Right click in any one of the rows (1, 2, 3, etc.) to display any hidden rows. (If all records are not still selected go to step 8.)
Change the font and font size to something easy to read. We personally like Calibri 11 or 12.
Turn on Filter. (All columns should now show a Filter dropdown.)
Double click one of the dividing lines in the header between columns to resize each column so all contents are displayed (without text wrapping)
Compare EACH columns header in Row 1 to the original header in Row 2 and correct any errors.
Note, if you have selected the wrong example format header or, your import format is non-standard, you will need to take extra steps to resolve this.
Check to make sure each required field identified by RED font is populated.
Using Filtering and Sorting to compare the data in the file to make sure it matches the required format.
If all the header names are present and correct, but they are not in the correct order or you are missing some, you may have to use the User-Defined format.

edd bad format in columns

Special Characters - Some characters or symbols can cause problems when used in your data. The issue is that Access may treat them a reserved program code. The primary character that causes problems is the single quote symbol that can also represent a foot symbol, but there are others. See the FAQ https://staging.geotech.com/resources/faq/using-geotech-software/general-questions/are-there-reserved-symbols-in-ms-access-that-will-cause-problems for more information.

Null (Blank) Dates -- All normal samples should contain the sample date in the SampleDate_D field. Many times however the lab will not include a sample date for lab QC samples. There are two ways to work around this. If the sample in questions is truly a lab QC sample just check the Allow Null Dates (shown below) in the Samples section of the Import Wizard - Data Checking Options form, The other way is to copy one of the lab dates (LabRecvDate_D or AnalDate_D) into the SampleDate_D field.

allow null dates

dupes Supersedes

Copying to Another File – As a last resort, you can copy the data into a new spreadsheet, taking care to only select the cell range that only contain your results.

If you are unable to import your file after going through the above checks, Geotech Support can assist you in resolving the import issue(s).

Permalink Print

How do I import Lab and Field Data?

The last two data levels in Enviro Data are Samples and Analyses. These are generally imported together using an Electronic Data Deliverable (EDD) file from your lab. This is convered in depth in the Getting Started and Documentation available from either the EDITOR or VIEWER main screen and in the EDITOR Tour. The steps shown below are general and provide an overview of the process.

1. Using one of the DTS format files, such as DTSFormat8.xls, as a template, create an Excel sheet with your samples and results information. SuccessfulImport8.xls is an example that you can consult when populating your own import files. These files are located in the Enviro\Edata8 folder in your directory. Notes: All lookup code columns with red captions must be populated. You may use “z” as a default code for lookups (SampleTypeCode, etc.) SampleTop, SampleBottom, DuplicateSample and Superseded can be left blank or default to zero. If you have digital data in a columnar format, but different from one of the Enviro Data DTS formats, you can use the User-Defined Excel option in the Import Wizard. Contact Geotech if you need help with that.

2. After constructing your import file, click on Import Data on the EDITOR MENU.

3. Select the File Type and Format that matches your import file format.

4. Click Select at the right of the File Path and Name text box. Navigate to the file you made and click Open.

5. Click Finish.

6. The HELP MATCH STATIONS form may open if there’s a discrepancy in spelling Station names. You can use the dropdown on the right to find the correct station, or click Add Station to add a new station, if needed. After matching stations click Finish.

7. Help screens for matching or adding new parameters and matching reporting units may appear if needed.

8. When all is correct the import will run to completion and provide a summary of what was imported. Additional report screen may be displayed if they were selected. .

Permalink Print

Regulatory Limits

Can I combine a set of regulatory limits into groups?

What are Regulatory Limits and how do I use them?

Regulatory Limits let you compare the incoming data to one or more currently selected regulatory limits to see if any of your data exceed regulatory standards. Of course, you will have to have entered (or imported) the regulatory limits that you want to compare to. Depending on the report, you may apply up to 12 regulatory limits to your data. Where you wish to compare one of more set of limits, you have to setup Regulatory Limit groups for the limits you have entered.

In Enviro Data you can enter the limits into the program, or import them from a spreadsheet file, which may be easier, especially if you have them in digital form.

There are two parts to setting up the limits - creating the limit types and entering the limits.

From the EDITOR, click on Manage Lookups in the Setup group and click on Reg. Limit Types.

Regulatory Limit Types form

This will bring up the Regulatory Limit Types form where you can add the regulatory limit type that you will be adding limits for. You can also set several options for how the limit will be displayed when a limit is exceeded. These include any permit options, whether you wish to display an exceedance when the result is at or exceeding the regulatory limit, the report order that will be used for various limit types, the background color when a limit is exceeded and various font options.

For example, you could enter “FA” for the Reg. Limit Type Code, and “Factory” for the Regulatory Limit Type. Then click on Close. Fill out the Activity Log and click on Done. Close the Manage Lookups form.

Now that we have entered the limit type we can import the limits.

Enviro Data comes with spreadsheet files that make it easy to import your limits. ImportRegLimits.xls is an empty file in this format, and ImportRegLimitsExample8.xls contains some example data. Fields in red font are required fields. Of course, the RegTypeCode and RegType must be the same as you entered above.

import reg limit spreadsheet example

Open the ImportRegLimits8.xls file and enter some limit data. Save the file under a new name, and close Excel.

Select Import Data from the Editor Import / Edit section. This will open the form seen below.

Select “Reg Limits Import” from the dropdown box under File type and format. Use the Select button to browse for and select the file you created.

reg limit import wizard

The remaining options in the Import Wizard apply only to analytical data import, so we don’t need to go through them.

Click on Finish to proceed with the import, then OK. You will see a popup telling you how many limits were imported.

You don’t have to import the limits before you import your samples and analyses. In fact, you don’t need limits at all unless you want to use them in your retrievals.

Beginning with Version 8D, reg. limits can now be tied to specific Stations.

For a better understanding of Regulatory Limits and Regulatory Limit Groups we recommend you see the section on Regulatory Limits in the Basics section of the User Documentation.

Permalink Print

How can I display multiple regulatory limits in a crosstab export?

Why are my Regulatory Limits not showing up in reports or exports?

Regulatory Limits contain multiple required fields that must be matched with your data for them to work as expected. Several of the most common issues that will prevent them from being used in reports or exports like the crosstab are summarized below. Note however, the required settings in Reports and the Exports (like the Crosstab) must also be set correctly.

Sample Matrix Does Not Match the Sample or Not Set

The sample matrix of the reg. limit must match the matrix of the sample being compared for a comparison to be made. If the sample was imported with a Sample or Lab Matrix different from the regulatory limit, then no comparison can be made. The most common example of this is where the lab EDD uses a matrix of "Water" when the sample matrix is "Groundwater". This will need to be corrected either in the Sample or Analysis data.

The matrix for each individual parameter can be changed in the Editor --> Manage Lookups --> Parameters. If the Matrix needed is not shown, click on "Unknown" and using the dropdown select the correct Matrix and Units and click on OK. (Note that Unknown will simply be moved down one row when this is done.)

Parameter Units section of the Parameters form to Set Matrix

If you need to change the data and the data has recently been imported, the easiest option is to Undo the import, correct the EDD and reimport. Otherwise, you will need to Edit either the Sample or the Analyses data.

If the Regulatory Limits have the incorrect Matrix, you can edit this by clicking on the Regulatory Limits button in the Editor and next clicking on the Reg Limits button. This opens the Edit Regulatory Limits form where you can filter for the desired limits and then use the dropdown in the Matrix column to make changes.

Editing the Regulatory Limits Matrix

Reporting Units Can’t be Converted or No Conversion Available

While Enviro Data will attempt to convert both the analytical and reg. limit units to match to what is set in Parameter Units. If there is no conversion factor in the Unit Conversion table, the reg. limits with those units will be ignored because the reg. limit and analyses units do not match. The most common example is where a soil parameter with a unit of kg/mg is being compared against the same parameter but for ground water with a unit of ug/L.

If a conversion had not been setup, one can add a unit conversion from the Editor --> Manage Lookups --> Unit Conversion (under Multiple Tables).

Null (Blank) Values

One other problem is where a reg. limit was created without providing a limit value in the RegLimit (value) field. This can be a little tricky to detect and correct but below are two ways to find and fix these.

Editing Records in RegLimits Table

The quickest way to find null reg. limits is to find and open the RegLimits table by pressing the F11 function key on your keyboard to open the object viewer in Access. (If the Tables is not selected, click the drop-down in the upper right and select "Tables" as shown below.)

reg limit in table view
Finding the RegLimits table in Access.

Use the drop down in the Limit field and Filter for Blank records as shown below.
You can either correct or delete these records as needed, However, ensure that you filtered on the correct field, and that only records with no value in the Reg.Limits field are deleted.

reg limits filter for blank

Setting a filter to select only blank Reg. Limit records in the RegLimits table.

Editing Records using Edit Regulatory Limits form

In the Editor, click on Regulatory Limits and then Limits in the Setup group to open the Edit Regulatory Limits form. In that form, click on the drop-down icon in the Limit column as shown below and select Sort Smallest to Largest. This will bring all those with no value to the top of the list.

reg limits sort on form

Sorting records in the Edit Regulatory Limits form.

Scroll to the right a few rows at a time using the scroll bar at the bottom of this table until you can see both the Limit and Enabled columns in your screen.
Turn off each of these “nulls” by unselecting them in the Enabled column.
Note that there are two tabs on this form (highlighted on the left). These are for limits at All Sites or Site-Specific If you have limits for each, you need to do this for repeat Step 3 for each tab.

reg limit edit enable

Enabling or turning off Reg. Limits in the Edit Regulatory Limits form

Permalink Print

In a Crosstab, how can I have a reg limit number show up with a note or an asterisk (*)?

Upgrading / Database Conversion

I upgraded to Enviro Data 8 and I am getting a "missing or broken reference" error. How do I fix this?

Why weren’t all of my tables converted when I upgraded to the new version of Enviro Data?

An Enviro Data database contains a complex set of tables each with specific structures and requirements for their fields. Each minor upgrade, 8C to 8D for example, Imay have new tables, fields, in some cases increased referential integrity and checking of your data during the upgrade.

Before starting, if you are trying to upgrade an old table that you have not used in a while and are not sure what version format it is in, you need to determine that first. You can do that by opening the database in Access, pushing the F11 key on your keyboard (which opens the table listing) and then opening the Versions table. The table's version number should be shown in the first record. If it is an Enviro Data 2012 table, it can be updated simply by attaching to it in the Editor and following the prompts. Databases used with version 2010 or older will need to be converted in steps to the next version and then to version 2012 before upgrading to Version 8.

If your database is older than Version 2012, please contact Geotech Support. for assistance.

If you are using a recent version, and simply upgrading to a newer version. simply open your new Editor, attach to your database, and follow the directions.

Checking Structure Updates

To ensure that everything is converted correctly, Enviro Data 8 displays an Update Structure Summary showing the number of records in the main tables both before and after the conversion. The example below shows the main tables before being converted. (Note that the other tables are also being updated in the process but only the main ones are shown.)
update data structure form before conversion

If the structure in one or more of these tables violates data integrity, possibly because it had been modified, or some other problem, this will cause the conversion to be flawed but the resulting database will still be attached to the Editor. When this happens, you will see in the form shown below that one or more “Converted” tables do not contain the same number of records that the “Initial Record Count” showed.

YOU MUST PAY CLOSE ATTENTION TO THE UPDATE STRUCTURE SUMMARY FORM TO MAKE SURE YOUR DATA WAS CORRECTLY CONVERTED.

In the above case, an extra field had been added to the Analyses table by the client. Additionally, the RegLimitType table contained some records with descriptions that were not unique.

The issue Geotech encounters the most when upgrading a database to the Ver. 8 format is where a client has modified one or more of the tables in Enviro Data by adding additional fields. In the case above, the extra field in the Analyses table was simply deleted. the non-unique descriptions were modified, and the database was able to be converted.

A more difficult issue to find is a problem in a lookup table. If a lookup table fails to convert. You will not see it listed, but this problem will prevent a related table to not be converted with other tables dependent on it. Most lookup tables contain a field for both a code and for the code’s description. And all these descriptions must be unique in Version 8. This was not required in Ver. 2012.

Another example is where a RegUnit in the RegLimits table has a code for a unit that does not exist in the ReportingUnits table. To identify this can require creating a query with the relationship between the table in question and the appropriate lookup table.

So far, the other most common problem of this type has been in the RegLimitTypes table where limits were imported from a spreadsheet with a RegType that exceeds the 50 characters limit that the field can hold. This means the RegType (the description), is truncated and is now identical to one for a different reg. limit. The easiest way to find these is to open the table in question, sort the description field alphabetically and look for long descriptions that match. The fix is to abbreviate them to 50 or fewer characters and make them unique. In the example below (from the same database as above) the Reg. Limits had been imported with some of their descriptions longer than 50 characters. When these were truncated, they became identical. See below, and how they were corrected.
regLimitConvtBad
Reg Limit Types with unique descriptions
This fix also applies to other lookup fields where the descriptions may not be unique. The issue is figuring out which tables to check. You can get a clue of which lookups to check based on which of main table (starting with Sites) that did not convert correctly. For example, if the Stations table had a problem, check the lookups shown under Stations using Manage Lookups in the Editor. In the example above, you probably don’t have to check every Station lookup, only those in the “Show Less” tab of the form.

Manage Lookups form Stations section View each lookup in Datasheet view sort the description field and look for identical values and correct any you find. Then move on to the next lookup table for the appropriate table. If you can’t find the problem there, you can click Show More and check more of the Stations lookups.

After a successful upgrade, we recommend moving the new Version 8 database into the C:\Enviro\EData8 folder if you are working on a local copy.

When you start the conversion process the source database is copied with “_GCSBackup” appended to the file name. Additionally, the source database is not touched at all, so you have two backup copies.

If after reviewing the above you still need assistance, please contact Geotech Support.

Permalink Print

I have old Enviro Data databases. Can I use them with Version 8?

Do I need to convert an Enviro Data 2012 file for use with Version 8?

Can I use an Enviro Data 8 database with version 2012B?

What are the steps to upgrade an Enviro Data database to a newer version?

Converting an Older Enviro Data Database

Introduction

Before beginning, please read this entire Introduction section down to Specific Steps for Conversion.

When first using a new version of Enviro Data, or if needing to upgrade an Enviro Data database used with an older version of Enviro Data, it must first be converted. This generally involves adding any new tables and/or new fields to your old database.

An Enviro Data database contains a complex set of tables each with specific structures and requirements for their fields. When newer versions are released, changes are generally made to the database structure including adding tables, fields and specific properties of fields. In Version 8, In order to protect the data integrity, we have also increased referential integrity by checking of the data in may tables during this upgrade.

If you are trying to upgrade a database that you have not used in a while and, you are not sure what version it is in, you need to determine that first. You can do this by opening the database in Access and on your keyboard click the F11 key which opens the table listing. Make sure you are in the table list and then open the Versions table. The database's version number should be shown in the first record. However, if you have been actively using this database and have just upgrade your Enviro Data program you will not need to do this.

If this database is in the MS Access .MDB format, follow the directions below. However, if the databse to be converted is a SQL Server database, a special proceedure must be followed to upgrade it to the newest Enviro Data 8 table structure since the new tables and/or new fields must be added using SQL scripts. Geotech can perform the upgrade for you during a short Teams meeting. Please contact Enviro Data SQL Conversion Support to set this meeting up.

Note that if your database is Version 2010 or older, you must first convert it to a 2012 version format. If you need assistance with this, please contact Enviro Data 2010 or Older Conversion Support.

Specific steps for Conversion

For version 2012 or earlier version of the 8 format, simply attach to the database in the latest version of the Editor. This is done by opening the Editor and clicking on Attach Database on the main Editor form to open the Attach Database form and select the target database usig the Select Access File button.

If the database you are attaching to does not need a conversion, you will simply be attached to it. If however, it does not meet the criteria for the new version of your Editor, you will get the following error message.

Note this in the example below, we are upgrading a Version 2012 database.

Database Attachment Errors message

Click OK and you will see the Update Structure form. Clck OK to continue or Cancel to cancel the conversion.

update to ver8 database

Note that you may see slightly different working in the form above if you are converting a Version 8 database, but the rest of the steps are the same.

Click OK and Enviro Data makes a copy of your database is created in the same folder but with "GCSBackup" appended to the file name. For this example, the backup file was named EnvDData2012_GCSBackup.mdb

You will also be presented with a summary of the number of records in the main Enviro Data tables. Note that you will have a chance to review these in the next form.

update data structure

Click OK to start the conversion.

When the conversion has completed you will see a comparison of the tables from above along with the number or records actually converted.

update sucessful

CAREFULLY CHECK THE RECORD COUNTS FOR EACH TABLE AS THEY MUST MATCH. If they do not match, see the FAQ Why weren’t all of my tables converted when I upgraded to Enviro Data Version 8? - Geotech Computer Systems, Inc.

If these record counts match, the conversion was successful. The converted file is stored in the original folder with "(Version8)" appended to the name. In this example the new file was named EnvDData2012(Version8).mdb.

Permalink Print

iFAQ Search

Using Enviro Data

Crosstab Export

How can i save a parameter's result that does not have a numerical value like color or pass-fail?

Why am I getting a Run-Time Error 1004 when creating a Crosstab Export?

What causes a 'Record To Large' error when creating a crosstab?

How can I use a Crosstab Export Templates to create a custom header and footer on my tables?

Crosstab Export Templates

How can I display multiple regulatory limits in my crosstab report?

General Questions

Why are my undetected results now reporting the wrong Value after upgrading to Version 8D?

Why am I getting an error "Can't connect to" GCSLICENSE.MDE?

What are Detection Limits and Limit Types and how should they be stored and used?

Why are my Calculated Parameters not giving the same results as in Version 2012 or 8x?

What should I do before contacting support?

Why does the Quick List and List buttons on the Select Data form display the data different?

How are Duplicates and Supersedes handled in Enviro Data?

Why am I getting the error message saying that "not all unit conversions were successful"?

Why are trailing zeros not showing up / decimal places not displayed as imported?

How does Enviro Data use Analytical Flag Codes?

What are Lookup tables and how does Enviro Data use them?

What are Parameter Units and why do I need to set them?

Why were no records returned when I selected data?

What happened to the Enviro Data internal map?

How do I store geological data?

How can I view or list my data?

How can I use the NEW Enviro Data Lab Master Services Agreement Tool?

How can I set the File Open dialog to open in my C:\Enviro\EData8 folder?

How do you create an Enviro Data Subset?

My computer with Enviro Data is not running right. What can I do to fix it?

Can I connect to my Enviro Data database saved in a File Share in Azure Blog Storage?

Are there any characters or symbols that will cause problems if I use them in my data?

Why do my forms take up the whole page or are in tabs in Access?

Importing Data

I am having trouble importing an EDD. How can I troubleshoot this Import?

When importing data why am I getting the error "The default Analytic Flag or some other field is invalid"?

How do I determine what constitutes a unique sample and/or a unique analysis?

How do I fix an "ImportProblem" error/problem during import?

I imported a file that produced many Duplicate/Superseded errors. How can I fix this?

What Should I Do About Missing Parameters During Import?

My EDD wont import or I get errors when importing. How can I troubleshoot an Import?

How do I import Lab and Field Data?

Regulatory Limits

Can I combine a set of regulatory limits into groups?

What are Regulatory Limits and how do I use them?

How can I display multiple regulatory limits in a crosstab export?

Why are my Regulatory Limits not showing up in reports or exports?

Sample Matrix Does Not Match the Sample or Not Set

Reporting Units Can’t be Converted or No Conversion Available

Null (Blank) Values

Editing Records in RegLimits Table

Editing Records using Edit Regulatory Limits form

In a Crosstab, how can I have a reg limit number show up with a note or an asterisk (*)?

Upgrading / Database Conversion

I upgraded to Enviro Data 8 and I am getting a "missing or broken reference" error. How do I fix this?

Why weren’t all of my tables converted when I upgraded to the new version of Enviro Data?

I have old Enviro Data databases. Can I use them with Version 8?

Do I need to convert an Enviro Data 2012 file for use with Version 8?

Can I use an Enviro Data 8 database with version 2012B?

What are the steps to upgrade an Enviro Data database to a newer version?

Converting an Older Enviro Data Database

Introduction

Specific steps for Conversion

Error Messages

How do I fix a Run-time error 13 - Type Mismatch?

Why am I getting the error "Cannot open any more databases"?

Why am I getting the error message saying that "not all unit conversions were successful"?

What causes a 'Record To Large' error when creating a crosstab?

Why am I getting a Run-Time Error 1004 when creating a Crosstab Export?