This article is reprinted from the May 1990 edition of TechNotes/dBASE
IV.  Due to the limitations of this media, certain graphic elements
such as screen shots, illustrations and some tables have been
omitted.  Where possible, reference to such items has been deleted. 
As a result, continuity may be compromised.  

TechNotes is a monthly publication from the Ashton-Tate Software
Support Center.  For subscription information, call 800-545-9364.

Cross-Tab Totaling
Jeffrey B. McCrimon

There are occasions when you find it necessary to count the number of
times that a field with a particular value occurs.  A good, and
relatively common, example of this would be a check book application. 
Within this application you might want to count the number of checks
written to any particular payee.  Figure 1 shows a listing of a
database file used as a check book register.  After counting the the
number of checks written to each payee and putting those results into
a new database, we have Figure 2.

Figure 1

Record#  CHK_NO DATE     PAYEE                            AMOUNT 
      1    1001 01/13/54 Knight's Gym                     120.00 
      2    1002 01/16/90 Corner Grocers                    82.40 
      3    1003 01/20/90 Hunter Books                      12.45 
      5    1004 01/22/90 Hardbottom Software               67.32 
      4    1005 01/28/90 Corner Grocers                    24.94 
      6    1006 02/02/90 University of the Swamplands     690.00 
      7    1007 02/12/90 Corner Grocers                    76.32 
      8    1008 02/15/90 Hunter Books                      15.95 
      9    1009 02/16/90 Hardbottom Software               32.95

Figure 2

Record#  PAYEE                          CHK_CNT 
      1  Corner Grocers                       3 
      2  Hardbottom Software                  2 
      3  Hunter Books                         2 
      4  Knight's Gym                         1 
      5  University of the Swamplands         1

On first thought, you may think that the TOTAL command is what you're
looking for.  However, the TOTAL command only sums the existing
numeric fields of a database file for a field that have the same key
value.  That is, what you would end up with would be the sum of the
checks for each payee, rather than the number of checks.
This article presents CrossTab.PRG, a program designed to count the
number of times a field in a database file has the same value and
place the result in a second database file.

Using CrossTab.PRG

You can use CrossTab.PRG in one of two ways.  Here is the syntax:

DO Crosstab WITH "[<database file>]","<key field>",
        "<result database>","<result field>","<results>"

or, if the database file you wish to work with is in use:

DO Crosstab WITH "","<key field>",
        "<result database>", "<result field>","<results>"

where <database file> is the database file you wish to work with.  If
<database file> is not specified, then CrossTab.PRG will use the
database file in currently selected work area. 

<Key field> is the name of the field that the count will be performed
on.  It is not necessary for this field to have an index tag.  Any
indexing is handled by the program itself.   
<Result database> is the name of the second database file that will
hold the result totals.  

<Result field> is name of the field that will contain the count for
the <key field>.

<Results> is a keyword that tells CrossTab.PRG what to keep in the
<result database>. The <results> argument can be one of three values. 
If it is "ALL", then CrossTab.PRG sums all of the numeric fields of
<database file> to the <result database>.  If <results> is "TAB" or
left blank, then CrossTab.PRG only saves the <key field> and the count
of the <result field> in the <result database>.

Given the source and result files in the example above (shown in
Figures 1 and 2),  if we wanted to get a count of the number of checks
written to each payee we would type

DO Crosstab WITH "CheckReg", "Payee", "CheckCnt", "Chk_Cnt", "tab"

Extra Goodie!

Since CrossTab.PRG creates temporary files, we need a way to generate
temporary filenames that don't already exist in the current drive and
directory.  So included is a user-defined function, GetFile(), which
takes a root filename (1-7 characters) and a file extension, then
returns a filename that consists of the root followed by a random
number.

Conclusion

A warning to users of CrossTab.PRG, since CrossTab.PRG adds a field to
the structure of a copy of your database, the number of fields in your
database is limited to only 254 fields.  Assure that you have
sufficient disk space to have a duplicate of the database being
totaled temporarily on disk.  This is particularly important if the
files are on floppy disks or are very large. 

CrossTab.PRG

* Program.....: Crosstab.PRG
* Author......: Jeffrey B. McCrimon
* Date........: January 24, 1990
* Versions....: dBASE IV 1.0
* Note(s).....: This procedure allows the user to perform a 
*               cross-tabulation on a field in a database.
* ---Set up parameters.
PARAMETERS dbfile,fldname,ctabfile,ctabfld,results

* ---Initialize variables
st talk=SET("TALK")
SET TALK OFF
NULL = ""
results = UPPER(results)

* ---Check parameters for valid values.
IF LEN(TRIM(dbfile)) = 0    && Was the database filename supplied?
   dbfile = DBF()           && No, then use the current database file.
ENDIF
IF LEN(TRIM(results)) = 0 .OR. results <> "ALL"
   results = "TAB"
ENDIF
iserror = IIF(LEN(TRIM(fldname)) = 0, "crosstab field", NULL)
iserror = IIF(LEN(TRIM(ctabfile)) = 0, "result database", NULL)
iserror = IIF(LEN(TRIM(ctabfld)) = 0, "result field", NULL)

* ---Were there any parameters missing?
IF LEN(TRIM(iserror)) = 0
   USE (dbfile)

   * ---Create scratch and temporary files.
   scrtchfl = Getfile("SCRT", ".DBF")
   tempfile = Getfile("TEMP", ".DBF")
   COPY TO (scrtchfl) STRUCTURE EXTENDED
   USE (scrtchfl)
   * ---Add result field to scratch database.
   APPEND BLANK
   REPLACE Field name WITH (ctabfld), Field type WITH "N", Field len
WITH 5, ;
           Field dec WITH 0, Field idx WITH "N"
   USE

   * ---Create temporary database from scratch database.
   CREATE (tempfile) FROM (scrtchfl)
   * ---Add records from original database to temporary database.
   APPEND FROM (dbfile)
   * ---Place value one in result field for every record.
   REPLACE ALL &ctabfld WITH 1
   * ---Create index on crosstab field.
   INDEX ON &fldname TO (scrtchfl)
   * ---Do we want all of the fields in resultant database?
   IF results="TAB"
      SET FIELDS TO &fldname,&ctabfld
   ENDIF

   * ---Sum the result field based on the crosstab field.
   IF results="ALL"
      TOTAL ON &fldname TO (ctabfile)
   ELSE
      TOTAL ON &fldname FIELDS &ctabfld TO (ctabfile)
   ENDIF
   USE

   * ---Erase temporary files.
   ERASE &scrtchfl..DBF
   ERASE &scrtchfl..NDX
   ERASE &tempfile..DBF
ELSE
   * ---Display error message.
   ? "The "+iserror+" parameter is missing!"
ENDIF
* ---Reset the environment and exit program.
IF st talk="ON"
   SET TALK ON
ENDIF
RETURN
* EoP: CrossTab


FUNCTION Getfile
        * Returns unique filename.
        PARAMETERS mroot,mext
        PRIVATE isgoodfile
        mroot = LEFT(mroot,7)
        taglen = 8-LEN(mroot)
        isgoodfile = .T.
        DO WHILE isgoodfile
                mtag = LTRIM(STR(RAND(-1)*(10^taglen),taglen+1))
                isgoodfile = FILE(mroot+mtag+mext)
        ENDDO
RETURN (mroot+mtag)
* EoF: CrossTab.PRG

