topic Re: Summarize Multiple Tables into one Table in ArcGIS Pro Questions

Summarize Multiple Tables into one Table

VenugopalAgrawal — Thu, 02 Sep 2021 07:34:58 GMT

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?

Re: Summarize Multiple Tables into one Table

JohannesLindner — Thu, 02 Sep 2021 09:03:32 GMT

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

Re: Summarize Multiple Tables into one Table

VenugopalAgrawal — Thu, 02 Sep 2021 10:07:55 GMT

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.

Re: Summarize Multiple Tables into one Table

JohannesLindner — Thu, 02 Sep 2021 10:23:46 GMT

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.