Create table by comparising 2 tables
I am new at Access but have some help with creating a db. I would like to know if it is possible to compare table 1: prior data and table 2: current data, and then create a table 3, with all the changes/deletions from the 1st table to the 2nd. All the data is workflow tasks that are dates. I need a new table that will show all the updated dates, new dates, and removed dates. is that possible?
Software/Hardware used:
Software/Hardware used:
ASKED:
November 6, 2008 4:38 AM
UPDATED:
November 6, 2008 3:08 PM
Answer Wiki:
Using SQL, you could write a query to get the differences between table1 and table2.
I don't remember the access syntax, so please make the necessary corrections.
It could be something like this:
<pre>select table2.*, 'CHANGED' as status
from table1, table2
<JOIN CONDITIONS>
where table1.field1 != table2.field1
or table1.field2 != table2 != field2
<or ...>
UNION
select table2.*, 'ADDED' as status
from table2
where <primary key fields> not in
(select <primary key fields>
from table1)
UNION
select table1.*, 'DELETED' as status
from table1
where <primary key fields> not in
(select <primary key fields>
from table2)</pre>
You could create a new table from the result of this query.
In access I think (but not sure) this can be achieved this way:
SELECT * INTO [NEW_TABLE]
FROM ...
Hope this helps.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
