Open Access User Group Meeting - 13 August 96 A well attended meeting - extra chairs had to be brought in! OA News Lindy Kidman advised the meeting that she had spoken to Marc Sapper, the new Australian Distributor, about Open Access and Windowbase. Open Access 4 currently retails for $750. Open Access 4 Plus is still due out early in 1997. Further releases of Windowbase are unlikely in the near future but the SPI Internet product WorldDoc is proving popular. This is an easy-to-use editor for HyperText Markup Language (HTML) - the "standard" page layout language of the World Wide Web. More Network Tips In the last newsletter there were several tips for overcoming printing problems within networks. A further suggestion which was raised at the meeting was to experiment with the "Fast printing direct to port" setting under the Windows Control Panel (Printers/Connect). Spreadsheet Query Language Michael Paine explained that he had unsuccessfully tried to obtain some statistics about Australian snow falls prior to a skiing trip to Perisher Valley in July. He therefore came back from the trip with a snow depth chart going back to 1964. He entered the data into an Open Access Spreadsheet and then wanted to count the number of years when the snow depth in a certain fortnight exceeded a given value (for example, the number of years when the depth in the second half of July exceeded 1 metre). The Open Access Spreadsheet functions include some "query" functions. The appropriate function was QCNT(), which counts the number of cells which match a query. This is normally part of the Data Manager system found on the advanced spreadsheet menu )Terry later demonstrated some the features of Data Manager). Michael found that the query functions could be used without invoking Data Manager. The data is required to be in a column with a nominal field name at the top (e.g. AUG1 for the first fortnight in August). Now find a blank area of the spreadsheet, such as at the bottom of the data. Enter the field name (e.g. AUG1) then directly below it enter an expression of the form: ! >=100. In this example the "query" retrieves all of the records in the AUG1 column with a value greater then or equal to 100 (centimetres). To invoke the query go to another blank cell and enter the expression +QCNT(range1,0,range2) Where range1 is the data range, including the cell containing the field name and range2 is the two cells containing the query. An example is +QCNT(B4:B27,0,B30:B31). In this way Michael calculated the probability of the snow depth exceeding given values during any fortnight of the snow season. He graphed the results, which can be viewed at his Web site http://gco.apana.org.au/~mpaine. In brief, the best chance of good snow is late August, when there is an 85% chance of snow over 1 metre and 28% chance of snow over 2 metres. By the crowds at the snowfields it seems that a lot of people already knew this! Database Print Form Terry gave a demonstration of using levelbreaks in OA Database print forms. There are also some tips about this, and much more, in Michael Paine's booklet OA_BOOK.TXT which can be downloaded from his Web site as OA_PROG.ZIP. Next Terry explained the use of the Miscellaneous setting "Group header includes first record". If your print form uses the group header section then you can copy some, or all, of the entries in the Record section into the Group Header section. This allows you to print the record information on the same line as the group header information, and suppresses printing the same data again in the record section. The effect is GROUP1_DATA RECORD1 OF GROUP1 RECORD2 OF GROUP1 RECORD3 OF GROUP1 GROUP2_DATA RECORD1 OF GROUP2 RECORD2 OF GROUP2 etc Duplicate database records when trying to create a unique key Sometimes when you try to modify a database to create a unique key field you receive the message "duplicate value in unique key". This means you have more than one record with the same value in that field and the indexing process is unable to proceed. Short of manually stepping through the database trying to locate the problem you can create a simple Programmer program to do the job. Lindy demonstrated how it works: VIEW data = FROM vehicle \\ ! Define file ORDER vkey ! Sort it for checking USE data FIND data TOP message = '' temp = data.vkey FIND data NEXT b = TRUE PUT 'DO you want to delete duplicated records?(yes, no)' GET b WHILE RECNO(data)>0 IF temp = data.vkey PUT data.vkey IF b DELETE data PUT ' duplicate deleted!' END IF PUT DO NEWLINE ELSE temp = data.vkey END IF FIND data NEXT END WHILE PUT 'Checking complete, press ' GET b You will need to substitute your database and field names in the above example. Exporting database data in comma separated variable format Wal Shand asked how data which exceeded the 255 line length limit of Open Access could be exported in CSV format. The usual methos of creating CSV files from database records is to create an OA print form of the desired layout and to "print" the records to "FILE". However, if the combined data exceeds 255 characters then OA will automatically insert a line feed in the text file and this will mess up the importation of the CSV data into the other software package. After some brainstorming we found that we could trick OA by creating a new "printer" definition based on the "FILE" printer. Go to Utilities/Configuration/Printer_definitions. Highlight FILE and press to make a copy. Change the printer name from FILE to, say FILE_CSV. Now go down to the Newline Sequence (about the 64th impossible question). Enter the characters (32). This has the effect of suppressing the carriage return, newline sequence which is the default. Instead a space is "printed". That gets over the problem of OA inserting carriage returns when you don't want them but how do you then force carriage returns at the end of each line. This requires some further trickery: use the translatable characters feature to force an unused character, such as the cap (^) to output the carriage return, newline sequence (13)(10). Now in your Database print form use the cap after the last field in your form. You can even layout the print form with one field under the other since the normal carriage returns are ignored. The print form might look like this: field1+',' field2+',' field3+'^' After the break Keith gave a talk about Microsoft Access. \