GemTeck Environmental Software develops environmental data management software for groundwater records and water quality monitoring programs

1) Installation of EQWin Data Manager Ver 6
2) Creating and Linking to Databases
3) Data Validation and Import
4) Conversion Process from EQWin Ver 5.0 to EQWin Data Manager Ver 6
5) Reports, Queries and Graphs
6) Calculations and Validation Scripting
7) General Questions/Errors

1) Installation of EQWin Data Manager Ver 6.:

Q6.1:

I have been using EQWin Data Manager for about a month now, but for some unknown reason the main form, tool buttons and menus are not active and are "greyed out".

A:

EQWin Data Manager needs an activation code to operate after 30 days from the original installation date. The first screen that opens when you start EQWin Data Manager contains
a number that is unique to each computer. Once this has been sent to us, we can supply you with an activation code.

2) Creating and Linking to Databases

Q2.1:

How do I open an EQWin database that someone else has created/saved on our local network server?

A:

The technology we use to connect to databases is through ODBC. The details of this connection are stored on the original computer that created that database in the first instance (not like OLE that MS Access uses to create and store .mdb files). Therefore, you need to set up a new DSN (Data Source Number) to that database from the computer that is trying to access that existing database.
The way to do this is to open EQWin System Manager from your computer and use the "Link” Wizard on the Database Set-up tab, and then follow the instructions. Hint: Enter the same name as the original database you’re trying to connect to and then "browse” to the folder on the network that stores that database. Use the drop down menu under "Access File name" and select the one already created by that person....then follow the rest of the instructions on the screen.

Q2.2:

I have moved my Access database file to a new folder on our local network server and now I can’t open it?

A:

The answer is essentially the same as above, due to the ODBC technology use to connect to the databases. Again, you need to re-set up a new DSN to that database from the computer that is trying to access that database. Open EQWin System Manager from your computer and use the "Link” Wizard, and follow the instructions....

Q2.3:

We are having a problem with linking to an existing database

A:

This is generally not an EQWin issue, but a WINDOWS issue. The path/address cannot be longer than 130 characters. What it means is you have to use shorter path names for folders/directories, and store the *.mdb database file near the top of a directory structure.

3) Data Validation and Import:

Q3.1:

Which characters may be legitimately used when importing data into EQWin Data Manager Ver 6? Although EQWin does not appear to accept “.” “,” or “brackets”, some of these characters are necessary in Contaminated Land Assessment, because of Organic Chemistry naming conventions.

A:

Some characters, especially commas, can cause crashes when used in key fields such as Parameter Code and Station Code. These characters are used in SQL, and as SQL is the basis of the query function in EQWin, the crashes may occur when certain SQL query commands are used. Support for commas and other characters is a thing that cannot be rushed into the software and cannot be done quickly, easily, and safely. It would require finding substitutes for, or modifying the behaviour of, some of the most powerful programming commands available in SQL to work with queries and tables.

Allowing spaces presents a very practical and real problem, in that users will surely have trouble figuring out why importing a code like "Total Cu" does not check out against the code "Total Cu" in the database. It's clear here, but not very clear when proportional fonts are used and the items are not side-by-side.

Underscores are fine, as are parentheses, but the characters we had to exclude were commas, semi-colons, spaces, single quotes, and double quotes. Over time, we will work to eliminate these restrictions.

However, parameter code aliases work with all characters and may contain commas, spaces, etc. (except semi-colons, which are used to separate items in the list of aliases).

Q3.2:

What date system does the import process use?

A:

MS Excel uses the system short date format to interpret cell values as dates.

Even with slashes as separators, the date 4/15/03 cannot be interpreted by Excel when the system short date format is set to y/m/d or d/m/y, because 15 is not a month. If Excel is unable to interpret the value as a date, it interprets it as text. As a result, EQWin Data Manager has no date information for the field and has to leave it at zero.

