How to update a field in one table from another (a backup)?

[sueb]

I'm not sure how this happened, but the current version of a table in my database has lost all the values in one field. The previous backup has the correct values.

How do I copy the correct values into the current table? I know I'd use an update query, but I've only written one, and I really don't want to screw up the table any more than it already is, so maybe someone could tell me exactly how to write it.

The field in question is called [OR Time In Room], and it's in a table named "DataTable". (Don't blame me--I inherited this!)

Thanks!

[pod]

they are a few quick queryless ways I can think of doing this, since I have done the same thing a few time.

...presuming you have the same number of records in both the backup table and the live one:

• and if only the one column's values differs between the backup and the live, why not replace the entire table with a copy of the backup.
• else you could simply select the entire column in the backup table, copy, then select the same column in the live table then paste.

[NeoPa]

1. Create a (temporary) linked table from your main (current) database to the table in your backup database. We'll call this [DataTableBup] for now.
2. Build a query, based on the main table ([DataTable]), within your main database and :
   1. Make it an Update query.
   2. Add the backup table ([DataTableBup]) to the query and connect the two tables with an INNER JOIN (Type 1 link) on the PK (Primary Unique Index).
   3. Set the value of the required field in the main table to that of the field in the backup table.
3. Run the query.

[sueb]

Thanks, NeoPa! I got as far as step 2.2, but I don't know the syntax to address the field in the backup table.

My query contains the field Number (this is like a case number and is unique) from both tables, and the "OR Time In Room" fields from both tables. Because I didn't know how to make a link between tables in different projects, I copied the backup table into my live project, so the tables are named "DataTable" and "DataTable copy (2)".

What do I type into the Update To cell?

[NeoPa]

I believe it would be [DataTable copy (2)].[OR Time In Room], but I normally work in SQL when working on something not in front of me. If you'd like to post the SQL then I can tell you the change more reliably (Not that I don't expect this to work - Just in case it doesn't). To see the SQL of the query simply select View SQL from the View menu. What is there is simple text and can be copied and pasted normally. Don't forget to use the CODE tags if posting code in here though ;-)

PS. To link tables in a database (so that you refer to the new linked table in your references, but the actual table is in a separate database, use File / Get External Data / Link Tables... This leaves you with a Linked Table in your database. For your use, copying the table in is a fine alternative approach. remember to delete the table when you're finished with it though, and to Compact/Repair your database after that. Otherwise it will not recover the internal space used for that data - even if the table is deleted.