I have 40 tables with about 2000 row with a shared ID that I want to merge into 1 table with 2000 rows. I only want to retain a distance field calculated within each table. The distance field is uniquely named Dist_miles_[name] with "[name]" as a unique value for each table. This way in the final table I will have: Key value, Dist_miles_[nameofAdvisor1], Dist_miles_[nameofAdvisor2], Dist_miles_[nameofAdvisor3]... Dist_miles_[nameofAdvisor40].
I would like to use iterate table and join field, and delete field to remove all the extraneous fields. However, the specificity of the variable I want to keep poses problems because I can't select it in the join field tool. I was hoping the join field tool would take a wildcard and be able to pull only the Dist_miles* into the final output table, but it doesn't.
Is there a workaround this?
Used Polygeo's tip to create modelbuilder. Works fine for single table. Tried to iterate on tables but doing that created an error at Calculate tool: "A field name was not found or there were unbalanced quotation marks. ERROR 999999: Error executing function. Failed to execute (Calculate Field)."