Here is an experiment that can be done in Excel to test whether a cell value is a date:
1. Say the (apparent) date value in cell D4 is 4/15/03.
2. Enter this formula in another cell: =D4+1
3. If D4 contains a date, the formula will add a day. If not, the formula will display a #VALUE error.

The system short date format is set under Windows Control Panel, Regional Options, Date. When working with a lot of dates in Excel, I suggest using a setting with a 4-digit year (yyyy/m/d or m/d/yyyy or d/m/yyyy.)

Q3.3:

Is it possible to convert dates while importing? Sometimes dates are written in European or international format (dd.mm.yyyy or yyyy.mm.dd).

A:

Date formatting must be done in MS Excel in such a way that Excel recognizes the cells as dates. EQWin does not care what they look like, only that Excel sees them as dates.

Q3.4:

I get some odd-looking dates when I use high/low dates on the Date Range dialog.

A:

Well, there may possibly be some “weird” sample dates that have inadvertently imported. The only way to find these “odd-looking” dates is to use the Query function under the Database Maintenance tab in the EQWin System Manager. You can View Contents of Table by selecting the samples table (eqsampls) and by sorting on the CollectDateTime field.

Q3.5:

What coordinate system does EQWin Data Manager Ver 6 handle?

A:

Latitudes/longitudes and UTM coordinates (as Eastings/Northings) are supported in EQWin Data Manager Ver 6, but Local (mine) Coordinate systems are not. Latitudes/longitudes are to be in decimal degrees. The units of the Eastings/Northings also need to be specified (ft, meters) and consistent.

Q3.6:

When creating import templates (for the consistent mapping of data fields in MS Excel to those in the database), does it mean that the date has to be formatted in Excel?

A:

No. If a date field is NOT formatted in Excel, then it assumes the operating system default date format, otherwise it uses the Excel Date format set.

Q3.7:

How do you develop user-defined validations in EQWin Data Manager Ver 6?

A:

Example of water temperature validation:
X = [Temp] < 0 ? "Temperature < 0"
X = [Temp] > 25 ? "Temperature > 25"
The calculation setup dialog has extensive help on the Help tab, and also has a Test button to test the calculation.

Q3.8:

Does EQWin Data Manager Ver 6 recognize/accept symbols such as µ?

A:

Yes, those symbols are acceptable and become part of the spelling. When defining conversions and importing lab data, the spelling must match the original units, as defined in the codes table. You could also define "helper" conversions like:

Original	Converted	Factor
-------------------------------------------------------------------
ug/l	µg/l	1.0

This would handle labs reporting as ug/l when you have µg/l in the database.

Q3.9:

We could output a calculated parameter, but could not work out how to trigger the calculation on importing so a calculated parameter would actually be stored in the database. We got no warnings or errors, and NO data

A:

There is no mechanism to do it via EQWin. It would have to be done in Excel using Excel formulas. I will add a warning about trying to import calculated parameter.

Q3.10:

What does it mean when EQWin says "Merge samples with the same date/time"?

A:

This is for samples where the individual parameters are input in separate steps, e.g., field and lab results. If the sample numbers are not the same, you end up with two samples each containing part of the results. This option combines the results based on station/date/time/sample class so that they appear to be from the same sample (which they originally were). The combining is done at the report stage only; the samples remain separate in the database.

Q3.11:

In reporting, what does the setting "Time Period Merging" indicate?

A:

If you sampled a group of stations once per month, but not all on the same day, the Stations Across report format would have separate rows for each sampling date. This option could be used to arrange all the samples on the same line based on the sample month rather than the day. This only applies to report formats where it makes sense.

Q3.12:

Validation script error?

A:

The problem is in the script itself. The message flags up for some cells and not others, e.g. where FE-D is not greater than FE-T in all cases.

The script as written is: X = [FE-D] > [FE-T] ? X="Dissolved > Total"
The correct script for this is: X = [FE-D] > [FE-T] ? "Dissolved > Total"
There are some related examples in the online help.

