How do you analyse data from several surveys together?

Link them through the thing they have in common, usually the site. When different surveys attach to the same facility, you can report across them, so a water quality result and a functionality check appear side by side for the same water point.

Video: Linking data sources in mWater on YouTube

Why this matters

Programmes accumulate surveys: one for construction handover, one for annual monitoring, one for water quality, often run by different teams. Kept separate they can each be summarised but never combined, which is precisely what questions like "are the systems we built still working and safe?" require. The site is the join that makes the combination possible without merging the teams or their forms.

How to do it

  1. Make sure each survey links its responses to a site rather than standing alone.
  2. Confirm the surveys reference the same site records, not separate lists of the same facilities.
  3. Build your visualisation from the site table, then reach into each survey through the relationship.
  4. Use expressions to pull the latest response per site where a facility has been surveyed many times.
  5. Consider a calculated data source if the combined query is heavy or needs reusing widely.

Go deeper