First, regarding the joins - you have two tables, TaxParcel and Assessor, and you join them on ParcelNumber. The Assessor table has a BTREEDUP index on the join field, the TaxParcel table has no indexes on that field. The $100 question - should ParcelNumber be unique both in TaxParcel and in Assessor? Can you have more than one TaxParcel with the same ParcelNumber? Can you have more than one Assessor with the same ParcelNumber? If the answers to both these questions are no, then you can just add a unique index to each table and this is going to make the relation 1:1 and the result table of the join will be editable. Eg, if you do this: --SQL9 ALTER TABLE [TaxParcels Table] ( ADD INDEX [ParcelNumb_x] BTREE ([ParcelNumb]) ); ALTER TABLE [Assessor] ( DROP INDEX [Index], ADD INDEX [ParcelNumb_x] BTREE ([ParcelNumber]) ); ...then this produces a table with a unique index, pickable and selectable and editable, you can link it as a drawing and use with no further tricks: --SQL9 SELECT P.*, A.* FROM [TaxParcels] as P LEFT JOIN [Assessor] as A ON P.[ParcelNumb] = A.[ParcelNumber]; If there are multiple assessors for the same ParcelNumber, then yes, when you click into a TaxParcel you cannot see all of the matching records in Assessor, because there might be several of them and the Record pane does not traverse 1:N relationships. But maybe you don't want to see all of the matching records, and just want to see how many Assessors the clicked TaxParcel has or the AccountNumber of the first such Assessor. If so, that can be done by rewriting the query and using a different type of join and a group. On to the queries that create copies of data - the 'Delete records and insert new records' query should probably look like this: --SQL9 DELETE FROM Temp; INSERT INTO Temp ( [mfd_id], [ParcelNumb], [Geom], [mfd_id 2], [ParcelNumber], [AccountNumber] ) SELECT P.*, A.* FROM [TaxParcels] as P LEFT JOIN [Assessor] as A ON P.[ParcelNumb] = A.[ParcelNumber]; You could also DROP TABLE Temp; SELECT ... INTO Temp ...; but DELETE / INSERT is better because it lets you keep formatting, coordinate systems, etc, which DROP / SELECT INTO would erase. The list of fields in the SELECT is best expanded from SELECT P.*, A.* into citing specific fields, otherwise if you add some fields to TaxParcels or Assessor, or rename them, the list of fields in the SELECT will stop matching the list of fields in INSERT and the query will fail. Regarding closing and reopening the MAP file producing different results depending on whether you exit the application in the middle or not - this is a little weird, and the only thing that comes to mind is that there is something else holding the MAP file opened (eg, a failed attempt at linking) and that an attempt to save from within the application actually failed, and the file was kept in memory, and after you exited the application, the stray bit of code holding the MAP file had to yield and you got to see that the save indeed failed. If you can ever reproduce it, please contact tech support, and also keep an eye on the log window - it might contain some clues. In any case, it is good that a failed save did not result in a loss of data beyond what failed to save, and old data was kept protected.
|