Q3.13:

I created 3 user-defined fields (UDF’s) in the Samples table, then proceeded to attempt a sample import. When I went to map these user-defined fields they were not visible. Is there a "refresh" function I must complete first?

A:

No. It is to do with the communication between multiple applications that is the issue. First shut down Excel, EQWin Data Manager and then EQWin System Manager. Then restart all these applications for the UDF's to take effect in the different applications.

If you use the System Manager to modify a database while EQWin is using it, EQWin cannot detect any changes, so you would have to restart it or close/reopen the database after making the changes. That could happen if you start the System manager from the desktop. However if you start the System Manager from EQWin Tools, EQWin closes the database automatically. Basically, it's not advisable to modify a database that is in use by EQWin, so close it first.

Q3.14:

When I Map the Sample Number in Excel, but don't include any values in the column, and then try to import the file, no warning or error message appears during the data checks. However, when we click on the Update Data button it appears as if that the data are imported into the database, but when we checked they hadn't been. Why?

A:

Sample numbers are the primary identification for samples and every sample must have a unique Sample Number in the same way as every station must have a unique Station Code. During sample data import, the Sample Number is used by the scanning procedure to decide which rows or columns of the spreadsheet contain samples. If a Sample Number cell contains no value, that entire row or column is skipped and EQWin continues with the next one. In the following example, row #4 would be skipped even though it contains values in other columns. There would be no errors or warnings because row 4 is not scanned and imported at all.

(Row)	Sample#	Station	Date
1. 2. 3. 4. 5. 6.	2003-205 2003-205 2003-205 2003-205 2003-205	L-1 L-2 L-3 L-4 L-5 L-6	2003/7/15 2003/7/15 2003/7/15 2003/7/15 2003/7/15 2003/7/15

Since this sometimes causes problems, the following messages were added in EQWin Ver 6.0.5.5:

"Count of Sample Numbers found: 5"
"Count of samples scanned and checked: 5"

Q3.15:

When importing data in the LIMS format, in one parameter (Cn-T), the "n" is in lower-case, and is correctly highlighted as an error.... but then so are all the other CN-T parameters with "N" in upper case in the rows below it...but they are obviously correct in uppercase. Why?

A:

This is not a bug, and fixing the lower-case parameter removes the error message from all the rest of the upper-case parameters. There is a fairly complicated technical explanation to this, but suffice to say that it is not a bug, and is expected to behave in this fashion.

Q3.16:

Can there be more than one Sampling Frequency code in this field separated
by a comma?

A:

Although it originally worked as such, it is not going to work in the long run to have multiple codes in one field, as it will be an ongoing source of trouble…. because it violates a basic principle of relational database design.

The rule in EQWin now is: there can only be one code value in a coded field. If you need some combination of codes, set up each required combination as a separate code (the combinations can contain semicolons if wanted):

Codes defined
-------------
M - Monthly
Q - Quarterly
MQ - monthly & quarterly (or it could be set up as M;Q)

To use this field, you can set up a filter on the Filters tab of a query, like this:

SamplingFrequency Contains Q .....(this returns Q and MQ samples)
SamplingFrequency Contains M .....(this returns M and MQ samples)
SamplingFrequency = Q................ (this returns Q, but not MQ samples)

Q3.17:

How is the Sampling Session automatically generated?

A:

The Sampling Session is generated from the operating system date/time, and not from any sample data.

Q3.18:

When I try to retrieve a set of samples by selecting "Select sampling session", the Sampling Session drop down list shows my latest 3 imports as weird numbers (i.e. 20030722141143), and not in a year-month format (i.e. 2003-02) as imported.

A:

EQWin now automatically generates default Sampling Session values if they are not provided/mapped during input. I would therefore say that you possibly imported one batch on 2003/07/22 at 14:11:43.The import checks would have indicated this.

