Summarize Multiple Tables into one Table

294
3
09-02-2021 12:34 AM
VenugopalAgrawal
New Contributor

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:

VenugopalAgrawal_0-1630567845394.png  VenugopalAgrawal_1-1630567914415.png   

VenugopalAgrawal_3-1630567988207.png

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:

VenugopalAgrawal_4-1630568062994.png

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

0 Kudos
3 Replies
JohannesLindner
MVP Regular Contributor

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 


Have a great day!
Johannes
0 Kudos
VenugopalAgrawal
New Contributor

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:

 

VenugopalAgrawal_0-1630577083363.png

 

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:

VenugopalAgrawal_1-1630577239433.png

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

0 Kudos
JohannesLindner
MVP Regular Contributor
  • 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.

Have a great day!
Johannes
0 Kudos