This is a tricky one...

[Tanya556]

In Access 97, I have a table that is link and it gets updated nightly. This table is an Items table with no Unique ID (Not by choice). I have created a table with identicale fields except "ItemID" field. Anyone know how I can "update" table2 from table1's data. For example, lets say table1 has 500 rows and table2 has 500 rows that are identical, except table2 has a unique id. Say the next day table1 has 510 rows and table2 has 500 rows. How can I update table2 to insert the 10 rows it does not have? Since you all know that I cannot modify a linked table, and I need to create relationship with this data, I only have this option. I know this sounds far fetch, but I don't have much to work with. I greatly appreciate your suggestions. Thank you for your time.

 

[Blorf]

Does the linked datasource change, or is data just added?

If it is just added, then using a record number may work. IE you have 500 in your table, the linked source has 510, so you would only add records 501 - 510.

If this is the case, can number them by creating a blank table with an autonumber field. With each update, you would copy that blank table to a work table (So it always starts with 1), and append your linked data to it. Then, the autonumber field can be used as a guide.

Hope this helps.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[MakeItSo]

You could also use one or more of the other fields as identifier.
let's say you have the fields [lastname] and [firstname] in both tables, identical over all records except for the ten new ones. Then this would do the trick:

INSERT INTO Table2
SELECT a.* FROM
Table1 a LEFT JOIN Table2 b 
ON a.firstname=b.firstname AND a.lastname=b.lastname
WHERE b.lastname IS NULL

This will select all records from Table1, but only those from Table2 matching, therefore all new fields in table1 will be joined to NULL in Table2...
;-)

Hope this helps,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de

 

[Tanya556]

Thank you very much for responding to my issue. I created a statement similar to how Andy suggested. It worked but the table has so many rows it took a good 30 minutes to append. What I found out, and It works much faster was using the Append Query feature in Access. I had to add a new field to both Tables "Process_Date" and use the following statement...

INSERT INTO Table2( Process_Date )
SELECT Table1.*, Table1.Process_Date
FROM Table1
WHERE (((Table1.Process_Date)=Date()));

Thanks again for your help.