Solved Append query overwrite existing rows in target table, instead of creating new rows. (1 Viewer)

tcneo

Member
Local time
Tomorrow, 07:53
Joined
Dec 7, 2021
Messages
68
Hi everyone,

For my database, I have a append query which takes all the rows from Temp_Table (except the primary key field) and appends it to the main table (Task_Detail_Table).

What I discover is that it does not add the rows as new records to the main table but overwrites existing rows instead.

What did I do wrong and how can I fix it? I want to add the rows from Temp_Table to the main table and I expect the primary key to be auto-generated for the newly added rows.

Attached is the database stripped of all the tables, forms, queries and reports which are not relevant to the issue (for sensitivity reasons).

The query in question is intQuery_to_append_from_Temp_to_Task_Detail_Table_for_Others_TR.

Thanks in advance.
 

Attachments

  • Append_rows.accdb
    644 KB · Views: 198

June7

AWF VIP
Local time
Today, 15:53
Joined
Mar 9, 2014
Messages
5,423
There is no db attached. At least post the SQL statement.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:53
Joined
Feb 19, 2002
Messages
42,981
If your query uses a right join between the two tables, it can update existing rows and add new rows. Perhaps that is what you did.
 

tcneo

Member
Local time
Tomorrow, 07:53
Joined
Dec 7, 2021
Messages
68
works for me.
strange, it doesn't work in my main database. either it overwrites an existing row. or MS Access will return an error that it is not able to append the row due to key violation.
 

June7

AWF VIP
Local time
Today, 15:53
Joined
Mar 9, 2014
Messages
5,423
I ran both of the INSERT SELECT queries. New records are created.
 

tcneo

Member
Local time
Tomorrow, 07:53
Joined
Dec 7, 2021
Messages
68
update. I "compacted and repair the database" and the query in the main database worked as it should!

i'm not sure why. but i'm thankful nonetheless.

thanks to all who helped by looking at this thread.
 

Users who are viewing this thread

Top Bottom