Tutorial 2- Introducing ESdat

1 For use with ESdat version 5 (version 5 software updates available from ) Tutorial 2- Introducing ESdatTutorial 2 Introducing @EarthScience Information SystemsP a g e |1 Tutorial2 Introducing ESdatTable of the Sample Laboratory existing new 2 Introducing @EarthScience Information SystemsP a g e |2 SummaryThe purpose of this Tutorial is to provide a quick overview tutorialprovidesan overview level introduction to the following topics: What ESdat isand does Opening ESdat databases Navigating through ESdat Data filtering Importing Laboratory Data Assigning laboratory duplicates Assigningunique codes to eachchemicalcompoundSubsequent tutorials addressthese topics in more depth, as well as otherpowerfulanalysisand the full list of ESdatfunction and topic Tutorial applies to both Access and SQL versions of ESdat . The Tutorial is conductedusingthe Access version of will use theSample Hydrogeology and Contamdatabase for any examples 2 Introducing @EarthScience Information SystemsP a g e |3 Introducing ESdatESdat is an environmental data management system, which provides an electronic data trailfor all your field, laboratory and historical monitoring makes it easy to import, track, manageandinterrogateair, water and soil informationformultiple sites, and multiple of data from multiple sources, and uses a range of validationprocesses to ensure data is accurate, up to date and graphic below highlights the data inputs and outputs possible with functionality(logically)

2 Starts right from sample planning process, specifically usingthe LSPECS extension of location and types of samples, how samples are to be collected, and the analyses to bedone can be specified here, streamlining field sample collection and laboratory requests can be sent, withlaboratoryreports received in a specified can be importedintoanESdat database fromtablets,laboratory reports, data loggers,field notes, and/or historical data held either as handwritten notes, orfromExcelspreadsheet makes it easier to import this data, through the use of a range of pro-formaimporttemplates,or alternativelyimport templatescan be developed by the user for a a library ofenvironmental standards, which canbe imported into a projectdatabase, against which analytical results can be 2 Introducing @EarthScience Information SystemsP a g e |4 Alternatively,site or projectspecific standardscan be developed, which will automaticallyflag exceedancesfor can be compared against environmental standards, allowing rapid identification ofpotential problem allows for easy organisation of data to facilitate a variety of outputs (either internalwithinanorganisation, or to external parties).

3 Filtering of dataallowsthe user tofocus on the information of can be mapped, graphedortabulatedfor reporting and/or exported to avariety of visualisation assurance reportingfeatures also allow for rapid assessment of data provided bylaboratories, facilitating QA reports and data manipulation, editing and checking short, ESdat contains numerous features that will speed up data entry, builds innumerous QA check points, enhances consistency and efficiency of data entry and use, andprovides numerous options for data option to meetuser 2 Introducing @EarthScience Information SystemsP a g e |5 Opening theSample DatabaseStart by selectingtheESdat5application via:Windows Start AllPrograms ESdat (Folder).When ESdat opens, the Startupdialogue boxwilllaunch,shown in the figure ESdat Startup dialogue box,existing databasescanbe openedoranew databasecan this is the first time ESdat has been opened only theSample Hydrogeology and Contamdatabasewill be visible,as shown in the figure more(Access*)databases are created(for new projects),theywill appear in the dialoguebox when ESdat is reopened.

4 ClickOpenESdatA prompt will appear askingwhether you want to view theESdatTutorial 'Yes'will launch the Tutorial Documentation you are now reading, clicking No willcancel the dialogue box and continue to the program.*For SQL Server users, please refer to the online help for management of projectsTutorial 2 Introducing @EarthScience Information SystemsP a g e |6 Window NavigationData Views View, Query & Interrogate DataThe figurepresented onpage 8presents the ESdatDataView, which is the first view toappear when opening components of theESdatData Viewhave been highlightedin the figure presented onpage copy of page 8can be printed as a hard copy(or opened in a separate window)toreferto andassist navigation during completion of this(and subsequent) buttons at the top of the screen(in the pale green area)areView Typebuttons(orCommand buttons)used to openthe followingESdatfunctions: LSPECS (Laboratory Sample Planning Electronic Custody and Signoff) ImportViews Data Views Reports CheckUnitsThe following description focuses upon the Data Views View below theView Typebuttonsare theData-Typebuttons, which allowaccess todifferent types of datawithin a database,such as.

5 Location Boreholes / Wells Geology Water Levels A variety ofChemistryresults Logger DataTheseData Typebuttons canbe customisedif Areais located beneath filter isconstructeditappearsin tabs are visible beneath the FilterArea: Data Map FilterTheDatatab presents the selected Data Type in a tabular format. For example, if the WaterChemistry Data Type is selected, all water chemistry results will appear under the Data 2 Introducing @EarthScience Information SystemsP a g e |7 The toolbar to the left of the data relates to actions affecting the data panel (such as editing,sorting or summarising), and the tool options will change depending onwhether Data, MaporFilter on the Map Panel andnotethe tools available. Then click back to the Data Panel andnotethedifferenttools presents the same selected data (if associated with coordinates) in a mapdisplay. For example, if the water chemistry data is associated with surveyed coordinates(such as surveyed groundwater wells) this can be displayed under the Map tab.

