Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append headaches (Access 2000)

    Appending headeaches
    How can i append only new orders?

    I order to gather every day sales information from an office i have made 2 append queries. All works fine for the first time, but after that each time i append, i receive the sum doubled, and so on. In order to get some help i will have to make myslef more clear.I calcluate on 2 fields: sum and extended price. So for example if the first time i receive sales 30 tons, next time the query shows 60 tons which is not true. Obviously i will have to append only new orders, but how the query should know which orders are new and how can i forbid for entering information second time? The table Orders1 and Order details1 are being sent to me from another town and i will have to append it to the query orders and order details.
    The field OrderId in the tabble order has a unique value and is an a Autonumber, whule the field OrderId in the table order details is a Number. In this way the two tables are connected in one to many relationship. In order to show my query to the Forum, i have converted them in an SQL form

    1. query AppendOrders in SQL form :
    INSERT INTO orders ( OrderID, CustomerID, OrderDate, paymentid, invoicedate )
    SELECT orders1.orderid AS Expr1, orders1.customerid AS Expr2, orders1.orderdate AS Expr3, orders1.paymentid AS Expr4,

    orders1.invoicedate AS Expr7
    FROM orders1;

    2. query Append Order details

    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];

    My question is how can i append only the orders which have not been appended?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append headaches (Access 2000)

    If your id's are chronological, you could use a DMax function in the where clausule.
    INSERT INTO orders ( OrderID, CustomerID, OrderDate, paymentid, invoicedate )
    SELECT orders1.orderid AS Expr1, orders1.customerid AS Expr2, orders1.orderdate AS Expr3, orders1.paymentid AS Expr4,
    orders1.invoicedate AS Expr7
    FROM orders1 WHERE orders1.orderid >DMax("Orderid","Orders");


    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] WHERE [order details1].OrderID > DMax("Orderid","Order details");
    Francois

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

    Re: Append headaches (Access 2000)

    Why are you appending OrderID to table orders if that field is an autonumber? It is possible to append to an autonumber field, but if you are controlling what goes into it, then why have an autonumber?

    You can certainly control what gets appending using SQL, but if OrderID in table orders is indexed unique (Yes, No duplicates), then you shouldn't be able to append the same order twice. Are you sure you have OrderID keyed as a unique index in table orders?
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append headaches (Access 2000)

    Dear Charlotte, Dear Francoise,

    thank you so much to you both for your kind and very profesional and considerate answers.I am writing to both of you You must know that this problem is of a great importance to me and therefore solving it will solve also a lot of my problems.
    Please allow me to explain once again what i am doing. I am collecting the sales from different companies and each company sends the information under the name orders1 .I have a mirror table called orders and i want to append all the orders from the companies on this table,one by one.The order number does not repeat itslelf ,it is unique.But each office has a different range of allocated order numbers.What i want is to receive the orders1 from the different companies and append it to my orders table.I have set warning to False, since otherwise i cannot do it,as Charlotte says. Also,since the orders are sent not chronological i cannot use the DMax function as Frnacoise says.

    So the following append query runs fine, and gives me the right results:
    INSERT INTO orders ( OrderID )
    SELECT orders1.orderid AS Expr1
    FROM orders1;
    However next time when i receive the table orders1, the appending is twice. For example if the first time i have sales of 30 tons, nect time i have sales of 60 tons which is impossible. A lot of trouble for me.
    In a nutshell, i want to append from the table orders1, containing all the orders for a given company, only the orders that do not exist on my orders table. I think the find unmatched query will do the job but i cannot finish it. For example the following query i have built with the help of the wiard for find unmatched tables, finds exactly those orders that do not exist on my table oders and need to be appended:

    SELECT orders1.orderid
    FROM orders1 LEFT JOIN orders ON orders1.orderid = orders.orderid
    WHERE (((orders.orderid) Is Null));

    This time the query is not an append query but a special query with a different sign on the grid.My question is, how can i append now in a code the results of the above query? I have tried in vain.
    If i solve it my problem is also solved.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append headaches (Access 2000)

    Here some code that loop through orders1, check if the order exist and if not, insert the new order and order detail.
    Don't forget to set the reference to Microsoft DAO 3.6 Object Libray in Tools | References.
    <pre>Sub ImportOrders()
    Dim db As DAO.Database
    Dim rstOrders As DAO.Recordset
    Dim rstOrders1 As DAO.Recordset
    Dim strCriteria As String
    Dim strSQL As String
    Set db = CurrentDb
    Set rstOrders = db.OpenRecordset("Orders", dbOpenDynaset)
    Set rstOrders1 = db.OpenRecordset("Orders1", dbOpenDynaset)
    rstOrders1.MoveFirst
    Do While Not rstOrders1.EOF
    strCriteria = "ID = " & rstOrders1!OrderID
    rstOrders.FindFirst strCriteria
    If rstOrders.NoMatch Then
    strSQL = "INSERT INTO orders ( OrderID, CustomerID, OrderDate, paymentid, invoicedate ) " & _
    "SELECT orders1.orderid AS Expr1, orders1.customerid AS Expr2, " & _
    "orders1.orderdate AS Expr3, orders1.paymentid AS Expr4, " & _
    "orders1.invoicedate AS Expr7 FROM orders1 " & _
    "WHERE orders1.orderid = " & rstOrders1!OrderID
    DoCmd.RunSQL strSQL
    strSQL = "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] " & _
    "WHERE [order details1].OrderID = " & rstOrders1!OrderID
    DoCmd.RunSQL strSQL
    End If
    rstOrders1.MoveNext
    Loop
    Set rstOrders = Nothing
    Set rstOrders1 = Nothing
    Set db = Nothing
    End Sub</pre>

    IF your OrderID's are text change the two where lines to
    <pre>"WHERE orders1.orderid = '" & rstOrders1!OrderID & "'"
    "WHERE [order details1].OrderID = '" & rstOrders1!OrderID & "'"</pre>

    Francois

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Append headaches (Access 2000)

    Hi,
    If I understand correctly, I think what you want is something like the following:
    INSERT INTO orders ( orderID, CustomerID, OrderDate, paymentID, invoicedate )
    SELECT orders1.orderID, orders1.CustomerID, orders1.OrderDate, orders1.paymentID, orders1.invoicedate
    FROM orders1 LEFT JOIN orders ON orders1.orderID = orders.orderID
    WHERE (((orders.orderID) Is Null));
    this will only append records from orders1 into orders where there isn't already a record in orders with a particular orderID.
    Is that what you were looking for?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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