Select Data then Populate

[Cirrus]

Hi Everyone,
I have what seems to be a fairly simple problem but I cannot figure out how to solve it.
My question is quite long so I have had to place it in a zipped word document that is attached.
Thanks in advance and please contact me if more information is required.

 

[Steve C]

Sorry, can't seem to read your attachment - post the first three paragraphs - that'll be enough to get started

 

[Cirrus]

[FONT=&quot]Background: I have an ArcGIS spatial dataset that contained a large number of "unknown ag" values. Using the polygon neighbor tool in ArcGIS I was able to create a table that identified the adjacent polygons to the "unknown ag" polygons. The "unknown ag" polygons were adjacent to many other "known" polygons and the resultant table has multiple rows of "known" data associated with each "unknown ag" polygon.
I want to identify the "known" data that has the largest area and associate a corresponding classification (which will be either "perennial crop" or "annual crop" and is in the same row of data as the known area) to the "unknown" record.
Reattached word document and a screen shot.
[/FONT]

 

[Cirrus]

here is the rest of my question;
[FONT=&quot]Here is a screen shot of my table:
Where:[/FONT]

• [FONT=&quot]src_ALCES contains my "unknown ag" value[/FONT]
• [FONT=&quot]nbr_ALCES contains the value that I want to update src_ALCES with (or populate a new added field)[/FONT]
• [FONT=&quot]src_AG_ID contains the unique identifier for the src_ALCES field[/FONT]
• [FONT=&quot]nbr_Shape_ contains the area that I want to use to determine which nbr_ALCES value should be used to update src_ALCES[/FONT]

[FONT=&quot]If you look at the highlighted rows of data you will notice that src_AG_ID 18 has 6 adjacent polygons (which are shown as 6 rows of data). I want to be able to pick out the largest area in nbr_shape_ (57470.766) and associate the corresponding nbr_ALCES value (Perennial Crop) to src_AG_ID 18 (either by updating src_ALCES or by adding a new field with is then updated).

So basically, I don't know how to create a query that will select the largest area for a given src_AG_ID and then return the corresponding nbr_ALCES value.

[/FONT]

 

[CJ_London]

So basically, I don't know how to create a query that will select the largest area for a given src_AG_ID and then return the corresponding nbr_ALCES value

The following query will do this

SELECT src_AG_ID, nbr_ALCES 
FROM Unkn_Ag 
WHERE nbr_Shape_=(SELECT MAX(nbr_Shape_) FROM Unkn_Ag AS Tmp WHERE src_AG_ID=Unkn_Ag.src_AG_ID)

 

[Cirrus]

CJ_London - thanks so much! Worked great!

 

[CJ_London]

Always helps when posters provide some decent information!