Creating an Observations Data Model

Creating an Observations Data Model

Using Microsoft Access

Tyler Jantzen, CRWR

July, 2006

Table of Contents

· Collect raw data. 1

· Determine relationships between TRACS and ODM data structure. 4

· Import data as new tables into the existing ODM schema. 5

· Join the Results tables into a single table. 9

· Create necessary relationships for the loaded TRACS tables. 11

· Create filler and null entries in supporting tables. 13

· Load raw data into ODM.. 15

· Create Programmatically Derived Tables. 25

· Delete raw data tables from ODM.. 27

· Downloads and Additional Information. 28

o Observations Data Model Information. 28

o Tutorials. 28

The following is a step-by-step example of the creation of a Workgroup HIS Observations Data Model (ODM) database. This example will import water quality observations data from the Texas Commission on Environmental Quality (TCEQ) into ODM version 3.0. The ODM will be created in Microsoft Access 2003, and will also use Access for intermediate data manipulation.

Download Supporting Files

Collect raw data

The first step in populating the ODM is the collection of raw observations data. This example uses water quality observations data from the TRACS database at the TCEQ. This data is available at http://www.tceq.state.tx.us/compliance/monitoring/crp/data/samplequery.html.

Because this data is not currently queryable, it will be downloaded, imported into a database, transformed to meet the ODM schema, and stored on a local SQL server or to leave in Microsoft Access as a .mdb file. This data is also available in the supporting files .zip file.

1. Select the Event file and Result file from the TRACS database for a specific date range and basin. This example will use the Nueces River Basin (021) and Canadian River Basin (001) over the year 2000.

2. Save the Event and Result files as .txt files on your hard drive. Descriptions of the fields in each of these files can be found by clicking on RESULT file structure and EVENT file structure.

3. Repeat steps 1 and 2 for the entire list of Sampling Stations: ftp://www.tnrcc.state.tx.us/pub/WaterResourceManagement/WaterQuality/DataCollection/CleanRivers/public/stations.txt . These files can be found at the above links or by browsing through the TCEQ website. Titles for the fields in this table can be found at ftp://ftp.tnrcc.state.tx.us/pub/WaterResourceManagement/WaterQuality/DataCollection/CleanRivers/public/stnsmeta.txt . Alternatively, only the stations for the Nueces and Canadian rivers basins could be used, as these are the only sites that will have values in this example.

4. Repeat steps 1 and 2 for the STORET codes. STORET codes are 5 digit codes created by the U.S. EPA to describe parameters in their Storage and Retrieval database and are linked to descriptions of water quality parameters. The portion of these codes used by TCEQ can be found at ftp://ftp.tnrcc.state.tx.us/pub/WaterResourceManagement/WaterQuality/DataCollection/CleanRivers/public/sw_parm.txt. Titles for the fields in this table can be found at ftp://ftp.tnrcc.state.tx.us/pub/WaterResourceManagement/WaterQuality/DataCollection/CleanRivers/public/parm_meta.txt.

These files can be saved with any file name. The names used in this example are displayed below. As a convenience, these files have been included as part of this tutorial.

Determine relationships between TRACS and ODM data structure

With the raw data downloaded, the relationships between the fields in the raw data and the ODM data schema must be determined. These relationships will be different for each data source. It is worth spending significant time and effort on this step, as it will significantly increase the utility of the final product, and reduce time spent in other steps. Not only must the relationships between fields be determined, but a systematic method of loading the data should be considered. This example will create a simplified version of the ODM. In other words, not every field from the TRACS data that has a matching field in the ODM will be transferred. Only the most important information will be populated in this example: the value of the observation, the location of the observation, and the type or variable for that observation. Additional fields and information may be added if desired. Creating a complete ODM with the TRACS data would also require additional raw data gathering. For example, SpatialReferences is one required table in the ODM. This information is not readily available in the TRACS data, and would have to be determined to complete the ODM.

The following table describes the source and destination tables and fields for populating this example ODM.

TRACS Table	TRACS Field	ODM Table	ODM Field	Data Type
Result	Value	Values	Value	Number: Double
Result	Storetcode	Values	VariableID	Number: Long Integer
Stations	Station_Id	Sites	SiteID	Number: Long Integer
Stations	USGS_Gage	Sites	SiteCode	Text
Stations	Long_Description	Sites	Name	Text
Stations	Latitude	Sites	Latitude	Number: Double
Stations	Longitude	Sites	Longitude	Number: Double
Stations	County_Name	Sites	County	Text
Stations	HUC	Sites	Comments	Text
StoretCode	Storet_Code	Variables	VariableID	Number: Long Integer
StoretCode	Long_Description	Variables	Variable	Text

Import data as new tables into the existing ODM schema.

1. Open the ODM 3.0 Schema in Access. The ODM 3.0 is available here, or in the supporting files. It is advised to work out of and modify a copy of this schema, keeping a clean version available “just in case.” To do this, create a copy of the file ODMSchema.mdb in Windows Explorer, and rename to something appropriate for this example.

2. Go to File/Get External Data/Import. Browse to the saved Stations.txt file. To view this file you will need to change the “Files of type” setting to include text files. Click Import.

In the Import Text Wizard select the Delimited button and click Next.

Select Other as the delimiter, and enter the “|” character into the field. Because the first row in this data set is partially blank, select First Row Contains Field Names. If you get an error message, click OK. Click Next.

Click Next to import the data into a new table.

Create appropriate headings for each of the fields. These headings can be found in the associated Stations_meta.txt or at ftp://ftp.tnrcc.state.tx.us/pub/WaterResourceManagement/WaterQuality/DataCollection/CleanRivers/public/stnsmeta.txt. During this process the Data Type can also be specified. Setting this correctly at this point will make later processes easier. The field type of the imported data needs to match the field type in the ODM 3.0 field which will eventually be populated. These relationships along with required field types can be found in the table in section 0.

Click a field, and enter the appropriate Field Name and Data Type. When all the field names are entered and the proper Data Types selected, click Next.

Because the Station table is linked to the Event table via the StationID, the Station ID will be used as the primary key. Select Choose my own primary key, and browse to StationID. Click Next.

Title the name of the table to be imported “z_Stations.” Adding the “z” prefix groups all the raw data at the end of the list of tables, making sorting and identification easier. Click Finish.

3. Use the same procedure as in step 2 (with some minor adjustments) to import data from the Storet codes file. The field names are in Storet_meta.txt, or at ftp://ftp.tnrcc.state.tx.us/pub/WaterResourceManagement/WaterQuality/DataCollection/CleanRivers/public/parm_meta.txt.

Adjustments: -There are no field names, so leave the “First Row Contains Field Names” box uncheckedx

-Use the StoretCode field as the primary key

-Title the table “z_Storet”

Because all the water quality information collected in the TRACS database is indexed by the EPA STORET code system, this example will use the STORET code as the primary key. However, the STORET codes table downloaded in step 4 includes a small subset of codes that begin with the letter P. These are all porewater measurements. Because the StoretCode field type is a long integer, these values cannot be imported. Fortunately, none of the TRACS results are porewater measurements, so these “P” codes can be deleted from the table.

Click OK to both message boxes that pop up.

4. Use the same procedure as in step 2 (with some minor adjustments) to import data from the first of the Event files (in this case the file Canadian_Event.txt). Although information in the Event file is not used directly in the ODM, it is used to connect the Result and Stations tables. The field names are in Event_struct.txt, or at ftp://www.tnrcc.state.tx.us/pub/WaterResourceManagement/WaterQuality/DataCollection/CleanRivers/public/event_struct.txt.

Adjustments: - There are no field names, so leave the “First Row Contains Field Names” box unchecked

- Use TagID as the primary key.

- Title the table “z_Events”

The TagID will be used to connect the Results and the Stations tables.

Because the other Event files will be loaded into the same table, the name for the table does not indicate basin.

Click OK to both message boxes that pop up.

5. Use the same procedure as in step 2 (with some minor adjustments) to import data from the remaining Event files (in this case the file Nueces_Event.txt). Because these remaining Event files are being loaded into the z_Events table created in 4, they will not be loaded as a new table.

Adjustments: - There are no field names, so leave the “First Row Contains Field Names” box unchecked

- In the third window, select the “In an Existing Table” button, and select the “z_Events” created earlier.

6. Use the same procedure as in 2 (with some minor adjustments) to import data from the Result files (in this case the files Canadian_Result.txt and Nueces_Result.txt). Because there is no existing unique identifier (and thus no existing primary key) these files will have to be loaded into separate tables and combined later (in contrast to the Event files). The field names can be found in Result_struct.txt, or at ftp://www.tnrcc.state.tx.us/pub/WaterResourceManagement/WaterQuality/DataCollection/CleanRivers/public/result_struct.txt.

