Join and HIstorical Data 1 - Many relationship

[JayKappy]

I have a base set of data, Street Centerlines.
I have a historical table that has multiple entries per street

These two are related by a unique field.
In the Historical table I have a Year field

What I want to do is join the two and populate a Year Field in the Street Centerlines...BUT i need the most recent year from the Historical Table.
I can do this with a join but I am sometimes getting the Older Year and Sometimes getting the Newest Year.

Where a particular entry in the historical table may match to Street "4534" but have 3 records with 3 different years.

Street Centerlines
'4534'

Historical Table
'4534'  2002
'4534'  2005
'4534'  2010

How do I do this and make sure that I get the most recent year in all the recrods?

That make sense?
Thanks

[JoeBorgione · Accepted solution]

I have a base set of data, Street Centerlines.
I have a historical table that has multiple entries per street

These two are related by a unique field.
In the Historical table I have a Year field

What I want to do is join the two and populate a Year Field in the Street Centerlines...BUT i need the most recent year from the Historical Table.
I can do this with a join but I am sometimes getting the Older Year and Sometimes getting the Newest Year.

Where a particular entry in the historical table may match to Street "4534" but have 3 records with 3 different years.

Street Centerlines
'4534'

Historical Table
'4534'  2002
'4534'  2005
'4534'  2010

How do I do this and make sure that I get the most recent year in all the recrods?

That make sense?
Thanks

Nice problem to solve! 

The first step I would take is to create a summary table for the unique id and get the 'maximum' year for each id.  You'll get a count of the record ids, along with the maximum (most recent) year

In the second step, create a relation between the summary table and the historical data table based on the MaxYear attribute in the summary table and the Year attribute in the historical data.

Select ALL the records in the summary table; then choose related tables.  This selects just those records in your historical data table with the most recent (maximum) year, regardless of id.  Write those selected records to it's own table; let's call it History2.

Finally, you can then JOIN your current data to History2 via the unique ID.

Give this a spin and see if it does the job for you...

[JayKappy]

.....................................................................

[JayKappy]

Think I got it...almost there.

[JayKappy]

Yes that worked....worked great...

This is what I acutally did

I went into Access and Created a new table and did basically what you said to do in step 1
That gave me a list of unique records with the oldest year associated with it.
I then took this table and simply joined (unique ID) it the actual Feature Class I was trying to update
Then exported to new FC and simply calcualted the year into the appropriate field

SLight twist of what you recommened but worked no the less....

But THANK YOU for your thoughts on the table creation in Access to get the unique records with oldest Year.  This was the critical part and the part I was racking my brain against.

Cheers...

[JoeBorgione]

Creating a summary table is pretty easy within a personal or file geodatabase as well as setting up the relationship in ArcMap.  Glad it worked for you!

[JayKappy]

How do you create a Summary table in ArcGIS?

[JoeBorgione]

Short answer; right click on the field of choice and select summarize.

Longer answer(s): here and here