Counting values across different fields

[sinisa93]

Hi!

I have several fields in one table that use same value list. Is there a way I can count how many times values from this list appear in the fields? I'm aware I could count this for each field individually but I'm intrested to do this for all fields together.

TnX! 

[comment]

44 minutes ago, sinisa93 said:

have several fields in one table that use same value list.

Your question suggests a structural problem. Instead of multiple field, you should probably have multiple records in a related table.

 

44 minutes ago, sinisa93 said:

count how many times values from this list appear in the fields?

What would the result of this count look like? It seems you need to do an individual count for each value on the list.

 

47 minutes ago, sinisa93 said:

I'm aware I could count this for each field individually

So how exactly would you do that?

[sinisa93]

Hi!

Thank you for your answer.

I might have structural problem but so far multiple fields that use same value list served my purpose.

Well, it would be perfect if I could have grand summary in E.g.table view that would show total number of appearances of values from the list across multiple fields. I know I can do individual summary of values in table view for each field that use this list but this is not what I'm looking for.

 

 

[comment]

2 minutes ago, sinisa93 said:

I might have structural problem but so far multiple fields that use same value list served my purpose.

And now they don't. I believe that with time you will run into more issues. That's why I would suggest you fix the real problem here.

You haven't answered any of my questions.

[sinisa93]

Hi!

I should probably be more precise and I hope It won't get to complex.

I'm creating DB for local karate team. As you probably know, they have different belts signifying level of acquired martial skill. This team in particular has lots of blue, brown and black belts members and each of them got their belts on various locations/events. The list of possible locations is fixed so I created a value list for this (Location A, Location B, Location C etc.). 

Since each member got his/her various belts on different location I have for each belt separate drop-down field called "Location- 'color' Belt"  that uses above mentioned value list. This is what I meant by saying I have one value lists for multiple fields.

So, I can easily find out how many E.g. blue belts have been given at each location. This I can do via table view adding Leading group by Location and subtotal to the field "Location-Blue Belt".

But what I would like is to get E.g. total of how many belts overall (all colors) have been given at Location A (or B,or C).

It would be perfect if somehow I could get this total in the table view but report or similar will also do.

[comment]

5 minutes ago, sinisa93 said:

Since each member got his/her various belts on different location I have for each belt separate drop-down field

Since each member can have many belts, what you should have is a table of Belts, where each belt would be a separate record with fields for MemberID, Color and Location. Then you can easily summarize this table by Location (or any other category), using a single summary field.

 

In addition, if you add a table of Locations, you could have a calculation field there =

Count ( Belts::LocationID )

to tell you how many belts overall (all colors) have been given at each location.

 

[sinisa93]

Hi!

Thank you for your answer. I restructured my DB according to your suggestion and It works fine!