Help

Re: How to count records from different table with specific conditions?

Solved
Jump to Solution
3586 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Mohsen_khaldoun
5 - Automation Enthusiast
5 - Automation Enthusiast

I would like to know how to Count records from different table with specific conditions?
For example I have a table called “Stats” where I would Like to put some spesific information (automatically) for example number of boys and girls (based on another table called Students with a field called sexe) ?

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Here’s an example setup. I teach an online class, and this is part of my base for organizing that class. I created a [Gender] table with two records: “Male” and “Female”.

Screen Shot 2020-08-18 at 5.36.35 PM

In my [Students] table, I added a link field named {M/F}, and linked to the appropriate gender record for each student:

Screen Shot 2020-08-18 at 5.38.09 PM

The [Gender] table now looks like this:

Screen Shot 2020-08-18 at 5.37.00 PM

Now I can add a count field to count the number of links to each record.

Screen Shot 2020-08-18 at 5.37.15 PM

Screen Shot 2020-08-18 at 5.37.22 PM

Counting gender by groups can be done depending on how you have your groups set up. In my case, each student also links to a specific class record in a [Classes] table. Here’s that table’s setup, where I already have a count field showing me how many students are linked to each class:

Screen Shot 2020-08-18 at 5.45.27 PM

To count gender by class, I added conditional rollup fields to that table. Counting males was tricky because I can’t just look for “male” in the gender link because that’s also part of “female”, so I had to narrow that down by those records that don’t contain “fe” in that link, and also aren’t empty.

Screen Shot 2020-08-18 at 5.48.01 PM

Counting females was similar. In that case, the condition only collected records that did contain “fe” in the gender link. I also added a third rollup to count those records with no link (mostly older records before I began tracking gender, plus some newer ones where I haven’t been as diligent about marking everyone). With that done, my [Classes] table looks like this:

Screen Shot 2020-08-18 at 5.51.32 PM

Does that give you enough to work with?

See Solution in Thread

3 Replies 3

One option would be to use a linked table (instead of a single-select field) to track the gender of the students. Make a [Gender] table with records named “Male” and “Female.” Link to those records from the [Students] table, and then you can see a summary on the [Students] table (using a Count field type to count linked records) of male and female students. Will that work for your needs?

The last part didn’t work for me. would you please explain with an example?
And what if I want to make stats about sexe of students by GROUPS (For example a groupe “A” can have 15 Males & 20 females)?

Justin_Barrett
18 - Pluto
18 - Pluto

Here’s an example setup. I teach an online class, and this is part of my base for organizing that class. I created a [Gender] table with two records: “Male” and “Female”.

Screen Shot 2020-08-18 at 5.36.35 PM

In my [Students] table, I added a link field named {M/F}, and linked to the appropriate gender record for each student:

Screen Shot 2020-08-18 at 5.38.09 PM

The [Gender] table now looks like this:

Screen Shot 2020-08-18 at 5.37.00 PM

Now I can add a count field to count the number of links to each record.

Screen Shot 2020-08-18 at 5.37.15 PM

Screen Shot 2020-08-18 at 5.37.22 PM

Counting gender by groups can be done depending on how you have your groups set up. In my case, each student also links to a specific class record in a [Classes] table. Here’s that table’s setup, where I already have a count field showing me how many students are linked to each class:

Screen Shot 2020-08-18 at 5.45.27 PM

To count gender by class, I added conditional rollup fields to that table. Counting males was tricky because I can’t just look for “male” in the gender link because that’s also part of “female”, so I had to narrow that down by those records that don’t contain “fe” in that link, and also aren’t empty.

Screen Shot 2020-08-18 at 5.48.01 PM

Counting females was similar. In that case, the condition only collected records that did contain “fe” in the gender link. I also added a third rollup to count those records with no link (mostly older records before I began tracking gender, plus some newer ones where I haven’t been as diligent about marking everyone). With that done, my [Classes] table looks like this:

Screen Shot 2020-08-18 at 5.51.32 PM

Does that give you enough to work with?