SOUTHWEST FLORIDA WATER MANAGEMENT DISTRICT
Contents
1. a oe a Meterstat uw ee o Manusl r sce s Autostat o xx oiteassesSs RESTAT 4 4 wo Ux cy MEETOS ues ee oc vk A Acknowledgments References e e Appendix A Instructions Datafile and e 9 9 9 9 9 e 9 9 9 9 9 9 9 9 9 9 9 o 9 9 9 9 9 9 9 9 9 9 9 e 9 9 9 9 9 9 9 9 9 9 9 9 e 9 9 9 9 9 9 9 9 9 9 9 e o 9 9 9 9 9 9 9 9 9 9 9 e 9 9 9 e e e 9 9 9 9 9 9 9 9 e e 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 e 9 9 9 9 9 9 o 9 9 9 9 9 o 9 9 9 9 9 9 9 9 9 9 9 9 9 9 5 on How to Frase the Search Data Bases 15 15 16 Title Appendix B Appendix C Appendix D patch Loading xd X 08 3 18 List of Existing Range Names for SAWUD files we 20 Description of Diskettes e 24 SOUTHWEST FLORIDA WATER MANAGEMENT DISTRICT hn aS Ren EEEE Et eT AGRICULTURAL WATER USE DATA BASE SAWUD by Michael C Hancock and James P Heaney Florida Water Resources Research Center University of Florida Gainesville Florida 32611 Introduction The Southwest Florida Water Management District SWFWMD needs to establish a moderate size data base to support its Agricultural Water Conservation Program The purpose of this study is to develop a proto type of this system using Lotus 1 2 3 Version 2 This system will include da2. SOUTHWEST FLORIDA WATER MANAGEMENT DISTRICT AGRICULTURAL WATER USE DATA BASE SAWUD X Michael C Hancock and James P Heaney Florida Water Resources Research Center University of Florida i Gainesville Florida 326 Publication No April 1987 SOUTHWEST FLORIDA WATER MANAGEMENT DISTRICT AGRICULTURAL WATER USE DATA BASE SAWUD by Michael C Hancock and James P Heaney Florida Water Resources Research Center University of Florida Gainesville Florida 32611 Publication No 97 April 1987 Publication No 97 SOUTHWEST FLORIDA WATER MANAGEMENT DISTRICT AGRICULTURAL WATER USE DATE BASE SAWUD by Michael C Hancock and James P Heaney Florida Water Resources Research Center University of Florida Gainesville Florida 32611 April 1987 Final Report to Southwest Florida Water Management District Brooksville Florida TABLE OF CONTENTS Title Introduction de dde a Description of the Data Base Organization X e de The SAWUD Program General Help Sia oue dw IND we se a Directory gt s es Data k ao Search Stats End and og a sae oe Si dk xx NOW 4 ee ode 9o es ow Update e dd ox Data entry LOOK 455 3 dee a d Bateh asa ox Help and Other DEGECH K ue 4 ns io Se i 265 dk Xe eos Find uxo ue EXLISOGU oe Directory 4 s OLADLLSULOS
3. As the cursor is moved past the bottom of the sereen more text will appear When finished the return key is struck once again and the main menu will again appear at the top of the sereen WARNING Do not move the cursor to any other part of the spreadsheet when choosing amp help file but on the green help file choices Striking return elsewhere may cause the system to crash If this should happen striking the escape key should clear the system and striking the Alt and M keys simultaneously will recall the main menu Additional help files may be added by the user as the system is expanded but not when operating the help file system If an additional help file is to be added or expanded upon the user must exit the system and retrieve the spreadsheet file containing the help infor mation named HELP Each section of help information is given the same range name that appears on the help file list If amp new file is to be added the user must obtain an empty space on the HELP spreadsheet file enter the help information give the section of the HELP file amp range name that describes the information contained within and resave the HELP file The user will then enter the AUTO123 file and add the name to the bottom of the help list two spaces under the last existing name The help list is found at range name HELP This procedure must be done when the user is not in the command system By entering Range Unprotect on the new cell the help
4. DIR STATS CONVERT HELP EXBATCH FILE NAME AUTO 123 DATAFTLE DIR STATS CONVERT HELP 24 Each of the disks contains the following DESCRIPTION Main file and introduction Water use data base Search and AIM directory Statistical routines Format conversion routines Help file Example of batch data entry file DESCRIPTION Main file and introduction Water use data base with example data Search and AIM directory with example data Statistical routines Format conversion routines Help file 1986 INBATCH VERSION 3 25 Example of batch data entry file Input to batch load Wordstar text of this manual
5. Each data set will 11 appear on the screen one at a time for inspection Any errors or missing data must be corrected or filled in before the statistical process can begin Be sure that all data entry blanks are filled before striking return to begin the statistical process If such errors or missing data are found be sure to note the site number for correction in the site data file at a later time Once the data set is complete or if no errors are found the return key is struck and the WAIT indicator will again appear a mes sage to wait will also appear on the spreadsheet screen When the process is complete amp graph of the time series of water use per acre will appear signaling the end of the process When the return key is struck again the time series information on monthly water use water use per acre days between readings and cumulative days will be pre sented along with the count mean maximum minimum standard devia tion and coefficient of variation of these time series The system will also ask the user if the graph should be saved for later printing using the Lotus 1 2 3 Printgraph program Once the program has finished the system will ask if the user would like to save the analysis of the sites onto a separate spreadsheet and the word FINISHED will appear above the first AIM site analysis The user is now free to roam about the spreadsheet manipulating the data in any way desired using Lotus 1 2 3 Release
6. The main files called MAIN or AUTO123 SEARCH or DIR DATA or DATAFILE and STATS can be accessed at any time by each of the files Other files include the HELP file the CUPS data base files the files containing time series data other than water use and user saved output files As the system grows and more data records are added to the disk more disk space will obviously be required although the record size should not require more than the one original floppy disk Below is a description of the four main system files as well as instructions on how to use the system The SAWUD Program The program is accessed by loading the Lotus 1 2 3 Release 2 system disk into drive and the SAWUD disk into drive B The SAWUD system is all inclusive so the program and data files are both on the same disk although expanded records may be stored on additional disks The main file of the SAWUD system will be booted automatically by the Lotus 1 2 3 program and the introductory page and menu below will appear at the top of the screen GENERAL HELP INPUT SEARCH STATS END QUIT A cursor will appear on the choice GENERAL and a short explanation of the choice will appear below the cursor The cursor may be moved to other choices through use of the left and right arrow keys with another explanation appearing below each choice A selection is made by moving the cursor to the desired choice and striking the return key PLEASE NOTH If there is
7. again All previous entries will have been saved however so the user needs only to reenter the last set of data UPDATE The UPDATE menu choice is similar to the NEW choice The user will first be asked whether or not to show a list of the existing AIM site numbers and owners If YES is chosen the list will appear on the screen When the return key is struck the user will be asked to enter the desired AIM number Once again the number is entered with the AIM prefix as described above The desired data base will then appear on the screen and the new information may be added as described above see Data Entry below Existing information may also be edited if desired When the return key is struck the user will be asked to inspect the data see NEW above If the data entry is correct the main input menu will appear If there is an error the entire file will be recalled and the user can begin again Data Entry The following heading appears whenever the NEW or UPDATE routines are useds WATER USE READING CROP FAC MON KWH MGAL RATE DATE All information for each reading must be entered each time In the case of the reading date an apostrophe must be entered before the date which must be entered as follows Month Day Year or 02 07 85 The apostrophe does not appear on the screen when entered but is neces sary in order for the spreadsheet to distinguish the date from a division calculation When entering NEW data for a
8. be instructed to move the cursor to the desired AIM site number and strike return The time series data from the main data base that correspond to the chosen AIM site will then appear on the screen for inspection By striking return once again the system will ask if the user would like to view another data set and the process continues until the choice NO is made When the choice NO is made the system will ask if the user would like to use the data If the choice is NO the search routine will end and the main search menu will appear at the top of the screen If the choice is YES the AIM sites that were extracted will be saved on a separate spreadsheet file for use with the statistics file routines The user may view as many records and perform as many searches as desired and exit the system by choosing MAIN when finished This will return the user to the introductory file DIRECTORY The choice DIRECTORY is used to enter data to the directory records or just to view the data records WARNING Any changes in the data may be permanent so be careful not to type over or erroneously change the data when in this mode When this choice is made the cursor will be brought to the bottom of the actual data base where the new data may be entered A list of approximately 30 parameters will appear at the top of the screen These parameters include AIM site number owner crop type mean precip itation irrigation type etc The entire list of th
9. choice will simply cause the menu to disappear and allow the user to move freely about the spreadsheet Use the QUIT option only if you are fully comfortable with the system or the system could be damaged or lost The choice END will exit the entire 1 2 3 system DATAFILE The time series data file is entered either by choosing INPUT and then DATA in the MAIN file or choosing DATA in any of the other files The Datafile contains all of the water use time series data and can be used to enter data from new sites update data from existing sites or simply inspect the data Once again a new menu will appear as follows NEW UPDATE LOOK BATCH HELP OTHER NEW The NEW choice is used to add time series data to the data base for an AIM site that does not previously exist The user will first be asked if the list of existing data bases should be shown If yes the list will appear on the screen and the user may view the entire list by using the up and down arrow keys DO NOT ENTER ANYTHING FROM THE KEYBOARD AT THIS TIME When finished the user strikes the return key and will be instructed to input the AIM site number of the new site which is entered as the letters AIM and then the three digit number e g AIM123 or AIMO02 In cases where more than one data station is located on the same site the AIM identification number will be followed by a lower case letter such as AIMO23a and AIMO23b etc Failure to enter the AIM site number in this
10. file has been saved Striking return at this point will allow the user to view the data output Striking return a second time will cause the main menu to appear at the top of the screen The SITEASSESS mode is somewhat experimental and should be used with care Additional parameters may be added to the list found in range name EVALAREA by adding the choice to the list with amp new number and range naming the corresponding area in the DIR file with that number The range name must be placed in the corresponding parameter range found in the range name DATAOUT in the DIR file RESTAT If another statistical run is desired the RESTAT may be chosen and the process may be repeated This choice will also ask if the user would like to erase the file containing the list of AIM sites for AUTOSTAT It is necessary however that the user rerun the SEARCH routine if a separate set of AIM sites is to be analyzed using AUTOSTAT The other commands allow the user to return to the other main files of SAWUD 13 MACROS As stated above the SAWUD system is run entirely by the Lotus 1 2 3 macro code located in the far upper right corner of each of the spreadsheet files This area of the spreadsheet should be entered only if the user has amp great deal of experience with Lotus macros and only if at least one backup copy of the system is available in case of a mistake that could destroy the program Macros are often very difficult to manipulat
11. file will then be able to be accessed the choice will then appear green like the other existing choices In many cases within the HELP file a range name will cover more than just the area with text in order to allow for expansion without changing the range name Be sure that the empty space you use is not included in another range name Also when expanding upon an existing help section be sure that the range name covers the newly added material INPUT The input selection allows for the input of data to both the directory of AIM sites contained in the SEARCH file and to the time series data bases contained in the DATA file When the input selec tion is made another short menu will appear allowing the choice of either entering data to the directory or to the time series data bases An explanation of both choices can be seen below in the sections titled SEARCH and DATAFILE Directory When the directory choice is made the user will be brought to the SEARCH file where the DIRECTORY choice in that file will allow input to the directory data base The use of this routine is described below under SEARCH Data When the choice DATA is made the time series data base and entry system file will be called The use of this routine is described under the section entitled DATAFILE SEARCH STATS END and QUIT The SEARCH and STATS choices allow the Search and Stats files to be recalled which are described below The QUIT
12. key followed by the menu choices Graph Name and Use The user may either move the cursor to each choice or may simply strike the first letter of each choice A list of the named graphs would then appear and the user would choose RAIN and stroke the return key The GNU in the macro represents the three menu choices by the first letters of each the RAIN is the graph choice and the is the macro symbol for the return key Thus the user has the choice of either striking the letters seen in the macro or simply simultaneously depressing the Alt and the corresponding macro letter When lengthy series of keystrokes are incorporated into macros the Alt letter pro cedure proves to be the much shorter path Several macro commands are available that represent Lotus func tions These commands assist in automation and simplification of the macros By mastering the use of macros Lotus may be customized to the individual needs Although one of the advantages of Lotus 123 is the elimination of the need for computer programming Lotus macros create the option to include simple yet powerful programming when there is a need ACKNOWLEDGMENTS This study was originally suggested by Mr William Smith of SWFWMD who also provided close cooperation with us during this study and offered many useful suggestions REFERENCES Le Blond G T and D F Cobb 1983 Using 1 2 3 Que Corporation Indianapolis Indiana Lotus Development Corporation 1985 L
13. or not the format conversion has been made If it has the choice YES will automatically upload the new data and return to the original menu If the choice NO is made the entire DATAFILE file will be saved and the conversion program will be prompted The original entry spreadsheets are stored by water year so a prompt will appear to enter the water year of interest Type in the full year e g 1986 Then a prompt will ask for the record number which is 1 12 according to the month of interest The program will then convert the chosen month and return to the DATAFILE menu see Appendix B for more details on this process The BATCH choice is then made and again the computer will ask whether or not the format conversion has been made The YES choice will then perform the batch loading If data for an AIM site not previously existing is contained in the batch load an error message will appear and the problem AIM site will be noted In this case the batch load will be abandoned without destroying any files and the Main program will then be recalled The user must check the original entry file to identify the error HELP and OTHER The choice OTHER produces a menu containing the choices SEARCH MAIN STATS and QUIT each of which is described in other sections The HELP choice recalls the same help files described above and the QUIT choice exits the menu SEARCH The Search file contains the directory records and the routines to
14. site without a rating simply enter the MGAL directly and leave the meter reading and rate columns blank For a site with a rating enter just the meter reading and leave the rate and MGAL column blank for they will be calculated automatically When entering UPDATE data follow the same rules except enter the pump rating into the appropriate column for each reading The same format must be followed at all times or malfunctions will occur IT IS ESPECIALLY IMPORTANT THAT ALL DATA FOR EACH READING BE ENTERED AS DESCRIBED ABOVE Do not attempt to use any of the Lotus functions or commands when entering data LOOK The LOOK choice is used to inspect existing data bases It is identical to the UPDATE choice except that no data may be entered or altered in any way Using the UPDATE mode without entering new data at the bottom of the file or entering data in the LOOK mode will cause malfunetions in the system BATCH The BATCH choice allows large amounts of update data to be added to the existing files automatically Data for new AIM files CANNOT be added using this choice This choice can be used for monthly additions of data to AIM sites already existing in the data base Data must originally be entered in the format specified in Appendix B The format is designed for convenient entry This original data sheet must then be converted for use by the SAWUD batch entry program When the choice BATCH is made the computer will ask whether
15. 2 functions and commands SITEASSESS The SITEASSESS choice can be used to combine meter information at a particular site according to a user supplied algorithm and can retrieve site parameter information from a previously performed SEARCH Once all of the needed information is retrieved the data is output to a file named DATA for further manual statistical analysis This mode is useful for acquiring data for regression analysis using several parameters When the choice SITEASSESS is made a list of the AIM site parameters will appear on the screen and the user will be asked to enter the corresponding number for the first desired parameter The computer will then pause to retrieve the information and ask for the next parameter If no parameters are needed simply enter N as the first entry When the user has asked for all the necessary parameters the letter N is entered REMEMBER This program is only retrieving the data from the last SEARCH performed in the DIR file Once a search is made the user may use the SITEASSESS mode to retrieve all this data in an easy to use form Once the parameters have been recalled they will appear on the screen and the user will be asked if meters need to be combined Since some AIM sites use amp combination of meters to determine water use these data records must be combined according to some algorithm These algorithms can be stored in the DIR directory data base and used here to combine the me
16. 6 HELP BO 1 4 BL8 HELPS BN1 BT 1941 LAT H746 LONG T746 MACRO 1015 10 MESS E30 MESSAGE CF1 L19 MONQ S746 NEWDATA IV8189 NUMBER A47 OUT AY7 AC307 OUTPUT AYO PARAM PARAM1 E27 PARAM2 F27 PARAM3 G27 PARAMJ H27 PARAM5 127 PARAMCH IH1 IJ 1 PARAMETERA745 Y 745 PARAMS PCPC C41 DJ2 ROWSP Y746 TOTO R746 WAIT B2 WAIT2 ANS WELL ACT46 YIELD V746 0 IP41 IP56 LO Tes 101 AX B43 IP24 CONVERT BATCH A0422 AU1502 FIND BE1 MACRO A41 SCRATCH A401 G722 SFIND BE 1 STOPPER G41 H41 0 A141 STATS AIMS A102 ALGO IR57 DATA A41 HU2001 DATAOUT A41 DISPLAY A2020 IV5000 EOUT A2024 1V6000 ERRMSG 1036 EVALAREA A2000 EVALOUT A202 HELP TB61 1G141 HELPS IN67 2002 KWH C46 C60 MACRO 1B1 1V8192 MESS 1 RIT MESS IJ8 22 MESS 3 179 MESS 4 TJ 10 MESSAGE CG1 L21 NEW G41 J281 NUM C42 OUTPUT A441 SITEIN IVv8192 TITLE IL17 1018 NO 101 NA B 12 AH 1025 Q A21 NS TH Y Z IB1 HELP ABBREV Y1 AF32 BATCH A824 H930 DATAFILE 191 H343 GLOSSARY I1 P161 GRAPH AH 1 AN 19 HELP A1 H190 INPUT A260 293 MACRO A615 H70 MANUAL A1 H1120 PARAMETERQ 1 W78 RANGES A933 H1133 SEARCH A344 H496 STATS A497 H64Y 23 APPENDIX D DESCRIPTION OF DISKETTES Two disks are included with the SAWUD manual a SAWUD System Disk and a SAWUD Tutorial Disk System Tutorial FILE NAME AUTO 123 DATAFILE
17. a breakdown in the program during the operation of the SAWUD system or the user feels that a mistake has been made striking the CRTL and BREAK keys simultaneously will exit the system The user may recall the file menu by striking ALT and M simultaneouly Each choice implements the following functions SAW UD AUTO123 MHAIN l File access 2 General information 3 End program STATS STATS DIR GIEEARCH A 1 Automatic and manual statistical analysis 2 Save time series data 1 Directory input i F 3 Save searches X DATA 1 Time series records 2 Time series input DATAWILE OTHER TIMES SERIES MILF FILES CUPS FILES Figure l Description of SAWUD GENERAL This choice causes a general explanation to appear on the screen briefly explaining the structure of the system and how each of its functions may be used The user is also referred to the more detailed explanations contained in the various help files HELP This choice will produce a list of available help files that may be used each time the help choice is recalled The user moves the cursor to the desired choice through use of the up and down arrow keys and strikes the return key If a color monitor is used the choices are highlighted in green Once the selection is made the help file will appear on the sereen and may be viewed by moving the cursor with the arrows or page up and down keys
18. approximately 10 years of monthly time series data The construction of the program is such that it may be easily expanded to accommodate more data SAWUD is a Lotus 1 2 3 Release 2 template program and requires an IBM compatible microcomputer with at least 600K memory An 8087 math coprocessor chip or similar device is also recommended but is not absolutely necessary Although the program 15 automated to such a degree that an in depth knowledge of the Lotus 1 2 3 spreadsheet is not required the user should know the basies of using amp spreadsheet The SAWUD system s programming is written completely in Lotus macro language The actual code is included in the far right upper corner of the individual spreadsheet files The macro commands cannot be moved to any other portion of the spreadsheet without major alterations of the program and can be easily damaged by any changes so it is highly recommended that the user not modify this section of the spreadsheets until they are very familiar with the use of macros A section below describes some of the macro programming techniques for users with experience with the Lotus 1 2 3 macro code Organization SAWUD is divided into four major working files and can use several separate data files The four working files are contained on one double density floppy disk while the extended data files may be stored on other disks The four files and the structure of the system are shown in Figure l
19. by the macros Changes to the parameter choices on the second line are done as follows 1 The names of the parameters may be changed by leaving the macro system changing the parameter name in the same place in the data base range named DATA the list of parameters choices range named PARAMCH and in the output table located on the third or fourth page down from HOME All changes must be consistent Be sure to resave the file when done Also be sure to keep the parameter choices unique that is don t repeat names 2 The parameter choices may be expanded or decreased by adding or subtracting the names to the headings and parameter choice list and adjusting the Data Query criterion output and input ranges accord ingly See the users manual for details on this operation Also check all Range Names to find which apply to the changed areas of the spread sheet and change accordingly Whenever changing parameters be sure to be consistent Also Lotus 1 2 3 Version 2 only allows 32 parameters to be used in a data query procedure Do not exceed this limit APPENDIX B BATCH LOADING The SAWUD data system has been adapted as of 12 86 to allow batch loading of update AIM site data as an alternative to using the UPDATE procedure in the DATAFILE mode Batch loading may only be used for the updating of already existing AIM files in the DATAFILE The NEW choice must be used to add new AIM sites to the data base Using the System Data is
20. ch month s data may be seen be striking the TAB twelve times for each of the twelve pages When the year is complete the file is named by the year it contains in the case above the file is named 1986 Since monthly readings are recorded by individuals at the AIM sites all of the month s entries may not arrive in time for the initial batch loading In this case late entries are added below the END line in the same format as the rest of the entries with an additional END 18 placed at the bottom of the late entries list For example if some of January s data does not arrive until after the January batch was loaded the file may look as follows READING ATM CROP ACRES KWH MGAL DATE corn 100 3000 01 23 86 citrus 700 65000 01 13 86 ATMOO2 citrus 245 23444 01 21 86 100 pasture 356 28431 01 11 86 END citrus 233 3456 01 28 86 beets 34 2349 01 22 86 END Once these new entries are added to the January list and the range name 1 is expanded to include them they will be read in the data base when the February batch load is made When the data format program is run the computer looks to the previous month s data to see if there are any late entries before it proceeds with the current entries Thus it is important to include all late entries regardless of how late they are into the previous month s late entry section For example if it is mid June and amp reading from Ma
21. d NEW M61 NAME Q62 NUM 062 LIST IP49 s1P50 NUMLIST 50 NAMELIST IQ49 1IQ50 RANGE 1 19 IQ50 6 Strike the HOME key and resave the entire file This procedure will work for the SAWUD DATAFILE as it was on 1 23 87 If any other range 4f names are accidentally erased they may be replaced by referring to the complete listing of range names and locations for each of the system files in Appendix C Any changes to the programming of the file may require additional alterations DIR SEARCH The DIR file may be reset by the following steps 1 Make sure you are not in the macro system strike QUIT on the menu 2 Go to the section of the spreadsheet named DATA by striking the GOTO key F5 typing DATA and striking return 3 Frase all of the data entries except the two sample data sets on the first two rows of the data base Leave these entirely intact although you may change the included sample data Y Strike the HOME key and resave the entire file This procedure will work for the SAWUD DATAFILE as it was on 1 23 87 Any changes to the programming of the file may require additional alterations The parameters are found in the headings of the data base the list of parameter choices and in the output extract table The line closest to the data the second line of the heading is the important line Changes to the first line require no special instructions since they are solely for the sake of the user and are not used
22. e so extreme caution is needed Although macros can be delicate a user with a knowledge of the macro code can quite easily change the code to fit the needs of an updated system The Lotus 1 2 3 User s Manual 1985 provides amp very good explanation of macro programming and several advanced guides are available e g Riddington and Williams 1985 Le Blond and Cobb 1983 The existing macro commands are documented with green unprotected cells beside the code but the user should review the code very carefully and fully understand what the code does before making any alterations A macro is a stored sequence of keystrokes that may be placed in a reserved section of the spreadsheet although they may be placed any where on the spreadsheet no other work should be done in the macro area Hach macro is identified by the symbol not to be confused with followed by one of the letter keys A Z When the macro is run by stroking the Alt and corresponding letter keys simultaneously the computer automatically types the keystrokes in the macro This saves not only the time of striking several keys repeatedly but allows the expert to write programs defined as a series of keystrokes that can be used by others An example of amp simple macro is the following gnuRAIN This macro represents the command to retrieve and view the named graph RAIN If one was to perform this procedure manually it would be ac complished by stroking the
23. ese parameters along with a short description of each can be found in the help file named PARAMETERS The user will then be instructed to input the infor mation for each of the new sites below the parameter headings Some of the records that include numbers need to be input as labels or text so an apostrophe must be typed at the beginning of each of the records The apostrophe tells the system that the data record in that cell is to be treated as text not a numerical value such as street address or meter serial number The first site s information is typed below each corresponding heading and example data record one at at time When a piece of information is input the user moves the arrow key to the right to enter 10 the next piece Each piece of information must be limited to one cell Cells for which there is no information available are left blank The return key is struck only when all data are entered and the user is finished Striking the return key at any other point may return the user to the main menu but the directory entry mode may be reentered by choosing the directory choice once again Once the new data have been added the return key is struck and the SEARCH menu appears at the top of the screen then the choice MAIN will return the user to the MAIN file Of course the user may remain in the SEARCH file to perform a search or any other routine STATISTICS The statistics file called STATS can be used alone o
24. ine will simply cause the entire directory file to appear on the screen and a light bar will highlight each record that meets the criterion of the search The user simply uses the up and down arrow keys to move to each entry and the left and right keys to move from side to side When the last entry meeting the search requirements is reached amp beep will sound By striking return the main menu will appear and the search is over Unfortunately if no data records meeting the requirements are found a beep will sound and a large menu will appear at the top of the screen At this point the user must strike the letter Q for Quit and restart the main menu by depressing ALT and M simultaneously The user may then try again Extract The EXTRACT routine will actually form a separate table of each data record that meets the parameters in the search Once the EXTRACT choice has been made the search will begin A blank table of all parameters will appear similar to that in the directory entry mode and the list of required data records will shortly appear The user is then free to move about the table and inspect the data table If it is desired to save this table it may be printed using the PRINT choice in the main SEARCH menu or saved using the FILE choice in the same menu When the inspection of the table is complete the user strikes return and the system will ask if the user would like to see any data If the choice is YES the user will
25. initially entered on disk using the Lotus 1 2 3 Release 2 spreadsheet program and the following format An example file is included with the SAWUD system disk set This example file called EXBATCH may be copied for each year of record and renamed as the year to which it corresponds The format of the entry file is as follows READING AIM NO CROP ACRES KWH MGAL DATE ss ee eee eee ee ee ee M S M dll PR ae is mre M Each entry is typed below the appropriate heading Note that the date must be typed in the same manner as is described throughout the SAWUD manual When the bottom of the update list is reached the word END is typed under the last entry in the AIM NO column An example would be READING AIM NO CROP ACRES KWH MGAL DATE AIMOO 1 corn 100 3000 01 23 86 citrus 700 65000 01 13 86 AIMOO2 citrus 245 23444 01 21 86 AIM100 pasture 356 28431 01 11 86 END When the entries are complete the area containing the actual data is given a range name corresponding to the month represented in the case above the number 1 for January Here the range from the entry AIMOO to the blank space below the date 01 11 86 is named 1 Do not include the column headings in the range name The entries for the next month are placed exactly one page to the right Each month s entries are added across until the year is over Thus when all twelve months are entered and the cursor is placed in the HOME cell ea
26. k both the INBATCH file and the initial entry files in order to find the error In most cases the error will either be a TONERE LENN AIM site or an error in the initial entry format 19 APPENDIX C LIST OF EXISTING RANGE NAMES FOR SAWUD FILES AUTO 123 ERRMSG IQ14 HELP A401 H481 HELPS AG1 AN1521 INFO A41 MACRO IO1 MESSAGE CQ1 X21 NO 102 1011 2 DATAFTLE B II83 H II105 AIMOOOA A61 F80 AIMOOOB G61 L8Y BATCH IB1 ERRMSG 19 HELP 11121 1N221 HELPS II201 IN2001 LIST IP49 IP 75 MACRO IO US MESSAGE CG1 K8 NAME FK62 NAMELIST IQ49 1Q75 NEW FG61 NUM FI62 NUMLIST IOJA45 IP75 OLDTEST FC79 PUMPS 1151 RANGE IPY9 19 15 RATE FD66 RATING IK12 SBATCH 1B1 STATS IL41 TEST FC80 TITLE 1022 1026 NO 101 AB T183 H NP 1J12 NV 133 AX Z A55 20 DIR SEARCH 1 A46 A300 10 M46 M300 11 N46 N300 12 P46 P300 13 Q46 0300 14 R46 R300 15 846 98300 16 T46 T300 17 V46 V300 18 W46 W300 19 X46 X300 2 E46 E300 20 Y46 Y300 21 246 2300 22 AA16 AA300 3 F46 F300 Y G46 G300 5 H46 H300 6 I46 1300 T J46 3J300 8 K46 K300 9 L46 L300 ACREMON N746 AIMS A48 A123 BASIN GT46 BOARD G30 1I43 CAP U746 COUNTY F746 CRIT E28 128 CRIT 1 E28 CRIT2 F28 CRIT3 G28 CRITY H28 CRIT5 128 CRITERIONE24 E28 DATA A746 AC1189 DATAAREA W1 AS101 DATABEG A744 ACT4A5 DOUT A48 AC 300 EMIT L746 ERRMSG IR104 GAUGE X746 GPH M74
27. manner throughout the use of the data system will cause a malfunction The user is then asked to enter the owner of the site the second means of identification used by the system Each time this information is entered the return key is struck Several metering systems may be used at the sites with some re quiring conversion coefficients The user will be asked to enter the type of pump system in the prototype the choices are pump in line or chemrun If a meter requiring a conversion factor or rating is chosen the user will be asked to enter this coefficient which must be determined in advance The system will then automatically apply this coefficient to the entered data Once the owner s name is entered a table of headings will appear on the screen and the data are entered just as in the directory entry mode see Data Entry below Once the last piece of information is entered strike return and a message will soon appear instructing the user to strike the return key once again and check the data At this time the user should inspect the new entry by moving about with the arrow keys Regardless of whether any mistakes are spotted strike return when the inspection is complete and the user will be asked whether or not the entry is satisfactory If the answer is yes the main menu will appear and the new data site will have been entered into the main data base If the answer is no the file will be recalled and the user may begin
28. otus 1 2 3 Release 2 Cambridge Massachusetts Riddington R W and M M Williams 1985 The Hidden Power of Lotus 1 2 3 Using Macros Brady Communications Company Inc Bowie Maryland APPENDIX A INSTRUCTIONS ON HOW TO ERASE THE DATAFILE AND SEARCH DATA BASES The time series data on the DATAFILE file of SAWUD and the directory records on the DIR SEARCH file may be completely deleted but with some manipulation If it is desired to wipe out all of the records and begin with amp clean file the following steps must be performed DATAFILE Two sections of the time series input file must be cleared the list of AIM sites and the actual data 1 Make sure you are not in the macro system strike CRTL BREAK 2 Erase all of the data records except the two samples at the begin ning of the data base numbered 1 and 2 The beginning of the data base is found 3 pages down from Home A1 3 Go to the section of the spreadsheet given the Range Name LIST by striking the GOTO key F5 typing in LIST and striking return Erase all of the list except the two entries at the beginning of the list called Sample 4 Delete all AIM site range names e g AIMO34 by using the Range Name Delete commands DO NOT ERASE THE TWO RANGE NAMES CALLED AIMOOOA AND AIMOOOB Also delete the following range names NEW NAME NUM LIST NUMLIST NAMELIST RANGE 5 Reassign these range names as follows by using the Range Name Create comman
29. r in con junction with the SEARCH file The program will perform elementary statistics on up to 20 AIM sites meters at a time and create summary information and graphs These data can then be saved in output files for use with more sophisticated statistical packages The opening menu appears as follows METERSTAT SITEASSESS RESTAT MAIN SEARCH DATA HELP QUIT METERSTAT The choice METERSTAT allows for statistics on individual AIM site meters or AIM sites having only one meter METERSTAT has two sub menus as described below Manual The choice MANUAL allows AIM site numbers to be entered directly to the program for statistical analysis The system will prompt the user to enter the AIM site numbers to be analyzed and retrieve the data one at a time When all the desired choices have been made the letter N is entered at the prompt and the statistical analysis will begin Autostat The choice AUTOSTAT will perform the same function as MANUAL but the AIM sites saved from a previous search will be entered automatically to the system In both cases the running time can take a few minutes but using the system on a hard drive disk system can reduce the running time significantly In both cases once the data have been entered the system will recall the requested data files and the word WAIT will appear in the upper right hand corner of the screen Do not attempt to enter data or use the keyboard when the WAIT signal is displayed
30. rch arrives make sure to include this in the October late entry section If a reading arrives after more current readings the data must be input to the DATAFILE manually and all corrections to the more current data must be made Please note that in the case of October the computer will be unable to include late entries from September since they are located in the previous year s file overcome this problem include September s entry of the previous year as the first entry of each new year and use the name range 0 Thus there will be 13 named ranges in each year s file How the System Works The file that is activated when the format conversion is performed is called CONVERT The program in this file retrieves the monthly and late entry data from the initial entry files converts the data into the format in which it is stored in the data base DATAFILE and exports the data into a temporary file called INBATCH When the BATCH choice is made in the DATAFILE menu and the user confirms that the conversion has been made DATAFILE will import the INBATCH file onto a reserved section of the spreadsheet and proceed to add each line to its corresponding AIM data base DATAFILE will also perform all calculations on rates and water use as does the manual UPDATE mode When finished the DATAFILE menu will reappear and the user may use the LOOK mode to check the results In the event of an error the user may exit the SAWUD system and chec
31. search for data by any of the site parameters When recalled a menu will then appear as follows SEARCH DIRECTORY PRINT FILE MAIN HELP QUIT SEARCH The choice SEARCH will prompt the system to ask for the number of parameters that the user would like to use in the search Up to five parameters may be used Once this has been entered a list of parameter choices will appear on the screen along with the words Parameter One Parameter Two etc The user will be instructed to choose the first search parameter by moving the cursor to the desired choice and striking return The choice will appear under Parameter One and the user will be prompted to type in the desired specific criterion e g for the parameter choice CROP the criterion choice may be corn citrus etc When the choice is entered the user strikes the return key and amp second choice may be made from the list of parameters Several special choices may be made as follows 1 If no second parameter choice is desired the user simply strikes the return key twice when the prompt for the second choice appears This enters two blanks into the system and cancels the second search parameter Be sure the cursor is in the blank space when this is done 2 When the criterion deals with numbers such as evapora tion precipitation etc the user may choose to use all of the data records with a corresponding value greater than or less than a certain value For example if the user wi
32. shes to view all of the data records with a mean annual precipitation of greater than 75 inches the fol lowing is typed in for the criterion choice P gt 75 or for mean annual evaporation less than or equal to 72 inches E lt 72 Be sure that the sign is typed before each of these entries or the search will not work A list of the abbreviations used for these types of searches appears in the Help file and is named ABBREV If it is desired to search between a range it is done in the following manner P gt 72 AND P lt 78 This will search for all records with mean annual precipitation between 72 and 78 inches Once again the command must be typed in the above format for the search to work 3 Search criteria may be abbreviated by typing any number of letters followed by a as follows TX This criterion when typed under the parameter CITY STATE ZIP for example will search for all cities beginning with the letter T such as Tampa Florida etc If the is placed at the beginning of the phrase the routine will search for all records ending with the entered letters or numbers This routine may be used to search for all records with a particular zip code beginning coordinates of latitude and longitude or to save time in typing long town names When the return key is struck after the last parameter choice the user will be prompted with a menu to choose between the FIND or the EXTRACT routines Find The FIND rout
33. ta set storage statistical analyses and output reports The data base will be designed to hold approximately 14 000 records which represent all agricultural water uses permitted by the consumptive use permit CUP program within the SWFWMD These files will represent both the set of total permits and the subset of all farm sites included under the agricultural irrigation monitoring AIM activity The larger set will contain approximately twelve data fields while the smaller will contain those plus another six to twelve fields Statistical character izations of the data sets shall include mean standard deviation stan dard error and variance of all fields The output from this study is to be in the form of Lotus 1 2 3 files that describe the procedures as well as a report summarizing this work This work was done during the period from June 21 1986 to September 30 1986 The SAWUD system consists of this manual amp ready to use System disk and an example Tutorial disk This report and diskettes represent the January 23 1987 submittal of the final report Description of the Data Base The SWFWMD Agricultural Water Use Data Base SAWUD was designed to allow the user to easily access water use records of agricultural sites throughout the Southwest Florida Water Management District and to be able to use these records for various statistical analyses The system was designed to accommodate over 600 AIM program sites with each site including
34. ter readings to get site water use Multiple meters for the same site are labeled with a letter at the end such as AIMOO2a and AIMOO2b When the choice YES is made the user will be asked to 12 enter the first AIM site number Up to four meters may be combined in this manner Enter the meter labeled a first The computer will then pause to retrieve this record and ask for a meter number again Now enter the b meter etc When all meters are entered enter N and the user will be asked for the site algorithm If you have entered meters a and b and the site water use is just the sum of the two meters enter a b If you have entered three meters and the site water use is a minus b then enter ta b Any combination of meters may be combined in this way Once the site algorithm has been added the computer will pause to compute the meter combination and the result will appear on the screen The user will then be asked again whether or not meters must be combined If YES the above process is repeated If NO the user is asked if any other site meters are needed This is used to retrieve meters that do not need to be combined If YES the user is asked to enter the AIM site number and the data will be retrieved This will continue until the user chooses NO At this point the entire data output will be placed into a separate file named DATA which may be used for further statistical analysis The user will be informed when the
Download Pdf Manuals
Related Search