Append or Update?

[bbeeching]

Please bear with me. I have existing information in a table that includes an auto number, description, and item number (among others) I need to update this table with item numbers generated outside the db. How do I loop through the recordset so that the correct item number is added to the correct description in the table. (the ID field is useless as it corrisponds with nothing)

I tried an Update query but that just deleted the test data. Any suggestion would be most helpful!

Thanks!
B

 

[Pat Hartman]

your update query should join the existing table with the "outside" table and update the existing table's Item number with the "outside" table's Item number for the matching record.

 

[bbeeching]

thats the rub .... there is no item number to relate it to... all I have to match it with is the description field.... I guess what I'm asking is how do I get the query to find the matching description in the main table and fill in the item number from the other table?

Thanks!
B

 

[Pat Hartman]

Then join on the description field. You MUST join on something unique or the update will not update the correct record.

Add the two tables to the QBE.
Draw the join line connecting the description fields.
Select the field you want to update from the main table.
Change the query type to Update.
In the UpdateTo cell, type the name of the field that holds the new value. Be sure to enclose the name in square brackets and include the table name to disambiguate it.

[tblExternal].[ItemNum]

 

[bbeeching]

Thanks Pat! I'll give it a go with the test data first, then go for it!

B