Merging Data from Two or More Observations Data Models

 

Tyler Jantzen, CRWR

July, 2006

 

Table of Contents 

 

 

This tutorial will use Microsoft Access to merge data from two existing ODM 3.0 databases into a single ODM 3.0 database.  See the tutorial “Creating an Observations Data Model Using Microsoft Access” for instruction on how to create an ODM database.  The TRACS ODM database created in the “Creating an Observations Data Model Using Microsoft Access” exercise will be merged with an ODM database created with local observations data collected by Dr. Paul Montagna at the Marine Science Institute.  More information can be found at http://www.utmsi.utexas.edu/staff/montagna/index.htm. 

 

This tutorial will assume that the user has three ODM database files: TRACStoODM_AccessExample_07_20_2006.mdb, PMontagna.mdb, and an empty ODMSchema.mdb.  A populated version of the ODMSchema.mdb database can be found as MergedODM.mdb.

 

Create Append Queries to Export Data from First Database

The merging of two or more existing ODM databases into a single merged database will use a system of Append queries similar to those used in the tutorial “Creating an Observations Data Model Using Microsoft Access.”  However, in this case, the data will be appended to a table in a different database than the source data.  Because the structure of both these databases is the same (both are ODM databases), there is a one-to-one relationship between source records and destination records. 

 

Open the working version of TRACStoODM_AccessExample_07_20_2006.mdb in Microsoft Access. 

 

1.         Create an Append Query in the TRACStoODM_AccessExample_07_20_2006.mdb to export the Units Table.

 

From the Queries tab on the main database view, select, Create query in Design View.  Add the Units table to the query.  Click Close. 

 

 

In the query design view, select Query/Append Query from the main menu. 

 

 

Select the Another Database button, and browse to the location of the database where the data will be merged into.  This database needs to be a copy of the blank ODM schema.  This example has used a copy of the blank ODM schema renamed MergedODM.mdb.  From this database, select the Units table from the drop-down list.  Click OK.

 

 

 

Add each field in the Units table to the Append query.  Because the names of the Units table in the source table are the same as the names of the Units table in the destination table, the destination table names are added automatically.  Double-click on each of the fields in the Units table.  Save this query as z_qry_UnitsExport.  Adding the prefix “z” will group all the Export queries together.  This will make future processes easier. 

 

           

 

Close the query. 

 

2.         Repeat step 1 for each of the tables in TRACStoODM_AccessExample_07_20_2006.mdb modified beyond the ODM 3.0 blank schema.  The table below summarizes which of the tables in this example need to have an export query.  If additional tables in the source database have been modified, create queries to export these as well.  Unless otherwise noted, include all fields from each table.  While the export queries can be created in any order, they must be run in a specific order.  A dependent table (such as Values) can only be exported once each of the supporting tables (Sites, Variables, Samples, QualityControlLevels, Qualifiers and OffsetTypes) have also been exported. 

 

Run Order

Source Table

Query Name

Notes

1

ISOMetaData

z_qry_ISOMetaDataExport

 

1

LabMethods

z_qry­_ LabMethodsExport

 

1

Methods

z_qry­_ MethodsExport

 

1

Qualifiers

z_qry­_ QualifiersExport

 

1

Units

z_qry­_ UnitsExport

Already created in Step 1

2

OffsetTypes

z_qry­_ OffsetTypesExport

 

2

Samples

z_qry­_ SamplesExport

 

2

Sites

z_qry­_ SitesExport

 

2

Sources

z_qry­_ SourcesExport

 

2

Variables

z_qry­_ VariablesExport

 

3

Values

z_qry­_ ValuesExport

Do not include ValueID

 

3.         Run each of the export queries made in steps 1 and 2 according the order listed in the above table.  Each of the “1” queries can be made in any order, as long as each is run before any of the “2” queries.  Click Yes when asked “Are you sure you want to run this type of action query?”.  Click Yes to append.  

 

In some cases an error occurs while appending records.  In most of these cases this error is okay, and you can click Yes.  This error occurs because of the same records already exist in the destination table.  For instance, the Units table already has entries from the ODM 3.0 blank schema.  However, in some of the databases the Units table is modified, so it is best to export it just in case. 

 

 

If the query was improperly constructed, a similar error will occur preventing the addition of all the records.  If this occurs, return to steps 1 and 2 and correct the problem. 

 

4.         Close the source database.

 

Create Append Queries to Export Data from Each Additional Database

Because the queries made in the above section will work with any ODM database, the queries simply need to be imported into each additional source database, and run in the same order listed above.  This can be repeated with each additional source database as necessary.  In this example, the data from the included PMontagna.mdb database will be exported to the MergedODM.mdb. 

 

1.         Open the PMontagna.mdb database in Access

2.         Go to File/Get External Data/Import and browse to the location of the first source database (in this case TRACStoODM_AccessExample_07_20_2006.mdb).  Click Import.  Select each of the Export queries created in the previous section.  This should be easier because each of these queries start with the “z” prefix.  Click OK.

3.         Run each of these queries in the same order as listed in the above section. 

For each additional source database exported into the merged destination database there is a higher probability of errors occurring.  As in the previous section, these should be due to the fact that a certain record already exists.  In this example the record for ISOMetadata is the same in both PMontagna.mdb and in TRACStoODM_AccessExample_07_20_2006.mdb.  Thus, the second time this field is exported it will experience an error.  However, the unique fields (such as sites and values) should export without error.  Use common sense when accepting and rejecting these errors.

4.         Close the source database.

 

Create a Series Catalog for the Destination Database

Because the SeriesCatalog table is a programmatically derived table, it is created in the destination database instead of being imported from the source databases.  This insures an accurate count of Site-Variable combinations. 

1.         Open the destination database (in this case MergedODM.mdb).  Check the tables to ensure that they have been properly populated.  Good tables to check are Sites, Values and Variables, as these contain some of the most important information.

2.         Import the SeriesCatalog select and append queries from those already created in TRACStoODM_AccessExample_07_20_2006.mdb. 

Go to File/Get External Data/Import and browse to the location of TRACStoODM_AccessExample_07_20_2006.mdb.  Click Import.  Select both qry_SeriesCatalogSelect and qry_SeriesCatalogAppend and click OK. 

 

3.         Run qry_SeriesCatalogSelect and qry_SeriesCatalogAppend (in that order). 

 

Congratulations!

You have successfully merged two ODM databases into a single database.  This single database can now be queried or used to investigate questions that require information from multiple sources.

 

Downloads and Additional Information

Observations Data Model Information

 

CUAHSI Community Observations Data Model Working Design Specifications Document- Version 3 (.pdf)

 

Blank ODM 3.0 Schema (.mdb)

 

Tutorials

 

Merging Data Supporting Files (.zip)

 

Creating an Observations Data Base Tutorial

Creating an ODM Supporting Files (.zip)

 

Using an ODM in a GIS Framework Tutorial

Supporting Files for Using an ODM in ArcGIS (.zip)

 

 

Primary Contact:

Tyler Jantzen

Graduate Research Assistant

Center for Research in Water Resources

University of Texas at Austin

e-mail: tjantzen@mail.utexas.edu
 

These materials may be used for study, research, and education, but please credit the authors and the Center for Research in Water Resources, The University of Texas at Austin. All commercial rights reserved. Copyright 2006 Center for Research in Water Resources.