How to update medal tally using another table

[Aaron_Ah_Yuk]

Hi Airtable Community. I’d like to create a medal tally for a school’s athletics meet where the results are counted by how many Gold, Silver and Bronze each school has. I’d like this medal tally to be automatically updated and sorted when I update an event table, e.g, if I update the winners for the table for 100m event by the school, the Medal Tally would automatically update.

[Rose_K · Accepted solution]

Hello @Aaron_Ah_Yuk!

Aaron_Ah_Yuk:

I’d like to create a medal tally for a school’s athletics meet where the results are counted by how many Gold, Silver and Bronze each school has. I’d like this medal tally to be automatically updated and sorted when I update an event table, e.g, if I update the winners for the table for 100m event by the school, the Medal Tally would automatically update.

I believer the setup you are looking for would take three tables, a School table, an Event table, and a Medals table which would be a many-to-many relationships / junction table between the School and Events table.

I created a basic example to show case how this would work. Below is the simple Event table (just listing a name):

Here is the simple School table (I’ll cover the medal tally momentarily)

Then using a linked records field in our third table School Events you can connect each school to each event with any medals that school earned (this table will be where you enter how each school scored for each event):

I then added a count field on the Schools table to count all occurrences of each school in the School Events table. The count

Let me know if I can help provide any more information on the above, and if this helped answer your question please mark it as the solution :white_check_mark: .

[Aaron_Ah_Yuk]

Thank you Rose. Very helpful.