Append Query

[gowans07]

I've created an append query to move records from one table to another. Am i right in thinking it should move the records then delete them from the previous table? If so then for some reason its not working for me. Using MS Access 2003.

INSERT INTO Tbl_Job_Temp
SELECT Tbl_Job_List.*
FROM Tbl_Job_List
WHERE (((Tbl_Job_List.[Date of Visit])<=Now()-120));

Above is the Sql Code for the current query. Also is it possible to append to two different tables? If is it possible to run two queries at the same time on event click of a command button? INSERT INTO Tbl_Assign_Temp
SELECT Tbl_Assign_Engineer.*
FROM Tbl_Job_List INNER JOIN Tbl_Assign_Engineer ON Tbl_Job_List.[Job ID] = Tbl_Assign_Engineer.[Job ID]
WHERE (((Tbl_Job_List.[Date of Visit])<=Now()-120));

Thanks in advance TG

[adam_k]

On your first question, no the append query is not supposed to "move" data or insert to the new table and then delete from the old one. Append query inserts data to the new table.
If you want to delete data from the old table, then you need a delete query. When using a delete query make sure that your criteria are correct.

For your second question: Yes, it is possible to run more than 1 queries on button click. You have to create a macro and use OpenQuery action (with the appropriate parameters) as many times as the queries you want to run. Then assign that macro to run on button click.
Hint: You might want to use the SetWarnings action to disable warnings before your queries and re-enable them once you are done. This won't prompt the user about the number of records affected and if they want to continue or not.

[gowans07]

On your first question, no the append query is not supposed to "move" data or insert to the new table and then delete from the old one. Append query inserts data to the new table. If you want to delete data from the old table, then you need a delete query. When using a delete query make sure that your criteria are correct.

For your second question: Yes, it is possible to run more than 1 queries on button click. You have to create a macro and use OpenQuery action (with the appropriate parameters) as many times as the queries you want to run. Then assign that macro to run on button click. Hint: You might want to use the SetWarnings action to disable warnings before your queries and re-enable them once you are done. This won't prompt the user about the number of records affected and if they want to continue or not.

Cheers for the reply adam_k, got them figured out now, just annoying that to transfer the records i have had to create 4 queries in total. Such is life.

Okay that clears that up to, I'll take your advice on "SetWarnings" such an annoying feature. On the delete query i have stumbled across, "Could not delete from specified tables" why would this be? I'm trying to delete from two archive tables under criteria set in one of those tables. Is this right or should i have two separate delete queries again?

Thanks :)

[adam_k]

You have to create 2 delete queries. You can only delete from 1 table, the rest of them can only be used for criteria.

If these 2 archive tables are joined, you can enforce integrity (have Access delete records). An example of this is when you delete a header record, access can delete delete all detail records associated with that header record.