I am trying to transfer over row data from a table to columns in a shapefile table. There is a common "SA" variable between the two (i.e. the study area number) but there is more than one land use type, hence why I must create two new columns for each landuse, in order to capture the area and percentage value for each type. A join won't work because it only captures the values for one land use as opposed to all of them, as the first table doesn't have as many entries as the second.
The reason I need to migrate the data is because I'm trying to use this in dynamic text on data driven pages.
I wrote a SQL script with the help of my friend but it won't execute through field calculator, giving the generic 99999 error:
UPDATE SApeelfinal SET LUp1 = (SELECT perc FROM stat_fin WHERE stat_fin.SA = SApeelfinal.FID_text and LANDUSE = 1) WHERE EXISTS (SELECT 1 FROM stat_fin WHERE stat_fin.SA = SApeelfinal.FID_text and LANDUSE = 1)
Though he said that I have to change the LANDUSE value for each time I run the script and as well change SUM_AREA and perc. I tried putting it in a toolbox but it wouldn't run that way either. If someone could enlighten me as to a better way to do this, I would appreciate it greatly. Also I was wondering if I had to manually create the new fields for this to work.
Here is a screenshot of the two tables:
You can also find a bigger version here: http://i.imgur.com/sx08u.jpg
The red is the common variable between the two tables, the blue is the field I want the percentage to go to (and there would obviously be a new one for area), and the green are the two fields I want to migrate over based on the SATXT and FID_text common fields.
Edit: I've tried using the following python script in the codeblock:
def getNewVal(newVal,landuse): if landuse == 1: return newVal else: return
With this in the calculate field:
But it returns the same error. Is there something I'm doing wrong?
edit: To clarify, there are no joins because joins make dynamic text not work.