Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append only the missing rows (Access 2000)

    I have tried to build an append function but it is not working ang gives me "Syntax error,missing operator"
    Can somebody help me? I have two identical tables, products and products1.The second table, called
    products1 has sometimes more rows than in the table products. I want to append only rows that are not

    present in the table products.
    Can somebody help me make my function really working ?


    Public Function FncAppend()


    Dim StrAppendProduct As String
    StrAppendProduct = " SELECT products.productid, products1.productid" & _
    " FROM products LEFT JOIN products1 ON products.productid= products1.productid" & _
    " HERE productsproductid Is Null" & _
    " INSERT INTO Products (productid,grade) " & _
    "SELECT o1.productid, o1.grade " & _
    " FROM Products1 As o1 WHERE NOT EXISTS (SELECT * FROM Products WHERE " & _
    "ProductID=o1.ProductId) "

    CurrentDb.Execute StrAppendProduct


    End Function

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

    Re: Append only the missing rows (Access 2000)

    You're trying to put a selection query (SELECT products.productid ...) and an append query (INSERT INTO products ...) into one SQL string. You just need the append query here:

    StrAppendProduct = "INSERT INTO Products (productid,grade) " & _
    "SELECT o1.productid, o1.grade " & _
    " FROM Products1 As o1 WHERE NOT EXISTS (SELECT * FROM Products WHERE " & _
    "ProductID=o1.ProductId)"

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append only the missing rows (Access 2000)

    Thank you very much for yout reply.The function works,but appends only the first next row and not
    all the rows.You cannsee it by yourself.If you delete all the records in the table products,then the function will append only the first row,and not all the records as expected.
    I do not how to handle this.Can you please help further?

    PLease accept my best regards

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Append only the missing rows (Access 2000)

    I would build a query using the "Find Unmatched Query Wizard" to build the SELECT part of the INSERT query, then paste the SQL of the Unmatched Query into the INSERT query replacing the SELECT part of that query.

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Append only the missing rows (Access 2000)

    I tried it on the Northwind sample database.
    <UL><LI>Duplicated Products into Products1
    <LI>Deleted all records in Products (there were 77)
    <LI>Created a new query with the SQL (replacing Grade, which doesn't exist in the Northwind Products table, by ProductName)
    <LI>Executed the query - it appended 77 records.[/list]So the problem is not in the SQL itself.

  6. #6
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append only the missing rows (Access 2000)

    Dear Has,

    I want hereweith to epxress my deepest appreciation and gratitude. It was so important to me to solve this problem.So you are right
    the sql is all right.The reason for my troubles is that i must describe ALL the fields if one field is missed then i receive this error.
    Thank yoy once again for your help

    Please accept my best regards

  7. #7
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append only the missing rows (Access 2000)

    Dear Hans,

    Can you help me clarify why i receive the message" syntaxerror missing operator for 01.code"
    when i want to add the next field beside grade to be appended ? My next field is called code
    and i have done the following


    Public Function FncAppend()
    Dim strAppendProduct As String
    strAppendProduct = "INSERT INTO Products (productid,grade,code) " & _
    "SELECT o1.productid, o1.grade, 01.code " & _
    " FROM Products1 As o1 WHERE NOT EXISTS (SELECT * FROM Products WHERE " & _
    "ProductID=o1.ProductId)"
    CurrentDb.Execute strAppendProduct
    End Function

    What is wrong in my expression ? Also i have to write a lot of fields further,
    for examle the fields after the code are the following
    categoryid, size,pack,Bulemsprice,unitprice etc etc, a lot of items and i am afraid
    i may commit an error again.Perhaps put them all in brackets somewhere ?

    So once again if i have onky productid and grade then it is ok,but i can't add on the next field successfully and somewhere i am wrong

    Once again, thank you very much for the help you have given me

    Best regards

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

    Re: Append only the missing rows (Access 2000)

    I suspect you have a typing error here - the alias for products1 is o1 (with a letter o), but in 01.code, you have the digit 0 (zero) instead of the letter o.

    If you want to insert *all* fields, you don't need to specify them. Instead, you can use

    strAppendProduct = "INSERT INTO Products " & _
    "SELECT * " & _
    " FROM Products1 As o1 WHERE NOT EXISTS (SELECT * FROM Products WHERE " & _
    "ProductID=o1.ProductId)"

Posting Permissions

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