Deleting Data

There are times when it may be necessary to delete data from the back-end database.

Before deleting any data, be sure to backup your database.

1.       Open the back-end database file

C:\Program Files\CRIS\<XXXX>\CRIS_<XXXX>.mdb

2.       Follow the instructions below, depending on what type of data is being deleted..

Deleting Imported Monitoring Data

Any of the data imported from spreadsheets into the database is stored in the Measurement Set, Reading and Value... tables. To delete imported monitoring data you must follow the steps below.

A. Modify Relationships

The first task is to modify the relationships in your database to allow for cascaded deleting of records. This will allow you to delete a record in the Measurement Set table, and have all the associated records in the Reading and Value... tables be automatically deleted also.

1. Using the Tools|Relationships menu, highlight the relationship between the Measurement Set table and the Reading table, right click and select Edit Relationship.

2. From the Edit Relationship screen, turn on the Cascade Delete Related Records check-box and click OK.

3. Repeat steps 1 and 2 for the following relationships:

   • Between Reading table and Value Boolean table

   • Between Reading table and Value Long table

   • Between Reading table and Value Text table

   • Between Reading table and Value Double table

   • Between Reading table and Value Lookup table

4. Close the Relationships window and save the results.

B. Identify Records to be Deleted

The next step is to identify which records in the Measurement Set and Reading tables should be deleted. The easiest way to do this is to add a new query to the database that displays all of the Measurement Set and Reading records together. To do this:

1.  Begin by creating a new query called All Data. This query can contain anything at all, as it will be replaced with a new query in a moment. Once created, open this query in Design View, then select the View|SQL View menu. This will display the dummy query as SQL Code.

2. Highlight all of the code listed below and copy it to the clipboard (using Edit|Copy in your browser):

SELECT [Measurement Set].[Measurement Set], Reading.Reading, Location.[Location Abbr], Location.[Location Name], [Measurement Set].[Start Date], [Measurement Set].[Start Time], [Measurement Set].[End Date], [Measurement Set].[End Time], Reading.Variable, [Value Mixed with Var Type].[The Value]
FROM [Value Type] INNER JOIN ((Location INNER JOIN [Measurement Set] ON Location.Location = [Measurement Set].Location) INNER JOIN ([Value Mixed with Var Type] INNER JOIN Reading ON [Value Mixed with Var Type].[Value Key] = Reading.Reading) ON [Measurement Set].[Measurement Set] = Reading.[Measurement Set]) ON [Value Type].[Value Type] = Reading.[Value Type];

3. Highlight all of the dummy SQL Code in your All Data query, and then select Edit|Paste from the Access menu; this will paste the code above into your new All Data query.

4. Close and save the All Data query.

5. Run the All Data query. This will display all of the data in your database. From this query you must identify the Measurement Set numbers associated with each and every record you wish to delete from the database - in general the most recently added records will appear at the bottom of the query (with the highest Measurement Set numbers)

C. Delete Measurement Set Records

Now that the Measurement Set numbers have been identified, you have two choices for how to delete the relevant records:

1. The first option is to open the Measurement Set table, highlight the relevant records, and then select the  Edit|Delete Records menu.

2. The second option is to create a "Delete Query" in Access to delete the records from the Measurement Set table - consult your Access documentation or the online help for more information on how to create Delete Queries.

Deleting Reference Table Records

In addition to the monitoring data imported from spreadsheets, it may also be necessary to delete records from one or more of the reference tables in the database.

To delete records from the Source table:

1. Before you can delete a record from the Source table, you must first follow the steps above to delete any and all records in the Measurement Set table that refer to this source. This is to ensure that all Measurement Set records have a valid Source record in the database.

To delete records from the Country, Region, Locality, Location and Point tables:

1. Because of the relationships between these tables, records must be deleted for these tables in the following order: Point, Location, Locality, Region, Country

2. Before you can delete a record from the Location table, you must first follow the steps above to delete any and all records in the Measurement Set table that refer to this location. This is to ensure that all Measurement Set records have a valid Location record in the database.

To delete records from the Variable, Variable Type, Subcategory and Category tables:

1. Because of the relationships between these tables, records must be deleted for these tables in the following order: Variable, Variable Type, Subcategory, Category. 

2. Before you can delete a record from the Variable table, you must first follow the steps above to delete any and all records in the Measurement Set table that refer to this variable. This is to ensure that all Measurement Set records have a valid Variable record in the database.

Topics      Steps      System Manual Home      CPACC Home

Questions or problems regarding this web site should be directed to [email protected].
Last modified: November 02, 2001