ESdat is designed to work closely with the underlying database in the management and manipulation of your data. It is likely that at some point users who are capable in the use of Access (and this applies mostly to the Access version) will want to add additional tables, or write additional queries. This can be achieved by directly opening the underlying database using the Access link. So long as the queries in the Database run in Access without errors they can be accessed through ESdat.The following guidance is provided in modifying the database.

Guidance

As certain tables, fields and queries (objects) are critical to the operation of ESdat is recommended that users not delete or rename objects without having a thorough understanding of the database workings (and making a backup).

Edits to queries should generally be saved as a new query with a user-specified name.

Customising Queries in Access

Current queries accessed through ESdat can be modified, new queries created, or queries deleted in the Access Database in which the data resides.

There is no restriction on the type of queries which can be created in Access. All of the functionality available through the Access Query Design or SQL views can be used to create your required query. Action queries (Make Table, Update, Append, Delete) cannot be run through ESdat.

Once the query has been saved it can be accessed through the "All Queries" datatype button, or if you have assigned it a (Case Sensitive) prefix which matches that assigned to one of the datatype buttons it will appear in the dropdown list under that datatype button.

The query must have  a field giving x and y coordinate fields in order for the data locations to be shown on a map.

Example Modification

The example below shows a modification of the query WaterLevel_AHD query so that only one result (the average) is returned per day per Well.  This may be useful if using a data logger.  The original query can be left as is if preferred, and the modification given a new name.

1. Open the Query in Design View.  If you get presented with a box of text select View - Design View from the Access menu's.  The screen should look like below:

2. Click the Totals Button

3. Modify the design as follows:

Use the DateValue formula to remove the time portion of the Date_Time and rename as Date as shown below.  The Group By instruction will now group the results by each date rather than each date_time record.

In the Water_Level Column change the Group By to Avg.

Preview the results and save as a new query.  If you wish to make these modifications yourself you should research Access functions and Queries.            

Editing Data Tables

It is possible to add fields in the data tables.   It is recommended that you don’t delete or rename fields.

Adding Fields to a data table is not going to cause any problems. To include the added field in the data output it may be necessary to modify the output queries.

*Changes to the database structure, or table properties may not be available while the database is open in ESdat.  This can be overcome by ensuring the table you wish to change is not being used in ESdat (ie by selecting a different Data Type in the ESdat Interface), or for a very small number of core database changes, closing the database in ESdat.

Adding Data Tables

There is no restriction on the addition of data tables in the Database.

In order to view the data through a Datatype button you need to construct a query based on the table, and give it a prefix. You can edit Data-Type buttons in ESdat under Setup->Data Type Buttons.  Each button is assigned a case sensitive prefix.

Deleting Data Tables

It is not recommended you delete any  tables without checking the relationship diagram.