Computing Average Monthly Flow Values using Microsoft Access

 In Water Resources Engineering, the analysis of period of record data is often performed to provide a summary of the conditions at a particular site.  In this analysis, variables such as the overall average, average by month, minimum value, maximum value, etc. can be determined.  In this post, the average by month will be computed.  The data used is USGS flow data for the White River at Newport.  This gage is located in the state of Arkansas.  The period of record extends back to 1927.  For this analysis, the data set has 33,928 values.  Some of the dates have missing data.  This data set was previously analyzed using Microsoft Excel.  In that analysis, the average value for the month of July was computed to be 16,758.45 cfs when accounting for the missing data (meaning that missing data is excluded from the computation of the average rather than having a value of zero assigned to them).  That analysis is shown in the following post:

Computation of Average Monthly Flow Values using Excel

Step 1:

The first step involved importing the flow data into the Access database.  The image below shows a small portion of the table.  The table is titled, "Newport flow data",  

Step 2:

From the menu, select "Create --> Query Design".  We then drag the Newport flow data into the gray space.  This allows us to select items from the table to be used in the query. 

Step 3:

In this step, date and flow are selected as the fields from the table titled, "Newport flow data".

Step 4:

From the menu, select the sum totals button (as shown below) to be able to access the ability to compute averages.  Note that you must be in Design View to access this option.

Step 5:

Since the flow data is the variable we are using to compute the average, "Avg" is selected in the "Total" row and the "flow" field.  

Step 6:

If we were to use this query as shown above, we would get the average of all of the flow values for the period of record.  However, we are looking for the average monthly flow values instead.  In order to get this, we need to make a slight modification to the date field to let Access know that we are wanting the average by month.  In the date field we change "date" to "format(date,'mm') ".  The 'mm' is used to let Access know that we are using a two value numeric format for the month in our date.  The edited field is shown below.  

 

Step 7:

Now that we have the query developed, we move from Design View to Datasheet View to view the results of the query (this is done from the View menu).  In the results shown below, the July average of 16,758.45 matches the value obtained when the data was analyzed using Excel.  This value confirms that Access is not incorporating missing data into the computation of the average value.