Access append query add new records only.. help wanted plz (1 Viewer)

[sibbbra]

hi,
I have a database attatched with from name 'Main' , and when I run append query named 'C-Add' from the form through clicking button 'Process' then,,, the query appends the previous order's enteries also. i.e they are copied teach time. how can i prevent this. plz help. urgent

 

[silentwolf]

Hallo,
what I do is use Indizies in the Table where you like to append the query.

Therefore there will no duplicate records be added.

HTH

 

[arnelgp]

1. i don't think you need C-Add query (adding record to table Orders).
2. Orders table is already open in your form. opening this query will definitely duplicate your record.
3. also Order_ID is Autonumber so you don't include it when inserting record.
4. you already saving the "order details" in table tblOrderProduct.

 

[sibbbra]

Hallo,
what I do is use Indizies in the Table where you like to append the query.

Therefore there will no duplicate records be added.

HTH

so kind of you. But how i should do it

 

[silentwolf]

Go to the Table you are appending into.

As shown in Picture click on Indizis and then add those fields in the table you need to have unique.
Make sure you add then Primarykey no and Unique to yes

 

[sibbbra]

1. i don't think you need C-Add query (adding record to table Orders).
2. Orders table is already open in your form. opening this query will definitely duplicate your record.
3. also Order_ID is Autonumber so you don't include it when inserting record.
4. you already saving the "order details" in table tblOrderProduct.

thanx for reply. actually, C-Add query appends record from subform frm customer sale and the customer in sub form are addedd each time in duplicate, when query is called. just run it and check. what should i do. plz help

 

[sibbbra]

Go to the Table you are appending into.

As shown in Picture click on Indizis and then add those fields in the table you need to have unique.
Make sure you add then Primarykey no and Unique to yes

i have tried but no records are added while doing so

 

[sibbbra]

1. i don't think you need C-Add query (adding record to table Orders).
2. Orders table is already open in your form. opening this query will definitely duplicate your record.
3. also Order_ID is Autonumber so you don't include it when inserting record.
4. you already saving the "order details" in table tblOrderProduct.

all the other things are according to my needs. but duplicate records are making my head to blow

 

[Pat Hartman]

If you create a unique index, you will not be able to add duplicate records. Did you add the suggested index? Is it defined as unique?

If the PK for the table is an autonumber, do NOT include it in the unique index.

 

[The_Doc_Man]

Putting a uniqueness constraint on the table would do it but would force you to have good error handling.

You could also do something like this...

INSERT INTO destination (list-of-fields) SELECT list-of-corresponding-fields FROM source WHERE source.uniqueID NOT IN (SELECT dest.uniqueID FROM destination AS dest);

That code says, in English, insert records into the destination from the source where the field that makes a record unique is not already in the destination table.

 

[sibbbra]

I dont have proper words to thank you

Pat Hartman and​

The_Doc_Man​

I have the query as follows:
INSERT INTO Orders ( Product_ID, Customer_ID, Order_Date, quantity, Unit_Price_Sale, Amount_Sale )
SELECT DISTINCTROW tblorderproduct.Product_ID, tblorderproduct.Customer_ID, tblorderproduct.Date, tblorderproduct.Quantity_Sold, Products.PUnit_Price_Sale, ([Quantity_Sold])*[PUnit_Price_Sale] AS Am_Sale
FROM Customers INNER JOIN (Products INNER JOIN tblorderproduct ON Products.Product_ID = tblorderproduct.Product_ID) ON Customers.Customer_ID = tblorderproduct.Customer_ID;
kindly guide me how would i should have above code
thanking u

 

[sibbbra]

Putting a uniqueness constraint on the table would do it but would force you to have good error handling.

You could also do something like this...

INSERT INTO destination (list-of-fields) SELECT list-of-corresponding-fields FROM source WHERE source.uniqueID NOT IN (SELECT dest.uniqueID FROM destination AS dest);

That code says, in English, insert records into the destination from the source where the field that makes a record unique is not already in the destination table.

so kind of u but i have to ask more

 

[sibbbra]

so kind of u but i have to ask more

If you create a unique index, you will not be able to add duplicate records. Did you add the suggested index? Is it defined as unique?

If the PK for the table is an autonumber, do NOT include it in the unique index.

If you create a unique index, you will not be able to add duplicate records. Did you add the suggested index? Is it defined as unique?

If the PK for the table is an autonumber, do NOT include it in the unique index.

so kind of u but i have to ask more

 

[sibbbra]

SOLVED !
I just made a delete query with this code and it worked !
DELETE Orders.*, (SELECT COUNT (*) FROM Orders AS L
WHERE (L.Order_Date & L.Customer_ID
= Orders.Order_Date & Orders.Customer_ID)
AND (L.Order_ID <= Orders.Order_ID)) AS Expr1
FROM Orders
WHERE ((((SELECT COUNT (*) FROM Orders AS L
WHERE (L.Order_Date & L.Customer_ID
= Orders.Order_Date & Orders.Customer_ID)
AND (L.Order_ID <= Orders.Order_ID)))>1));

Thanx to all of you