Calculating Participation Rates

[Wendy_Yelsik]

I want to store the total number of employees in the programs table to calculate the participation rate. Additionally, I'd like to break down the number of employees by position to calculate the participation rate for each position. I have three tables: Programs, Employees, and Tasks. While I can easily count the number of employees who complete each program or task, I am struggling with creating a field that sums the number of employees from the employee's table and places that total in the program's table to calculate the participation percentage.

[Russell_Bishop1]

Hi @Wendy_Yelsik ,

You need to create a junction table – that's a table that sits in-between two tables to store extra data about them.

For example, you need to store whether Employee A attended Program B – which means you need an 'Attendees' table that links Employees to Programs.

Fields for that new table would be:
[Employee (linked record)]
[Program (linked record)]
[Attended (checkbox)]

Logic:
Every record in the Attendees table must be a unique combination of an Employee and a Program.

Hope that helps!

[Wendy_Yelsik]

@Russell_Bishop1 - thank you for the information.

I already have that information in a table called "Tasks." However, I may not have the linked tables set up correctly. I've been using this base for years and have tons of data. Here's a snapshot of what the three tables look like.

Our "Approved" checkbox would be the attended checkbox. I'm trying to figure out how to get the number of employees from the employee table summarized to calculate the percentage of participation based on the total number of participants in one month. Ideally, that participation rate would be housed in the Programs table (or a new table), and a field would automatically sum the number of employees from the employees table by their position. For example, if Drivers have 90 employees on the employee's table, and 86 participated according to the Programs table ("DDONE" field), their participation rate would be 96%.

I could set up a rollup field for each position in the Programs table, but that would require a ton of additional fields. I've been manually adding the number of employees each month to calculate the percentage, but I feel there should be an automatic way since all the information is already in the base.

[Wendy_Yelsik]

oh-  I do not allow for multiple records in the tasks table.  Each employee can only have one record per program.  

[Russell_Bishop1]

Do you create records in the Tasks table for every employee, and only ✅ DDONE the ones who attended?

Each program needs to be able to grab the total number you're dividing by (Attend % Total). That either means you should:

• Create a Task record for every employee that this program is expecting to have join
• OR Create a new table for Employee Groups that (e.g. Drivers), link the employees to the correct groups, then link the program to the groups. Rollup all of the group totals.

[Wendy_Yelsik]

I don't create a task for each employee who should attend. The only task is created when the employee submits a Fillout form integrated in Airtable, which creates a new record in the task table. I also tried using the second option, but it seemed to require many fields because of our many positions. I may have set up that table incorrectly. Here's a snapshot. I have the employees rollup up in the field with the red circle, but I can't total up the employees who actually attended the program. Here is the snapshot of what I call the reporting table. @Russell_Bishop1 

[Russell_Bishop1]

but I can't total up the employees who actually attended the program.

---

@Wendy_Yelsik wrote:

I want to store the total number of employees in the programs table to calculate the participation rate. Additionally, I'd like to break down the number of employees by position to calculate the participation rate for each position. I have three tables: Programs, Employees, and Tasks. While I can easily count the number of employees who complete each program or task, I am struggling with creating a field that sums the number of employees from the employee's table and places that total in the program's table to calculate the participation percentage.

---

I'm not sure why that number isn't available. Isn't that just a rollup on the Tasks table from Programs where DDONE is ticked?

[Wendy_Yelsik]

Yeah. However, the DDONE consists of employees who have completed the programs. I have to get the number of employees, regardless of whether they completed the program, into that same table.  I have to think it through.   

[Wendy_Yelsik · Accepted solution]

@Russell_Bishop1 -  It's working! Your suggestions really helped me get everything configured correctly. Thank you! - Wendy