Export 1:M in ArcMap

265
1
11-12-2021 06:24 AM
Labels (1)
MikeHammett
New Contributor II

ArcMap 10.8.

I have a relate established between a US Census shapefile and other US federal data (FCC form 477). The 477 data is going to have many records for each census block.

A relate displays properly but isn't queryable. I can't export the data because that only gives me one record instead of many records.

How do I get a new data set that has all records?

1 Reply
BillLotz
Occasional Contributor II

Old post, but I found this (https://gis.stackexchange.com/questions/177506/one-to-many-joins-on-a-feature-class-to-a-table) : 

To multiply the features do this (available to those who have ArcGIS 10.1 or above).

  1. Place the features and table into the same file geodatabase (you must convert Shapefiles/Excel/DBF files into the geodatabase for this to work).
  2. Make sure your polygons have a unique ID field that will be preserved (you can create a Long field and calculate the ObjectID into it so that the ObjectID value will not be lost).
  3. Do a standard Join of the table (attributes, no spatial data) to the features (spatial data) on the matching field. Note that the attribute table of the features (spatial data) will have the same number of features that it did before, but don't worry about this, they all show up in the next step.
  4. Export the features to the same geodatabase as a new feature class (right click the layer from the table of contents and choose export). Note that the number of features in attribute table of the exported features now has the right number of features, the same as the original table (with attributes, no spatial data) or, in the case of a many to many join, a complete set of features for all combinations of features and related records.

The features will multiply in a new feature class where every feature will be present (even if it did not have a match in the table) and where there was a 1:M match before, the features will be multiplied to make a 1:1 match of each feature to all of its table entries. The Unique ID field you created in step 2 will allow you to relate the multiplied polygons to the original single polygon set. With the new feature class of multiplied features you can do selections on the table fields, summaries, Spatial Joins, etc.

If you want to generate a unique ID value that represents each unique species and disease combination you can use the 10.2 version of the tool described in this Blog post. Having this type of key lets you use it to do standard joins to summary tables and statistics based on a multi-field relationship as a substitute for creating an actual multi-field relationship using a Make Query Table set up.

Here is a good reference from esri: https://support.esri.com/en/technical-article/000001228

0 Kudos