count records grouped by field

[MJF]

Hi,

I want to count records grouped by the value of a field.

So the output should by something like

value1 12 records

value2 20 records

value3 1 record

...

How should I do this ?

Thanx for your help

[tallboy755]

Here it goes .... Lets say that you have three fields "Field1", "Field2", and "Field3".... If you want a count of how many records have the word "CALL" (or whatever the word or number may be)in "Field1". Here is what you need to do ... Create a calculation field called "calc_field1_call" with the following calcualtion

If ( Field1 = "call", 1, "")

That will put the number 1 in that firld of every record that tas the word call in field1 - Then create a summary field for the total of "calc_field1_call" and BINGO you have a your total number...

Sorry if this is kinda hard to read... I am really bad at explaining things.

[MJF]

Thanx for your reply, Andrew.

I guess that your solution wouldn't be very practical when

the field contains a lot of different values (like a table

with the total number of records > 10.000 and the number of

distinct values of field is in the order of 100 or so)

and/or the values of field are not known beforehand.

That is why I thought this would be better done by some kind of

script. Ony how to build such a script ...?

[Sabrina]

I had a similar problem . . .

You will need to track how many records you have for this to work. We'll call that variable g_num_Records. The fields with g_ at the beginning are globals and MUST be globals for this to work.

<! initializing variables>

count = 0

g_field_One_value_One_Count = 0

g_field_One_value_Two_Count = 0

g_field_One_value_Three_Count = 0

Loop

If (Field_One = value_One)

set Field (g_field_One_value_One_Count, g_field_One_value_One_Count + 1)

End If

If (Field_One = value_Two)

set Field (g_field_One_Value_Two_Count, g_field_One_Value_Two_Count + 1)

End If

If (Field_One = value_Three)

set Field (g_field_One_value_Three_Count, g_field_One_value_Three_Count + 1)

End If

count = count +1

goto Next Record

End Loop If (count = g_num_Records)

I then had a layout specifically to display the globals.

Hope this helps you out! It's long and laborous, but the only way I figured out to do what I needed!

[Fitch]

You need:

1. a Summary field that is Count(yourField)

2. a layout with a Sub-summary part (sorted by yourField) and no Body part

3. put yourField and the Summary field on the Sub-summary

4. Sort by yourField

5 Preview or Print

[Fitch]

Another option is to use a self-join relationship, yourField::yourField.

Then create a calc field Count(self::yourField).

[Mike D.]

Depending on what your requirements are, you can use Fitch's method or you can also accomplish this using a self relationship. Just create a relationship within the same file based on the value field. Then have a calculation of count(self::valuefield). The method you use is up to you.

HTH, Mike