Problem: Unable to get all records to join although all values in the two join fields match up
When joining tables with the option to keep all records, if all values in a field in one table match with all values in a field of another table, all records are included in the joined table. However, in some cases, when joining such tables, only some records are included in the joined table.
This may caused by leading spaces, preceding spaces, or both in the values of one or both of the join fields.
Solution or Workaround
Remove the leading or preceding spaces from the values in the relevant fields before joining the fields.
- In the attribute table of the feature, right-click the header of the field that is used for joining, and click Field Calculator.
- Use the following Python expression on the relevant join field to remove all the hanging spaces.
Note: Change fieldname to the user's respective field name.
- Use the following Python expression to remove any preceding spaces from the values in the join field.
Note: Apply these steps on both join inputs to ensure that hanging or preceding spaces are not hindering a proper join, and proceed with the join operation.
- ArcGIS Help: Calculate Field examples
- FAQ: Is it possible to identify mismatched records for a join operation?