Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copy and append (Access 2000)

    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.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: copy and append (Access 2000)

    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
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy and append (Access 2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy and append (Access 2000)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy and append (Access 2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy and append (Access 2000)

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

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: copy and append (Access 2000)

    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
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •