Using an APPEND query to move records from one table to another...

[FostermanUK]

I have an order system whereby there is a "basket" table and an order detail table.

I want to use an append query to move all the records from the basket table to the order detail table.

However, I also need to mark each record that gets appended to the order detail table with an "Order Id" that has already been saved in an Order table.

I.e. I save the main details of an order into the order table then copy records from the basket table into the order detail table along with an order ID that comes from a form.

The order ID is an autonumber.


Thank you all so much in advance =]

 

[Sum Guy]

You should be able to do this given the following.
Where is the OrderID assigned using AutoID. If it's in the Order table you should be OK. If an OrderID is being assigned in the Basket table, you're going to have a problem as the autonumbers in Basket and in Order will be fighting for domination.
I would NOT assign an OrderID in Basket and let the autonumber assign it in Order when you append.

 

[FostermanUK]

The Order ID is already assigned in the Order table. I just want to "paste" some records from the basket table along with the ID from the Order table.

 

[namliam]

Unless you have something like a " Basket ID" you will have to create some VB code for it.

If you do have have a "BasketID" field you can do an update query back into the basket table.
Then again, if you use a BasketID you can just store the basketIDs in the order table...
Do you need a seperate order table at all even? Possibly not...