Q3.19:

I've just updated to Ver. 6.0.5 and am encountering strange errors during the checking prior to data import. I've tried everything in Excel, but can't seem to account for why these particular cell formatting are not liked (sample attached).

A:

The problem with this file is that there are soft returns in some of the heading cells. I edited out the carriage returns and did the checks with no trouble.

Q3.20:

What are the Result Code and Results Quality fields used for?

A:

The Results Quality field is for importing formal data qualification codes. These codes refer to reportable conditions that give an indication of the data quality and the reporting accuracy of results. These used for reporting analytical results, which always precede the data qualifier code.

The Results Code field (used in earlier versions of EQWin) has been used as the default field during the conversion process in which to store data from the Results Code field of these earlier versions of EQWin (which may have been used for a variety of other purposes – as only one field was available in these earlier versions). It may also be used for the import of less formal internal observations (maybe comments, rather than formal data qualifier codes) regarding analytical values.

Q3.21:

I’ve got historical data in a LIMS format (1997 to present) and want to import it into EQWin. I’ve been trying to think of an efficient way to assign sample numbers since the number of rows that comprise a sample varies from sample to sample and I obviously want to avoid having to do it manually for the thousands of sample records over the 7 years worth of data. Is there an easy way?

A:

That's a tough problem. If you have station codes and dates in each row, and no duplicates for any station/date, you may be able to set up an Excel formula or macro that combines the two into a sample number.

Q3.22:

I’m using the new EQWin Data Manager Ver 6 and am importing analytical data. I have mapped all the fields, but when I check the data it does not recognize duplicate samples. They are all taken on the same date, at the same place and are analyzed for the same parameters. If you could help me out would be great. I have the sample class as F (for field duplicate) but it doesn't seem to recognize it.

A:

EQWin Ver 5 differentiates unique samples by station, date, time and sample class (duplicates, etc). However, in EQWin Data Manager Ver 6, unique samples are identified by SAMPLE NUMBER. Please ensure that you have unique sample numbers for each sample.

4) Conversion Process from EQWin Ver 5.0 to EQWin Data Manager Ver 6

Q4.1:

How do I change coordinates in EQWin Ver 5 and then import them into EQWin Data Manager?

A:

Use EQWin Ver 5 to edit them before doing the conversions. Change Latitudes/longitudes to decimal degrees, and change UTM coordinates to Eastings/Northings. Because of the informality allowed in EQWin Ver 5 coordinates, that's all we can do.

EQWin Data Manager Ver 6 also supports importing new field values from Excel into the station table. There is an item on the Excel add-in menu under "More, Table Import Wizard". All a person needs to do is set up a spreadsheet with the station codes and coordinates (Latitudes/longitudes and/or local E/N), and import it into the EQWin Data Manager Ver 6 database in order to correct and adjust coordinates. The new EQWin/ArcGIS-8 Interface also supports importing station Latitudes/longitudes from a map.

To get started, you can print the EQWin Ver 5 station table to the clipboard and edit the coordinate fields in Excel. To summarize:
1. Set up the EQWin Ver 5 station view to show station code and coordinate fields.
2. Send the station view to the clipboard and paste it into Excel.
3. Edit the coordinates in Excel and save the workbook.
4. Convert the database to EQWin Data Manager Ver 6.
5. Import the coordinates from Excel using the Table Import Wizard.

Q4.2:

How are EQWin Ver 5 codes converted to EQWin Data Manager Ver 6

A:

EQWin Ver 5 codes are converted as follows:

EQWin Ver 5 codes were not validated
The values of EQWin Ver 5 coded fields are copied to EQWin Data Manager Ver 6
The EQWin Ver 5 code tables are not copied (The method of storing the codes in EQWin 5 was complex and inaccessible to EQWin Data Manager Ver 6 without an inordinate amount of work).
After the conversion, the code tables in EQWin Data Manager Ver 6 are generated from scratch based on the values actually in use in the database.
Hence, unused codes from EQWin Ver 5 will be dropped.

