Standardising your Chemistry Lookup list in Access databases.

Having a list of ChemCodes in your ESdat database that is consistent with those being issued by the labs leads to great efficiency gains during data import. Having your compound names, groups, order, and output units suitable for reporting is key to realising efficiency gains in your outputs, such as Chem Tables or Maps.

A "Standard" Chemistry Profile is maintained by ESdat support. ESdat Server users can automatically synch their system to this standard at the click of the button (requires ESdat Server 3.5 or later). For those using ESdat with an Access database this option isn't available.

The following guidance is to completely replace your local Chemistry Lookup with the current "Standard" Chemistry Lookup Profile when using ESdat with an Access database. This only applies for a new database, or for your template database. This can't apply to a database that has chemistry data already in it as it won't be possible to delete your existing Chemistry Lookup information.

The steps are as follows:

1. Open your database in ESdat.


Delete your existing Chemistry Lookup Information.

2. select Data Tables - zRef_Combine_Compounds. If this table is populated delete all the rows. (for all deletes you can dump the data to Excel first if you wish to retain a copy before deleting)

3. select Data Tables - zRef_Environmental_Standards. If this table is populated delete all the rows.

4. select Data Tables - zRef_Chemistry_Lookup. Delete all the rows.

Import the new information

5. Create an import template for zRef_Chemistry_Lookup

6. Download the latest extract of the standard Chemistry Lookup and open in Excel.

7. Copy the content into the import template you created, making sure the column headings line up as expected.

8. Import the data.

9. There is typically no need to use Combine Compounds in ESdat for new projects as most laboratories can provide totals (eg Total PAH) as a specific result.

10. Import any Environmental Standards you need. These maybe maintained internally by your organisation, or can be obtained from the ESdat Website at http://www.esdat.net/Environmental_Standards.aspx

11. Import your site/project data