There are three tables which describe an organization who is going out and distributing items to households and institutions to help with COVID and flood relief (we’ve had a hard year here haha!). The progress for the distributions is measured through coded indicators and we have to total within those indicators how many institutions and people are reached by the combined activities.
Here are my three tables:
*Activities: Each activity will have only one beneficiary, but they may go back repeatedly to the same beneficiary.
*Beneficiaries: For each beneficiary we count the number of people receiving items, but it is split into a variety of categories, like gender, disability status, etc. So there are quite a few columns for these (I only put in a few for this example)
*Indicators: Each indicator has a code and each indicator can have different types of activities within it. I used concatenate to create unique ID’s for each combo.
Here are my two sticking points:
I set this example up with the link through Activities table because that seems like the more natural place to code them as part of our data quality assurance process. We go through each activity to double check the data and that’s when we could easily drop in the indicator. However, the data that goes into the Indicator table mainly relates to the beneficiaries, but it seems I’d need to pull the distribution data into the beneficiary table in order see what each beneficiary received and then add the link to the Indicator table based on that. Which seems cumbersome.
My dream situation: It would be amazing to have the indicators automatically code based on the distributions that are made. I.e. if a column such as food distn has any data in it it receives the appropriate indicator code for that activity. This would also help a lot in case there are changes made to the numbers in the distribution columns and we forget to change the applicable indicator.