Q4.3:

We use spaces in our database, not only for the UTM coordinates but also for the Top Elevations, resulting in many errors during conversion. I know you've allowed the space format for converting coordinates but maybe not for the Top/Bottom elevations and Open From and Open To fields.

A:

For all the decimal fields in conversion, the ' ' (space), ',' (comma), and '-' (hyphen) characters in Ver 5 will be ignored, i.e. the Top/Bottom elevations and Open From and Open To fields that contain these characters can be converted correctly by upgrading to EQWin Data Manager Ver 6.1.

5) Reports, Queries and Graphs:

Q5.1:

I can now print out a variance report before I update the import data to the database, however no parameters are printed and I get a bunch of numbers with nothing to reference them to?

A:

Having the Variance Report (and Exceedances Report) up front is a new feature. When you print the report, one of the options is the number of std. deviations. Only parameters where the current values exceed that limit in comparison to previous results are printed. If you set it to zero, you should see all your parameters.

Q5.2:

Which Excel chart formats does EQWin’s "Pick-up Format" function works with.

A:

About the only thing picked up is the chart title, which all chart types have I think. We don't try to pick up chart format options because there are too many.

Q5.3:

How do I print out a list of Stations codes (or parameter codes) that are in the database into an Excel spreadsheet?

A:

Here is how to print out a list of codes into MS Excel:
1. Connect to the database required.
2. Click on the SQL button on the EQWin toolbar.
3. Enter the following SQL statement:
select stncode from eqstns
select paramcode from eqparams

Q5.4:

How do I check to see what Sample dates there are in the database?

A:

Here is how to check the sample dates in a database:
1. Connect to the database required.
2. Click on the SQL button on the EQWin toolbar.
3. To see samples listed in ascending date order, enter the following SQL statement:
select * from eqsampls order by collectdatetime
4. To see samples listed in descending date order, enter the following SQL statement:
select * from eqsampls order by collectdatetime desc

Q5.5:

How do you select the Parameter Code to be the default in a Parameters Across
report?

A:

You can't. It's always the name that's used. However in Ver 6.1 you can modify the title when you’ve designed the report.

Q5.6:

I’m battling a bit with the new Pick Up Formats for Excel reports, Are there any tips I need to bear in mind?

A:

In order to pick up formats, EQWin must keep track of three things:

the selected query (.eqr) file in EQWin
the Excel workbook to which the output was sent
the Excel worksheet to which the output was sent

If any of these items is changed between the time the output is sent to Excel and the time the format is picked up, EQWin will be unable to pick up the format. Follow these tips when formatting output:

1. In EQWin, do not change to another tab (page) before completing the format capture. Leave the .eqr file as the active tab.

2. In Excel, make sure that the worksheet containing the output is front-most before capturing the format. If you need to switch to another worksheet, make sure to switch back to the output sheet before using the "Pick Up Format" and/or "Pick Up Page Settings" commands.

6) Calculations and Validation Scripting:

Q6.1:

Calculated Parameters: When a parameter is calculated from other parameters how does EQWin deal with values < the MDL? e.g. NO2+NO3 (calculated parameter) is equal to the [NO2] + [NO3]; where, [NO3] = 0.007 mg/l and [NO2] <0.005 mg/l

A:

As regards <MDL, EQWin Data Manager takes the values at "face value" and ignores the less than sign (<), and uses a factor of 1.0. If you wish to know whether there is a < MDL in one of the parameters in the calculation, then I suggest including all parameters in the calculation in the query so that you can compare the calculated result with the parameters used in the calculations. Also, the "Detection Level" field for calculated parameters is unavailable, and can't be used as it is in measured parameters.

7) General Questions/Errors:

Q7.1:

Error message "eqwin1.xls could not be found"

A:

Checking the Registry for Eqwin1.xls, when

