How to update one table from another table

[jereece]

There's got to be a simple way to do this. I have a table with 3 key fields (Work Order Number, Work Order Task Number, and SE Code)and several other fileds. This data is located on a SQL Server. I periodically run a query to copy this data down locally. I have another table where I document an evaluation of each of the unique Work Orders. So in the second table I need the 3 key fields and a couple of other fields like Name, Date and Comments. My problem is I want to run a query to compare the first table to the second table and write the information for the 3 key fields if it's not already there. I tried using an Append Query, but it just keeps adding on. I can't use a Make Table Query because it will erase the information for the Work Orders I have already evaluated.

Anyone have any suggestions?

Thanks,
Jim

 

[819 Ag]

I think I understand what you need, but I need some clarification on why an append query does not work.

By using an append query, the new information in table 1 should be transferred to table 2 when it is run. Are you saying that all the records come over every time you run the append query? Do you just want the new records to come over to table 2?

In that case, you may want to run a Find Unmatched Query and use that query in an append query to only transfer the new records to the other table.

I hope this helps.

 

[jereece]

Yes, when I run an append query, I get say 100 records copied to the second table. If I run the append query again, I now have 200 records. You are correct in that I just want the new records to come over. You mentioned a Find Unmatched Query. I am not familiar with this. Can you describe how to set up or point me to a tutorial on these?

I appreciate your help.

Jim

 

[Pat Hartman]

Why not use a unique primary key made up of the three fields? This will prevent duplicates from being added.

 

[jereece]

If I was the owner of the original database I would have a unique key such as an autonumber, but I don't own the original database, so I am stuck with 3 key fields to identify unique entries.

Any other suggestions?

Thanks,
Jim

 

[jereece]

Never mind. I did a little research on the Find Unmatched Query and this will make it work. Thanks for the help and suggestions.

Jim