Simtel MSDOS 1992 September

home *** CD-ROM | disk | FTP | other *** search

/ Simtel MSDOS 1992 September / Simtel20_Sept92.cdr / msdos / dbase / dq13.arc / DQ.DOC next >

Wrap

Text File | 1989-05-09 | 53KB | 1,070 lines

DQ Quick Database Utility written by Richard Dower Copyright (c) 1989 All rights reserved. What is DQ ??? DQ is a utility for getting information about dBASE files. All of DQ's activity is read-only and therefore will not alter your files in any way. (This may change if there is enough interest.) It allows you to display the structure, browse, count, locate, sum, average and goto specific records. It also allows you to find what each index file in your directory is indexed on and whether that index is a possible index for the current database file. It is primarily for DP professionals who need quick information about dBASE files. DQ gives them information about files and indexes that is usually only available by reading the documentation for a specific dBASE application, by talking to the original programmer, or using a debugger. One of the best features is the ability to open two files at once and view them both on a split screen. You can then relate the two files through a common character field. Then, when you move in the first file, you are moved in the second file to the record whose related field matches the first file's. DQ is small enough that you can usually run it from within your word processor or from within dBASE, Foxbase, or DBXL. Its great for finding how indexes are indexed, browsing two files simultaneously, or doing quick sums or locates. I have found DQ invaluable when writing an application which relates two files. There is no easy way to view the fields from two separate databases within dBASE. DQ not only allows you to view records from two different databases simultaneously, but it allows you to relate any two matching character fields. You can quickly browse two files which both have a social security field in common, for instance. Or, you can even open the same file in both work areas and by relating a field to itself, you can view 40 fields at one time for the same record! Simply select a different set of fields to display in either work area. Having trouble with an accounting application which uses an ACCOUNTS file and a TRANSACTION file related by an account number? With DQ you can easily browse through the data by relating the TRANSACTION file to the ACCOUNTS file through the account number. Then, when you look at a transaction, you will see all the information about the account also. You can also LOcate for a specific account number or SUM withdrawals, deposits, or balances in either database. The SUM command will also give you maximum, minimum, count and average. Commands Supported so far: CHECK check for records with non-printable characters COUNT counts records matching a condition DIRECTORY shows all databases in current index and allows opening of a database by light bar selection DIRECTORY INDEX shows all the indexes in the current directory, shows how it is indexed, and whether it is a possible index for the currently opened database. FIELDS show the fields and their definitions GOTO goto a specific record number HELP gives help about any command LOCATE locate a record matching a condition NEXT next locate QUIT quit RELATE relates two databases by a common field SKIP skip n records forward or back STATISTICS display structure SUM sums a numeric field on a condition Shows number of records matching condition, sum, average, maximum, and minimum. USE open a database Functions Supported so far: VAL(character field) used in the SUM command only LEFT(field,length) used in COUNT and LOCATE RIGHT(field,length) used in COUNT and LOCATE SUBSTR(field,start,length) used in COUNT and LOCATE See the section after the command summaries for a fuller explanation of the functions. All commands in DQ may be truncated to two characters and there is a 10 command buffer that allows you to edit and re-execute previous commands. The current record is always displayed, so there is no need for the DISPLAY command. If there is more than a screen full of fields, F2 will toggle between the multiple pages of fields. The current record number, whether the current record is deleted, total numer of records, and the date the file was last updated are always displayed on the screen. DQ can also give number of fields, field names, field types, file header size and record size. You have two work areas to open files within. Pressing F3 will move to the other work area. You may split the screen so that both work areas are showing at the same time by pressing F4. Pressing F4 again will unsplit the screen. Each work area works exactly as a stand alone version of DQ. The bottom of the screen will let you know which work area has been selected and all windows and messages will appear in the active window if the screen is split. Online Help is available by pressing F1 or entering HELP on the command line. HELP followed by a command will give help on that specific command. Version 1.3 1. Fixed some a positioning bug after a failed LOCATE. 2. Fixed a bug in numeric field compares in conditional statements. Example: sum salary salary > 0 I mistakenly compiled version 1.2 without a certain header file and what had always worked for me didn't work on the release version!! 3. Added the substr() function. Version 1.2 1. Now has two work areas. You may have two different databases open at the same time. 2. Allows splitting the screen to view both open work areas at the same time. 3. You may relate the database in WORK AREA 1 with the one in WORK AREA 2 on a common field. Then, whenever you move within the database in area 1, the record is found in area 2 that matches the related field. You may relate any character field from area 1 with one in area 2 as long as they are the same length. No need to have an index active. This is great for browsing data that is normally accessed though a dBASE program with SET RELATION command active. 4. Supports the VAL() function for summing numeric data in character fields. 5. Supports the LEFT() and RIGHT() functions for COUNTing and LOCATEing partial matches within character fields. Shareware is not free software. If you use and like the program, then the I ask that you register (purchase) the program for an amount much smaller than an equivalent commercial program ($10.00). By registering you will receive notice of available upgrades. Keep in mind that the purchase of a program from a public domain distributor or payment to an online service (such as GENIE) does NOT constitute registration of the program since I do NOT receive any money from that payment. Shareware is NOT public domain software, and continued use of a program carries with it the OBLIGATION to purchase the program. The free distribution method is intended only as a means of obtaining a copy to evaluate before purchasing. DQ is a shareware program. If you use it, please REGISTER. You will be placed on a mailing list for any future upgrade notices, and will be registered as a user of this product. DQ is very useful as it stands but will be continually enhanced. I will let users determine the functions that they would most like to have added and build from there. Future plans include expanded functions support, possibly adding more work areas, relating on partial string matches, and printed reports. Registration is currently $10.00. As features are added, this may be increased. Get in on the ground floor. For those interested in source code or libraries please contact me for prices and availability. DQ is written in Microsoft C with Essential's ScreenStar for the screen interface. Send registrations to: Richard Dower 13943 West 113th Street Lenexa, Ks. 66215 Phone : (913) 339-9348 DQ is provided without warranty of any kind, either expressed or implied. The user of DQ agrees to hold the author of this material harmless for any direct or consequential damages resulting from its use. dBASE is a trademark of Ashton Tate. DBXL is a trademark of WordTech. WordStar is a trademark of MicroPro International. FoxBase is a trademark of Fox Software. Why use DQ? 1. DQ can give you information the dBASE doesn't. A. DIR shows all the files in the current directory, how many records each has, the number of fields, when last updated, and the record size. B. The current record is always displayed. The command line prompt shows the active database. The current record number, total number of records, whether the record is deleted, and the date last updated are always displayed. In the example below, TEST is the active database. Since there are more than 22 fields in TEST, you can use F2 to move through the other fields. NOTE: the field STRING ends with two greater than signs, >>. This means the field is to large to display, i.e. more than 66 characters. Sample screen: Record Number: 1 of 6 DELETED Last Update: 04/06/89 NUMBER : 100.30: INTEGER : 43: LOGICAL :T: DATE :09/09/88: STRING :This is the maximum length allowed for a field. It is 254 cha>> SMALL :1: BIG :1111111111111.11100: F8 : : F9 : : F10 : : F11 : : F12 : : F13 : : F14 : : F15 : : F16 : : F17 : : F18 : : F19 : : F20 :1: F21 : : F22 : : F2 for more fields... TEST> C. DIR INDEX shows how each index file in the directory is indexed. It also tells you if the selected index is a possible index for the active database file. If not, it tells you which field doesn't match. It also supports FoxBase indexes. D. CHECK will check records in the selected database for characters not normal to dBASE files. It will stop on the first record that contains any non-printable character. The existence of one of these characters in a record usually means that that record has been corrupted. It checks for graphics characters and characters below the space on an ascii character chart. Some of the more familiar ones are "happy faces" and ESCAPE. 2. Speed and size. DQ is a small program and can thus be loaded quickly and in a small amount of memory. It can be carried around on a utility disk for browsing files, simple locates and counts, summing selected fields to check program output, and determining indexes. You don't need multiple disks of dBASE, FoxBase, or DBXL to troubleshoot dBASE files. My own benchmarks for COUNT and LOCATE show speeds faster than DBXL and dBASE and comparable to FoxBase. Since SUM gives the count, average, maximum and minimum, there is no comparable dBASE equivelant for comparison. You would have to write a program or do multiple commands to get the same information. Some scenerios where DQ is useful. Example: You are in your text editor or word processor. To complete your program, or documentation, you need to know the structure of a certain database and how its associated indexes are indexed. You have two choices : 1. Since DBXL, dBASE, and FoxBase are usually too large to load from within another program, and programs like Brief and WordStar are too large to load from within DBXL, dBASE, or FoxBase, you need to do the following. A. Remember where you are and quit your program. B. Load DBXL, dBASE, or FoxBase. C. USE the database of interest with all the indexes. D. Display the structure of the database. E. Display the status. F. Quit your database manager. G. Re-load your text editor. H. Go back to where you left off. Some of the major time in the above operation is spent loading and reloading large applications. 2. A. Escape to DOS without exiting your text editor or word processor. B. Run DQ. C. USE the database D. The FIELDS command will show the fields. E. DIR INDEX will tell you how all the index files are indexed. F. Return to your original program. Example: You have received a dBASE application with no documentation that was developed somewhere in your organization. You are expected to support the program. With the application are eight databases and fifteen indexes. By using DQ, you can quickly see how all the indexes are indexed, and by USING each of the databases in turn, you can determine which indexes are possible matches with which databases. Even having the source code for an application will not always get you that information. Or, if it does, it takes a lot of digging. Sample non-split screen. Notice: The database name is part of the prompt. (TEST2) The current record number and total number of records is shown. The last update of the database is shown at the upper right. The active work area is shown at the lower right. (AREA 2) The SUM command output shows that only 7 of the 20 possible records matched the seach condition "substr(string,2,3) owe". ┌───────────────────────────────────────────────────────────────────────────┐ │ Number: 1 of 20 Last Update: 04/16/89 │ │NUMBER : 100.30: │ │INTEGER : 43: │ │LOGICAL :T: │ │DATE :09/09/88: │ │STRING :testi >>│ │SMALL :1: │ │BIG :1111111111111.11100: │ │KEY2 :EEE: │ │F9┌────────────── SUM ───────────────┐ │ │F1│ │ │ │F1│ Count: 7 │ │ │F1│ Sum: 22222222222222.22000 │ │ │F1│ Average: 3174603174603.17500 │ │ │F1│ Maximum: 4444444444444.44400 │ │ │F1│ Minimum: 2222222222222.22200 │ │ │F1│ │ │ │F1│ DONE-Hit a Key │ │ │F1└──────────────────────────────────┘ │ │F19 : : │ │F20 : : │ │ F2 for more fields... WORK AREA 2 │ └───────────────────────────────────────────────────────────────────────────┘ TEST2>sum big for substr(string,2,3) = owe Sample split screens with two different databases. The bottom line of the window tells which area is active. The two screens that follow show how you can SELECT a different area (F3) quickly and the operation that is performed on that database gives its results in the area selected. In the following examples, the SUM command was performed first in area 1, then in area 2. (The screens have been trimmed slightly to fit on a printed page.) Notice: The database names are at the top of each work area window. The current record number and total number of records is shown for both work areas. The prompt and the SELECTED message shows the selected database. ┌─────────────────\PD\TRANS────────────┐┌─────────────────\PD\PD─────────────┐ │ Number: 1 of 33 ││ Number: 1 of 100 │ │ACT_NO :5 : ││ACT_NO : 5 : │ │DEPOSIT : 345.00: ││MAILER :A. C. DELCO-SP >>│ │WITHDRAWAL: 222.00: ││ADDRESS : >>│ │DATE :02/12/87: ││CITY : : │ │ ││STATE : : │ │ ││ZIP : : │ │ ││PHONE : : │ │ ││BALANCE : -678.20: │ │ ┌─────────── SUM ───────────┐ ││REMARKS : : │ │ │ │ ││PREV_DATE :02/11/87: │ │ │ Count: 27 │ ││ │ │ │ Sum: 3476.34 │ ││ │ │ │ Average: 128.75 │ ││ │ │ │ Maximum: 543.00 │ ││ │ │ │ Minimum: 2.00 │ ││ │ │ │ │ ││ │ │ │ DONE-Hit a Key │ ││ │ │ └───────────────────────────┘ ││ │ │ ││ │ │ ││ │ │ SELECTED ││ │ └──────────────────────────────────────┘└────────────────────────────────────┘ \PD\TRANS>sum deposit deposit > 0 ┌─────────────────\PD\TRANS─────────┐┌─────────────────\PD\PD───────────────┐ │ Number: 1 of 33 ││ Number: 1 of 100 │ │ACT_NO :5 : ││ACT_NO : 5 : │ │DEPOSIT : 345.00: ││MAILER :A. C. DELCO-SP >>│ │WITHDRAWAL: 222.00: ││ADDRESS : >>│ │DATE :02/12/87: ││CITY : : │ │ ││STATE : : │ │ ││ZIP : : │ │ ││PHONE : : │ │ ││BALANCE : -678.20: │ │ ││RE┌─────────── SUM ───────────┐: │ │ ││PR│ │ │ │ ││ │ Count: 6 │ │ │ ││ │ Sum: -3924.98 │ │ │ ││ │ Average: -654.16 │ │ │ ││ │ Maximum: 0.00 │ │ │ ││ │ Minimum: -3228.67 │ │ │ ││ │ │ │ │ ││ │ DONE-Hit a Key │ │ │ ││ └───────────────────────────┘ │ │ ││ │ │ ││ │ │ ││ SELECTED │ └───────────────────────────────────┘└──────────────────────────────────────┘ \PD\PD>sum balance balance <= 0 Limitations: Please let me know which of the following are important to you as a user. I suggest you place DQ.EXE somewhere in your path so you can call it from any directory. 1. You may not change your default directory while in DQ. 2. AND and OR have not yet been added. You can : COUNT FOR STATE = KANSAS or CO NOT STATE KANSAS or CO NOT NAME = "Richard Dower" or CO salary < 50000 LOCATE FOR STATE = KANSAS or LO NOT FEMALE ( FEMALE is a Logical field) or LOCATE FOR NOT MALE or lo tax >= 1.50 SUM tax tax >= 1000 or su salary female You can't: COUNT FOR STATE = KANSAS AND FEMALE or LOCATE FOR NAME = "Richard Dower" AND STATE KANSAS or SUM SALARY FOR TITLE = "MANAGER" OR TITLE = "SUPERVISOR" 3. The command line will not wrap. The limit for one command is the end of the command line. If you open a file that is not in the current directory and it has an extremely long path name, you will have a smaller available command line. Since all the commands can be abbreviated to two characters and since .and. and .or. have not been implemented, this should not be a limiting factor. 4. No printout of the information beyond screen dumps is available. 5. Fields longer than 66 characters are only partially displayed. This has no effect on the files I normally use but others may need to display a full 254 characters. Let me know. To move between records without using LOCATE, SKIP or GOTO use the following cursor keys : PgUp = up one record Ctrl + Home = Top of File PgDn = down one record Ctrl + End = Bottom of File You may move back through up to 10 previously entered commands by using the up and down cursor keys. Editing on the command line: NOTE: Ins does not toggle between insert and overwrite. It inserts one character under the cursor. To insert a word, press Ins multiple times first. Left Arrow - left one character. Right Arrow - right one character. Delete - delete character under cursor. BackSpace - delete character to the left of the cursor. Home - start of a field. End - end of a field. Ins - insert one space under the cursor and move existing text to the right. Control + Right arrow - next word Control + Left arrow - previous word Escape - clears a field and moves cursor to the beginning of the field. CHECK Checks the current database for bad records. A bad record is any record containing ascii characters below space and above the ~ character. That is, below 32 and above 126 on an ascii character chart. If any of these characters were in a record, it would mean that that record had been corrupted. CHECK stops at the first record that is bad. To search for more bad records, run &2CHECK again. COUNT Syntax: COUNT [FOR] [NOT] <field> [=, >, >=, <, <=] value Count occurances, or non-occurances, of value in field. Counting starts from and includes the current record. Values containing spaces should be surrounded by double quotes. Examples: CO NOT CITY "OVERLAND PARK" CO ST KS COUNT FOR NOT ZIPCODE = 66212 COUNT ZIPCODE = "66212" count name "Richard Dower" CO SALARY >= 50000 co for taxes < 100.50 co left(depart,3) MIS co right(zip,3) 215 co substr(address,5,6) "OAK ST" Note: the >, <, >=, and <= are for numeric fields only. Sample screen illustrating the COUNT command. The active database is CFS64111 (note the prompt on the bottom line). The COUNTed field is LOCAL and is a logical field. Record Number: 1 of 1602 LAST :DOWER : FIRST :RICHARD : NEW_PRIM :13943 WEST 113TH STREET : OLD_PRIM :7521 WEST 96TH TERRACE : CODE :DOW521: CITY :OVERLAND PARK: STATE :KS: ZIP :66212: PLUS4 :2317: USER :31: ┌────── COUNT ───────┐ FOREIGN :F: │ │ DNF :O: │ Count = 1330 │ LOCAL :T: │ │ INDIV :F: │ DONE Press a Key │ TEMP :F: │ │ MILITARY :F: └────────────────────┘ FORTH :F: E_DATE :03/01/89: T_DATE : / / : UNIQUE :1A : CFS64111>co local DIRECTORY In the example below, the light bar was positioned over the file ADJ0. To use the file, simply press RETURN when the light bar is over the desired file name. Optionally, you could have typed USE ADJ0 on the command line. When a database is opened, its name becomes part of the prompt on the command line so you always know the active database. Light Bar Movement Right Arrow - right one file (wraps to next line) Left Arrow - left one file (wraps to previous line) Up Arrow - up one row (wraps to bottom line) Down Arrow - down one row (wraps to top line) PgUp - previous page (if any) PgDn - next page (if any) NOTE: in the lower right hand corner of the screen it shows that the current display is 1 of 3. There are two more pages of database files (use the PgUp and PgDn keys to access). Sample screen from the DIR command: ╔════════════════════════════ DATABASE FILES ════════════════════════════╗elp ║ ADJ0 ADJ1 ADJ11212 ADJ2 ADJ3 ADJ4 ADJ5 ║ ║ ADJ6 ADJ66212 ADJ7 ADJ8 ADJA ADJB ADJC ║ ║ ADJD ADJI ADJJ ADJK ADJL ADJM ADJN ║ ║ ADJO ADJP ADJR ADJS ADJT ADJU ADJV ║ ║ ADJW ADJX ADJY ADJZ BIGFIELD CFS CFS66102 ║ ║ CFSP CFSPLUS CFSTEST CFS_LONG FIV0 FIV1 FIV11212 ║ ║ FIV2 FIV3 FIV4 FIV5 FIV6 FIV66212 FIV7 ║ ║ FIV8 FIVA FIVB FIVC FIVD FIVI FIVJ ║ ║ FIVK FIVL FIVM FIVN FIVO FIVP FIVR ║ ║ FIVS FIVT FIVU FIVV FIVW FIVX FIVY ║ ║ FIVZ FOU0 FOU1 FOU11212 FOU2 FOU3 FOU4 ║ ║ FOU5 FOU6 FOU66212 FOU7 FOU8 FOUA FOUB ║ ║ FOUC FOUD FOUI FOUJ FOUK FOUL FOUM ║ ║ FOUN FOUO FOUP FOUR FOUS FOUT FOUU ║ ║ FOUV FOUW FOUX FOUY FOUZ MORE ONE0 ║ ║ ONE1 ONE11212 ONE2 ONE3 ONE4 ONE5 ONE6 ║ ║ ONE66212 ONE7 ONE8 ONEA ONEB ONEC ONED ║ ║ ONEI ONEJ ONEK ONEL ONEM ONEN ONEO ║ ║────────────────────────────────────────────────────────────────────────║ ║ Records : 9 ║ ║ Last Update : 11/09/86 ║ ║ Fields : 12 ║ ║ Record Size : 52 Page:1 of 3 ║ ╚════════════════════════════════════════════════════════════════════════╝ DIRECTORY INDEX In the example below, CFSTEST.DBF was the active database. I built some absurdly large indexes just to quickly demonstrate the output of DQ. The index CFS and CFSPLUS are similar except CFSPLUS is indexed on a file with a field called FAST, while CFS is indexed on a file which doesn't contain that particular field. At first glance it appears as if the indexes would both work. DQ finds the offending field even though it is embedded in a substr() function. NOTE: the asterisk before an index file name marks that index as a FoxBase index, i.e. it has the extension IDX instead of the NDX extension of dBASE. Partial sample screen from the DIR INDEX command showing an index that doesn't match the active database: CFSTEST is the active database and the light bar was positioned over the index file CFSPLUS. Notice how the index is displayed without splitting any of the field names and that the offending field name was found even though it was within a substr() function. If by any chance you have an index that approaches 200 characters, the bottom line may end with a question mark. This signifies that there is a small amount of the index remaining that wouldn't fit in the alloted display space. It would also probably be the slowest dBASE application ever written. ╔═════════════════════════════ INDEX FILES ══════════════════════════════╗ ║ ADJ0 CFS CFSPLUS FIV0 FOU0 *LAST NAME ║ ║ ONE0 ROUTE SEV0 SIX0 *TEST THR0 TWO0 ║ ║*USER ║ ║────────────────────────────────────────────────────────────────────────║ ║ ONE+TWO+THREE+LEFT(FOUR,1)+RIGHT(FIVE,1)+SIX+SEVEN+EIGHT ║ ║ +SUBSTR(FIRST,1,2)+NEW_PRIM+LEFT(OLD_PRIM,2)+SUBSTR(FAST,2,10) ║ ║ +EIGHTEEN ║ ║ Not a CFSTEST index. Field FAST is not in CFSTEST Page:1 of 1 ║ ╚════════════════════════════════════════════════════════════════════════╝ Partial sample screen from the DIR INDEX command showing an index that does match the active database: CFSTEST is the active database and the light bar was positioned over the index file CFS. ╔═════════════════════════════ INDEX FILES ══════════════════════════════╗ ║ ADJ0 CFS CFSPLUS FIV0 FOU0 *LAST NAME ║ ║ ONE0 ROUTE SEV0 SIX0 *TEST THR0 TWO0 ║ ║*USER ║ ║────────────────────────────────────────────────────────────────────────║ ║ ONE+TWO+THREE+SUBSTR(FOUR,2,3)+RIGHT(SIX,1)+SEVEN+EIGHT+NINE+TEN ║ ║ +ELEVEN+TWELVE+THIRTEEN+FOURTEEN+FIFTEEN+SIXTEEN+SEVENTEEN+EIGHTEEN ║ ║ ║ ║ Possible index for CFSTEST Page:1 of 1 ║ ╚════════════════════════════════════════════════════════════════════════╝ FIELDS Sample screen for the FIELDS command. All the fields are displayed with their type, size, and decimal places. If there are more than 66 fields, when you press a key the remaining fields will be displayed. Field types are : C = Character N = Numeric L = Logical ╔══════════════════════════════════ FIELDS ══════════════════════════════════╗ ║ Field T Size D Field T Size D Field T Size D ║ ║ 1 LAST C 20 0 23 THREE C 2 0 45 T25 C 1 0 ║ ║ 2 FIRST C 13 0 24 FOUR C 3 0 46 F46 C 1 0 ║ ║ 3 NEW_PRIM C 32 0 25 FIVE C 2 0 47 F47 C 1 0 ║ ║ 4 OLD_PRIM C 26 0 26 SIX C 2 0 48 F48 N 1 0 ║ ║ 5 CODE C 6 0 27 SEVEN C 2 0 49 F49 L 1 0 ║ ║ 6 CITY C 13 0 28 EIGHT C 1 0 50 F50 N 1 0 ║ ║ 7 STATE C 2 0 29 NINE C 1 0 51 F51 N 1 0 ║ ║ 8 ZIP C 5 0 30 TEN C 1 0 52 F52 N 3 1 ║ ║ 9 PLUS4 C 4 0 31 ELEVEN C 1 0 ║ ║ 10 USER C 2 0 32 TWELVE C 1 0 ║ ║ 11 FOREIGN L 1 0 33 THIRTEEN C 1 0 ║ ║ 12 DNF C 1 0 34 FOURTEEN C 1 0 ║ ║ 13 LOCAL L 1 0 35 FIFTEEN C 1 0 ║ ║ 14 INDIV L 1 0 36 SIXTEEN C 1 0 ║ ║ 15 TEMP L 1 0 37 SEVENTEEN C 1 0 ║ ║ 16 MILITARY L 1 0 38 EIGHTEEN C 1 0 ║ ║ 17 FORTH L 1 0 39 NINETEEN C 1 0 ║ ║ 18 E_DATE D 8 0 40 TWENTY C 1 0 ║ ║ 19 T_DATE D 8 0 41 TWENTYONE C 1 0 ║ ║ 20 UNIQUE C 3 0 42 TWENTYTWO C 1 0 ║ ║ 21 ONE C 2 0 43 TWENEYTHRE C 1 0 ║ ║ 22 TWO C 2 0 44 T24 C 1 0 ║ ╚═══════════════════════════ Press a Key ════════════════════════════════════╝ GOTO Syntax: GOTO <record number> Go to record number and display the record. Examples: To go to the top record, GO 1. To go to the last record, GO <number of records>. HELP Syntax: HELP [command] If you enter HELP alone, you will be given a light bar menu with all the commands. Simply place the light bar over any command and press RETURN to get the full command explanation, usually with examples. You can go directly to the help screen for a command by simply entering HELP followed by the command, or its abbreviation. Examples: HELP SUM or he co Pressing F1 while at the command line prompt will give you an overview help screen. Pressing F1 while viewing a directory will give you help about selecting files from a directory. LOCATE Syntax: LOCATE [FOR] [NOT] <field> [=, >, < , >=, <=] value Locate next occurance, or non-occarance of value in field. Values containing spaces should be surrounded by quotes. Examples: LO NOT CITY "OVERLAND PARK" LO ST KS LOCAT FOR NOT ZIPCODE 66212 LOC ZIPCODE = "66212" locate for name "Richard Dower" LO SALARY >= 50000 lo for taxes < 100.50 lo left(depart,3) MIS lo not right(zip,3) 215 lo substr(address,5,6) "OAK ST" Note: the >, <, >=, and <= are for numeric fields only. NEXT Find next match for previous LOCATE command. If a LOCATE or NEXT fails to find a match, a message will be displayed indicating the end of file was reached with no matches found. QUIT Close all files and exit from DQ. RELATE Syntax: RELATE <field1> <field2> Relates two databases on a common field. Rules: 1. <field1> is a character field in the database in work area 1. 2. <field2> is a character field in the database in work area 2. 3. <field1> and <field2> are the same length. 4. WORK AREA 1 must be the currently selected work area. Once the two files are RELATEd, any movement in the file in work area 1 will cause DQ to search in work area 2 for a record where field2 matches the field1 in area 1. If there is no corresponding record in area 2, the bottom right of the area 2 window will display "NO MATCH". This display is in split screen only. When the screen is not split, the relation is still active behind the sceens. When you select WORK AREA 2 the relation is temporarily turned off. When you select WORK AREA 1 again, the records are syncronized again. To turn off the relation, either USE another file in either work area or enter RELATE with no field names. Example: You have a PAYROLL database with only Social Security Numbers to identify the information. You wish to know which record goes with which employee. Your EMPLOYEE database also has a Social Security Number field. You could write a dBASE program and do the following: SELECT 1 USE PAYROLL INDEX ON SSN TO PSSN SELECT 2 USE EMPLOYEE INDEX ON SSN TO ESSN SELECT 1 SET RELATION TO SSN INTO EMPLOYEE Now the two files will be syncronized. To view both records simultaneously would require considerable programming and using @ SAY commands to format the screen. In DQ you don't need indexes. Simply: Select WORK AREA 1 (F3) USE PAYROLL Select WORK AREA 2 (F3) USE EMPLOYEE Select WORK AREA 1 again (F3) RE SSN SSN This relates the SSN field in PAYROLL to SSN in EMPLOYEE. Now any movement in the PAYROLL file will cause DQ to find the matching SSN in the EMPLOYEE file. If the screen is split (F4), you may view both records at the same time. You may also use F2 to display any set of fields in either database. RELATE continued... Sample screen for the RELATE command. The files TEST.DBF and TEST2.DBF have been related on fields KEY and KEY2. You will not that these two fields match in the two databases. Whenever you move within area 1 (TEST.DBF) the record in area 2 (TEST2.DBF) will be found that matches the related field in area 1. If there is no match, the lower right corner of area 2 will display "NO MATCH". ┌─────────────────TEST─────────────────┐┌─────────────────TEST2────────────────┐ │ Number: 3 of 20 ││ Number: 20 of 20 │ │NUMBER :9999999.99: ││NUMBER : .10: │ │INTEGER :9999: ││INTEGER :9999: │ │LOGICAL :T: ││LOGICAL :T: │ │DATE :07/07/87: ││DATE :08/08/81: │ │STRING :DOWER >>││STRING :DOWER >>│ │SMALL :3: ││SMALL :6: │ │BIG :3333333333333.33300: ││BIG :6666666666666.66600: │ │KEY :XXX: ││KEY2 :XXX: │ │NUMSTR :273 : ││F9 : : │ │F10 : : ││F10 : : │ │F11 :W: ││F11 : : │ │F12 : : ││F12 : : │ │F13 : : ││F13 : : │ │F14 : : ││F14 : : │ │F15 : : ││F15 : : │ │F16 : : ││F16 : : │ │F17 : : ││F17 : : │ │F18 : : ││F18 : : │ │F19 : : ││F19 : : │ │F20 : : ││F20 : : │ │ F2 for more fields... SELECTED ││ F2 for more fields... │ └──────────────────────────────────────┘└──────────────────────────────────────┘ SKIP Syntax: SKIP [NEXT] [-] <number> Skip forward or back number records. If you skip past the beginning of the file, you will be placed on record number 1. If you skip past the end of the file, you will be positioned on the last record. Examples: SKIP 10 SK -20 STATISTICS Displays the record size, header size, and number of fields for the currenly opened database. SUM Syntax: SUM field [ [FOR] [NOT] <field> [=,>,<,>=,<=] value ] The number of records the SUM was derived from will be displayed as well as the average, maximum and minimum. Values containing spaces should be surrounded by quotes. Examples: SUM salary FOR POSITION = MANAGER SUM salary SUM allowance FOR NOT ZIPCODE = 66212 SUM fica CITY "OVERLAND PARK" SUM regist PROGRAM DQ sum salary salary > 0 sum salary for taxes >= 5000.50 sum val(numstr) sum val(numstr) hiredate > 03/05/89 sum salary left(code,3) MIS SUM withdraw right(zip,3) 215 sum deposit for not substr(state,4,5) "TH CA" Note: the >, <, >=, and <= are for numeric fields only. Sum always starts from, and includes, the current record in its output. If you wish to SUM an entire database, be sure you start at record number 1. SUM continued... Sample screen illustrating the SUM command. The active database is TEST. NOTE: the output of SUM is adjusted to reflect the size of the field being SUMed. The output of the first example shows a field definition of width 19 and decimal places 5. (19 is the maximum field width allowed). The second example shows the output of SUM on a field with width 1 and no decimals. By dynamically adjusting the output, your answers will be in the format of the original field. EXAMPLE 1: Record Number: 1 of 6 Last Update: 04/05/89 NUMBER : 100.30: INTEGER : 43: LOGICAL :T: DATE :09/09/88: STRING :testi >> SMALL :1: BIG :1111111111111.11100: ┌────────────── SUM ───────────────┐ │ │ │ Count: 6 │ │ Sum: 23333333333333.33000 │ │ Average: 3888888888888.88800 │ │ Maximum: 6666666666666.66600 │ │ Minimum: 9999999999.00000 │ │ │ │ DONE-Hit a Key │ └──────────────────────────────────┘ TEST>sum big SUM continued... EXAMPLE 2: Sample screen illustrating the SUM command using a condition. NOTE: only 3 records matched the condition so the average reflects the sum divided by 3. The active database is TEST. Record Number: 1 of 6 Last Update: 04/05/89 NUMBER : 100.30: INTEGER : 43: LOGICAL :T: DATE :09/09/88: STRING :testi >> SMALL :1: BIG :1111111111111.11100: ┌───── SUM ──────┐ │ │ │ Count: 3 │ │ Sum: 6 │ │ Average: 2 │ │ Maximum: 3 │ │ Minimum: 1 │ │ │ │ DONE-Hit a Key │ └────────────────┘ TEST>sum small date > 07/08/84 USE Syntax: USE [path]<database file> Opens a database file. The first record in the database will be displayed. If there are more than a screen full of fields, F2 will page through the other fields. USE allows you to open databases in other directories while the DIR command only looks in the current directory. Examples: USE TEST USE D:\FOX\ACCOUNTING\ACTIVITY FUNCTIONS: The functions supported by DQ are designed to be used one at a time and only with selected commands. When using a function, do not have any embedded spaces. Example: RIGHT WRONG val(month) left (code,3) left(code,3) left( code,3) right(key,4) left(code, 3) val(left(salary,2)) LEFT(field,n) - returns a string that is the first n characters in field. RIGHT(field,n) - returns a string that is the last n characters in field. SUBSTR(field, start,n) - returns a string that starts at location "start" in field and is n characters long. Commands which allow LEFT(), RIGHT() and SUBSTR(): LOCATE COUNT SUM (only for the conditional portion) Examples: SUM NUMBER SUBSTR(NAME,3,2) WE SUM VAL(NUMSTR) RIGHT(ZIP,2) 15 SUM SALARY FOR LEFT(TITLE,7) = MANAGER Note: RELATE does not yet support relations built on substrings. Examples: LO LEFT(NAME,5) DOWER CO RIGHT(ZIP,2) 15 LOCATE FOR LEFT(CITY,8) = "NEW YORK" coun right(key,3) "XYZ" lo substr(state,6,5) CAROLINA lo substr(state,4,5) "TH DA" VAL(field) - returns the numeric equivelent of a character field. Used to sum numeric data stored in character fields. Commands which allow VAL(): SUM Examples: SUM VAL(SALARY) SU VAL(deposit) sum val(withdraw) for usedate > 01/09/88 sum val(withdraw) right(zip,2) 15 Some tests to help you get started. If you play with the following commands on the sample databases, you will quickly find the speed and power of DQ. Include with the documentation are a few sample files. TEST.DBF - contains several numeric fields of different formats, a date field, many character fields, and several logical fields. BIG is the largest width possible for a dBASE numeric field. SMALL, which is the smallest width possible for a numeric field. STRING is the longest possible character field. KEY is a 3 digit character field used to demonstrate the RELATE command. The file TEST2 has a corresponding field called KEY2. NUMSTR is a field of numeric data stored in a character field to demonstrate the VAL() function. TEST also has 128 fields, the most allowed by dBASE. Some interesting tests to run on TEST are: SUM BIG SUM SMALL SUM BIG LOGICAL Sums only the records where LOGICAL is .T. SUM BIG NOT LOGICAL Sums only the records where LOGICAL is .F. SUM FIELD128 date > 01/01/87 FIELDS Shows the definition of all 128 fields. CO INTEGER > 200 LO INTEGER < 200 NEXT Now use some functions: SUM VAL(NUMSTR) LO LEFT(STRING,3) dow CO RIGHT(NUMSTR,2) 75 sum val(numstr) substr(string,3,3) wer To demonstrate the different work areas and the RELATE command: Press F3 until you have selected WORK AREA 1. USE TEST Press F3 again to select WORK AREA 2. USE TEST2 Press F3 a few times and between presses move around within each of the files, use the SUM command, etc. Press F4 to split the screen. Now you can see both files and the bottom of the screen tells which one is the SELECTED database. Press F3 several times to see how the screen is updated. Now perform several operations on each and see how each record is displayed in the split screen mode. Select WORK AREA 1. RELATE KEY KEY2 This will relate the field KEY in TEST to the field KEY2 in TEST2. If there is no corresponding match in TEST2, a message will show at the bottom right of the WORK AREA 2 screen (split screen only). Now move within TEST using the PgUp, PgDn, Ctrl+Home, and Ctrl+End keys. Also try LOCATE, GOTO, and SKIP. You will notice how nicely the records are matched between TEST and TEST2. You may also press F2 to display a different set of fields in either work area and the records will continue to match up. If you unsplit the screen by pressing F4, the records will still match. You can see this by pressing F3 and checking work area 2. Whenever you select WORK AREA 2, the relation is temporarily turned off. When you select WORK AREA 1 again, the records will be syncronized again. Either USE another file or enter RELATE without any field names to turn off the relation. The following index files have been included to demonstrate DQ's ability to determine index types and which fields are not in the open database. TEST.NDX - index file which matches TEST but not TEST2. TEST.IDX - FoxBase index file which matches both TEST and TEST2. TEST2.DBF - similar to TEST but with one field different. TEST2.IDX - FoxBase index which matches TEST2 but not TEST. To show how DQ handles index information, USE TEST. Now, enter the command DIR INDEX. Move the light bar over each of the indexes and not the information displayed at the bottom of the window. Now, USE TEST2 and move the light bar around again. Note how DQ matches the file with its corresponding index files and lets you know which field is not in the database if the index file is not a match.