Adjustments: - There are no field names, so leave the “First Row Contains Field Names” box unchecked

- In the second window, select the Text Qualifier “″”.

- For the Storetcode field, select the Data Type “Long Integer”

- For the Value field, select the Data Type “Double”.

- Let Access add primary key

- Title the table “z_Canadian_Results”

7. Steps 5 and 6 can be repeated for as many sets of Results and Event files as needed.

Join the Results tables into a single table

Because the Results tables do not have a unique identifier in the TRACS database, they cannot be imported into the same table. Thus, they were imported into separate tables. These must be joined into a single table.

1. On the left side of the main Access form, go to the Objects/Queries and Create query in Design view.

2. Click Close on the Show Table form.

3. On the main menu bar, go to View/SQL View.

In the SQL view window, type or paste the following text:

SELECT TagID, StoretCode, GtLt, Value, -6 AS UTCOffset, 1 AS OffsetTypeID, 0 AS QualifierID, 1 AS SourceID, 0 AS [QualityControlLevel], 0 AS DerivedFromID, 0 AS SampleID, 0 AS MethodID

FROM z_Canadian_Result

UNION SELECT TagID, StoretCode, GtLt, Value, -6 AS UTCOffset, 1 AS OffsetTypeID, 0 AS QualifierID, 1 AS SourceID, 0 AS [QualityControlLevel], 0 AS DerivedFromID, 0 AS SampleID, 0 AS MethodID

FROM z_Nueces_Result

;

This SQL code creates a query which includes the TagID, StoretCode, GtLt, and Value fields from the z_Canadian_Result table unioned with the same fields from the z_Nueces_Result table. The query also adds various ID fields, and assigns a given value to each record for each field. This will assist in filling the Values table, and will be described later. This code can be modified to include additional fields. Also, additional UNION SELECT sections can be added to include more Results tables in the Union.

Go to File/Save As to save this query as qry_ValuesUnion. This name will identify this query as one that will eventually help to fill the Values table.

Click the Run button on the toolbar . Be patient. The Run process may take many minutes, depending on the number of records being selected. A query table should appear:

Close the results table (and save changes).

Create necessary relationships for the loaded TRACS tables

1. From the main Access toolbar, click Tools/Relationships

The relationships that are part of the ODM schema can be seen here. To facilitate the loading of TRACS data into this schema, Access needs to know which TRACS fields are related.

2. Add the TRACS tables to the Relationships page

Navigate to an empty portion of the Relationships page. Right-click and select Show Table. Add the tables z_Events, z_Stations, and z_Storet. Also add the query qry_ValuesUnion. Expand each of these tables to view all the fields.

3. Create relationships between these tables according to the following table.

Source Table	Source Field	Result Table	Result Field	Relationship Type
z_Events	TagID	qry_ValuesUnion	TagID
z_Stations	StationID	z_Events	StationID	One-to-Many
z_Storet	StoretCode	qry_ValuesUnioin	StoretCode

For each of the above relationships, drag the Source Field on top of the Result field. In the Relationships dialog click Create.

The relationships should look like this:

Save and Close the Relationships page.

Create filler and null entries in supporting tables

The ODM 3.0 relationship structure is large and complicated, as can be seen by viewing the Relationships page. Many fields are linked to other fields by unique identifiers. These unique identifiers are usually fields that end in the suffix “ID”. For instance, VariableID links each value to a description of the variable for which it was measured.

In previous versions of the ODM, the unique identifier was automatically generated. However, in version 3.0 the unique identifier is user-generated, allowing values such as StationID from the TRACS data to be used as unique identifiers. This change in the ODM schema gives the model more flexibility, but requires the careful ingestion of data to ensure that unique values remain unique. Access will not allow the loading of unique values that already exist.

Because these unique identifiers are so important to the structure of the model, Access will not allow records to be created with missing identifier information. For example, one can not enter a value without a valid SiteID. In this exercise only a small portion of the ODM is being populated. Tables such as Methods and Qualifiers are not being generated. To create this simple example, some value for MethodID and QualifierID needs to be created so that a complete entry for the Values table can be generated. The SQL code written in section 3 already created null values in for the Values table. However, the corresponding records for the supporting tables still need to be created.

1. From the main Access page, Open the following tables and create the following entries:

Complete the information in Sources with information about your organization. Alternatively, information about the TCEQ could be included, since the TCEQ is the ultimate source of this information.

Load raw data into ODM

