move records based date in field

[sal21]

I don't think that will work.

????
Help?

[HansV]

Easy! See Post=33962 higher up in this thread... (again)

[sal21]

You cannot do this in "one shot". It will take two steps:
1) Append the records that satisfy the condition to table_history. The SQL is:

INSERT INTO table_history SELECT * FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)

or alternatively

INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))

2) Delete the records that satisfy the condition from table1. The SQL is:

DELETE [DateField] FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)

or

DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))

hummmmmmmmmmmmmm....

If i have only 3 dates and 30/04/2010 the query move all into table_history?

If yes is possible to controll this situation?

In efect i need to have always 3 newst date and 30/04/2010 in each conitions, sure!

[HansV]

In the previous posts in this thread you mentioned 30/04/2009, now 30/04/2010. Which is it?

And what exactly do you want? Dates before 30/04/... or dates after 30/04/... or equal to 30/04/... or ...? I'm getting very confused.

[sal21]

In the previous posts in this thread you mentioned 30/04/2009, now 30/04/2010. Which is it?

And what exactly do you want? Dates before 30/04/... or dates after 30/04/... or equal to 30/04/... or ...? I'm getting very confused.

SORRY ME!
correct is this date 30/04/2010
and
Dates before 30/04/2010

[HansV]

Does this do what you want?

INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)) AND [DateField]<#04/30/2010#

and

DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)) AND [DateField]<#04/30/2010#

[sal21]

Does this do what you want?

INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)) AND [DateField]<#04/30/2010#

and

DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)) AND [DateField]<#04/30/2010#

sorry but no...

example date_filed:

15/01/2001
...
18/01/2005
...
10/01/2011
...
23/01/2010
...
22/01/2010
...
16/09/2010
...
15/01/2002
...
30/04/2010

insert into Table_histiry table:

15/01/2001
15/01/2002
18/01/2005
22/01/2010

remain into table1:
30/04/2010
16/09/2010
23/01/2010
10/01/2011

in effect insert into history table all from table1 but exclude from the insert the 3 newest dates and the famous 30/04/2010...

[HansV]

This then?

INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 WHERE [DateField]<>#04/30/2010# ORDER BY [DateField] DESC))

and

DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 WHERE [DateField]<>#04/30/2010# ORDER BY [DateField] DESC))