Nested SQL to Upate Record.

[only412c]

I am trying to find an efficient way to update record in an Access table from a Tempory table created in Access.

I wrote a sql statement in VB to append data to the permanent table in access from the temporary table created.

I want to establish a query to compare both the temporary table for duplicates based on claim number and only allow updates and new records to be store in the permanent table from the temporary table? Any way of doing this using SQL? or if not, is there another way? I have used the Select Distinct but it dumps the data I do not want, instead of the one that I want.

 

[BSman]

I have to make the following assumption based on the information you provided:

There is one (and only one) record in the permanent table for each claim.


If that is correct, then you would do something like this:

1. Create an update query that takes everything from your temporary table and just matches the claim number in your permanent table. The claim number in the permanent table must match the claim number in the temporary table. You then have the query update the appropriate columns in the permanent table where there is a match of the claim numbers in the two tables.

2. Modify your append query so that it also links the claim number in the temporary table to the claim number in the permanent table, but this time the link specifies that all records from the temporary table are returned and only matching records from the permanent table are returned. Then the query should have criteria for the claim number in the permanent table should be null. This way only the new records in the temporary table are returned. And the new records will then be appended to the permanent table.