arcmap join table to layer

5301
8
Jump to solution
03-21-2016 01:55 AM
SvivaManager
Occasional Contributor II

Hello,

I'm trying to understand a weird behavour of ArcMAP.

I have a polygon layer and an excel sheet which both of them have a common column.

The layer have 1182 records. The Excel have 6615 records.

I'm doing a join on them and the layer stays the same with 1182.

From what I understand, I need to export this joined layer to actually see the entire 6615 records joined all together..

Why do I need to export it first? If i already did join, why can't I see it on the layer without exporting it? Am I missing something?

Thanks,

Shay.

\

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
JohnNowlin
Occasional Contributor II

Shay,

FYI, you are asking a question about ArcMap within a forum related to a different product, ArcGIS Explorer for Desktop, so you might get more answers if you posted in a forum about ArcGIS for Desktop (ArcMap is part of that suite).

Jayanta is correct, you cannot perform a one to many join in ArcMap.

What you are experiencing is not a flaw of ArcMap, it is a result of trying to perform a one-to-many join, meaning you have one geographic feature (your polygon), and you are attempting to join many records to each feature. ArcMap will just take the first record it comes to and this will be the only one showing in the table.

Other than using a relate rather than a join, here are some other paths you might consider.

Some other options would be to aggregate your data before joining as a one-to-one; in Excel you could create a pivot table such that the row should be the attribute you are joining to the polygon, then summarize the relevant information such as taking a count, the mean, or the sum of other attributes, then join these results to your polygon. This will be a one-tone relationship, because there is one row of information that you are joining to one polygon.

Another path which might work if your 6615 records have point based coordinates,plot them with their respective X,Y locations then do a spatial join, such that the polygon attributes are added to each record (point). This would be a many-to-one join.

Hope this helps,

j

View solution in original post

8 Replies
JayantaPoddar
MVP Esteemed Contributor

When you perform an attribute join, it's one-to-one or many-to-one relationship. To perform a one-to-many ralationship, try RELATE or RELATIONSHIP CLASS.

Deciding between relationship classes, relates, and joins—Help | ArcGIS for Desktop



Think Location
WesMiller
Regular Contributor III

If you are willing to move your data to a geodatabase you could use Make Query Table—Help | ArcGIS for Desktop

JohnNowlin
Occasional Contributor II

Shay,

FYI, you are asking a question about ArcMap within a forum related to a different product, ArcGIS Explorer for Desktop, so you might get more answers if you posted in a forum about ArcGIS for Desktop (ArcMap is part of that suite).

Jayanta is correct, you cannot perform a one to many join in ArcMap.

What you are experiencing is not a flaw of ArcMap, it is a result of trying to perform a one-to-many join, meaning you have one geographic feature (your polygon), and you are attempting to join many records to each feature. ArcMap will just take the first record it comes to and this will be the only one showing in the table.

Other than using a relate rather than a join, here are some other paths you might consider.

Some other options would be to aggregate your data before joining as a one-to-one; in Excel you could create a pivot table such that the row should be the attribute you are joining to the polygon, then summarize the relevant information such as taking a count, the mean, or the sum of other attributes, then join these results to your polygon. This will be a one-tone relationship, because there is one row of information that you are joining to one polygon.

Another path which might work if your 6615 records have point based coordinates,plot them with their respective X,Y locations then do a spatial join, such that the polygon attributes are added to each record (point). This would be a many-to-one join.

Hope this helps,

j

SvivaManager
Occasional Contributor II

Thank you everyone for the answers John - First thing, before posing this, I was looking for a desktop forum of course, but nothing relevant showed up really so my apologies but thanks it was very helpful.

When trying to move this thread to another forum I still don't see any ArcGIS Desktop related places.. any clue what am I missing?

D.png

0 Kudos
JohnNowlin
Occasional Contributor II

No worries, hopefully you got some reasonably understandable answers. ArcMap is a program within the product called “ArcGIS for Desktop,” the terminology has shifted over time for ESRI’s Desktop products, so really no one can blame you for not knowing where to go. The main forums don’t even use the product name(s) in the title, but these places should be a good resource for you. I think your question reasonably fits in any of them, but you would likely find the most easy to understand answers in new-to-gis . . . less jargon, more descriptive. . .

https://community.esri.com/community/gis/new-to-gis

https://community.esri.com/community/gis/mapping

https://community.esri.com/community/gis/analysis

https://community.esri.com/community/gis/managing-data

Best Regards,

John W. Nowlin

National Service Systems Developer | Stiles Machinery Inc.

AndrewMartinez4
New Contributor III

You could also perform a one-to-many join by using the "Make a Query Table"  tool just make sure to export the result because it is saved as a temporary file.

SvivaManager
Occasional Contributor II

I get the impression I'm not very "synced" with the way ArcMap works but the obvious result I would expect to have when doing a join with another table, is to actually see the outcome in the original layer - even if that's a one to many relation and means duplicating records for the time being as long as it's joined.. just feels more of a natural outcome to me.

0 Kudos
JohnNowlin
Occasional Contributor II

One last link ☺ this is a good jumping off point to a lot of ArcMap resources. . .

http://desktop.arcgis.com/en/arcmap/

John W. Nowlin

National Service Systems Developer | Stiles Machinery Inc.