Woody, Renee and other Listers, thanks for the responses. They all sort of cover ground I have been over. Most of the replies center around having to save/commit an intermediate query and reopening it during the session, or geocode the Excel file. This, I can do no problem. But its not a live link. :-(
I have a personal dislike of duplicating data unnecessarily. Why should I create an extra table, when the raw data contains all the information I need? I guess I'm lazy. If you give a job to a lazy man, he will try to find an easier way to do it. The whole thing can easily be done in MSAccess. Here is the SQL script after 2 minutes work... SELECT Parcel.ParcelID, Sum(Meter.Consumption) AS SumOfConsumption FROM Parcel INNER JOIN ((Owner INNER JOIN Meter ON Owner.Owner = Meter.Owner) INNER JOIN Terramatch ON Owner.Owner = Terramatch.Owner) ON Parcel.ParcelID = Terramatch.ParcelID GROUP BY Parcel.ParcelID; How can I replicate this in MI so it can be displayed graphically? I suspect the simple answer is no. We only have a couple of MI Pro6.5 licenses and MBasic 5.0. The plebs use Proviewer 6.5. If I can nut this problem out, I can extend the concept over our entire local govt rating database, making one very happy financial manager :-) If I have to bite the bullet, I'll script an MBX to do it the 'dumb' way but the entire purpose is to have a live link to the financial reports. -----Original Message----- From: Woody Woodruff [mailto:chartertwpunion@;voyager.net] Sent: Thursday, 7 November 2002 3:55 p.m. To: James Torrie; [EMAIL PROTECTED] Subject: RE: MI-L Linking Multiple Tables 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: 3978
