Procedures for Updating Weather Databases and the Weather Data Website on the NMSU Corona Range and Livestock Research Center
Technical Report 46
L. Allen Torell, Kirk C. McDaniel, Shad Cox, Suman Majumdar
College of Agriculture and Home Economics, New Mexico State University
Authors: Respectively, Professor, Department of Agricultural Economics and Agricultural Business; Professor, Department of Animal and Range Sciences; Superintendent, Corona Range and Livestock Research Center, and former Graduate Research Assistant, Department of Agricultural Economics and Agricultural Business, all of New Mexico State University (Print friendly PDF)
This technical report describes the procedures required to update and maintain weather data that is collected on the Corona Range and Livestock Research Center (CRLRC). The intended audience for this report is professional and research staff who will download and update Corona Ranch weather data in the future. It is also a reminder to the authors about the process and steps that were used to update and maintain the weather databases. The directions assume use of Office 2003 Excel and Access applications. It is further assumed that the reader knows how data are stored and queried in both Access and Excel and that the file being described is open and the user is following along with the directions. Experience with pivot tables and pivot charts is also useful.
Corona Weather data are stored in two Access databases and in numerous spreadsheets that are linked to these databases. One database is maintained for weather data collected by the South House (SH) and Oil Well (OW) data loggers and recorders. The second database is for the Natural Resources Conservation Service (NRCS) Soil Climate Analysis Network (SCAN) site located on the Corona Ranch and referred to as the Adams Site (http://www.wcc.nrcs.usda.gov/scan/site.pl?sitenum=2015&state=nm). Data from these sites are stored and available on the NMSU Agricultural Economics Website (http://agecon.nmsu.edu/corona). As a person responsible for updating the files you will need to map a network drive to the Agricultural Economics server at \\agesvr1\agepages\Corona. To obtain a username and password for the server, contact Dr. Allen Torell at firstname.lastname@example.org. The Corona weather webpage is stored in this root directory. Data files are stored in the folder called Datafiles.
Introduction How to Update South House (SH) and Oil
Weather data for the SH and OW sites were downloaded and recorded in separate spreadsheet files over earlier periods by Dr. Kirk C. McDaniel and his students. Data are now recorded in a similar way by professional staff stationed at the Corona Ranch. Annual spreadsheet files are maintained on the Agricultural Economics Corona Weather web server. Annual data are loaded into an Access database called "Corona_SH_and_OW_Weather_Data.mdb."
Downloading Data from the OW and SH CR-10 Recorders
The weather stations located at the Oil Well (designated as 201 in the database) and South House (101) sites are manually downloaded periodically throughout the year. The station data loggers record data on a limited-memory hard drive that can store 78 days of data. When data loggers are full, the oldest data are overwritten with newer data and the old data are lost forever. Attention to timely downloads is a must to keep a full and accurate weather record for each site. A two-month (60-day) schedule for downloads is probably the most convenient and manageable. The storage module (SM 192), along with the keyboard display (CR10KD) is utilized for downloading and transporting data from the field to the office for permanent download and storage. Further mention of the storage module collectively includes storage module (SM 192) and keyboard display (CR10KD) as a combined unit. Again, this storage module has limited capacity and can only store approximately 280 days of data (140 days from each site). When the storage module is full it will not allow any further data download and will not erase any stored data. Periodically, the storage module will need to be erased to allow further downloads. A rule of thumb has been to erase the storage module every other download sequence after current data are downloaded correctly. Directions for erasing the storage module are addressed in the later section describing use of the PC208W interface program.
Using the Storage Module for Station Download
Plug the storage module 9-pin male serial cable plug into the weather station data logger 9-pin female serial receptacle (there is only one located on the data logger unit facing you). At this time you should see activity on the storage module display. The following keystrokes and explanation will complete the download task:
Directions for downloading only data recorded from last download:
|* 8||Start manual dump to storage module
(Display should be 01:)
|A||Advance; shows start location of new data
(Display should be 03:)
|A||Advance; shows ending location of new data
(Display should be 04:)
|1A||Dump data; Advance; download is complete when numbers stop changing|
It may be necessary to download all the data from the data logger, and the following directions will download all data:
|* 7||Displays current location of data on hard drive (Display should be 07: and 4 or 5 digits; disregard decimal with 4 digits). Write this pointer location number down.|
|* 8||Start manual dump (Display should be 01:) to storage module; Advance|
|71A||Key in the 4- or 5-digit pointer location number plus 1 (e.g., if point location number was 13569, key in 13570)|
|A||Advance; shows start location of new data
(Display should be 03:)
|A||Advance; shows ending location of new data
(Display should be 04:)
|1A||Dump data; Advance; download is complete when numbers stop changing|
Using the PC208W Software Interface for Storage Module Download
Once all the data have been downloaded to the storage module it can be taken to the office for downloading to a PC. To connect the storage module to a PC it is necessary to connect, through the 9-pin peripheral, to the RS232 interface (SC532) and then connect the SC532 to the serial port of the PC with a 25-pin to 9-pin serial interface card. Make sure the SC532 is plugged into a 120v outlet for power. At this point the keyboard display will light up all LEDs. Open the PC208W program on the computer, which will open as an independent tool bar at the top of the screen. Click on the Stg_Module button to open the storage module utility. Click on the Connect tab located at the lower right of the window. After a few moments the Connect tab will change to Disconnect; at this point the storage module is in communication with the storage module utility. If this is the first time using the utility with this PC you will need to click the File Naming Options button to direct the utility to save the data in the appropriate folder on the PC. Once this has been set it will always save the new file by advancing the file name one number and saving it to the selected folder. Separate data logger downloads will be saved as individual files. To download the data from the storage module you need to click the Get New button at the left of the window and wait for data to download. Download status is indicated at the lower right hand side of the window.
Erasing the storage module is accomplished using the above utility as well. Click the Erase tab at the bottom left-hand corner of the window. Be sure to click the Erase Data button and not any of the other buttons. Clicking the Erase Data button will ensure that the storage module programming is restored after the data are erased. Press the Disconnect button after the task has been completed. Station raw hourly data are now filed in the folder chosen and ready for upload to the database as discussed next.
Updating the Database
If all goes well and the CR-10 recorder functioned properly with no skips in data recordings over the period, hourly data are added to the table called "Summary" in the Access database file called "Corona_SH_and_OW_Weather_Data.mdb" (located at http://agecon.nmsu.edu/corona/). This section describes how to add the data to the Access database. The "Summary" table is the main table of the database and it contains hourly data with 20 data columns as described below. Variables included in the table are:
- SITE: Code name of site (101) for South House and 201 for Oil Well)
- SITENAME: Name of site (SH South House and OW for Oil Well)
- YEAR: Four-digit year number (e.g., 2003)
- MONTH: Serial number for the months of a year (e.g., 6 for June)
- WEEK: Serial number for the week of the year (e.g., 45 for 11/1/2005)
- DAY: Serial number for the day of the year (e.g., 305 for 11/1/2005)
- HOUR: Serial number for the hour of the day (e.g., 1300 for 1:00 p.m. and 2400 for 12 a.m.)
- DATE/TIME: Date and time of a particular day (e.g., 10/30/2005 8:00 p.m.)
- ATEMP: Air temperature (oC)
- STEMP1: Soil temperature (oC) at 10 cm
- STEMP2: Soil temperature (oC) at 50 cm
- RH: Relative humidity
- WS: Wind speed (meter/second)
- WD: Wind direction (degrees on the compass)
- VOLT: Voltage on system
- RAIN: Rainfall (mm)
- Extra1: This column actually contains nothing.Users should ignore this column.
- Extra2: This column actually contains nothing. Users should ignore this column.
- SMOIS1: Soil moisture at 10 cm
- SMOIS2: Soil moisture between 10 cm and 30 cm
For the convenience of database users a Welcome screen (Figure 1) with buttons to open different tables and queries and to add new hourly or daily data has been built into the MS Access database. From this screen a user can browse various parts of the database with a mouse click. Data are easily viewed from the Welcome screen, but the knowledgeable user can minimize or close this screen to view other tables and queries. If the Welcome screen is closed the screen can be activated by opening the form called Welcome. The user can also view the data by opening the following queries or other queries.
- Hourly data — Open "Query_Combined_Daily"
- Daily data — Open "Query_All_Data"
- Monthly data — Open "Monthly_Summary_Query"
The knowledgeable user can set criteria in these queries to provide different views of the data. Care must be taken to remove any temporary queries that you may define for a particular analysis.
Figure 1. Welcome Screen for SH and OW weather database.
Adding New Hourly Data to the Weather Database
- Open Excel and select File, then Open from the top menu.
- Navigate to where the CR-10 data file is located and select All Files in the Files of Type section.
- Choose the Delimited option in the Text Import Wizard, and using Comma as the separator retrieve the data from the data logger file into Excel.
- Delete columns M and N, as they are not needed.
- Click on the header of column B and insert a blank column. If column A is "101" add "SH" down the inserted column, and if column A is "201" add "OW" to the corresponding cells in this new column. You are adding a column with the abbreviations for the research site.
- Column C is now YEAR. Click on Column D and insert two blank columns.
- Column H is now ATEMP. Click on column H and insert a blank column. In cell H1 type the formula "=DATE (C1, 1, F1) +TIME (G1/100, 0, 0)" and copy it down. Format this column with a date and time format (e.g., 3/14/01 1:30 p.m.). Verify that the correct date and time are displayed.
- In cell D1 type the formula "=MONTH (H1)" and copy it down.
- In cell E1 type the formula "=WEEKNUM (H1,2)" and copy it down. If this formula does not work go to the Tools menu, then Add-Ins and turn on the "Analysis ToolPak."
- At column Q, which should now be SMOIS1, insert two blank columns. The spreadsheet should now include data through column T and be organized into 20 columns of data as described earlier. Minimize this file.
- Open the spreadsheet file called "Add hourly data to this table.xls." This spreadsheet has the required headings, and Access macros link to this file to import data from it. The file is currently available on the NMSU Ag Econ server.
- In "Add hourly data to this table.xls" delete all the data (except the column headings) on the sheet titled "Insert Data Here." From left to right, the columns should be SITE, SITENAME, YEAR, MONTH, WEEK, DAY, HOUR, DATE/TIME, ATEMP, STEMP1, STEMP2, RH, WS, WD, VOLT, RAIN, Extra1, Extra2, SMOIS1, and SMOIS2.
- Paste the data from the temporary spreadsheet file you built with all cells pasted as values. Verify that all columns match and are in the right order.
- Check the data for errors. The recorders will record a number like 6999, -6999, 9999, or -9999 when an error occurred. You will need to fix or delete these errors. This is most easily done by selecting Data, then Filter, and then Autofilter from the menu at the top.
- Now, select all of the data including the column headings. In the menu bar go to Insert, then Name, and then Define. A dialog box with caption Define Name will pop up. Type "Import" in the space given just below Names in workbook in the dialog box. Then click on OK at the right-hand side of the dialog box. This provides a named range called "Import" for the data imported into Access. This named range should be defined to include all of your imported data from cell A1 to the last data entry in column T.
- Save and close the spreadsheet file after writing down the date and time of the first data entry.
- Open the OW and SH Weather Database. Move to the query called "Query_All_Data," and—for the site you are importing data for—look to see what day and hour were the last time data were previously entered into the database. Go back to the spreadsheet you are about to import data from and, after verifying the similarity of data for the overlap days, delete any rows that would result in duplicate rows.
- In the Welcome screen click on Add Hourly Data (Figure 1). Macros must be enabled. As you proceed, a message box will pop up warning about adding data properly to the Excel file. If you have done the previous steps properly then click Next. Read the instructions carefully and click Continue. Select the link called ImportH with your mouse. Note that the mouse pointer will likely look busy at this point, but proceed anyway. Move and link up to the Excel file called "Add hourly data to this table.xls." Click OK to refresh the links. Microsoft Access will confirm a successful refresh. Click OK again on the dialog box that pops up and then click Close on the Linked Table Manager.
- A macro is executed that transfers the data from the spreadsheet called "Add hourly data here.xls" to the bottom of the Access table called "Summary." A message box will pop up confirming successful transfer. Click on OK in the message box. Open the table called "Summary" and verify the data were
- You are not done yet. You must update the daily tables whereby additional queries are executed to compute and merge the new data to daily averages.
- To update the daily data tables and queries with the new data, click on the Add Daily Data button on the Welcome screen (Figure 1) and follow the directions (detailed next).
Adding New Daily Data to the Weather Database
In some cases, hourly data were not available and daily weather recordings were used from other weather stations at or near the Corona Ranch. Daily data were used to define weather variables from September 1989 until October 1990 when the weather stations at the study sites became operational. Nearby NOAA data were primarily used to define weather conditions during these early years. The amount of rainfall was usually the only useful data recorded in the daily data file. The last two columns in the query called "Query_All_Data" indicate whether data have been replaced from another source and what this source was. If you replace hourly or daily data from a different source you should check and add data for these two columns.
- To add daily data to the database, open the Excel file "Add daily data to this table.xls." that is on the \\agesvr1\agepages\Corona server. The data are stored on Sheet1. Descriptions of variables are given on Sheet2. Data for many of these variables were not available or were not recorded on a daily basis. However, the variable names are needed as placeholders and for proper merging with the hourly data once it is tabulated to a daily time step.
- It is important that you do not delete any of the existing data from the spreadsheet file. Instead, add new data to the end of appropriate columns. Enter "1" under the column "Count" (column AF) for each entry, indicating that one daily value is being recorded.
- Once the data are entered you must redefine the length of the range name that will be exported to Access. To do this, select all of the data (old plus new Columns A through AF) including the column headings. In the menu bar go to Insert, then Name, and then Define. A dialog box with caption Define Name will pop up. Type "Import" in the space given just below where it says Names in workbook in the dialog box. Then click OK at the right-hand side of the dialog box. Save the spreadsheet file.
- The next step is to import and link the daily data to the Access database. To do this, open the Weather Database. In the Welcome screen (Figure 1) click on Queries and then click on "Query_All_Data." The hourly data query will open. Now, go to View in the Access menu bar and click on Design View. Make sure that no criterion is set for the hourly data query. Close the query and exit back to the Welcome screen.
- In the Welcome screen click on Add Daily Data. Read the instructions carefully and click Continue. The Linked Table Manager will open. Select the check box to the left of Import. Note that the mouse pointer may look busy at this point, but proceed anyway. Click OK to refresh the links. Microsoft Access will eventually confirm a successful refresh. Click OK again in the dialog box that pops up and then click Close on the Linked Table Manager. This refreshes the interactive link with the daily data table that is stored in Excel. This will execute a macro called Add Daily Data that first deletes the existing "Dailydata" table and recreates a blank table with the same name and headings, and then adds to this the imported daily data and tabulations of daily values calculated from the hourly data in the database. Two sources of data are merged: the Excel table called "Import" that includes the spreadsheet data and the Access query called "Daily_Averages". Two append queries are executed for this purpose, "Append1" and "Append2," stored in the Queries section of the Access database. A message box will pop up confirming the successful addition of data.
- Because averages are computed from hourly values it is important that any updated hourly data be entered before updating the daily data. It is also important that this daily data query is executed after updating the hourly data.
How to Update Monthly Adams Site Data
Data for the Adams SCAN site is downloaded and retrievable within a day of recording, but it is best to update the data at the end of each month. Accumulated data can be downloaded at the end of the month as a columnar text file from the website located at http://www.wcc.nrcs.usda.gov/scan/site.pl?sitenum=2015&state=nm. The variables included in the text file are described at the Adams website as "Sensor Label Descriptions." Excel macros are used to consistently and accurately break the data into columns and add the data to the Access database maintained at the Corona Ranch website. The data are stored in separate spreadsheets for each year (November through October) in comma separated value (CSV) format. The annual CSV files are then merged together to create the Access database. The basic steps required to update the Adams site weather data are to download the text file from the Adams SCAN site and arrange the data into the same format and columns used in the Access database.
Formatting Adams Site Data in Excel
- Historical data files for updating Adams site weather data are located on the NMSU Ag. Econ. Web Server (\\agesvr1\agepages\Corona)
- Go to the Adams weather SCAN site at http://www.wcc.nrcs.usda.gov/scan/site.pl?sitenum=2015&state=n
- Follow the link "TK Formatted Historical Files for this Site (FTP Server)" to the correct annual folder and download the appropriate month's data file to your computer by right clicking on the TEXT file ("2015_200708.txt" as an example) located at the SCAN site and selecting Save Target As…
- Open the spreadsheet file called "Adams_Site_Data_Template.xls" (this file is stored on the Ag. Econ. Web Server at \\agesvr1\agepages\Corona).
a. Enable the macros that are included in this spreadsheet program. b. Look at the data that are stored in this spreadsheet on Sheet1 to become familiar with how the data should look when done. c. Sheet1 of the "Adams_Site_Data_Template.xls" spreadsheet must initially be set correctly by erasing all the data that was previously stored on the sheet and by deleting the columns labeled Date. The required steps have been automated by pressing the macro button on Sheet2 labeled Clear Sheet1. d. Leave this file open, as you will run the macros included, but in another file described next. Minimize "Adams_Site_Data_Template.xls."
- Right-click on the text file that you downloaded ("2015_200708.txt" as an example) and select Open With and then Excel. The data need to come into Excel so that all data are in column A as a single text string. You can also bring the data into Excel correctly by opening the text file with Excel, and when it tries to convert the text to columns select Delimited and then specify no option for the delimiter. The text will appear as a jumbled mess. Both spreadsheets, the one with the new data and "Adams_Site_Data_Template.xls," should be open in the same session.
- You now need to move the data out of Column A to the "Adams_Site_Data_Template.xls" spreadsheet, separating the data and putting it into the correct columns. You do this by having both this spreadsheet with the new data and the file "Adams_Site_Data_Template.xls" open at the same time. In the spreadsheet file (new data file downloaded from the website) you now run the macro called Movedata, then switch to "Adams_Site_Data_Template.xls"; notice how the text has been moved to this spreadsheet with the data transferred to the proper columns. If any messages appeared asking whether you desire to replace the existing data then the data did not import correctly, and you should verify that the correct columns were included and that all previous data have been deleted.
- The data should now be separated into four sections with each section starting with a variable called Date_Time. You must now make sure all the sections have the same number of row entries. However, the total number of row entries will vary depending on the month for which data are entered. If one of the sections has data missing (e.g., a missed hourly recording), that section will have fewer rows. Column BW will help you determine whether Section 1 and Section 2 have an equal number of entries. Move down column BW and if data are missing the TRUE/FALSE comparison will turn from TRUE to FALSE (highlighted in yellow). As an example, for August 2007 (Figure 2), the second section was missing an entry for 708171600 (8/17/2007 4:00 PM). The TRUE/FALSE comparison in column BW (i.e., was the date of the first section equal to the date of the second section?) turned FALSE at this point. To correct the problem, the 708171700 (8/17/2007 5:00 PM) row (and all the data below it) was copied down one row for this middle section. The missing date code (708171600) was manually inserted along with blank cells for other data entries. Column T gives the hour of the recording, and data in this column were moved up while "24" was entered at the end. The TRUE/FALSE comparison must now be copied down from the top again and reevaluated to verify no other data are missing. Note that for the inserted row the TRUE/FALSE comparison may remain FALSE (yellow) because the difference between the two cells is not exactly zero. This is not a problem. Once corrections are made, the number of rows in Section 1 and Section 2 should be the same and the TRUE/FALSE comparison should say TRUE all the way to the bottom of the column.
Figure 2. An example of correcting missing data.
- Column BX provides a similar comparison between Sections 2 and 3. Use the same procedure as Step 7 to add any missing rows to the data.
- As a check, move across the last row of data and make sure each of the first three sections have an equal number of entries. Delete extra data the macro wrote at the bottom of the "Date" column.
- The rows in the fourth section of the spreadsheet (detailing average amounts for the day) must now be moved down to be recorded on the comparable midnight row entry of the other sections. A macro called Addformulas has been written to do this. First, make sure that an entry is included in Section 4 for midnight on each day of the month and then run the macro called Addformulas. Notice that the daily data has been moved down columns BI to BT to correspond to entry on the midnight row.
- Recording devices at the Adams site might have recorded data incorrectly and you have no way of knowing about minor errors. When the recorder knowingly had an error it recorded -99.99 for some variables. Using conditional formatting the spreadsheet will display the cell in red whenever a number not in the range -80 to 80 was recorded. You need to assess whether these "red" cells are in fact an error, and you will most likely delete these erroneous hourly recordings.
- Open "Adams_2007.xls" (or the current year spreadsheet) and move to the bottom of the data. Data are entered in this spreadsheet in one-year blocks (Nov. 1—Oct. 31). If you are starting a new year then delete the data but leave the headings.
- Carefully cut and paste the data from the "Adams_Site_Data_Template.xls" spreadsheet into the "Adams_2007.xls" spreadsheet (or the current year version). Note that not all columns are copied, so carefully verify that everything is in the right place and that data were copied to the bottom of each column (no data are missing). Formulas are included to calculate the year, month, day, hour, and minute, and you will have to copy those formulas down the column as new data are added. Format column F as a number (not a date) with 6 decimal places.
- Save the file as "Adams_2007.xls" (or the current spreadsheet year). Now erase all the rows of labels and information at the top so that only columns of data are saved. This includes deleting the row with variable names. Re-save the file as "Adams_2007R.csv" (in CSV format).
- Spreadsheet CSV files for multiple years are combined next using the "MERGE.bat" file. Run the "MERGE.bat" file to combine the multiple years of data to a file called "Hourly_data.txt." If you add another year (spreadsheet) you will have to alter the code of "Merge.bat." To run the batch file, click on the Windows Start menu, then click Run and enter "CMD" into the box. This brings up a DOS session. From the DOS prompt, navigate to the directory where "MERGE.bat" is saved. To navigate in DOS, type "cd\directoryname\subdirectoryname" (note: you must start at the C:\ prompt); for example, the whole command might be "cd\Documents and Settings\Documents\Adams Site," depending on where you have stored the "MERGE.bat" file on your computer. Once you have navigated to the correct directory, type "MERGE.bat" at the DOS prompt. This merges all the years of data to a text file called "Hourly_data.txt."
Updating the Database
- Open the Adams Site Access database and right-click on the Table called "Hourly_data" and update the link using link manager with the right mouse button. Point to (link to) the "Hourly_data.txt" file you just created and update the link.
- Open the Table called "Hourly_data" and move to the bottom. If there are blank lines at the bottom of the table this is because there are blank lines at the bottom of the spreadsheet. If this is the case, you need to go back to the CSV file and delete those blank lines by hitting delete after selecting those rows at the left spreadsheet margin. If there are no blank lines, verify that the other data imported correctly.
- With the imported, updated and combined data, the tables in the Adams Site database are totally rebuilt. So, delete the Table called "Combined_Hourly."
- Right-click on "Combined_Hourly_template" and copy it as "Combined_Hourly."
a. Why? The data type is wrong in "Hourly_data" as read from the spreadsheet and we must redefine the data types so they are as defined in the table called "Combined_Hourly." b. Run the query called "Append1" to append the data to "Combined_Hourly."
- Verify your success by opening the Adams Site database and ensuring that all data was imported and combined properly.
- Reduce the size of the Access database file by selecting from the Access menu Tools, then Database Utilities, and then Compact and Repair.
How to Update Research Report Tables and Charts
Research Report 761 includes various charts and tables. These data are linked to the two Access databases and can be updated relatively easily. The following directions for the update are provided (Table 1). Before you start, save an unaltered backup of each file. For each table and chart, the location and linkage of the data are described, along with the process that would be followed to update the spreadsheet table or chart.
Once the Access databases and Excel spreadsheets are updated, the outside user can only access the data from the Zip files stored in the Datafiles directory on the server. You will need to drag the appropriate Excel and Database program file to the appropriate Zip file to complete the update and make the data available to the user via the web.
|Table 1 and Figure 2||1914-2006Rainfall_Summary.xls||Data are not linked||1. Modify the data on sheets Data _Inch1914_2006 and Data_mm_1914_2006. Add new data rows as needed. After adding new data, look at the text formulas in Table 1 (on the Rain_Averages sheet) and verify that the proper cells are referenced from the data sheets. Change the chart data source reference to expand years.|
OW and SH site data is linked on sheet OWSH_Data to the "Corona_SH_ and _OW_Weather_Data.mdb" database. The link is to the query called "Query_Combined_Daily."
Adams site data is linked on the sheet ADAMS_Data to the "Adams_Weather.mdb" database. The link is to the query called "Query_Daily_Rainfall."
1. Go to the data on the sheet OWSH_Data and right-click anywhere in the data. Edit the data query if necessary and refresh the data coming from the Access database. Copy down the formulas in columns AI and AJ if new data are added.
2. Go to the data on the sheet ADAMS_Data and right-click anywhere in the data. Edit the data query if necessary and refresh the data coming from the Access database. Copy down the formulas in columns L to N if new data are added.
3. If all works correctly, you should be able to update the data links and just refresh each pivot table (with a right mouse click). You should always ensure that the data range for each pivot table includes all of the data after refreshing. You do this by clicking in a pivot table, selecting Pivot Table Wizard and then the Back button. Verify the data range or change the range if needed.
|Figure 3||OW_SH_Adams_Rainfall.xls||See above||4. After updating the tables on sheet Table4, change the chart range names in Figure 3 as needed. Change long-term averages referenced as needed on the Table4 sheet.|
|Figure 4||OW_SH_Adams_Rainfall.xls||See above||5. After updating the OW and SH data on OWSH_Data, refresh the pivot table on sheet Figure4. Near the top of Figure4 you will find Frequency formulas that use data from the pivot table (columns O, S, W, AA, and AE). Appropriately change the ranges referenced. The frequency function is an array formula, so you must select the whole range to change it. For example, to change column O you would first select all of range O4:O9 and then function F2 to edit the formula. Because it is an array formula, when done you must enter CTRL Shift_Enter.|
|Tables 5 and 6||Corona_Rain_Gauges.xls||Data are not linked; rather, new data is typed directly on the sheet called Data.||
1. On the sheet called Data enter data in columns A, B, C, and D. Column E is calculated from column C so copy the formula down.
2. In column D enter any information about the type of storm, etc., that occurred.
3. Enter a "rain trace" as 0.0010 inches.
4. Go to the bottom of the data and enter new data at the bottom. You may want to copy down data for the 7 locations and change the data after pasting.
5. If, in a particular month, it did not rain at a particular site, enter a zero (0); otherwise a blank space will appear in the pivot table.
6. It does not matter in what order you type the data.
7. The data range is a "dynamic named range," so it will automatically grow as you type new data. The pivot table will automatically include the new data.
8. After entering new data, go to one of the pivot tables, right-click in the table and refresh the pivot table. Do this for each of the pivot tables.
|Annual plotpivot—not included in report||Corona_Rain_Gauges.xls||This sheet reads data from the
monthly pivot so as to plot annual totals by rain gauge site. The pivot table and pivot chart are presented on the sheet Annualplotpivot.
9. If more years are added to the data, then add a new data link at the bottom of this sheet.
10. Change the pivot table link as needed
|Monthly Plot—not included in report||Data is read from the sheet called Data using a dynamic range name called Data.||11. A pivot table is included here so as to provide a flexible plot option by year and rain gauge site.|
|Table 7||Killing Frost.xls||Data is linked on the sheet called Data to the "Corona_SH_ and _OW_Weather_Data.mdb" database. The link is to the query called "Query_Combined_Daily."||
1. Load this particular file in Excel 2007, as the pivot table was built in this newer version. Go to the data on the sheet called Data and right-click in the data. Edit the data query if necessary and refresh the data coming from the Access database.
2. The pivot table on the sheet called Frost was used to highlight those dates where temperatures dipped below 32°F and 28°F . This table will visually help determine the last and earliest frost of the year.
3. Frost dates are manually typed in table 7.
|Table 8||Table8_Degree Days.xls||Data is linked on the sheet called Lookup to the "Corona _SH_ and _OW _Weather_Data.mdb" database. The link is to the query called "Query_Diurnal_Temp."||
1. Data linked on the Lookup sheet are used to compute average diurnal daily temperatures using a pivot table in columns O and P of this same sheet. If temperature is missing, the average value is substituted when calculating degree days.
2. Go to the data on the sheet called Lookup and right-click anywhere in the data. Edit the data query if necessary and refresh the data coming from the Access database.
3. Appropriate data must be typed or cut and pasted to the bottom of the sheet called Data. Data are entered for columns A, B, C, D, E, and G. Other columns are calculated. Eventually, degree days for each day are computed in column H of the Data sheet. Column F evaluates whether data for the day in question is missing on the Degreedays sheet. If it is missing, the average for that day is used from the pivot table; otherwise, the reported diurnal data is used.
|Figure 5 and Figure 6||Soil_moisture_SH_OW.xls||Data is linked on the sheet called Data to the "Corona _SH_ and _OW_Weather_Data.mdb" database. The link is to the query called "Query_SoilMoisture_Daily."||
1. Open this particular file in Excel 2007, as the pivot table was built in this newer version. Go to the data linked on the sheet called Data and right-click anywhere in the data. Edit the data query if necessary and refresh the data coming from the Access database.
2. Click on the chart and save the chart as a user-defined chart on your computer. By doing this you can get back to this look when the data arerefreshed.
3. Alter the pivot table layout by selecting SMOIS200 for the data if you desire to graph the deeper probe.
4. Select a different site or year if you desire these to be altered.
5. The charts in the research report were plotted to PDF in two- year increments and cut and pasted together in Adobe Acrobat.
|Figure 7||OW_SH_temperatures.xls||Air temperature data are stored on the sheet AT_Data. Daily air temperature averages are copied and pasted into columns H, I, and J from the Form called Daily_ Average_Temperature in the OH and SH database. Diurnal temperatures are cut and pasted from the Form called Diurnal_ Daily_Avg_tmp.||
1. Copy and paste data from the Access database forms. To copy the data, view the form in pivot table view.
2. No changes should be needed for the updated chart on the AT_Chart sheet.
3. Copy and paste data from the Access database forms. To copy the data, view the form in pivot table view.
4. The data are pasted to columns C and D on the ST_Data_10cm sheet.
|Figure 8||Data for the soil temperature 50 cm chart are cut and pasted from the form called Daily_ Soil_ Temperature_ 50cm following a procedure similar to that of Figure 5.
6. Copy and paste data from the Access database forms. To copy the data, view the form in pivot table view.
7. The data are pasted to columns C and D on the ST_Data_50cm sheet.
8. No changes should be needed for the updated chart on the ST_Chart_50cm sheet.
|Figure 9||OW_SH_temperatures.xls||Data about relative humidity are linked to the Access database via "Query_Daily_RH_Average" inserted on page RHlookup.||
1. Go to the data on the sheet called RHlookup and right-click anywhere in the data. Edit the data query if necessary and refresh the data coming from the Access database.
2. After updating the data, find the pivot table located on sheet RH_Chart and verify the full data range is included as input to the pivot table. Refresh the pivot table. This step should update the chart.
|Figure 10||OW_SH_temperatures.xls||Data about daily wind speeds and direction are linked to the Access database via "Query_Daily_Wind" on sheet called Wind||
1. Go to the data on the sheet called Wind and right-click anywhere in the data. Edit the data query if necessary and refresh the data coming from the Access database
2. On the sheet MAXWS there are three frequency tables. Change the range references in the frequency calculation formulas. The frequency function is an array formula, so you must select the whole range to change it. For example, to change the maximum wind speed frequency computations you would first select all of range F5:F14 and then function F2 to edit the formula. Because it is an array formula, when done you must enter CTRL-Shift_Enter.
To find more resources for your business, home, or family, visit the College of Agriculture and Home Economics on the World Wide Web at aces.nmsu.edu
Contents of publications may be freely reproduced for educational purposes. All other rights reserved. For permission to use publications for other purposes, contact email@example.com or the authors listed on the publication.
New Mexico State University is an equal opportunity/affirmative action employer and educator. NMSU and the U.S. Department of Agriculture cooperating.