Because of the interdependencies between tables via unique identifiers, tables that are least dependent on other tables should be loaded first. These can be considered the most “exterior” tables, and can be visualized in the Relationships page. For instance, of the tables that will be filled in this example, the Sites and Variables tables are most exterior. The Values table is dependent on unique identifiers such as VariableID and SiteID. In this example, these values have already been determined (VariableID is StoretCode, and SiteID is StationID), so the order of loading tables is less important. However, some datasets will require this ordered loading of tables.

Each table requires essentially two queries to load data from TRACS to ODM. The first query selects the raw data from the source table, and adds additional fields to this data. A second query appends the data in the first query to the ODM table. Only one source table or query can be included in an append query. Thus, if multiple tables need to be added, additional intermediate queries must also be added.

1. Load the Sites Table

1.a. Create a query to select the raw data

In the Objects menu on the main Access page, go to Queries. Select Create query in Design view. Click Close in the Show Table box. In the main menu bar, go to View/SQL View.

Type the following text into the SQL text editor:

SELECT StationID, USGSGage, LongDescription, County, Latitude, Longitude, "2" AS SpatialReference, "Texas" AS State

FROM z_Stations

;

This query calls the Station ID, USGSGage, Long Description, County, Latitude and Longitude fields from the z_Stations raw data table. It also adds the value “2” to each record as the Spatial Reference. This value is associated with the SpatialReferenceID value for the Datum NAD 83, the datum for the TRACS data. Also, because all this data is collected in Texas, the state name of Texas is added to each record in the State field.

Save this query as qry_SitesSelect.

Run this query.

Close the query results.

1.b. Create an append query to load the qry_SitesSelect query into the ODM Sites table

Go to Queries/Create query in Design View from the main Access window. In the Show Tables window go to Queries, select qry_SitesSelect, Add, and Close.

From the main menu, go to Query/Append Query. In the drop-down menu select the Sites table, and click OK. This action changes the type of query from a Selection query to an Append query. You will notice that an additional row titled “Append To” is added in the query design table.

The user can now select a field from qry_SitesSelect results, and assign it to be appended to a specific field in the Sites table.

Double-click StationID. It automatically appears in the first column in the query design table. Go to the Append To drop down cell, and select SiteID. Because StationID is a unique identifier in the TRACS database, it can be used as the unique identifier SiteID in the ODM.

Do the same with the following fields. You will notice that if a source field has a field in the Append To menu with a matching name that the Append To relationship will be generated automatically. SpatialReference is added twice because both LatLongDatumID and LocalProjectionID fields are required to be filled.

Source Field	Append To Field
StationID	SiteID
StationID	SiteCode
LongDescription	Name
County	County
Latitude	Latitude
Longitude	Longitude
SpatialReference	LatLongDatumID
SpatialReference	LocalProjectionID
State	State
USGSGage	Comments

Save the query as qry_SitesAppend

Run the query. Depending on the amount of data being appended, this can take a long time.

Click OK to append.

Close the qry_SitesAppend design window.

Open the Sites table and make sure that the data was appended.

Some columns may be hidden (such as SiteID). If this is the case, right-click on the top left corner of the Sites: Table window, and select Unhide Columns. Make sure that each of the Columns are selected.

Congratulations! You have successfully populated an ODM table with raw data by using Access queries and data loading functions.

2. Load the Variables Table

Follow the same procedure used to load the Sites table in step 1.

2.a. Create a query to select the raw data.

Create a new query in design view. Do not show any tables. Instead go to SQL View.

Type the following text in the SQL text editor:

SELECT StoretCode, ShortDescription1, ShortDescription2, ShortDescription3, LongDescription, MinimumValue, "137" AS UnitID, "Surface Water" AS [Sample Medium], "137" AS TimeUnitID, "Water Quality" AS GeneralCategory

FROM z_Storet

;

The UnitID of “137” is defined as Dimensionless. This is not accurate, as most of the variables are defined by units in the Storet code table. However, because of the difference between Storet unit vocabulary and ODM controlled vocabulary, the transfer of units would be difficult and time consuming. For the sake of simplicity, this example will assign the Dimensionless unit to all Storet Codes. However, the LongDescription (which will be assigned to Variable Name) contains an indication of the units. The other field contents are derived from ODM controlled vocabulary.

Save this query as qry_VariablesSelect and Run.

Notice that the Storet Codes that started with a P (Porewater measurements) were not transferred. See 3 for a discussion of this issue.

2.b. Create a query to append the Variables table with the results from qry_VariablesSelect.

