To join tables using multiple fields to create a unique sequence where an entity say a parcel can beunique identified according to more than one criteria as shown below. This could minimize data redundancy in tables and editing time
If the feature classes/tables are in the same geodatabase or directory you can use the Make Query Table tool to do this. http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00170000006r000000 It only supports an inner join and is not editable without exporting it, but it can do this type of join and handle all relationship types (one-to-one, many-to-one, one-to-many, and many-to-many). MS Access is faster and supports outer joins, so if you can use a personal geodatabase you can get the result faster in that program if the data is only tabular. But the Make Query Tool creates features as well as table rows, so it is better than Access in that situation. Even so, you would be better off using the Make Query Table tool as part of a process for converting the multifield value to a single field join value to get better performance. Even MS Access performs better and offers more options with single field relationships than with multi-field relationships.
If we want to edit a table while filtering based on a multi-field join to a table, we could use a subquery and EXISTS:
(in either a Definition Query or the Select By Attributes window)
exists (
select
null
from
related_table
where
--this is the equivalent to a multi-field join: main_table.event_id = related_table.event_id and main_table.year_ = related_table.year_
)
The downside is that the multi-field join via the subquery only filters the rows. We can't use it display columns in the Attribute Table from the related table.
Alternatively, if we want to display columns in the Attribute Table from the related table, we could create a database view that includes the data from both the main table and the related table. And do a multi-field join via SQL in the view. And either use/view it as-is in ArcGIS (read-only). Or in ArcGIS, join it to original table and edit the table -- while being able to see the columns from the related table.
