Copy to table

[Vugar]

Hi everyone,

I take information from many tables by query and I try to copy data to linked (SP) table. I copied by Append Query. One problem that append query duplicate data. Do you know how set rule that query not copy duplicate, copy only new data.

thank you

 

[NauticalGent]

Vugar! Seems like I just talked to you!

Ok, at the risk of looking like a fool I will attempt to answer this and not suffer too harshly from the elders.

Update queries work for existing records only and you are correct about append: it does it with extreme prejudice.

So, if I understand your question correctly, how want to keep a linked SP list current by pulling data from several tables. The update version is simple, all you need is an update query. To add records that do not exist in a another table, you will have to use the "Not in" criteria for your append query.

One way to do this that I have used is to use the wizard to get the syntax and then modify it for your append query.

Give it a go and let us know how it works...

 

[Simon_MT]

You have link into the the Table you are appending with a LEFT JOIN and use a a field like ID from the Appended table like ID = Null. In other words only Append if the record does not exist.

There is a VBA method using recordset count and proceed with update it Recordcount = 0 or Recordcount = 0 RecordFound = False then proceed to update.

Simon

 

[ypma]

Providing you have a unique field which has the index set Yes (No Duplicates) only new unique records will be added

For this to work you would have to have the same unique field on both the query and the table you are appending to . Hope this is of use to you, there may well be other ways to achieve this as I don't profess to be and expert.

Regards Ypma

 

[Vugar]

My query it's sum of fields and there not unique ID.

 

[Simon_MT]

I realised that - but there must be an identifier otherwise you would know if there are duplicates.

Simon

 

[Vugar]

every when query run all data will copied and duplicate data

 

[Simon_MT]

You have modify your process so that the data is removed before the Append (danger of bloating) or testing within the Append Query to ignore records already in the destination table. Also use DISTINCTROW in the query to do just that.

Simon

 

[Vugar]

Could you please tell how I can ignore copy duplicate data in Append query

 

[jdraw]

Can you be specific on what makes the duplicate a duplicate?
You can put a unique composite index on the "duplicating fields" and Access will prevent the insertion/addition of records with duplicate indexes.

 

[Vugar]

I can't give unique to field because it's SP list