arcgis desktop - Using many-to-many join in ArcMap

I'm working in ArcMap 10 and can move around in R, though am not an expert.

I have a shp file and an xlsx file. The shp file is 20,221 rows and includes a field GID (grid ID) which is not unique among rows because multiple polygons named with a PID (park ID) can have the same GID. PID-GID pairs are unique.

The xlsx file 230,895 rows and also has a GID field which is not unique among rows because for each GID there are 60 years worth of data, each year represented by a row. Year-GID pairs are unique.

How can I combine the two files so that for each PID-GID combo in the shp file I get a row for each GID-year combo in the xlsx file?

I've been playing with joins and query tables and can't get it yet.

Answer

The Make Query Table tool, based on GID will get what you want - every GID in the shape will get all matching GIDs in the xlsx (so, duplicate records). This will potentially generate a very large number of records. For example, if a GID appears in one table four times and six in the other, you will get 24 records.

Note the result is temporary and must be exported to a new feature class. In order to use that tool you need to bring your shapefile and xlsx file (as a table) into a geodatabase to get it to run. There are a number of tools and features (such as nested SQL statements) only available if the data is in a geodatabase, and it can vary depending on that underlying database (OLE, personal, SDE, file, etc.). Null values are another thing only supported in databases (not shapefiles). Also note that Excel files don't always play nice with ArcGIS, and it may be better to convert it to a csv first. You may or may not need a schema.ini file and to make sure your column headings are valid (no spaces or restricted characters, don't start with numbers, etc.).

As Richard points out in his comment:

If you have Desktop 10.1 or later, once you get your data into the same gdb, you can use a standard join and standard export to convert your data from a Many to Many relationship to a 1:1 relationship. The Join will not show this in the table view prior to the export, but the exported file will have all of the related records combined based on the Join field. See the section on One-to-many and many-to-many relationships on this help page: http://resources.arcgis.com/en/help/main/10.1/index.html#//005s0000002n000000.

apparently as of 10.1 while a 1:M or M:M Join will only display the first returned matching record, if that join is done on data in a database, when the result is exported or fed to geoprocessing tools it will have all of the record combinations.