Updating tables 1

[tek1]

I have two tables. Lets just call the tables table 1 & Table 2. In both tables I have Cost Information. Table 1 has the most current cost information in it. Both Tables have unique identifiers in them. What I need to do is update table 2 with the cost information from table 1. I did not think I could link the tables because Table 2 has 9 more fields than table 1 and Table 1 has 35000 records and I only need to update Table 2's 2000 records.

 

[ghubbell]

Tek1:
Make a backup copy of both tables before you try this in the event you make a mistake.
There has to be a link to the tables somewhere, regardless of the number of fields a table has. If you can think of one (an ID number perhaps), open a new query in design view. Add both tables to the query. Drag the connecting field from T1 to its "mate" in T2. Now, double click on the line in between. This will popup the join dialog. Select "All records from T2 and only those..."
Drag or double-click the ID field from T2 to the "QBE" grid.
Do the same with your field from T2 that you want to update.
Again, the same from T1 for the ID field, and once more for the field with your new data from T1.
In the first line of criteria below T1's ID type in Not Is Null
Take a moment to look what we're doing: I want all the records from T2 (2000) and only their "mates" in T1 where the ID in T1 has a value. This is a handful but if we did it right you'll only see a maximum of 2000 records. If this is to your liking, Copy the entire column of new data from T1 and then select the similar column from T2, then Paste. Voila! your 2000 records should be updated. There are lots of ways to do this: "Append Queries", in Visual Basic Code etc., however there's nothing like being able to watch~see what's going on! I hope this gives you the results you want! Gord
ghubbell@total.net