Create a new query in design view. In the Show Table window, select qry_VariablesSelect from the Queries menu, Add, and Close.

Change the type of Query to an Append Query. In the main menu go to Query/Append Query, select the Variables table and click OK.

Append the following fields:

Source Field	Append To Field
StoretCode	VariableID
LongDescription	Variable
UnitID	UnitID
Sample Medium	SampleMedium
TimeUnitID	TimeUnitID
GeneralCategory	GeneralCategory

Note: It would be more appropriate to use Short_Description_1 and Short_Description_2 as the source for the Variable field and use Short_Description_3 as the source field for UnitID (and creating a relationship between the STORET Code and the ODM units table). However, the current controlled vocabulary of the units table is such that few of the STORET Code units have similar units in the ODM Units table. Thus, the Units table will not be used for this example. Instead, the UnitsID field in the Variables table will be given the default value for “dimensionless”. Where appropriate, units have been included as part of the Long_Description from the STORET table.

Save the query as qry_VariablesAppend and Run.

Because 135 of the records do not contain a StoretCode (they were erased because they included the letter P), a notice that some records didn’t append will pop up. This is OK. Click Yes. Close the Query design window.

Go to the Variables table to ensure the correct fields were populated.

3. Load the Values table

Because the Values table is dependent on the relationship between the Result and Event tables through the TagID, an intermediate query will have to be used in addition to the Selection and Append queries.

The Selection query was already created as a Union query when the Results were joined into a single table earlier in the tutorial.

Like all tables in the ODM, the Values table requires a unique identifier for each record. So far, each table filled in this exercise has had a logical unique identifier that could be transferred from the ODM. However, the Value table does not have an appropriate unique identifier to transfer from the TRACS dataset. Thus, the field type for ValueID will be set to AutoNumber so that Access creates its own identifier.

3.a. Change the ValueID field type to AutoNumber

The field type cannot be changed while the Values table is related to other tables. Thus, the relationships will have to first be deleted, the field type changed, and then the relationships rebuilt.

Go to Tools/Relationships.

Move the windows around so that the Values Table is accessible. Because there are two versions of the Values table (Values, and Values_1), the Values_1 table will have to be moved.

Select the relationship line leading to ValueID and Delete it (right-click and select Delete, or push the Delete key). Click Yes to delete.

Delete all Relationships connecting the Values and Values_1 tables to other tables.

Close the Relationships page and Save changes

Open the Values table in Design View.

Change the Data Type from Number to AutoNumber (Click and browse on the drop-down Data Type cell).

Close the Values design window and Save changes.

Open the Relationships window (Tools/Relationships)

Re-create the relationships to the Values table. To do this, Drag-and-Drop the field name from the source field to the related field. The table below summarizes the relationships that need to be recreated. This information can also be found on page 7 of “CUAHSI Community Observations Data Model Working Design Specifications Document- Version 3.” For each of these relationships, make sure that Enforce Referential Integrity is checked.

Field	Source Table	Related Table	Relationship Type
ValueID	Values	Groups	One-to-Many
ValueID	Values	DerivedFrom	One-to-Many
QualityControlLevel	QualityControlLevels	Values	One-to-Many
SampleID	Samples	Values	One-to-Many
SourceID	Sources	Values	One-to-Many
MethodID	Methods	Values	One-to-Many*
QualifierID	Qualifiers	Values	One-to-Many
OffsetTypeID	OffsetTypes	Values	One-to-Many
VariableID	Variables	Values	One-to-Many
SiteID	Sites	Values	One-to-Many
Value	Categories	Values	One-to-Many*

* Do not Enforce Referential Integrity

Re-create the relationships to the Values_1 table.

Field	Source Table	Related Table	Relationship Type
DerivedFromID	Values	DerivedFrom	Many-to-Many*

* Do not Enforce Referential Integrity

3.b. Create an intermediate selection query.

Create a new query in design view. Add the qry_ValuesUnion query from the Queries tab. Also add z_Events from the Tables tab.

Notice that two series of available fields appear in the query design window. Because a relationship based on TagID was created in 0, a line connects the TagID line in each box. This existing relationship allows the user to create a single query including data from multiple tables.

Note: the use of multiple tables is not possible in an append query, forcing the creation of an intermediate query.

Add the following fields from qry_ValuesUnion: Value, StoretCode, Gtlt, UTCOffset, OffsetTypeID, QualifierID, SourceID, QualityControlLevel, DerivedFromID, SampleID and MethodID

