If you are still having problems with this and need help, then you need to give us a lot more information. Such as: What database are you using (looks like Access?); What are the table structures; what do you want to delete etc etc. A really good starting point is to give some sample data and some expected results.
Also read the following:
Some guidelines for posting questions in the forums[
^]
Article Idea: How Not to Ask a Question[
^]
In the meantime: This will fix your attempt
SELECT * INTO EmpRetirement
FROM EMP
WHERE EMP.JopEnd<=Now()
ORDER BY Emp.ID, Emp.Agenow;
You will need a 2nd query for the Retirement table..
SELECT * INTO Retirement FROM EMP
WHERE EMP.JopEnd<=Now()
ORDER BY Emp.ID, Emp.Agenow;
Now some things to consider:
1. It is bad practice to have multiple tables with the same schema. I'm not going to go into a long discourse here - research
"Database Design techniques"[
^]
2. If those tables already exist and you are not using Access the queries will fail - you will need to use
INSERT INTO EmpRetirement
SELECT * FROM EMP …
If you are using Access you will be prompted and told that the table will be overwritten - i.e. all existing data will be lost.
3. It is also bad practice to use
SELECT * - list the columns that you want explicitly. What if someone adds a column to EMP but not to EmpRetirement - your code will immediately fail. It is also why @DerekTP123 has mentioned auto-number/auto-increment columns - if both EMP and EmpRetirement have an autonumber column then your query will fail.
The final part is that you apparently want to delete the stuff once copied. That would be
DELETE *
FROM EMP
WHERE EMP.JopEnd<=Now();
I advise you to check that the insert was successful before deleting any data though
Submit an article or tip