Summarize Multiple Tables into one Table

Question

Hi All,

I have a database, with a shapefile representing different settlements and 2 tables representing different data about individuals within these settlements. These tables are constantly updated from the field through field maps. I have shown an indicative example in the images below:

I want to create a summary table that can summarize the data from both the age and income table at the same time. The summary table should look like this:

Does anyone know of a tool or workflow that can accomplish this?

JohannesLindner · Answer

* Delete all Mean_Amount and Mean_Age fields
* Create Mean_Amount and Mean_Age in your summary table
* After "Join Field (2)", calculate the original fields to be equal to the newly joined fields, then delete the joined fields.

VenugopalAgrawal · Answer

Hi Johannes,

Thanks a lot for responding. However, the issue here for me would be that I am not familiar with Scripts or Queries, so I don't think I will be able to try out your suggestion.

However, I did try to automate the process using a sequence in ModelBuilder. In that particular database, I had 3 tables to summarize, so I made a model which looked like this:

While this did give me the result that I wanted, The issue is that Every time I run the model, rather than updating the fields in the final output table, it adds new fields to the table. Do you know if there is any way that this can be avoided? After running the model 3 times, the output table looks like this:

So rather than these new fields being added, i am trying to figure out how to get the other fields to be updated.

JohannesLindner · Answer

If the database is an Enterprise Geodatabase, you can use this SQL query to create a query layer (saved in the map document) or database view (saved in the database):

SELECT d.OBJECTID, d.Shape, d.AreaCode, a.AverageAge, i.AverageIncome
FROM Districts d

LEFT JOIN (
  SELECT AreaCode, AVG(Age) AS "AverageAge"
  FROM AgeData
  GROUP BY AreaCode
  ) AS "a"
ON a.AreaCode = d.AreaCode

LEFT JOIN (
  SELECT AreaCode, AVG(Income) AS "AverageIncome"
  FROM IncomeData
  GROUP BY AreaCode
  ) AS "i"
ON i.AreaCode = d.AreaCode

https://pro.arcgis.com/de/pro-app/latest/tool-reference/data-management/make-query-layer.htm

https://pro.arcgis.com/de/pro-app/latest/tool-reference/data-management/create-database-view.htm