copy and append (Access 2000)

[Markus]

I want to copy all the rows from the table products having fields branch4 and items4 Not Null, into the table order details.The problem is that the table order details does not have fields branch4 and items4, instead it has fields cartons and quantity.so i have to insert branch4 into cartons and items4 into quantity.Can somebody help me ? My sql for the table products is :
SELECT products.Productid, products.branch4, products.items4 FROM products;
And for the table order details:
SELECT [order details].OrderID, [order details].cartons, [order details].Quantity FROM [order details];
I wanted to apply the following function to append:
Public Function AppendAudit()
Dim strAppendProduct As String
strAppendProduct = "INSERT INTO order details " & _
"SELECT * " & _
" FROM Products1 As o1 WHERE NOT EXISTS (SELECT * FROM Products WHERE " & _
"ProductID=o1.ProductId)"
CurrentDb.Execute strAppendProduct
End Function
However this function does not take into account the different fields to append.

[johnhutchison]

The way to do things like this is to build the query you want using the normal query grid, then switch to SQL and copy the SQL into your VBS code.

Even if all details cannot be done there, you can often solve the part of the sql that troubles you. The query grid allows you to nominate which field goes where in the append.

Or just save the query and run it using: docmd.openquery strqueryname

[Markus]

Thank you for your reply. I cannot do it since the tables are not related and buliding an sql query out of this is not possible.Is there a simple command in Access of copying the data from one field into the data of another table under another field ? Something of the kind :
INSERT INTO order details (cartons, quantity ) FROM Products( branch4,items4

What is wrong with my function ApendAudit?

Thank you in advance

[HansV]

I don;t understand what you want to accomplish. It makes no sense to append records from the Products table to the Order Details table. The tables have different structures and serve different purposes. If you would add new records to Order Details and only fill the Cartons and Quantity fields from the Branch4 and Items4 fields in Products, you'd get an error message because required fields such as OrderID would be missing.

[Markus]

Thank you for your reply. The reason for my enquiry was the following.Just now we are auditing our branches.We are deleting all the available quantities and we are sending a new order marking the event ,in order to start the calculations from that date.In order to do that, we have to write and send very long orders for each branch. I just wanted to save our work , open a new order, and in the order details copy the available stock from branch4 into the field cartons.If i can do this, the process will be automated.
I know that you and your people are very conscientious and always try to help eveybody, therefore i want to write that this time my task is not so critical,we might do without that,we will just write down the orders and the job will be finished.So if the question is impossible or too time consuming it is better to leave it as it is.

kind regards

[HansV]

I'm sorry, I simply don't understand.

[johnhutchison]

I don't understand either, but using my strategy of building an append query in the query grid, I got this query which display the syntax for appending when the field names don't match.

INSERT INTO Table2 ( name2 )
SELECT Table1.name
FROM Table1

This table takes field name form table1 and puts it into field name2 of table2