lroyce

I am trying to figure out the easiest way to copy values from one table to another. I have two tables in an ms access database: Hardware and Archive
Right now, when Hardware is returned from a shipment, the transaction number is set to "0" to indicate the item is back in stock. Before that takes place, I would like to write the transaction number (transaction_no) and the hardware id (key_id) to the archive table as a cross reference so users can see what hardware was associated with which transaction.
Is there a way to select the transaction_no and key_id from Hardware and write those values to fields with the same names in the Archive table?
I have been trying to find an example somewhere. I can't believe I am the only person ever to have this problem
Thanks in advance for any help

00100b

The first piece that you will need is the condition to identify which records are going to be added to the archive table.

Then, you can perform a query similar to:

lroyce

The value of transaction_no would be the field I would use to select the records. Thanks!

lroyce

Before I actually did any coding, I set the query up like this in MS Access:

INSERT INTO Archive a SELECT b.Transaction_No, b.KEY_ID from Hardware b where b.Transaction_no = "10024"

And I am getting "Syntax Error in INSERT INTO Statment"

What did I do wrong?

00100b

You can't alias the the table defined by the INSERT INTO clause and you haven't included the field list that will be inserted into.

Try:
If you were to build the query using the QueryBuilder and then click on the "SQL" view of the query, then you would see how Access would build the query string.

lroyce

Thanks. I set it up the way you suggested and it works great!