Add the following fields from z_Events: StationID, EndDate, EndTime and EndDepth

Save the query as qry_ValuesIntermediate, Run, and Close

3.c. Create a query to append the Values table with the results from qry_ValuesIntermediate.

Create a new query in design view. In the Show Table window, select qry_ValuesIntermediate from the Queries menu, Add, and Close.

Change the type of Query to an Append Query. In the main menu go to Query/Append Query, select the Values table and click OK.

Append the following fields:

Source Field	Append To Field
Value	Value
StationID	SiteID
UTCOffset	UTCOffset
StoretCode	VariableID
EndDepth	Offset
OffsetTypeID	OffsetTypeID
QualifierID	QualifierID
SourceID	SourceID
QualityControlLevel	QualityControlLevel
MethodID	MethodID
SampleID	SampleID
DerivedFromID	DerivedFromID

Save the query as qry_ValuesAppend and Run.

Create Programmatically Derived Tables

Some tables within the ODM contain information that is programmatically derived. This data is not imported from outside sources, but is instead generated within the database itself. These tables are used to quickly access information about the data, often called metadata. An example of a programmatically derived table within ODM 3.0 is the SeriesCatalog table. This table has a record for each unique site and variable combination. Once this table is created, it is easy to determine the number of variables measured at a single site, as well as how many values have been measured for each of those variables.

1. Create a SeriesCatalog selection query

Create a new query in design view. At the Values, Sites, and Variables tables to the Query design. The SeriesCatalog will contain information about the Values, Sites, and Variables tables.

Add fields to the query from the following list of table-field combinations:

Field	Table
SiteID	Values
SiteCode	Sites
Name	Sites
VariableID	Values
Variable	Variables
UnitID	Variables
SampleMedium	Variables
ValueType	Variables
DateTime	Values
DateTime	Values
UTCOffset	Values
GeneralCategory	Variables

Once the query has been set-up in Design View, go to View/SQL View to open it in SQL View.

The SQL code should currently look like this:

SELECT Values.SiteID, Sites.SiteCode, Sites.Name, Values.VariableID, Variables.Variable, Variables.UnitID, Variables.SampleMedium, Variables.ValueType, Values.DateTime, Values.DateTime, Values.UTCOffset, Variables.GeneralCategory

FROM Variables INNER JOIN (Sites INNER JOIN [Values] ON Sites.SiteID = Values.SiteID) ON Variables.VariableID = Values.VariableID;

Modify the SQL code to look like the following (Additions are in Bold)

SELECT DISTINCTROW Values.SiteID, Sites.SiteCode, Sites.Name, Values.VariableID, Variables.Variable, Variables.UnitID, Variables.SampleMedium, Variables.ValueType, Min(Values.DateTime) AS BeginDateTime, Max(Values.DateTime) AS EndDateTime, Values.UTCOffset, Count(*) AS ValueCount, Variables.GeneralCategory

FROM Variables INNER JOIN (Sites INNER JOIN [Values] ON Sites.SiteID = Values.SiteID) ON Variables.VariableID = Values.VariableID

GROUP BY Values.SiteID, Sites.SiteCode, Sites.Name, Values.VariableID, Variables.Variable, Variables.UnitID, Variables.SampleMedium, Variables.ValueType, Values.UTCOffset, Variables.GeneralCategory;

Save this query as qry_SeriesCatalogSelect

Run the query.

2. Change the SeriesID field type in the SeriesCatalog table to Autonumber

Select the SeriesCatalog table, and open in Design View. Change the type to Autonumber. Close the table design view, and Save changes.

3. Create a SeriesCatalog append query

Create query in Design View. Select the qry_SeriesCatalogSelect created in the previous step as the table to show. Go to Query/Append Query to change the query type. Select the SeriesCatalog as the table to append and click OK.

Fill the Append query with each of the fields in the qry_SeriesCatalogSelect. The Append To field will be filled automatically because the field names in qry_SeriesCatalogSelect match those in SeriesCatalog.

Save the query as qry_SeriesCatalogAppend.

Run the query. Select Yes to append the rows. Open the SeriesCatalog to ensure that the records have been filled.

Now that the SeriesCatalog has been filled, the user of the ODM can easily determine the number of records for each unique Site-Variable combination.

Delete raw data tables from ODM

Now that the raw data from the TRACS tables has been imported into the ODM structure, the raw data tables can be deleted. Because they were named with the “z” prefix, they should be easy to f