1. This procedure has to be done from the problem machine using the log-in that is having problems.
2. Quit both Excel and EQWin.
3. Run Windows Registry Editor. This can be done from the Windows Start menu under Start, Run. The program to run is called either regedit.exe or regedit32.exe.
4. When the Registry Editor starts, find the top-level branch under My Computer called HKEY_CURRENT_USER.
5. Under HKEY_CURRENT_USER, open successive branches as shown in the diagram:

Software
	Microsoft
		Office
			8.0 or 9.0 or 10.0 or 11.0 (which one depends on your Excel version XL97=8 / XL98=9 / XL2000=10 / XP=11)
				Excel

6. Under the Excel branch you should see the following two branches, among others:
Add-in Manager
Microsoft Excel
7. Double-click the Microsoft Excel branch. If it contains any items (it should), they will be listed in the right-hand pane. If you find any OPEN item (OPEN, OPEN1, OPEN2, etc.) that refers to Eqwin1.xls, delete it.
8. Double-click the Add-in Manager branch. If it has any items, they will be listed in the right-hand pane. If you find an item that refers to Eqwin1.xls, delete it.

Q7.2:

What does it mean, to “Compact” a database?

A:

To “Compact” a database is a term used by MS Access to reduce the free space that accumulates over time as a result of general use (over time the database increases in size) – something like defrag!

Q7.3:

Is the ">" sign handled the same way as the "<" sign for a data value?

A:

Yes

Q7:4

What’s the best way for customers to document and report software error messages and bugs to GemTeck?

GemTeck requires a documented step-by-step process of the events (keystrokes and applications opened) that preceded the software error message or bug occurring and we therefore request that you complete following steps prior to reporting an error or bug:

Document all of the steps and keystrokes you take to generate the error message or bug. We suggest that you create and retain this information in a Word document for your records and then cut and paste the contents into out Report an Issue form.
In your documentation, be sure to include:
- applications open at the time of the error
- sequence of opening these applications
- where the applications were opened from i.e. directly from the Excel and/or EQWin tool bars, individually from the desktop icon, from the Start menu, etc.
Now repeat these error message or bug "sequence of events" again using the EQWin Demo data database to see if the same error message or bug occurs.
If the error message or bug does NOT occur with the Demo database, then try to replicate the error message or bug situation with your database again but using the following procedure:
- Close down all open applications
- Shut down and restart the computer
- Open EQWin from the desktop tool button
- Open Excel from the desktop or by clicking on Start / Programs / Microsoft Excel
- Do not open any other applications
- If the error message or bug occurs again, note down all the steps and keystrokes you have taken again
Now complete the Report an Issue form ensuring that you "cut and paste" all of the documentation into the form, and send it to GemTeck Technical Support. Thank you in advance for your assistance in effectively resolving software issues.

Q7.5:

I have seen that the units conversion maintains significant digits, but do calculated fields (i.e. Calculated Parameters)?

A:

For Calculated Parameters the default number of significant figures in calculation results is based on the (look-up) parameter value in the calculation that has the most significant figures. In addition to this, calculation results can be formatted in Excel, or the default number of significant figures (or decimal places) can be overridden and limited by using the #D and #S macros in the EQWin calculation script:

In the EQWin calculation script use #S=3 as the first row to report results to 3 significant figures.
In the EQWin calculation script use #D=2 as the first row to limit results to 2 decimal places.

Q7.6:

What do you mean the module provides the translation capability for the user?

A:

Because EQWin is used in so many languages, we could never afford to develop EQWin in 10+ different languages, so we created a "library of terms" system whereby the user translates the English in the LH column into the language of their choice into the RH column. Have a look at our website under http://www.gemteck.com/prodGISinterface.htm, and read the information there and click on the relevant links. Hopefully this would give you a better idea. The only drawback of this system is that this is only applicable to the User-Interface, whereas the on-line help still remains in English.