don't let append query add the same records again

[megatronixs]

Hi all,

I'm using an append query that needs to add the records from another database into a table. I can get it to get the data and add the records. There are other columns in the database that are not in the one the data is pulled from. If I would run the append query again, it would add the same records again at the end. How can I avoid this? I only need to add new records that are not yet in the database I want to pull the records to.

Greetings.

 

[pr2-eugin]

You could make use of the Find Non Matching records from the Query Wizard and then use the result of the result to be updated.

 

[plog]

You didn't provide table names so lets use ToTable as the table where the records are being appended to and FromTable as the table where they are being appended from.

1. In your APPEND query, bring in ToTable and LEFT JOIN it to FromTable (show all from FromTable and just matching records in ToTable).

2. Link on the appropriate fields to determine if a record already exists in ToTable.

3. Bring down the primary key from ToTable (or a field that isn't ever Null). Then in the criteria underneath that, put 'Is Null'.

That query will exclude all the existing matches between the two tables and only allow new records to be appended to ToTable.

 

[megatronixs]

Hi all,

Thanks for the tips
I have a small problem as the table "to" (called tbl_High in Worklist.mdb) has ID nr, but the table "from" (called tbl_All_Cases in Main.mdb) does not have an ID nr, but a BIN nr that is equal on both, but the BIN nr is not set to "no duplicates" and could appear a few times in the table :-( (no primary key) and there are fields that could be null.
So, not to many chances to get it like I would like to.

Greetings.

 

[megatronixs]

Hi all,

I solved this by using the today's date in the query to only get newer records that have only today's date. I will need to imput first the old ones that where anyway on a excel sheet. So once those are in the database, the new ones will not duplicate as it will only look on today's date.

Greetings.

 

[georgesobhyy]

You didn't provide table names so lets use ToTable as the table where the records are being appended to and FromTable as the table where they are being appended from.

1. In your APPEND query, bring in ToTable and LEFT JOIN it to FromTable (show all from FromTable and just matching records in ToTable).

2. Link on the appropriate fields to determine if a record already exists in ToTable.

3. Bring down the primary key from ToTable (or a field that isn't ever Null). Then in the criteria underneath that, put 'Is Null'.

That query will exclude all the existing matches between the two tables and only allow new records to be appended to ToTable.

Sorry, can you explain more your idea, as this issue is like a nightmare to me

 

[jdraw]

George,
You have commented on a 5 year old thread/discussion.
If you are having trouble with JOINs, then see this for details and examples.
Better to start a new thread than attach to an old one.
Also, see the Similar Threads at the bottom of the page for ideas.

 

[isladogs]

This article may also help: Synchronise Data