Append headaches (Access 2000)

Topic

New Reply

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?

Reply | Quote

Replies

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”);

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

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

IF your OrderID’s are text change the two where lines to

"WHERE orders1.orderid = '" & rstOrders1!OrderID & "'"
"WHERE [order details1].OrderID = '" & rstOrders1!OrderID & "'"

Reply | Quote

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?

Reply | Quote