I'm trying to use a field in my shapefile with summarized zip code data. I have a table of hospital admissions data and in this table there is a field of zip codes which are repeated multiple times. What I'm trying to do is join this field of zip codes to my existing US zip codes boundaries shapefile, which mentions the zip codes only once. So the relationship is one:many.

I realize I must summarize the field on my table and then join it to the shapefile that way, but I don't want to lose all of the other data. In lieu of doing just a relationship between the shapefile and my data table, I'd like to do a join so that I can display the data in various ways. Is there a way to do this? Here is a picture of my tables:



If you are intending to do analysis with this data consider setting up a RELATE, this will help you pass selections from one table to another, eg. you select the polygon on screen and then run the relate that passes the selection to the hospital admissions table. So one polygon selecting many admission rows.

If you intend to summarize and display this summarized data you could summarize the admissions table setting zip code to be the Case field. Note you can add more than one field to group by. But it may be easier (from a workflow perspective and others may disagree here) to summarize a single column whilst grouping by zip code, so everything is clear cut when you eventually join the table.

  • Thanks so much for your advice.. Must admit I am a newbie and don't know quite how to group by zip code while summarizing. Could you lend me some more sage advice?? I truly appreciate your help :)
    – Desi Sage
    Jun 27 '16 at 14:58
  • Nevermind that last silly question - Got that figured out! However ---→ Have another question. Do I have to just do this for every attribute field and have the zip code be the case field each time to have a multitude of tables that I can join to each other on one specific zip code field and then join that table to my shapefile? I have columns as such--hospital name, hospital county, member county, member zip code, and admissions count. I think it would be fantastic if the end user could see how many people from one county went to which hospital.
    – Desi Sage
    Jun 27 '16 at 16:14
  • It's entirely up to you. For example you could sum number of people by zip code then member count by adding another case field (if that is actually a sensible thing, I don't know your dataset). Best thing to do is have a play, if you write to IN_MEMORY then the tables are temporary rather than filling up your hard drive with test data, it's what I do when I'm trying to figure out what is best. If you don't know what an in_memory workspace is, you need to read the help file. Good luck.
    – Hornbydd
    Jun 27 '16 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.