Problem with keys in update query.

[alfranco17]

Hi.

I have two tables, t_transactions and temp_t_transactions. I created temp_t_transactions by copying the structure of t_transactions. Primary key is field "id_trans" and is an autonumber. Both are permanent tables.

I want the user to work on the temp_t_transactions table, and when ready, copy that info to t_transactions, which is the permanent database. When I try to run the following query, I keep getting an error message that says there it "didn't append 1 record to the table due to key violations". I am not including the key field for either table in the SQL code. This is the code. It works fine the first time I run it, when t_transactions is empty, but fails when it has even a single record.

INSERT INTO t_transactions ( id_trans_type, id_product, f_quantity, id_locación, effdate, actdate, id_user, id_po, id_so, id_inv, f_comments )
SELECT 'TRANSFER' AS f_trans_type, temp_t_transactions.id_product, temp_t_transactions.f_quantity, temp_t_transactions.id_locación, temp_t_transactions.effdate, Now() AS actdate, 'AFRANCO' AS id_user, temp_t_transactions.id_po, temp_t_transactions.id_so, temp_t_transactions.id_inv, temp_t_transactions.f_comments


FROM temp_t_transactions;

Any help will be greatly appreciated.

Thanks.
Armando.

[June7]

I tested your query on tables I built following your structure. Can't replicate issue. Want to provide db for analysis? Follow instructions at bottom of my post.

Why this 'temp' table setup?

[alfranco17]

Thanks a lot, June. When following your instructions to share the database, I ran "Compact and repair", and now it works! I guess that's what was missing.

I am using the temp table because I want the user to add records in one place, and then, when they are correct, transfer them to the good table.

How often should I run "compact and repair"?

Thanks again! I had spent three hours trying to figure it out.

[June7]

Run C&R definitely after design modifications as they cause increased file size or whenever you encounter odd behavior like you experienced. You can set the db to run C&R everytime it closes.

[alfranco17]

Run C&R definitely after design modifications as they cause increased file size or whenever you encounter odd behavior like you experienced. You can set the db to run C&R everytime it closes.

Thanks! I will set it up like that.