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

    Append tables (Access 2000)

    I have a function appending the table order1 to the existing and table orders1. After the appending i delete the table orders1. Next time i import a fresh table orders1, which i append in the same way.However running the same procedure again with the new table orders1, produces duplicate records in my static table orders.I do not know whether this problem can be solved, but before asking for help i must explain how i work. The number of the orders are unique numbers, they do not repeat themselves, but i cannot foretell which numbers i will receive with the table orders1. This is because we have allocated order numbers to different cities.For examples the city Marianne issues orders between
    10000 and 30000, city Auberge between 30000 and 40000 and so on.Therefore if i have received the table orders1 from Auberge and update it, and next time i receive the table orders1 from the city
    Marianne, the procedure is not able to recognize which are the new records in order to append only them.
    My question is, is there any way in Access to make the procedure recoginze which is the new order,and to append only those orders that do not exist in my static table orders.Or, in case this is not possible, to help me find some procedure to clean and eliminate the duplicate records.
    I will be extremely grateful to any help in this respect.
    Below is my function:
    Public Function append()
    Dim db As DAO.Database
    Set db = OpenDatabase("C:BEUniversity.mdb")
    db.Execute " INSERT INTO orders ( OrderID, CustomerID, OrderDate, paymentid, PaymentMethodID, bankid, invoicedate, AuftragNr )

    " & _
    " SELECT orders1.orderid, orders1.customerid, orders1.orderdate, orders1.paymentid, orders1.PaymentMethodID, orders1.bankid,

    orders1.invoicedate, orders1.AuftragNr" & _
    " FROM orders1;"
    db.Close
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append tables (Access 2000)

    If the order numbers are indeed unique, then indexing the field as No Duplicates in your existing table will keep any duplicates from being appended, regardless of the SQL you use. It isn't really very clear what the problem is though, so I may have misunderstood. If you're updating existing records for *changes* rather than new records, that presents a different problem.
    Charlotte

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

    Re: Append tables (Access 2000)

    First of all you say that you append from order1 to orders1 then delete orders1 ( I would hope you mean you delete order1), I presume you mean you append from orders1 to to orders then dleete orders1.

    Is the order number field OrderID the unique field?

    You can append from orders1 the non-duplicates into orders as follows:
    INSERT INTO orders ( OrderID, CustomerID, OrderDate, paymentid, PaymentMethodID, bankid, invoicedate, AuftragNr )
    SELECT orders1.orderid, orders1.customerid, orders1.orderdate, orders1.paymentid, orders1.PaymentMethodID, orders1.bankid, orders1.invoicedate, orders1.AuftragNr
    FROM Orders1 LEFT JOIN Orders ON Orders1.Orderid = Orders.Orderid
    WHERE Orders.Orderid is Null

    This will only insert records into orders from orders1 where the orders1 records are NOT in orders already.
    HTH
    Pat

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

    Re: Append tables (Access 2000)

    Thank you very much for your replies. In my tables orders the orderid is an unique field and i have solved the problem with the appending thanks to your advices.
    However i have another, a second table which i need to append ( to add only new records) and there i am at a loss, i do not know how to achieve it. The table is called order details and there the orderid is not an unique key. My wish is to append according to the orderd number.Unfortunately i do not use cascade deleting.
    How do you proceed in similar cases? Perhaps make a query with the two tables?


    Below is my original append query but without the possibility to append only new records:
    CurrentDb.Execute " INSERT INTO [order details] ( OrderID, ProductID, UnitPrice, Quantity, Discount, liters, extendedprice, pieces, cartons )" & _
    " SELECT [order details1].OrderID, [order details1].ProductID, [order details1].UnitPrice, [order details1].Quantity, [order details1].Discount, [order details1].liters, [order details1].extendedprice, [order details1].pieces, [order details1].cartons " & _
    " FROM [order details1];"

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

    Re: Append tables (Access 2000)

    Mark,
    What do you mean by "add only new records", does this mean records from 'Order Details' which don't appear in 'Order Details1'?

    If so an append query like I suggested above will work.

    I am not too sure if I have understood your problem.

    HTH
    Pat

Posting Permissions

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