summarize tables with one-to-many relationship

330
3
05-14-2012 12:55 AM
AnneKull1
New Contributor
Hello!
I have a map with forst cadastral units. The attribute table of this map consists of different ID numbers which cal be used to join (or relate) other tables (for instance forest types, ages, heights etc) to this map. The problem is that relationship between these tables is one-to-many (to one cadastral unit correspond many rows in thematic attribute tables). One row contains proportion number (how many % this type makes from the entire unit). I want to calculate the areas of different forest types (column tree code) and make a summarized table. Later I want to make similar tables from trees with different ages, heights etc. I cannot join all the records with the same ID to the corresponding unit. I also cannot do anything with related tables. In original table there are many million records and about 30 forest types (tree codes). I include a sample layer (metsaeraldis5.shp) with some records and a table (element5.dbf) with corresponding records.
I would be very grateful if anybody could help me.

With best regards,
Anne
0 Kudos
3 Replies
NobbirAhmed
Esri Regular Contributor
Here is one way of doing this (hope you get a better solution from other users).

Transfer your data to a file geodatabase. Use Copy Features (Data Management > Features) and Copy Rows (Data Management > Tables) tools to copy data to geodatabase. Use Add Join tool (under Data Management Tools > Joins) to join the table to the feature layer.

And, the catch is here, you must then use Copy Features again to copy he (joined) input feature layer (which has the join) to a new output.

This output will have m-rows for each input row that has a 1-m relationships.

If you have to stick to shapefiles/dbf then review this topic:
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//005s0000002n000000

A similar issue is discussed in this forum post:
http://forums.arcgis.com/threads/57475-One-To-Many-Join

There is a suggestion to read this blog with tip to perform a 1:M join:
http://blogs.esri.com/esri/supportcenter/2012/05/10/a-quick-tip-on-performing-a-1m-join/
0 Kudos
AnneKull1
New Contributor
Thank you for the useful tips. I did not understood the part where I had to copy features again after join. But I transfered both table and shapefile to file geodatabase and used Make Query Table tool. I got a layer with attribute table where are rows for each records from 1:M table.

Best,
Anne
0 Kudos
NobbirAhmed
Esri Regular Contributor
The join is temporary (lives in memory) - you can keep the joined table around (i.e., permanent) by making a copy or saving the mxd :) The query table solution is better (IMO).
0 Kudos