6 This datacan be shown with a basemap if available one of the many available mapping tabassists in the creation of a filter, to focus on a particular sub-set of data. Forexample, if the water chemistry data contains heavy metals analysis, the filter allowstheuser to focus in on a particular metal. Or a particular depth of a databaseis opened,Locationinformation(the first Data Type)is displayed through the different Data Type buttons in both the Data and the Map Panels, andnote the different information displayedfor each Data Type selection.(The data being viewed is of course, part of the demonstrationSample Hydrogeology andContamdatabase.)TheExportstoolbar on the right shows the external links, reportingandexport options for data withinESdat( ,Google Earth).Oncedata has beenselected and filteredas required, these options allowexporting ofthedata to the desired output or 1 Introducing @EarthScience Information SystemsP a g e |8 VIEW TYPESDATA PANELMAP PANELFILTER PANELEXTERNALLINKS,REPORTINGANDEXPORTBUT TONSFILTER AREADATA TYPE BUTTONSFILTER TEXTT utorial 1 Introducing @EarthScience Information SystemsP a g e |9 Import View Import DataThe figurepresented on page10presents the components of the ESdatData Viewscreenhave been blue area at the left of the Import view labelledBlank Import Templatesprovides eightbuttons to access templates for the major types of data imported into ESdat .

7 Locations(sample location details, such as soil bores, test pits or sediment samples) Wells(groundwater well construction details) FieldChemistry(orother field parameters, such as temperature) HistoricalChemistry(as distinct from ESdat format lab supplied analytical reports) Groundwater levels(including NAPL measurements) Time-variant TOC(where well collar heights ( TOC) have varied over time throughdamage, subsidence or similar) Borehole/Geological logging-Standard( environmental)and Geotechnical logsTheAll Data Tablessection at the bottom of the blue areas provides a list of allblankimport templatedata tablesas well asreference tables (labelled as zRef_ ) in the reference tables refer to existing information (data) that has been imported into ESdatto allow specific example, reference table zRef_Environmental_Standards includes third party publishedassessment criteria that are used to create summary tables to allow comparison ofanalytical data against the arealabelledFilesallowsimportingofa range of specificfiles, including: laboratory analysis (as supplied by the analytical laboratory in the correct ESdatformat) logger data populated Import Templates ( templates from the blue area that havebeenpopulated with data) data interchangefile (for transferring data from one database to another) documents (which allows generation of a record of documentssuch as previousreports relating to a projectand the file directory for easy access) AGSformat files (geotechnical and geo-environmental file format)Theorange arealabelledLibraryallowsimportingofthe following referencefiles: EnvironmentalStandards(refer to the ESdat site for available standards- ) Post Import Calcs.

8 Designed to provide more sophisticated calculating or updatingfunctionality than is available using the combined compoundsfunctionality Combined compounds: allows automatic addition of compounds (such as TPHfractions) Tutorial 1 Introducing @EarthScience Information SystemsP a g e|10 DATA IMPORT TYPESA ccess to all data andreference (zref) tablesSelect to populate importtemplate with data alreadyin databaseTutorial 1 Introducing @EarthScience Information SystemsP a g e|11 Importing Laboratory ChemistryLaboratory chemistry datasupplied in the ESdat formatcan be automatically importedusinganESdat. InESdatclick theImportView Typebutton From theorangeFilesarea,selectLaboratoryThis will open theSelect ESdat Sample of Chemistry Result EDD filedialogue box. Navigate to the Sample Directory,typically: C:\ProgramFiles\EScIS\ESdat5\SampleConta m;or C:\ProgramFiles (x86)\EScIS\ESdat5\SampleContam(on 64 bit PCs) Select the Ifa dialogue box appears stating Lab Report already exists and Do you wish todelete and clickYesThe lab data will load in Excel, initially showing the Sample data(note two tabs at thebottom of the Excel page Chemistry_SamplesandChemistry_Results).

9 There are a number ofQASamples in the datathat need have the Sample Type updated andduplicates to be reconciled with parent samples(as shown inrows 22-26inthefigurebelow).For the Field Duplicates(Field Dupe) correctSample Typeneeds to be nominated as the laboratory has assumed anormal sample as it was a blind parent sample of the duplicate also needs to bereconciled( samplehasbeenduplicated). Tutorial 1 Introducing @EarthScience Information SystemsP a g e|12In thisexample data, theField_IDcolumn provides theparent , field notesmaybe the source of this do this forField DupeBH26B(Row22)do the following: Click on cell I22 (Sample Type column) selectField_Dfrom the drop-down menuA dialogue box will open prompting youtoClick on the SampleCode of the sample for whichthis is a duplicate. Clickanywhere in Row 16(the Parent Sample) clickOKYou will noticethat in Row 22 (the duplicate sample) (ColumnJ)is updated to show theSampleCodeof theParent Code(LocCode)column is updated with the location of remains unaltered Repeat this procedure for the other Field Duplicates(Row 23 and Row 24).

10 There arealsotwo blanksamples in this batch(see Lines 25 and 26).The type of blank needs to be specified. In the row containingTrip Blank(Row 25),click ontheSample_Typecell(columnI) From the dropdown menu, selectTrip_B In the row containingEquipment Blank(Row 26),click ontheSample_Typecell From the dropdown menu, selectRinsateNote the Field ID is left as it was specified in the field, but the LocCode, which was originallyidentical to the Field ID, is now data should appear consistent with the figureoverleaf: Tutorial 1 Introducing @EarthScience Information SystemsP a g e|13 Note the Field ID is left as it was specified in the field, but the LocCode, which was originallyidentical to the Field ID, is now blank. Fromthe Excel(top)toolbarselect theADD-INStab Click on theUpload Dataicon Accept the defaults and clickImportThe data will now turn green indicating it has been uploaded in the same Excel workbook: Navigateto theChemistryResultsSheet(via the tabs at the bottom of theExcelwindow) Import the Chemistry data(as per the previous two steps)The laboratory data is : Excel is used as a tool to update theESdatdatabase.