Append only if different

[krisch100]

Hi,

I have two tables, table1 keeps track of all history, table2 can be changed. I want to design a query so that I can append info. from table2 into table1 only if the record or row is entirely different.

For example: Table1 contains

ID Name DateTime Status $
1 A 1/1/05 1:00 Paid 10
2 B 1/3/05 1:00 Pending 20
3 B 1/3/05 3:00 Paid 20
4 C 1/5/05 4:00 Pending 10


Table2 contains

Name DateTime Status $
A 1/1/05 1:00 Paid 10
B 1/3/05 3:00 Paid 20
C 1/5/05 5:00 Paid 10
D 1/6/05 1:00 Pending 5


After the query, table1 should look

ID Name DateTime Status $
1 A 1/1/05 1:00 Paid 10
2 B 1/3/05 1:00 Pending 20
3 B 1/3/05 3:00 Paid 20
4 C 1/5/05 4:00 Pending 10
5 C 1/5/05 5:00 Paid 10
6 D 1/6/05 1:00 Pending 5


I don't want to use the update Query because I want to retain the historical data. I need the table2 to look like this because I can pull it up and only see the most recent data for each individual. To accomplish this, I made [Name] in the table2 the primary key and run a query to append data from table1 to table2.

Please help if you know how to append only different data. Or please let me know a simple way to do this.

Thanks.
Kris

 

[Gates Is Antichrist]

I'm not fluent on compound keys in Access, but a clunky but at least working approach would be to create a field that is a concatenation. E.g. if you had ID_Name_Date_Time you could insist on its uniquity and nothing would be overwritten in your history thereof.

If you're coming from Excel (considering the Board's name!), you can just add a column like F1=A1&B1&C1&D1 or such.

That's a "simple way" - I hope someone with Access key expertise jumps in to improve this. I highly suspect that a bona fide compound key is doable.

 

[Andrew Fergus]

Hello Kris and welcome to Mr Excel.

Following on from GIA's post, one way of doing this would be to make all of the fields in Table1 the key - in the table design, highlight the following fields : id, name, date, time and status and then click on the 'key' symbol on the toolbar. Now when you use the append query, only unique items can be appended into Table1. But I'm not a fan of multiple keys in this manner and a correctly constructed query would be the better solution.

Another way would be to create an append query that identifies the records in Table 2 that are not in Table1. Here is the SQL to do it this way instead :

INSERT INTO tbl1 ( name, [date], [time], status, [value] )
SELECT tbl2.name, tbl2.date, tbl2.time, tbl2.status, tbl2.value
FROM tbl2 LEFT JOIN tbl1 ON (tbl2.name = tbl1.name) AND (tbl2.date = tbl1.date) AND (tbl2.time = tbl1.time) AND (tbl2.status = tbl1.status)
WHERE (((tbl1.name) Is Null) AND ((tbl1.date) Is Null) AND ((tbl1.time) Is Null) AND ((tbl1.status) Is Null));

You can get into the query SQL via View -> SQL View but use your actual table and field names.

HTH, Andrew.