When you said MI only performs joins on base tables, you hit the nail on the head. You have to take your OwnerMeterLink table and perform a Save Copy As. I suggest that you alter the name of the Save Copy As table to distinguish it from the named query created by the Into Table Named part of the first SQL. Close the query formed table and open the table you just created, then run the second SQL. Love MI, but its kindda clunky when you get to relational stuff. You may want to set up Access as a front end in cases like this. The problem here is that you lose the functionality of the OwnerMeterLink table refreshing itself every time you open the work space, so you'll have to do the Save Copy As every time, be sure to delete old tables first, cuz renaming is required instead of overwriting when you do this.
Another approach maybe to map the Excel spreadsheet using the same linking fields, then do a Point thematic on the mapped symbols instead of a region thematic. I assume you will want to do this by ranges of consumption. I am a little under informed here about table structure, but it seems to me that there must be locational information in the table that holds the consumption values, either by PID or address, or ???, so I would try to geocode that table. I just re-read you PS, kindda takes the wind of this sail. But really, geocoding every three months doesn't seem like a big deal, and there's nothing like finding and correcting a few unmatched records to keep your data straight. William "Woody" Woodruff Zoning Administrator Charter Township of Union, Isabella County, Michigan (989) 772 4600 EXT 41 Visit our web site at http://www.geocities.com/ctuzoning/index.htm -----Original Message----- From: James Torrie [mailto:jamest@;rangdc.govt.nz] Sent: Wednesday, November 06, 2002 7:25 PM To: [EMAIL PROTECTED] Subject: MI-L Linking Multiple Tables Dear Listers I am trying to thematically map water usage by land parcel using three tables. The three tables are... Parcel.tab is the land parcel polygons. Owner.tab are points with contact details. A parcel may have multiple owners, and multiple parcels may belong to one owner.IE Many to Many relationship. WaterMeter.tab is an unmapped Excel spreadsheet listing each Owner's KeyID and quarterly water consumption data. An owner may have more than one meter and a meter may supply more than one parcel. If I run this SQL statement... Select * >From OwnerDetails,WaterMeter Where Owner.KeyID = WaterMeter.KeyID into Table Named OwnerMeterLink I get a table with all fields from the two tables. Great! The problem is the next step. Select Sum(WaterMeter.Q1Units),Owner.KeyID >From Parcel, OwnerMeterLink where Parcel.obj contains OwnerMeterLink.obj MI only allows dynamic joins between base tables. How can I set up my workspace to dynamically link the Watermeter table to the parcel table? The end result I am trying to achieve is to show the sum of the water used by all meters associated with each parcel. James Torrie GIS Officer Rangitikei District Council New Zealand PS I dont want to have to geocode the excel spreadsheet every 3 months (if a meter supplies two parcels, only one parcel will recieve a point label), and I musn't add extra fields to the tables. --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 3961 --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 3969
