I have a table that lists sponsorships that we receive every year for our conference. This may not have been the best way to do it, but the way i have it is that a sponsor is added as a record every year and the year is listed in the next column. The result is that the sponsor may show up a few times but when sorted by year i see their history (i.e- Sponsor A sponsored for 3 years in a row and increased their sponsorship every year).
Now i have an interface that shows lots of data as a dashboard ($ we received every year, # of sponsors this year etc). From the sponsorship table, I want to have a number element that shows how many sponsors sponsored us more than 1 time (as in- looking at those records that show up more than once and have a matching name and understanding from that they've come back). Any ideas on how to do this?
In the immortal words of Bon Jovi, you're halfway there. What it sounds like you have is a table that longs to be a join table but isn't quite there yet. Your existing table, the one that has the sponsors listed multiple times, is great for analyzing data by conference by not so great at analyzing data by sponsor.
Here's a solution:
Create 2 new tables: a Sponsors table and a Conferences table. The first field of both of those tables is the same text as you have in your existing table for Sponsors and Conferences. If your sponsors are John Smith, Jane Brown, and Leslie Knope, then those are the Name fields of your Sponsors table. If your conferences are Lead The Way 2015, Hyperfocus 2016, and WTF 2017, then those are the Name fields of your Conferences table. You can change them later.
In your existing table, link the Sponsors and the Conferences fields to the new tables. Do not allow links to multiple records in this table. Do allow links to multiple records in the other tables.
Once everything is linked, you can add a page in your interface that uses the record picker to allow you to choose a sponsor and see how many conferences they sponsored.
You might discover - I hope - that you're able to squeeze more analytics out of your conference interface by linking things together.