Find missing items in a table join

3090
7
04-22-2015 12:32 PM
Highlighted
New Contributor

Hi Everyone,

I am curious, is there a simple way to find which values are missing when I do a table join? I am linking 1330 items from a table to a point shapefile but only 1273 are linked. I would like to know which ones were missed so that I can determine the error in the missed items. Thanks!!

Josh

Tags (1)
Reply
0 Kudos
7 Replies
Highlighted
Regular Contributor II

I sort one of the joined fields in ascending order.  Nulls are at the top.

Jeff Ward
Summit County, Utah
Reply
0 Kudos
Highlighted
Regular Contributor II

Or select by attributes where JoinedField IS NULL.

Jeff Ward
Summit County, Utah
Reply
0 Kudos
Highlighted
New Contributor

Thank you Jeff. Key is that i linked the larger table to the smaller one. I have always been doing it the other way around which doesn't help.

Reply
0 Kudos
Highlighted
Regular Contributor II

The above methods should still work.  Open the big table, run a select by attributes and select records where shapefile.FID IS NULL.  These would be records that have no matching feature in the shapefile.  That is, if when you set up the join you used the "Keep all records" in the join options.

Jeff Ward
Summit County, Utah
Reply
0 Kudos
Highlighted
Frequent Contributor

Joshua, the method Jeff is describing will work, but you need to reverse the join first, that is join the smaller table to the larger one, and you can then see which rows of the larger table has null entries for the point fields. As he said, make sure you keep all records in the join.

Reply
0 Kudos
Highlighted
Regular Contributor II

The methods I mentioned above will select the records in your source table that don't have a corresponding feature in your shapefile.  If you want to select features that don't have a matching record in the stand alone table I would use a relate.  Relate the standalone table to the shapefile, select all of the records in your table and click on the "Related Tables" button along the top of the table window -

RelatedTables.jpg

That's going to give you all of the features that match your table.  Reverse the selection and you have the features that don't match.

Jeff Ward
Summit County, Utah
Reply
0 Kudos
Highlighted
Regular Contributor II

You may have to remove your original join before you set up the relate.

Jeff Ward
Summit County, Utah
Reply
0 Kudos