move records based date in field

[sal21]

I have two table:
tabl1 and table_history
I need a sql to move in "one shot only" all records from table1 to append into table _history but only the oldest records from table1 based the 3 oldest date.
Example:

field date of table1 contain
11/10/2010
04/10/2010
15/12/2008
14/11/2007
09/01/1989
07/11/2010
11/11/2001
....
in this case move into table_history all records from table1
09/01/1989
15/12/2008
14/11/2007
11/11/2001

i effect i want to maintain into table1 always the records with the 3 newest date.

Note:
- i use vb and ject conn
- all two tabel have the same strucutre fileds name and formatt field

[HansV]

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))

[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))

No words!

[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))

OPS!

But lost important info...
I have table1 in c:\mydir\mdb1.mdb
and
table_history in c:\mydr1\mdb2.mdb

sorry for that:-)

[HansV]

Create a link to table_history in mdb1.mdb. You can then run the code in mdb1.mdb.

[sal21]

Create a link to table_history in mdb1.mdb. You can then run the code in mdb1.mdb.

Clear me...
But if have only 2 or 1 or 3 date in DateField the query not return action? or not?

[HansV]

If you have three or fewer dates, the append query and delete query won't do anything.

[sal21]

If you have three or fewer dates, the append query and delete query won't do anything.

tks!

[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))

TIPS...
i need to exclude from this query always all records with date 30/04/2009, possible?
In ewffet maintain always all records with date 30/04/2009 into table1

[HansV]

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/2009#

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/2009#

(Dates in SQL strings must always be specified in US date format mm/dd/yyyy)

[sal21]

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/2009#

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/2009#

(Dates in SQL strings must always be specified in US date format mm/dd/yyyy)

.. but based the suggestion about the format US date independent for all version of Access, Italian or English?

[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))

but the first or second option to insert and delete use the same time for execution?

[HansV]

I think the second option will be faster than the first one for both the append and delete queries, but I haven't tested it on a large number of records.

[sal21]

I think the second option will be faster than the first one for both the append and delete queries, but I haven't tested it on a large number of records.

... but for the insert and for the deleting rcords i can use the second?


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

and

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

????

[HansV]

Yes.

[sal21]

Yes.

ok. tsk fo rpatience:-)

[sal21]

Yes.

OK. Can you adjust based my last request (about 30/04/2009) the two query,...

Code: Select all

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

and

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

Kiss!

[HansV]

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

[sal21]

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

Wath you think about tath?

Code: Select all

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/2009#)

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/2009#)

[HansV]

I don't think that will work.