Counting different categories in one field... is there a better way?

[lonesomejubilee]

Hello,

I have a field which has a drop down box to select a category, let's say "red", "blue", "green", etc. Each record requires this field to have a selection. I want to count how many times each color was selected. I have hundreds of thousands of records, each with a color or category selected. Currently I do this by using the PatterCount function. It works but it is very, very cumbersome... as I have about 30 different "colors" to choose from so I have to make an individual PatternCount field to summarize each one individually.

Secondly, what is the best way to do the "counting" in another table so I don't have some many fields in my primary table?

At first I was thinking using a Summary field, but that only summarizes the entirety of the field, not individual categories within a field... so that's why I went with patterncount.

Thanks in advance...

LJ

[mr_vodka]

You can use a summary field and create a subsummary report. Group it by the category field. The summary field would be a "count" of that field. Then on your report layout, you can put the summary field next to the category field in the Subsummary part. Delete the body part as you dont really need it for the report.

As another option, you could store the selection choices in another table, create a relationship between the two tables, and then use a calculation field with an aggegrate function of Count () but in your case I think the easier way to go maybe the subsummary report.

[lonesomejubilee]

Hi, I don't really understand what you mean, as I understand, you can't make a summary report out of non-numerical fields, and my category or "colors" field is text, not numbers. So how would I go about creating a summary field out of that?

THanks.

[lonesomejubilee]

Oh, and by the way, the choices are in another table so it makes it easier to add new choices via look-up

[David Jondreau]

Not sure how you're using PatternCount here. But summary fields are the standard way to do this. If you sort by color and put a Sub-Summary part in your layout you can get a list of all the colors and their counts with only one field.

Another solution may be a custom function. Does this help?

[mr_vodka]

Here is a sample file.

categories.zip

[lonesomejubilee]

Hi,

You must forgive me because I guess I am not very apt at explaining what I needed...

In your example it will count the total number of possibilities... i.e. the count is always 13, but I want to know the total number of times each color was selected... or maybe I'm not doing it right.

Imagine that drop down field as part of a larger set of fields in a table, it's like I want to count within - within a group.

The Count of summary thing counts the current number (13), but not individually..

[lonesomejubilee]

David,

Just to let you know how I used PatternCount....

I have a field that says PatternCount (Field; "Red")

.. which returns 1 if the selection is red... then I do a summary field of the patterncount field...

and it counts up all the times Red was selected. Like I said, it's the long way around and it requires both the count and sum fields for each possible choice... in my case around 30 so you can see how I'd want a smaller solution.

LJ

[mr_vodka]

Go to Scripts and run the "generate report" script to generate the summary report.

[lonesomejubilee]

Ooops... sorry.

[lonesomejubilee]

Thanks Mr. Vodka, it works now, but if I wanted to take it one step further and run that same report, but by person... how would I do that? In other words, individually by person... seeing how many times they selected each color? I know I ask alot and thanks for your patience

LJ

[lonesomejubilee]

Thank you, thank you, thank you... for all your help! Duh... the reason my thing wasn't working out was so stupid and simple... I wasn't sorting the found set in one area, and then I didn't match the break field with what I was sorting by. Thanks for all your help and support...

Now I can get my report done! Yippee!!!

THE LONESOME JUBILEE...