Datasheet

60
Data Retrieval, Processing and Final Storage in the Nile Basin Database
Annexes
Annex 5: General Design of the Append Queries for Final Processing of the Report
Output Files in MS Access.
The function of the pre-defined append queries is to perform the final processing steps in MS Access. This
includes:
two separate text fields containing day and month, and year respectively, are converted into a single date •
field;
short datalogger ID (due to the logger’s memory configuration limited to a number below 255) is transferred •
into an official numerical station identifier, in case of Hydromet-IDs consisting of 8 digits;
automatic quality control: checking if the data values are within an expected range;•
adding of ‘source’ value: indicating the origin of the data set;•
re-arranging column structure to NBD format.•
The window below presents the design of the “Meteo_Daily AWS” append query, which uses “Meteo_Daily” as
auxiliary source table and “AWS Meteo_Daily Data” as the final destination table in the NBD.
The query employs two tables: ID-Converter and an auxiliary MS Access table, in this case Meteo_Daily. The tables
are linked through the Datalogger-ID field.
Field 1 is used to transfer the (short) datalogger ID into a permanent unique station identifier.
Field 2 serves to convert the two fields “Year” and “MM-DD” into a single date.
Field 3 has the function to add a source value to the final data table. The number 104 has been allocated for data
stemming from the FAO GCP/IRAF/304/JPN and FAO GCP/INT/752/ITA Projects’ Monitoring Network.
Field 4 to 10 contain the actual data values.
Automatic quality control is accomplished by specifying validation rules in the target tables.
The design of the other append queries is analog. Input (auxiliary) and Output table should be changed according
to the combinations presented in Table 5 in paragraph 6.2.
The first three fields are identical for all queries, while the remaining fields depend on the data contents of the
auxiliary table.
An overview of the various auxiliary tables (Group 1) is given in Table 4 in paragraph 6.2.