I'm going around in circles with this now. So posting on here to see if a fresh pair of eyes will solve it. Thank you so much for your attention and participation.
Events take place, and people attend these. I have attended each event and would like to know how many consecutive events any participant has had and who is on the longest current streak (so involving the most recent event). All my thoughts suggest this should be a calculated column either in the EVENTS table or the PARTICIPANTS table. Still, I can't work out a way to increment the count, so the consecutive attendances are counted properly.
In my mind, the result should be in one of these two formats.
EVENTS
| EventNumber | PersonID | ConsecutiveCount |
| 1 | 66 | 1 |
| 2 | 66 | 2 |
| 3 | 66 | 3 |
| 5 | 66 | |
| 6 | 66 | |
| 7 | 66 | |
| 8 | 66 | 1 |
| 10 | 66 | 2 |
| 12 | 66 | |
| 13 | 66 | |
| 14 | 66 | 1 |
| 16 | 66 | 2 |
| 17 | 66 | 3 |
| 20 | 66 | |
| 21 | 66 | |
| 1 | 48 | 1 |
| 2 | 48 | |
| 3 | 48 | 1 |
| 4 | 48 | |
| 7 | 48 | |
| 8 | 48 | 1 |
| 9 | 48 | 2 |
| 10 | 48 | 3 |
| 11 | 48 | 4 |
| 12 | 48 | 5 |
| 14 | 48 | |
| 15 | 48 | |
| 16 | 48 | |
| 17 | 48 | |
or
PARTICIPANTS
| PersonID | Consecutive Count |
| 66 | 3 |
| 48 | 5 |
Download the EVENTS table ( contains EventNumbers and PersonID)
https://geordieintelligence-my.sharepoint.com/:f:/g/personal/ross_geordieconsulting_co_uk/EiPMJAmWVP...
