Problem with append query..it doesn't work (1 Viewer)

[Safari]

Hi All ,

I need your help

My problem is that the data is repeated every time the query runs

I want to run the append query without duplicate data and append only the new records, not the old and new ones.

Attached is the database for help and modification



Query name: Appendfrom

Table to be append : StoreTransaction

append from the table: Headstoretransfer + StoreTransactionTRANSFAER They have a one-to-many relationship

Appending transno field with the SerialTransfaerfrom . field

If there is no transno number, it is appended (NOT IN function)

And in the case of a transno number, it is not appended.

Thanks in advance ..

 

[The_Doc_Man]

Basically, what you are describing (i.e. don't insert if already there) looks like this:

INSERT INTO table (field1, field2, ..., fieldn)
SELECT src1, src2, ..., srcn ) FROM srctable AS S
WHERE S.src1 NOT IN ( SELECT T.field1 FROM table AS T );

 

[Safari]

Basically, what you are describing (i.e. don't insert if already there) looks like this:

INSERT INTO table (field1, field2, ..., fieldn)
SELECT src1, src2, ..., srcn ) FROM srctable AS S
WHERE S.src1 NOT IN ( SELECT T.field1 FROM table AS T );

thanks

could you please .. edit my database aapend query for more clear .. i will check the code for more understand to me .

 

[The_Doc_Man]

I have a personal policy regarding opening other persons' databases. I will not touch other people's code like that. No disrespect is intended, but after 28 1/2 years with the U.S. Navy I don't do that. I can give advice and explain things - but your code is yours. It's MY personal quirk and is not directed at you.

 

[Safari]

you are a great man

thanks

 

[Pat Hartman]

If you have a natural unique index, then you need to add that to your table. Then when you run the append query, Access will ignore all duplicates and just discard them. Even using the query suggested by doc is insufficient. Yes, it will keep that query from appending duplicates but without the unique index constraint on the table, you can still add duplicates via forms and the unique index constraint will prevent duplicates regardless of what process tries to append them.