5 Replies Latest reply on Aug 13, 2014 9:25 AM by WF7A

# How to count discrete values from separate fields?

### Title

How to count discrete values from separate fields?

### Post

Greets, all:

I'm short of coffee this morning so maybe that's why I can't find/figure out an answer to a problem I need to solve: I need to count the number of discrete values from separate fields; I've found info on something like it on the Web for when you want to count discrete values in _one_ field but not from _many_ fields.

Example: You have five fields, each one assigned a numerical value:

DB1 = 1

DB2 = 3

DB3 = 2

DB4 = 3

DB5 = 1

So, the result I'm looking for should look like this:

Count of 1s: 2

Count of 2s: 1

Count of 3s: 2

Count of 4s: 0

...etc.

Suggestions and help will be greatly appreciated. Thanks!

• ###### 1. Re: How to count discrete values from separate fields?

Example: You have five fields, each one assigned a numerical value:

I've found info on something like it on the Web for when you want to count discrete values in _one_ field but not from _many_ fields.

To me this doesn't look like your data should be placed in many fields, but in one field of many related records and then the methods for reporting these counts becomes very straight forward in most cases.

The need to generate such a report is one of the major reasons why you would use a set of related records with one value to each record in the first place.

• ###### 2. Re: How to count discrete values from separate fields?

Well, here's the problem with that: the separate DB fields mean separate things, e.g.,

DB1 = Skill rating

DB2 = Presentation rating

DB3 = Knowledge rating

DB4 = Assessment rating

DB5 = Response rating

...and so on, so it's not one parent that has related children records pertaining to that one record's "subject", but five different subjects under a single heading (Ratings)  that need to be tallied.

Maybe I should use the List function to move them all into one field, or..?

• ###### 3. Re: How to count discrete values from separate fields?

That still does not rule out using a related table. Each record can have:

An fk field to match to the parent

A RatingType field with "Skill", "Presentation", "knowledge", etc

A RatingValue field with the actual rating

This is sounding more and more like a survey/audit/questionaire type project. If so, you might check out this thread: Need aid on generating a report from a survey layout.

• ###### 4. Re: How to count discrete values from separate fields?

There IS an alternative that does not require redesigning your database, but it's pretty rigid and "messy":

Define a different calculation field for each possible rating value:

cRating1Count:

Sum ( DB1 = 1 ; DB2 = 1 ; DB3 = 1 ; DB4 = 1 ; DB5 = 1 )

cRating2Count:

Sum ( DB1 = 2 ; DB2 = 2 ; DB3 = 2 ; DB4 = 2 ; DB5 = 2 )

and so forth...

It's "rigid" because if you ever have to add another rating, you have to redefine all of these calculation fields to include the added rating field. If you set up a related table for these results, adding or removing a rating from the list is a data entry task not a database design modification task.

• ###### 5. Re: How to count discrete values from separate fields?

Thanks, will do!  ...and thanks for the fast response times. :)

On Edit: Yeah, that's pretty messy up there. Methinks I'll explore the other route. Thanks again, though.