AnsweredAssumed Answered

portal of a portal of a portal: managing nested join tables

Question asked by JamesPickett on Nov 14, 2012
Latest reply on Nov 15, 2012 by philmodjunk


portal of a portal of a portal: managing nested join tables


     I designed my database to have a table for Individuals, a table for Locations they might have traveled, and then a join-table linking the two of them.  Initially, my Locations table had fields for the name of the location, the province of the location, and the country of the location.  

     Subsequently I realized that it made more sense to use another join-table to link the Locations table back on itself.  E.g., if I have a location entry for Dallas I now use a join table to add "Texas" (which has a separate entry in the table) as the location's provincial designation rather than type out "Texas" and lose the accompanying data for the province-level entry. This seemed a more robust solution because occasionally locations were part of different provinces and countries historically, so I can add multiple entries for each (e.g. list both U.S.A. and Mexico for the province of Texas and even include accompanying chronological data in the join table).

     Now the problem: Previously, I could call up anyone who had been to Texas, for instance, since that information was captured in a field on the Locations table which also showed up in the portal on my Individuals table.  Now, however, that information is captured in a join table and hence no longer shows up in the Locations portal in my Individuals layout.

     Is there a solution to this problem to get the best of both worlds?  I imagine I am not the first one to run into the problem of telescoping join tables...

     Many thanks in advance.