I'm setting up an EI database in which I would like to compare chemistry data against standards that vary with pH or hardness.
In the past (not EI) I have worked on a database where there were two "standards" tables set up; one for constant standards and one for varying standards. Querying these two tables along with the equivalent of the "Observations" table would allow for comparison of standards. The "Constituents" table in EI has a similar purpose, but I am not sure how easy it would be to allow for standards that vary with pH and hardness.
Are there any templates I can work from or advice for this issue?
Not long ago we had a client from British Columbia make this same request. On their behalf we implemented a means of entering sample-specific standards. This is not documented in our standard documentation. The pH or hardness-dependent standards values are calculated by the client (you) and are entered in a table called Local Standards.
Here's an excerpt from an email to this client regarding this table and the entry of ph- and hardness-dependent standard values:
In order to allow for pH and hardness dependent standards, you will need to add a Local_Standards table within your Access database. For regulatory standards that are not pH or hardness dependent, you can if you wish enter these in the Constituents table. That’s a more concise way of entering standards. The Local_Standards table will include the following fields:
Field
Data Type
Notes
ID
AutoNumber
Not obligatory to include this field.
Well
Text
This field contains the id for a particular location. All locations (borings, air monitoring, wells, water samples, soil samples) need to have this field populated.
Screen
Text
This is an id identifying a vertical horizon at a given location. It should be unique for each vertical horizon, but the field can be replicated for multiple constituents.
Date
Date/Time
Constituent
Text
Fraction
Text
This is to indicate whether a sample is total or dissolved. If neither, or you don’t care then simply enter a hyphen.
Media
Text
Usually “water”, “soil”, or “air”
Standard Name
Text
Regulatory Standard
Standard
Number – double
Numeric value of Standard
Make sure when populating this table that for each record, the values Well, Screen, Date, Constituent, Fraction, and Media fields match with a record somewhere in the Observations tab. If you send your email address to support@enviroinsite.com I'd be happy to send you a data template.
