Access Append To Query - Creating duplicates not unique

[user9]

I am trying to append records to a table where there are three fields: a. The values are the same in tables 1 and 2 b. If there is an updated date in table 1, append all three fields to table 2 c. All records in table 1 field 3 have the same values (the most recent disc date -10/1/2013), as so the records in table 2 field 3 (i.e. 9/1/2013) For example - the value in field 1 is "ABC" with a last register date of 8/15/2013 (field 2) and last disc date of 9/1/2013 (field 3). If on the most recent disc the last register date changed to 9/15/2013 with a disc date of 10/1/2013, I want to append that record to table 2 (not overwrite).

[Dal Jeanis]

Okay, so table 1 is your current data, and table 2 is historical data. There's no particular unique key on table 2.

However, it does not make sense when you say "as so the records in table 2 field 3 (i.e. 9/1/2013)". They can't all have the same date in field 3 if you're appending, not overwriting.

Please mock up some data (at least five records each, some that match, some that don't) of table 1, table 2 (before update) and table 2 (after update) how you want the resulting updates to be done.

Use the word "code" within square braces [] before your table data and /code in square braces after your table data, so that the forum will not squish together your layout.

[user9]

Okay, so table 1 is your current data, and table 2 is historical data. There's no particular unique key on table 2.

However, it does not make sense when you say "as so the records in table 2 field 3 (i.e. 9/1/2013)". They can't all have the same date in field 3 if you're appending, not overwriting.

Please mock up some data (at least five records each, some that match, some that don't) of table 1, table 2 (before update) and table 2 (after update) how you want the resulting updates to be done.

Use the word "code" within square braces [] before your table data and /code in square braces after your table data, so that the forum will not squish together your layout.

Thank you for the reply! All records in table 1/field 3 have the same disc date (this is the new data). Additionally, all records in table 2/field 3 have the same disc date (this is the previous disc). I want to append records that have more recent dates in field 2 but leave alone records that do not have updated values in field 2.

I will add some mock data as you suggest as this should clarify.

[user9]

Table 1 (new data)
Table 2 (historical data before update)
Table 2 (historical data proposed after update)


Unique ID
Last Completed
Disc Date
Unique ID
Last Completed
Disc Date
Unique ID
Last Completed
Disc Date

Example 1
100
09/15/2013
10/01/2013
100
08/15/2013
09/01/2013
100
08/15/2013
09/01/2013








100
09/15/2013
10/01/2013

Example 2
200
08/15/2013
10/01/2013
200
08/15/2013
09/01/2013
200
08/15/2013
09/01/2013

Example 3
300
09/15/2013
10/01/2013
(no record)
300
09/15/2013
10/01/2013

Example 4
400
09/15/2013
10/01/2013
400
07/15/2013
08/01/2013
400
07/15/2013
08/01/2013





400
08/15/2013
09/01/2013
400
08/15/2013
09/01/2013








400
09/15/2013
10/01/2013

Example 5
500
09/15/2013
10/01/2013
500
11/15/2010
12/01/2010
500
11/15/2010
12/01/2010








500
09/15/2013
10/01/2013

[Dal Jeanis]

Looks fairly basic.

In a test copy of your database, try this. First, of course, modify the SQL to use your table names and field names.

IQUERY1:
INSERT INTO histTable (UniqueID, LastDate, DiscDate)
SELECT NT.UniqueID, NT.LastDate, NT.DiscDate
FROM NewTable AS NT
WHERE NT.UniqueID & NT.LastDate NOT IN
   (SELECT HT.UniqueID & HT.LastDate 
      FROM histTable AS HT);

[user9]

Looks fairly basic.

In a test copy of your database, try this. First, of course, modify the SQL to use your table names and field names.

IQUERY1:
INSERT INTO histTable (UniqueID, LastDate, DiscDate)
SELECT NT.UniqueID, NT.LastDate, NT.DiscDate
FROM NewTable AS NT
WHERE NT.UniqueID & NT.LastDate NOT IN
   (SELECT HT.UniqueID & HT.LastDate 
      FROM histTable AS HT);

The logic here makes sense, though the query is taking so long to run that it needs to be canceled. I have HT.UniqueID & HT.LastDate set as primary keys (and NT.UniqueID as well) with a join from NT.UniqueID to HT.UniqueID. While HT is a linked table to an Oracle database, it shouldn't be taking as long as it is. Suggestions welcome!

[Dal Jeanis]

Create a copy of HistTable called TempHistTable, copy structure only, no data, then try these three in order;

IQUERY1D:
DELETE FROM TempHistTable;

IQUERY1T:
INSERT INTO TempHistTable (UniqueID, LastDate, DiscDate)
SELECT NT.UniqueID, NT.LastDate, NT.DiscDate
FROM NewTable AS NT
WHERE NT.UniqueID & NT.LastDate NOT IN
   (SELECT HT.UniqueID & HT.LastDate 
      FROM histTable AS HT);

IQUERY1U:
INSERT INTO HistTable (UniqueID, LastDate, DiscDate)
SELECT TT.UniqueID, TT.LastDate, TT.DiscDate
FROM TempHistTable AS TT;

That should be a bit faster than trying to update the same table you're selecting from.