ArcMap Inner Join Behavior

Discussion created by schunzel on Nov 30, 2011
I have a question about ArcMap join behavior as I'm getting unexpected results.  To my understanding if one joins a table with many matching records for a foreign key, ArcMap will select the first record match to join and discard the others.  My problem is that ArcMap continues to acknowledge the left over records with a definition query or even a selection query.

For example, I am trying to map snow removal actions from a log produced by our field crew.  One of the fields on the log is the road condition at a certain time.  I want to be able to map the last road condition reported.  I would think this could be accomplished with an inner join, which keeps only matching records.  The join results confirm this after the join and the map is as expected.  The problem occurs when I use a definition query specifying a specific value, like "Road Closed", after which ArcMap selects all records in the log, not just the first match.  The same result occurs with a selection query.

Am I missing something or is this expected behavior in ArcMap?  Or does anyone have another solution?  Both the log table and feature class I'm joining to are in a Personal Geodatabase (.mdb).

Thank you,