Aggregating attribute data with one-to-many relationship class in ArcMap?

Question

I am using ArcMap 10.6.

I am joining a table of public housing developments to a parcel map layer, and there is a one-to-many relationship between the parcels and developments: multiple buildings may occupy any given parcel, and each has its own row of attribute data, such as number of housing units. I'm hoping to aggregate the data from the housing developments to the parcel layer, e.g. so that I can display the sum of all housing units contained in each parcel.

I could do this quite easily with an Excel pivot table or with the R aggregate() function, but if possible I'd like to avoid bringing in a whole new table (i.e. with the aggregated data) and keep the relationship within the geodatabase, which I'm less sure how to do.

In very simplified terms, my data looks something like this:

And I'd ultimately like the parcel layer's attribute table to look like this:

How I might accomplish this with a relationship class/ArcMap relate?

It's not the end of the world if I have to aggregate the data outside of ArcGIS and bring in a separate table, but I feel like that should be avoidable, and I'm curious as to how.

Answer 1

The only real reason to do what you want is to be able to see updates in the relationship in real time, which primarily comes into play with symbology, labeling and selecting records. If your objective is to do symbology based on the summarized Housing_Unit values there is no real-time option. Your only option is to summarize the One-To-Many relationship table into a new One-To-One relationship table. This could be done by the Summary Statistics tool through doing a SUM of the HOUSING_UNITS field and using the ParcelID field as a Case Field prior to joining the new table to the parcel features if you want to do this strictly with ArcMap tools.

If you are wanting to Label features based on the Summary value you could do a variation of the approach outlined in my Creating Labels with Related Table Data blog. Instead of listing rows of data read by the cursor for each ParcelID case value, you would perform a sum of the Housing Units and output that result to the label. If you are dealing with a very large set of records in both related tables this technique could cause performance degradation when refreshing the screen. At some point the performance of doing a summary table at controlled intervals will perform faster than doing real-time updates from this label technique, but fortunately that normally doesn't become an issue until both data sources have more than 100K records in each.

It is possible to also adapt this label technique to the Field Calculator, but I consider the Field Calculator and Summary Table approaches to be the same thing, since either way you are not expecting data to update in real time. With Enterprise databases you may be able to select records without creating a new table, but for file geodatabases, shapefiles, etc. I think you would have to either join a One-To-One summary table to the features or transfer the summary to the parcels through a script or field calculation involving cursors to do record selections based on the summary value.