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) ?
Solved
How to count records from different table with specific conditions?
Best answer by Justin_Barrett
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”.
In my [Students] table, I added a link field named {M/F}, and linked to the appropriate gender record for each student:
The [Gender] table now looks like this:
Now I can add a count field to count the number of links to each record.
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:
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.
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:
Does that give you enough to work with?
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?
Justin_Barrett wrote:
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)?
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”.
In my [Students] table, I added a link field named {M/F}, and linked to the appropriate gender record for each student:
The [Gender] table now looks like this:
Now I can add a count field to count the number of links to each record.
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:
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.
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:
Does that give you enough to work with?
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.