Compare two tables and Note Diff

[gloringg]

Hi,

I have three Tables in the MS Access database as below:

Company_Data

Name|Age|Department|Salary

User_Data

Name|Age|Department|Updated On

Changes_Data

Name|Age|Department|Salary|Updated on

and i have a compare button on the VB form.
Is there any code that can compare the Company_Data with User_Data and copy all the records not found in User_Data to Changes_Data table.
When updating it should update the "updated on" Column to the current Date.

Same needs to be done in the opposite way. Compare User_Data with Company_Data and note the diff.
No Need to update Salary in this case.

Please help me with this.

Thanks.

 

[cjard]

In ORACLE I can do

INSERT INTO Changes_Data
SELECT * FROM Company_Data
INNER JOIN
(
SELECT [key fields] FROM Company_Data
MINUS
SELECT [key fields] FROM User_Data
)find
ON [key fields]

Or:

INSERT INTO Changes_Data
SELECT cd.* FROM 
Company_Data cd
LEFT OUTER JOIN
User_data
ON
  [key fields join condition]
WHERE
  User_Data.[any key field] IS NULL

In access this probably translates to:

SELECT cd.* INTO Changes_Data
FROM 
Company_Data cd
LEFT OUTER JOIN
User_data
ON
  [key fields join condition]
WHERE
  User_Data.[any key field] IS NULL

Naturally, your [key fields join condition] is the field or set of fields that are supposed to be equal in each row for you to determine that the rows are equal. e.g. if Name is the key field:

SELECT cd.* INTO Changes_Data
FROM 
Company_Data cd
LEFT OUTER JOIN
User_data ud
ON
  cd.Name = ud.Name
WHERE
  ud.Name IS NULL

Do you understand how this works? The query only returns rows that do not join to another row. By definition this means these rows have no matching row in the other table


Lastly, can I request that you post your SQL queries in the proper forum in future. This forum is actually for reading files queries, though a lot of ADO.Net queries end up here. THe database specific forums are much better for SQL questions