APPEND Query for changed record

[dben901]

My project is working with 2 tables in a form.


OwnerTable1 is a large file with all properties that is periodically updated and used as a reference file.
OwnerTable2 is a much smaller file that contains a number of properties that require monitoring and followup inspections. These properties can remain active through more than one owner.

As activities for a property occurr I want to verify if an owner has changed to a property from a form.

My thought is to link an Append query to a command button.
Once executed the Append query would go to OwnerTable1 (reference file) to see if the field "Owner's Name" has changed from OwnerTable2 and if it has then append the changed record to OwnerTable2. This will give me a history of ownership in OwnerTable2.

If the ownership has not changed the a message box would open that the "Ownership is Current".

If the ownerhship has changed a message box would state: "The Ownership has been updated."

Any assistance in this project would be appreciated.


DB

[June7]

A query cannot 'check' if anything has changed. APPEND (INSERT) sql action does not modify existing data. UPDATE sql action modifies existing data.

If tables are properly related then this is entirely unnecessary.

If you want to provide db for analysis, follow instructions at bottom of my post.

[dben901]

Thank you.

I have attached the file.

My intention was for the query to have a criteria where if the owner name is different in Table1 from Table2 the record from Table is appended to Table2.

The purpose of the database is to keep track of vacant homes and make sure that repairs are made and the grass is cut.
It will eventually have inspections and code violations with letter reports to the owner or responsible party.

Thank you.

[June7]

Can do a 'find unmatched' query and append the records from Table1 not already in Table2. But that is not modifying records already in Table2.

http://www.tek-tips.com/viewthread.cfm?qid=1240925

Which two tables are involved?

[dben901]

The unmatched query sounds like what I need to use.
I do not want to modify the existing record in Table2 (Owners table in the project) rather i want to append the changed/unmatched record in Table1 (TaxFile table in the project) to Table2.

I will try the information in the link you provided.

Thank you.

[dben901]

After carefully looking at the link provided it seems this example is the inverse of what I am trying to do.

In my instance I have an identical table structure for two tables Table1 (TaxFile table in the project)
Table2 (Owners table in the project) each with a key field GISID. I want to match the records from each table with the identical GISID then Append the record from Table1 to Table2 if the "Owner's Name" is not a match.

Table1 (TaxFile table in the project) Table2 (Owners table in the project) ACTION
FIELD: "Owner's Name" FIELD: "Owner's Name"

DATA1a: SMITH, JOHN DATA2a: SMITH, JOHN As a result of a match an
append action does not occur

DATA1b: SMITH, JOHN DATA2b: JONES, JOHN PAUL As a result of a mismatch an
append action occurs and adds
the changed record to Table2

Thank you.

[dben901]

The system formatting changed this. Let me try again.

After carefully looking at the link provided it seems this example is the inverse of what I am trying to do.

In my instance I have an identical table structure for two tables Table1 (TaxFile table in the project)
Table2 (Owners table in the project) each with a key field GISID. I want to match the records from each table with the identical GISID then Append the record from Table1 to Table2 if the "Owner's Name" is not a match.

Table1 (TaxFile table in the project)---> Table2 (Owners table in the project)--> ACTION
FIELD: "Owner's Name"-----------------> FIELD: "Owner's Name"

DATA1a: SMITH, JOHN-----------------> DATA2a: SMITH, JOHN---------------> As a result of a match an
-----------------------------------------------------------------------------> append action does not
-----------------------------------------------------------------------------> occur

DATA1b: SMITH, JOHN-----------------> DATA2b: JONES, JOHN PAUL----------> As a result of a mismatch
-----------------------------------------------------------------------------> an append action occurs
-----------------------------------------------------------------------------> and adds the changed
-----------------------------------------------------------------------------> record to Table2

Thank you.

[June7]

Something weird about your tables. Either of the following queries should work but they just trigger error message and won't append any records.

INSERT INTO Owners (GISID, OwnerLName, OwnerFName)
SELECT TaxFile.GISID, TaxFile.OwnerLName, TaxFile.OwnerFName
FROM TaxFile;

INSERT INTO Owners(GISID, OwnerLName, OwnerFName) SELECT TaxFile.GISID, TaxFile.OwnerLName, TaxFile.OwnerFName
FROM TaxFile LEFT JOIN Owners ON (TaxFile.OwnerFName = Owners.OwnerFName) AND (TaxFile.OwnerLName = Owners.OwnerLName)
WHERE (((Owners.[Owner's Name]) Is Null));

When I do this query I get one record appended.
INSERT INTO Owners (ID, GISID, OwnerLName, OwnerFName)
SELECT ID, GISID, OwnerLName, OwnerFName
FROM TaxFile;

[dben901]

After thinking this over as a result of your response it has occurred to me that my approach may not have been correct.
I was unable to resolve the error messages as well and believe it is because of duplicate ID values.

It seems to me I should have a three stage process.

1. For a new property, enter the base information necessary for the VProperty table.
2. Once this information is entered then a button is clicked to add all of the detailed owner inbformation from the TaxFile table.
3. Another button is clicked to verify and maintain the VProperty table, if there are changes in the TaxFile table for the "OWNER'S NAME" field the changed record is appended if the property was sold and this would provide an ownership history.

This seems to make the most sense to me.
Am I correct?

[June7]

Duplicate ID values occurred to me as well and I checked the tables and none of the fields involved in the queries were set as No Duplicates. I don't understand why these SQL actions fail. I also thought cause could be table relationships. So I removed them but that didn't help. You have VProperty, TaxFile, Owners all related by the common GISID. GISID is not primary key in any table.

Don't understand your data structure. Re item 2: why is data duplicated between Owners and TaxFile? Re item 3: there is no owner info in VProperty table.

How would you accomplish 2 and 3 - SQL action? The INSERT SELECT SQL actions not working. Not even a simple INSERT will work:

INSERT INTO Owners(GISID, OwnerLName) VALUES("1_1_", "Jones");

There is something critically wrong with this db and I can't pinpoint it.