Find missing items in a table join

3478
7
04-22-2015 12:32 PM
JoshuaMuller
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)
0 Kudos
7 Replies
JeffWard
Occasional Contributor III

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

Jeff Ward
Summit County, Utah
0 Kudos
JeffWard
Occasional Contributor III

Or select by attributes where JoinedField IS NULL.

Jeff Ward
Summit County, Utah
0 Kudos
JoshuaMuller
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.

0 Kudos
JeffWard
Occasional Contributor III

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
0 Kudos
SepheFox
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.

0 Kudos
JeffWard
Occasional Contributor III

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
0 Kudos
JeffWard
Occasional Contributor III

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

Jeff Ward
Summit County, Utah
0 Kudos