How To Use One Table To Update Another Table?

[aytee111]

I can't see your first and third screen shots, the writing is too small. However, in looking at the screen shot in post 3, vendor 1082 is not a good example as it only has one dept. Vendor 1084 has multiple depts.

The question is how to determine which record to update. If a dept belongs to a class then you can say: change all records with class 821 from dept 88 to dept 78. This would not give the key violation error message you received, so there must be something else.

Try and find the problem with the original query and how those 14,xxx records are structured.
1 - take the update query and make it a make-table query - store all the fields from the source table
2 - if you can, add a field to the source table with updated y/n
3 - in the update query, update this new field to Y


4 - compare both tables - change the update query to a select query and find the missing records

From this you can determine why you are getting key violations. I suspect that the screen shots you have provided show the "good" records, the way it is supposed to be, but there are "bad" records out there causing the issue.

See if the source table is connected to another table in the database (relationships manager).

[aytee111]

Post a database containing 100 vendors with all their records, make source a local table (copy those same 100 vendors) - select those that need updating.

[kc27]

Thanks. I will have some time later today to work on this again. Earlier today, I went back over the notes from the person who developed and maintained these databases (no longer with the company), and he states that he would update the tables by doing a "Find Unmatched Query". Do you think I am over-thinking this, and he accomplished updating the destination table by doing the two steps below?

1. Create and run an unmatched query looking for records that did not have a match in the destination table, then change that query to an append query to add them to the destination table.

2. Do another unmatched query looking for records that did not have a match in the source table, and then change that query to a delete query to remove them from the destination table?

Results from this query would need to be added to destination table




Results from this query would need to be deleted from destination table

[aytee111]

No, this won't work. Take your data:
- vendor 1082, class 821, dept 88
- dept changes to 78
- unmatched append query adds ONE RECORD for dept 78
- unmatched delete query deletes TEN RECORDS for dept 88

[kc27]

OK, thanks for checking. I thought I was on to something.

[kc27]

1 - take the update query and make it a make-table query - store all the fields from the source table
Done

2 - if you can, add a field to the source table with updated y/n
Source table is a linked table I cannot modify. I exported it to Excel, then imported it as an Access table with new "Updated" field

3 - in the update query, update this new field to Y
Done

4 - compare both tables - change the update query to a select query and find the missing records
Need some clarification:
Open the "Make Table" query and revert it back to an update table then change it to a select query?
Join the updated source table and the new table from the "Make Table" query at the vendor and class fields?
What criteria am I using for the select query.

[aytee111]

I'm sorry, I made a mistake, I meant the destination table, to add the Updated flag. What I am trying to do here is find those 14,000 records. The first query will make a table of all the records to be updated, the total number of records in post #10 - 212490. Then you will run the update query against the destination table and only some of those records will be updated (212490 minus 14,610). Then you can see the original records on the make-table, compare those to the destination table, and all the ones that don't have the update flag set will be the 14,610. Then we can take a look at those records and try to determine WHY they weren't updated.

[kc27]

Not a problem. I do want to clarify your instructions from post number 6 so that I am getting accurate results.

One of the first rules of database normalization is that data is NEVER repeated - this problem you are having is exactly the reason why it should not be done. If you require it then a number of queries need to be created.

1 - join the two tables by vendor and class, update the dept on the second table if it is different
2 - create a unmatching query on the three fields and append the missing vendors to the second table - see here how to do that

Now you are left with orphan records in the second table, records that don't exist in the source. Create another unmatching query and delete those records.

It's the part in step one where you state "update the dept on the second table if it is different".

Below is the update query. How do I enter the "if it is different" criteria? Just by entering <> for criteria?

[aytee111]

Post #8 had it right.