Counting Number of Records in Each Catagory


The following application will give you a count of how many times a value is 
entered in a single field in a database.  For example, you may have 1500 
records of sales to business's  and you would like to know how many times a 
sales transaction took place for each business.  You may want to know which 
zip codes have the highest rate of occurance in order to get bulk mailing 
rates.

The fields and data shown in the following example are from the sample 
database EMPLOYEE.DTF.

On the Column/Sort spec number the field whose entries you would like to 
count.  This may be your state, zip or country field.  Any field formatted as 
text will work.

COL/SORT SPEC:

Department: 10,AS,CS

On the Derived Column Spec you enter the columns as shown below. The heading 
of the first column should reflect what you would like to see on the report.  
The heading of the second column will not print and it can be left blank.  It 
has been named here to help clarify the programming explanation.

DERIVED COLUMN SPEC:

Heading: Count
Formula: @TEXT(#10<>#50,@COUNT(#50,#10))
Column Spec: 30

Heading: Delayed copy of Department
Formula: #10
Column Spec: 50,I

The output from the data in the Employee database would appear in the 
following format.  Here we have asked for a listing and count of the number of 
records for various departments in the company. 

**Note you could easily ask for a count of the total number of employees by
adding the (T)otal code to the Column Spec. i.e. 30,T.

 Department     Count
 ----------     -----
 ACCNT          1
 ADMIN          6
 EXEC           3
 LEGAL          2
 OPS            1
 PROMO          1
 R&D            3
 SALES          15




Q & A Application Note       Rev. 6/92           #2204     Page 1 of 1