Joining/Combining multiple tables & their values by Unique ID

Discussion created by SRTCadmin on Aug 27, 2012
Latest reply on Aug 30, 2012 by jmccallesri-ca-esridist
I have 10 jurisdictions' tables all with a unique identifier. Each table also has 30 fields in addition to the unique ID. The schema for all the tables is meant to be identical (field names, field types, etc.). In some cases, a unique ID (#41 for example) has values in several of the 10 jurisdictional tables. Some unique IDs have only on match (e.g. are only in 1 table).

I'm trying to join all these up into one table, with only one row for each unique ID, and the sum of all the 30 fields for each unique ID. ArcMaps join utility seems to struggle with so many joins at once; either way I'm left doing many many field calcs to get what I need. IT hasn't worked in ArcMap anyway I get black values (I don't think ArcMap likes 11 tables all together...)

I've been working through the merge and append GP tools however I'm getting multiple rows for each unique ID. I can solve it with a summary table on the unique ID. HOWEVER the main problem is once I run merge, and then summarize by unique ID and SUM all the 30 fields as part of the summary dialog, it appears all or most of the values are 10X what they should be. I've tried to adjust precision/scale although it sounds like in FGDB it doesn't matter. I've tried to work through it by calc'ing each field / 10 but it seems so odd... All tables are meant to have same schema, all 30 fields are double, unique ID is short.

Any help is greatly appreciated!

ArcGIS (ArcInfo, Advanced) 10.1 w/ latest SPs & Patches