counting field

[stevoreeno]

I have created a priority field that catergorizes by using the letters "a" through "d" . Is there any caluclation that would give me a summary for each letter entry? For examle if I have 100 records with 30 "a" entries, 20 "b" and 50 "d", is it possible to have a summary or counting field for each letter telling me how many occurances there are? Currently the way I do this is to make a seperate field for each letter category, then sum each field.

[Nearo]

Here's one way to do this. Create the following calculations:

CountA - Calculation - Number: If(Priority = "a", 1, 0)

CountB - Calculation - Number: If(Priority = "b", 1, 0)

CountC - Calculation - Number: If(Priority = "c", 1, 0)

CountD - Calculation - Number: If(Priority = "d", 1, 0)

Now, create summary fields to count these.

TotalA - Summary - Total of: Count A

TotalB - Summary - Total of: Count B

TotalC - Summary - Total of: Count C

TotalD - Summary - Total of: Count D

[stevoreeno]

I actually have another field that can has 40 different categorized responses. Is there an easier way to get around creating these extra fields for each different category?

In excel there is a formula that will count based on each category

e.g.

=COUNT IF(category1:category20,"A")

it is formula that will count cells 1-20 if the cell contains an "A"

[signal]

Filemaker has a similar function. Check out aggregate functions. You can use an aggregate function like count[ ] and put a calulation inside of that.

[This message has been edited by signal (edited March 01, 2001).]

[Gerd Muller]

make a new self-relation on your category field. make also a new number field called "one", where always the value "1" is placed. Then make a new calculation field with the formula sum(selfrelation: :one). You will get the sum of each category in your database, no matter how many categories you will ever have!

if you need a summary of categories, sort by category, make a layout with a sub-summary when sorted by category and place your formula there! this should do it...

Gerd

[This message has been edited by Gerd Muller (edited March 02, 2001).]